It is quite a common request to create a Transact-SQL query to compare a parent and a child table and find out if there are any parent records that don’t have a corresponding record in the child table. Generally, this can be done with one of the three ways:
So which one of the below is a better practice.Lets take a look.
# a) Using a NOT EXISTS
FROM TestTableA a
WHERE NOT EXISTS (SELECT * FROM TestTableB b WHERE a. col1= b. col1)
# b) Using a Left Join
SELECT a.col1 FROM TestTableA a
LEFT JOIN TestTableB b ON a. col1= b. col1
WHERE b. col1 IS NULL
# c) Using a NOT IN
SELECT col1 FROM TestTableA
WHERE col1 NOT IN (SELECT col1 FROM TableB)
In each case, the query above, will return identical results. But then, which of the three provides the best performance? Assuming everything else is equal; the best performing version to the worst performing, will be, as displayed above, from top to bottom. In other words, the NOT EXISTS variation of this query is by far the most efficient one.
If you are not sure which variation to try, you can try them all and see which produces the best results in specific circumstances. This can greatly help in in overcoming performance issues.