SSWUGtv – Characteristics of Good Leader
Good managers create high-caliber teams that they enjoy and are proud of. But how can a manger be liked and trusted without sacrificing authority? Laura Rose is on today’s show with some great tips!
Watch the Show
IN vs. EXISTS
Last week we took a moment to respond to a question regarding when to use IN vs. EXISTS. My current practice is to use EXISTS for control statements such as WHILE EXISTS or IF EXISTS. Use IN within query statements.
Exists may also be used in a query much like an IN statement. However, more often than not it requires making a correlated sub-select query meaning that for each row in the outer query, the sub query is executed. IN, on the other hand, may simply execute the inner query once and use merged results for the outer query. Mostafa brings clarity to this issue in his response below.
There are other issues when using EXISTS and IN that are exposed when we negate the clause, such as NOT EXISTS or NOT IN. Mark, one of our readers, provides additional issues you need to take into account today.
Mostafa writes:
Thanks for your useful discussions. However, for the main difference of IN and EXISTS, I believe in the following fact:
When we use Exits instead of IN, the execution process starts with the outer select, and for each tuple of the outer table, the inner select has to be executed once again. But, when we use IN, the inner query is firstly executed and a set of values obtained. Then each tuple of the outer table is just compared with the obtained values rather than referring to database repeatedly.
The other fact is that we can always use Exists instead of In, but not vice versa.
I will be grateful if you send me your comment.
Mark Writes:
I would like to point out an important difference between NOT IN and NOT EXISTS when the target column contains nulls.
Taking your example let’s say we want to get the name of every department NOT containing an employee named Ramesh. If DeptID is NULL for some employee named Ramesh the following query will always return an empty result set; since IN uses three valued logic.
SELECT DepartmentName
FROM Department
WHERE DeptID NOT IN (
SELECT DeptID
FROM Employee
WHERE FirstName = ‘Ramesh’)
Using NOT EXISTS however would produce the desired result.
SELECT DepartmentName
FROM Department
WHERE NOT EXISTS (
Select 1
FROM Employee
WHERE Employee.DeptID = Department.DeptID
AND Employee.FirstName = ‘Ramesh’)
Thanks for writing btaylor@sswug.org with your input.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)