MySQL left outer join with where clause - return unmatched rows

Question

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"

Answer 1

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