Shuold I use not exists or join statement to filter out NULLs?

Question

SELECT  *
FROM    employees e
WHERE   NOT EXISTS
        (
        SELECT  name 
        FROM    eotm_dyn d
        WHERE   d.employeeID = e.id
        )

And

SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL

Which is more efficient,some analysis?

Answer 1

I really think you should profile for such a question. Not only does it depend on the exact database product, but in theory it could also depend on the skew of your data.

However! By default I would say write the code that most clearly expresses your intent. You are after employee records without a matching eotm_dyn, so IMO the clearest code is WHERE NOT EXISTS. It probably won't matter, but I would use SELECT 1 (not SELECT name), since the name is not important in the "without a matching eotm_dyn logic.

Once you have code that expresses what you intend and works, then look at optimising based on profiling.

Answer 2

Assuming the column values involved can not be NULL -

MySQL:

LEFT JOIN/IS NULL is more efficient than NOT EXISTS - read this article for details.

Oracle:

They are equivalent.

SQL Server:

NOT EXISTS is more efficient than LEFT JOIN/IS NULL - read this article for details.

Postgres:

Like Oracle, they are equivalent.

If you have trouble with the detail that the column values can not be null, yet use the LEFT JOIN / IS NULL - remember what a LEFT JOIN signifies. This link might help.