SELECT * FROM employees e WHERE NOT EXISTS ( SELECT name FROM eotm_dyn d WHERE d.employeeID = e.id )
SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL
That is more effective,some analysis?
Presuming the column values involved cannot be NULL -
LEFT JOIN/IS NULL is more effective than
NOT EXISTS - read this article for details.
NOT EXISTS is more effective than
LEFT JOIN/IS NULL - read this article for details.
Like Oracle, they are equivalent.
For those who have challenge with the detail the column values cannot be null, yet make use of the LEFT JOIN / IS NULL - remember exactly what a LEFT JOIN signifies. This link may help.
I truly think you need to profile for this type of question. It doesn't only rely on the precise database product, but theoretically it might also rely on the skew of the data.
However! Automatically I'd say write the code that many clearly expresses your intent. You're after
employee records with no matching
eotm_dyn, so IMO the best code is
WHERE NOT EXISTS. It most likely will not matter, however i would use
SELECT 1 (not
SELECT name), because the
name matters not within the "with no matching
After you have code that expresses that which you intend and works, take a look at optimising according to profiling.