Conditional left Join MySQL without a subquery

Question

I have two tables: X and Y:

mysql> select * from X;
+------+------+
| id   | colX |
+------+------+
| 2001 |    0 |
| 2002 |    0 |
| 2003 |    0 |
| 2004 |    0 |
| 2005 |    0 |
| 2006 |   10 |
| 2007 |   10 |
+------+------+

mysql> SELECT * FROM Y;
+------+------+-------+
| id   | colY | score |
+------+------+-------+
| 2001 |   10 |     3 |
| 2004 |    0 |    12 |
| 2005 |    0 |    15 |
| 2007 |    0 |     1 |
+------+------+-------+

I need the following result, but without a subquery:

mysql> SELECT * FROM X LEFT JOIN Y ON X.id = Y.id WHERE colX=0 AND X.id NOT IN (SELECT id FROM Y WHERE colY > 0);
+------+------+------+------+-------+
| id   | colX | id   | colY | score |
+------+------+------+------+-------+
| 2002 |    0 | NULL | NULL |  NULL |
| 2003 |    0 | NULL | NULL |  NULL |
| 2004 |    0 | 2004 |    0 |    12 |
| 2005 |    0 | 2005 |    0 |    15 |
+------+------+------+------+-------+

• colX should be 0
• if id is present in table Y and colY = 0, then indicate the corresponding result
• If id is not in table Y, give score = NULL

I tried the following request, but id 2001:

mysql> SELECT * FROM X LEFT JOIN Y ON X.id = Y.id AND Y.colY = 0 WHERE colX=0;
+------+------+------+------+-------+
| id   | colX | id   | colY | score |
+------+------+------+------+-------+
| 2001 |    0 | NULL | NULL |  NULL |
| 2002 |    0 | NULL | NULL |  NULL |
| 2003 |    0 | NULL | NULL |  NULL |
| 2004 |    0 | 2004 |    0 |    12 |
| 2005 |    0 | 2005 |    0 |    15 |
+------+------+------+------+-------+

Answer 1

I think this is the logic you want:

SELECT *
FROM X LEFT JOIN
     Y
     ON X.id = Y.id 
WHERE x.colX = 0 AND (Y.colY = 0 OR Y.colY IS NULL)

Demo