MySQL Join with WHERE and null values

Question

I'm currently struggling with a (fairly easy I think) SQL problem but I cant seem to figure it out.

Suppose I have the following tables:

Persons
+-------+----+
| name  | id | 
+-------+----+
| Steve | 1  | 
| John  | 2  | 
+-------+----+

Information
+----------+----+-----------+---------------------------------------------------+
| type     | id | linked_id | info                                              |
+----------+----+-----------+---------------------------------------------------+
| persons  | 1  | 1         | Info about Steve                                  |
| cars     | 2  | 1         | Info about a car, aka not stored in Persons table |
+----------+----+-----------+---------------------------------------------------+

If I would want the Persons table and a subset of Information (type=persons), my query would be something like:

SELECT * 
FROM Persons
LEFT JOIN Information ON Persons.id = Information.linked_id
WHERE (Information.type = "persons" OR Information.type IS NULL)

And this should be what I expect:

Desired Result
+-------+----+----------+------+------------+------------------+
| name  | id | type     | id   | linked_id  | info             |
+-------+----+----------+------+------------+------------------+
| Steve | 1  | persons  | 1    | 1          | Info about Steve |
| John  | 2  | NULL     | NULL | NULL       | NULL             |
+-------+----+----------+------+------------+------------------+

But this is the actual result:

+-------+----+----------+----+-----------+------------------+
| name  | id | type     | id | linked_id | info             |
+-------+----+----------+----+-----------+------------------+
| Steve | 1  | persons  | 1  | 1         | Info about Steve |
+-------+----+----------+----+-----------+------------------+

The "John" Person row, who doesn't yet have an Information row, should also be included in the result but it isn't.

What am I doing wrong? Shouldn't the OR Information.type IS NULL part of my query take care of this? The row isn't included though. Am I missing something else?

Answer 1

You need to put the condition in the ON clause as it performs before the joining of table.

SELECT * 
FROM   Persons
       LEFT JOIN Information 
          ON Persons.id = Information.linked_id AND 
             Information.type = 'persons'

• SQLFiddle Demo

OUTPUT

╔═══════╦════╦═════════╦═══════════╦══════════════════╗
║ NAME  ║ ID ║  TYPE   ║ LINKED_ID ║       INFO       ║
╠═══════╬════╬═════════╬═══════════╬══════════════════╣
║ Steve ║  1 ║ persons ║ 1         ║ Info about Steve ║
║ John  ║  2 ║ (null)  ║ (null)    ║ (null)           ║
╚═══════╩════╩═════════╩═══════════╩══════════════════╝