MySQL 使用 WHERE 和空值进行连接

Question

我目前正在努力解决一个(我认为相当简单的)SQL 问题，但我似乎无法弄清楚。

假设我有下表:

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 |
+----------+----+-----------+---------------------------------------------------+

如果我想要 Persons 表和信息子集 (type=persons)，我的查询将类似于:

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

这应该是我所期望的:

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

但这就是实际结果:

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

“John”人员行还没有信息行，也应该包含在结果中，但事实并非如此。

我做错了什么？我的查询的 OR Information.type IS NULL 部分不应该处理这个问题吗？但该行不包括在内。我还漏掉了什么吗？

Answer 1

您需要将条件放在 ON 子句中，因为它在连接表之前执行。

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

• SQLFiddle Demo

输出

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