I have a pair of tables i need to join.
This query return 1164 records
SELECT name FROM tableA
WHERE reportDay = '2022-Apr-05'
And this one return 3339 records
SELECT name FROM tableB
WHERE reportDay = '2022-Apr-05'
Doing a LEFT JOIN should (according to various internet tutorials) return all the records in tableA, plus all the records in tableB, with a NULL where there’s no matching row in tableB
However, this returns 1134 records. So 30 records from tableA are no longer being returned
SELECT tableA.name, tableB.name
FROM tableA
LEFT JOIN tableB ON tableA.name = tableB.name
WHERE tableA.reportDay = '2022-Apr-05'
AND tableB.reportDay = '2022-Apr-05'
Where am I going wrong? – I’ve checked that in both tables the ‘name’ field is unique by day (i.e. only one record per day)
To quote from the POSTGRES documentation
LEFT OUTER JOIN returns all rows in the qualified Cartesian product
(i.e., all combined rows that pass its join condition), plus one copy
of each row in the left-hand table for which there was no right-hand
row that passed the join condition.
I’ll admit to being unsure about the meaning of "the qualified Cartesian product" but the 2nd half of the sentence seems to suggest I should get all the rows from tableA and NULLS where no match is found in tableB
UPDATE – bloody hell that was quick! Thanks guys, I should have asked earlier, would have saved an hour of my life.
>Solution :
As @JNevill pointed out, the criteria has to be included inside the ON clause.
SELECT
tableA.name, tableB.name
FROM tableA
LEFT JOIN tableB
ON (tableA.name = tableB.name and tableB.reportDay = '2022-Apr-05')
WHERE tableA.reportDay = '2022-Apr-05'