Shuold I Use Not Exists Or Join Statement To Filter Out Nulls?

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

Solution 1:

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.

Solution 2:

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.