LEFT OUTER JOIN and WHERE EXISTS. Are they equivalent?-CodePudding

I would like to create equivalent MySQL query using LEFT OUTER JOIN to WHERE EXISTS. I am following this question: Are the SQL concepts LEFT OUTER JOIN and WHERE NOT EXISTS basically the same?

This is the original query:

SELECT *

FROM tableA
JOIN tableB ON tableA.tableA_id = tableB.tableB_id
JOIN tableC ON tableC.tableC_id = tableB.tableB_id


WHERE  NOT EXISTS (SELECT 1
                   FROM   tableD
                   WHERE  tableA.employee_id = tableD.employee_id AND tableC.tableC_datum = DATE(tableD.tableD_od_datetime) )

But this query return different values:

SELECT *
FROM tableA
JOIN tableB ON tableA.tableA_id = tableB.tableB_id
JOIN tableC ON tableC.tableC_id = tableB.tableB_id

LEFT OUTER JOIN tableD ON tableA.employee_id = tableD.employee_id AND tableC.tableC_datum = DATE(tableD.tableD_od_datetime)
                    
WHERE tableD.employee_id IS NULL AND DATE(tableD.tableD_od_datetime) IS NULL

Why are these two outputs not equivalent, please?

CodePudding user response：

The not exists and left join ... rgt.col is null approaches are identical. The left join however will contain columns from the unwanted table so just be specific with the select clause:

SELECT table_a.*, table_b.*, table_c.*
FROM table_a
JOIN table_b ...
JOIN table_c ...
LEFT JOIN table_d ...

I would rather avoid * at all and explicitly list exactly those columns that I need.