Search code examples
mysqljoincorrelated-subquery

Why LEFT OUTER JOIN reduces rows from left table?

Just want to show all data in table A and LEFT JOIN table B with coorelate subquery but fail to get all data in table A

SELECT a.id FROM tableA a 
LEFT JOIN tableB b1 ON a.id = b1.id
WHERE date = (SELECT MAX(date) FROM tableB b2 WHERE b1.id= b2.id)

tableA

id
======
1001
1002
1003
1004

tableB

id   date
=============
1001 20160101
1001 20160102
1003 20160102
1003 20160105

Expected Result

id   date
===============
1001 20160102
1002 NULL
1003 20160105
1004 NULL

Engine Return

id   date
=============
1001 20160102
1003 20160105
Solution

  • What I would do is a left join on a subselect, that only contains the max date per id like this:

    SELECT a.id, b.maxdate FROM tableA a 
LEFT JOIN (SELECT id, MAX(date) AS 'maxdate' FROM tableB2 GROUP BY id) b ON a.id = b.id

    This should also be quicker, since the select for the join will only be executed once while the select in a where clause will be executed for each row.