mysql外连接后where失效,MySQL使用where子句保留外连接 - 返回不匹配的行

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

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

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

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:

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:

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 pq rows that have no pq.id matching pe.pqid

I need to get this:

pq.id | pq.data | pe.data

1 | "abc" |

2 | "efg" | "xyz"

解决方案

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

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).

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