SQL: Joining table A and table B when rows may be missing in TABLE B

Question

I'm trying to join data from two table A, and B on a timestamp. I ideally want every row of tableA that meet the unitCode and time constraints, and if there is an associated row in tableB then join it, otherwise assume the coloumns from table B are attached but are null or empty.

Assume TableA looks something like this:

id | timestamp | itemA1 | itemA2 | itemA3

and TableB looks somthing like this:

id | timestamp | itemB1 | itemB2

so data might look like this:

timestamp | itemA1 | itemA2 | itemA3 | itemB1 | itemB2
[datetime]| 10 | 11 | 40 | 26 | 12
[datetime]| 10 | 11 | 40 | 26 | 12
[datetime]| 10 | 11 | 40 | null | null
[datetime]| 10 | 11 | 40 | 26 | 12
[datetime]| 10 | 11 | 40 | null | null

Currently I'm using this:

SELECT * FROM tableA AS a 
JOIN (SELECT * FROM tableB) AS b
ON (a.timestamp = b.timestamp)
WHERE b.unitCode = 'CODE' AND b.timestamp BETWEEN 'STARTDATETIME' AND 'ENDDATETIME' ORDER BY b.timestamp DESC`

but it only makes rows out of the instances where there is a corresponding tableB for a given tableA.

kchason kchason · Accepted Answer · 2017-10-13T17:46:39

Use a LEFT JOIN. A LEFT JOIN takes all records on the left side of the condition statement, and all those for which it applies on the right. However, you need to be careful in your WHERE clause, because if it has a value restriction on your right table (in your case table 'b') then it will still exclude those rows.

SELECT * FROM tableA AS a 
LEFT JOIN (SELECT * FROM tableB) AS b
ON (a.timestamp = b.timestamp)
WHERE b.unitCode = 'CODE' AND b.timestamp BETWEEN 'STARTDATETIME' AND 'ENDDATETIME' ORDER BY b.timestamp DESC`

LEFT JOIN reference: https://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html

JOIN graphic

As @Ike pointed out, your query could be corrected to be:

SELECT * 
FROM tableA AS a 
LEFT JOIN tableB AS b
    ON a.timestamp = b.timestamp
    AND b.unitCode = 'CODE' 
    AND b.timestamp BETWEEN 'STARTDATETIME' AND 'ENDDATETIME'
ORDER BY b.timestamp DESC`

Since as I mentioned earlier, if the conditions were in the WHERE clause, it would exclude the concept of the LEFT JOIN and therefore all rows with NULL values (because it didn't meet the criteria for table b) would be excluded.

To address your comment, if you added WHERE clauses, I would suggest (based on what I understand of your goal) only having the conditions affect tableA and have all the conditions for tableB be in the LEFT JOIN.

SELECT * 
FROM tableA AS a 
LEFT JOIN tableB AS b
    ON a.timestamp = b.timestamp
    AND b.unitCode = 'CODE' 
    AND b.timestamp BETWEEN 'STARTDATETIME' AND 'ENDDATETIME'
WHERE
    a.timestamp BETWEEN 'STARTDATETIME' AND 'ENDDATETIME'
ORDER BY b.timestamp DESC`

Now, if the a.timestamp condition is what you're looking for, then the condition for tableB in the LEFT JOIN is redundant because we already know that the timestamps in tableB must match a corresponding one in tableA.

SQL: Joining table A and table B when rows may be missing in TABLE B

1 Answers