'LEFT OUTER JOIN and WHERE EXISTS. Are they equivalent?
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
