We take pride in your success. We let our positivity drive us, day in and out. Talk to us at Mindfire to know us more.

Software Technology Tips

Home » Tips » Database » Which one to use in a SQL query:-"Not Exists"/ Left Join / Not In"?
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
 
SELECT a.col1
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.


Related Tags:
Database, SQL

Author: Pallavi Kar

top

Database

Let us Connect!

privacy

copyright (c) Mindfire Solutions 2007-2012. Login