FROM    employees e
        SELECT  name 
        FROM    eotm_dyn d
        WHERE   d.employeeID =


SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE 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.


They are equivalent.

SQL Server:

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 eotm_dyn logic.

After you have code that expresses that which you intend and works, take a look at optimising according to profiling.