MySQL 左外连接与 where 子句 - 返回不匹配的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17910729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL left outer join with where clause - return unmatched rows
提问by Xeos
I have two tables: pqand pe. I am trying to LEFT OUTER JOINleft table (pq) on right table (pe).
我有两个表：pq和pe。我正在尝试将LEFT OUTER JOIN桌子 ( pq) 放在右桌子 ( pe) 上。
- pqhas primary key column id
- pehas two-column primary key, so it may have many pqid's or none
- pe.uid column has to be used to extract only relevant data (
WHERE pe.uid = "12345") - pe.data should be joined to every pq.id row
- pq有主键列id
- pe有两列主键，所以它可能有很多 pqid 或没有
- 必须使用pe.uid 列仅提取相关数据 (
WHERE pe.uid = "12345") - pe.data 应该加入到每个pq.id 行
Here is how tables look:
以下是表格的外观：
pq:
id | data
1 | "abc"
2 | "efg"
pe:
pqid | uid | data
2 | 54321 | "uvw"
2 | 12345 | "xyz"
I can use the following query to match first 2 rows of pq.id to pe.pqid
我可以使用以下查询将pq.id 的前 2 行匹配到pe.pqid
SELECT pq.id, pq.data, pe.data FROM pq
LEFT OUTER JOIN pe ON pq.id = pe.pqid
ORDER BY pq.id LIMIT 2
I get:
我得到：
pq.id | pq.data | pe.data
1 | "abc" |
2 | "efg" | "uvw"
But if I use the WHERE statement like this:
但是如果我像这样使用 WHERE 语句：
SELECT pq.id, pq.data, pe.data FROM pq
LEFT OUTER JOIN pe ON pq.id = pe.pqid
WHERE pe.uid='12345'
ORDER BY pq.id LIMIT 2
I only get one row with matching pe.pqid AND pe.uid:
我只得到一行匹配的pe.pqid AND pe.uid：
pq.id | pq.data | pe.data
2 | "efg" | "xyz"
So with the WHERE clause I get the right pe.data, but I don't get pqrows that have no pq.id matching pe.pqid
所以使用 WHERE 子句我得到了正确的pe.data，但我没有得到没有pq.id 匹配pe.pqid 的pq行
I need to get this:
我需要得到这个：
pq.id | pq.data | pe.data
1 | "abc" |
2 | "efg" | "xyz"
回答by Gordon Linoff
Yes. The whereclause is turning the left outer join into an inner join.
是的。该where子句将左外连接转换为内连接。
Why? The value of pe.pqidis NULL(as is pe.uid) when there is no match. So the comparison in the whereclause fails (almost all comparisons to NULLreturn NULLwhich is considered false).
为什么？的值pe.pqid就是NULL（如pe.uid）当不存在匹配。所以where子句中的比较失败（几乎所有NULL返回的比较NULL都被认为是错误的）。
The solution is to move the comparison to the onclause:
解决方案是将比较移动到on子句：
SELECT pq.id, pq.data, pe.data
FROM pq LEFT OUTER JOIN
pe
ON pq.id = pe.pqid and
pe.uid='12345'
ORDER BY pq.id LIMIT 2
