MySQL使用where子句离开外部连接-返回不匹配的行

本文介绍了MySQL使用where子句离开外部连接-返回不匹配的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个表: pq 和 pe .我正在尝试在右表( pe )上LEFT OUTER JOIN左表( pq ).

I have two tables: pq and pe. I am trying to LEFT OUTER JOIN left table (pq) on right table (pe).

pq 具有主键列 id
pe 具有两列主键，因此它可能有很多pqid或没有
pe .uid列只能用于提取相关数据(WHERE pe.uid = "12345")
pe .data应该连接到每个 pq .id行

pq has primary key column id
pe has 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 | data
1  | "abc"
2  | "efg"

pe:
pqid | uid   | data
2    | 54321 | "uvw"
2    | 12345 | "xyz"

我可以使用以下查询将 pq .id的前两行与 pe .pqid

I can use the following query to match first 2 rows of pq.id to 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

我得到:

pq.id | pq.data |  pe.data
1     | "abc"   |  
2     | "efg"   |  "uvw"

但是如果我这样使用WHERE语句:

But if I use the WHERE statement like this:

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

我只有一行具有匹配的 pe .pqid和 pe .uid:

I only get one row with matching pe.pqid AND pe.uid:

pq.id | pq.data |  pe.data
2     | "efg"   |  "xyz"

因此，通过WHERE子句，我得到了正确的 pe .data，但没有得到没有 pq 的 pq 行.匹配 pe .pqid

So with the WHERE clause I get the right pe.data, but I don't get pq rows that have no pq.id matching pe.pqid

我需要得到这个:

pq.id | pq.data |  pe.data
1     | "abc"   |  
2     | "efg"   |  "xyz"

推荐答案

是. where子句将左侧的外部联接变成内部联接.

Yes. The where clause is turning the left outer join into an inner join.

为什么?当没有匹配项时，pe.pqid的值为NULL(pe.uid也是如此).因此，where子句中的比较失败(几乎所有与NULL的比较都返回NULL，这被认为是错误的).

Why? The value of pe.pqid is NULL (as is pe.uid) when there is no match. So the comparison in the where clause fails (almost all comparisons to NULL return NULL which is considered false).

解决方案是将比较结果移至on子句:

The solution is to move the comparison to the on clause:

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

这篇关于MySQL使用where子句离开外部连接-返回不匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

PE