MySQL left outer join with where clause - return unmatched rows
34,673
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
Comments
-
Xeos over 3 years
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"