SQL LEFT JOIN confusion-CodePudding

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.

CodePudding user response：

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'