LEFT JOIN removes rows that are not present on the right table-CodePudding

I'm trying to merge/join two tables.

TABLE1 (left) is the main table I am trying to join on, it contains:

strings that are in TABLE2;
strings that are not in TABLE2;
null values (normal in my case and would like them to be kept)

I use the following command:

SELECT * 
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE1.UNIQUE_ID = TABLE2.UNIQUE_ID_OF_CUSTOMERS

The issue is that it drops all the rows that are not in TABLE2 and does a form of a weird join that I cannot understand how to fix

CodePudding user response：

The first one will only return rows where AGE > 40, so a NULL will not be true. The second one moves the filter to the ON clause, so you will get rows where TABLE2 row is null.

SELECT * 
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE1.UNIQUE_ID = TABLE2.UNIQUE_ID_OF_CUSTOMERS
WHERE TABLE2.AGE > 40


SELECT * 
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE1.UNIQUE_ID = TABLE2.UNIQUE_ID_OF_CUSTOMERS
                AND TABLE2.AGE > 40