MySQL left an outer join with a where clause - returns inconsistent rows

Question

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

• pq has primary key column id
• pe has a two-column primary key, so it can have many pqid or none
• pe . A conditional column should be used to retrieve only relevant data ( WHERE pe.uid = "12345" )
• pe .data should be attached to each pq .id line

Here's what the tables look like:

 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 the first two lines of pq .id with 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 as follows:

 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 get only one line matching pe .pqid AND pe .uid:

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

So, with the WHERE clause, I get the correct pe .data, but I don't get the pq strings that don't have pq . id pe .pqid

I need to do this:

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

Answer 1

Yes. The where clause turns the left outer join into the inner join.

Why? pe.pqid is NULL (as pe.uid ) when there is no match. Thus, the comparison in the where clause is not performed (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