Q & A – Difference between IN and EXISTS
Shemaulik asks: “What is the difference between IN and Exists?”
I did a quick Google search and found many good examples. So, I don’t plan to go into much detail here.
IN and EXIST can both be used to determine the existence of a record in a query statement. A statement run with IN will return the entire list of records meeting the criteria. A Query Statement with EXISTS returns a true/false result. When it finds the first matching record, the query stops executing and a true value is returned. If no record is found, it returns false.
Exists is most often used to determine control of your program
IF EXISTS
(
Select 1
FROM LOGINS
WHERE UserID = ‘BEN’
)
BEGIN
Do something
END
Both IN and EXISTS may be used as a sub query to filter data. EXISTS must also be a correlated sub select; that means that you have the sub select statement takes as in imput a value from the outer query calling it.
If you wanted to get the name of every department having an employee with the first name Ramesh you could simply join the two tables. However, you will get duplicate department records for each department having more than one employee named Ramesh. You could use SELECT DISTINCT and group the aggregate results to get only one record per department. Or you could use a subquery.
SELECT DepartmentName
FROM Department
WHERE DeptID IN (
SELECT DeptID
FROM Employee
WHERE FirstName = ‘Ramesh’)
This will return only one record per department regardless of the number of Ramesh employees it locates. Using EXISTS requires a little more interaction between the two queries.
SELECT DepartmentName
FROM Department
WHERE EXISTS (
Select 1
FROM Employee
WHERE Employee.DeptID = Department.DeptID
AND Employee.FirstName = ‘Ramesh’)
If you have a lot of employees the second query may perform better because it stops execution when it locates the first Ramesh. Historically that was assured. However, I checked as far back as SQL Server 2008 R1 and found that the query plan for either statement is exactly the same. Historically, correlated sub select queries were the first thing you looked to remove when trying to improve performance. Once again, history doesn’t remain static in this case.
My personal preference is to use EXISTS for control statements, and IN for queries. I find the syntax easier to read when using them for these purposes.
Join the conversation with your preferences on IN and EXISTS. Perhaps you may like to add ANY to the discussion as well. You can send your thoughts to btaylor@sswug.org.
Cheers,
Ben
SSWUGtv
With Stephen Wynkoop
Join Steve as he talks with Don Boxley about some exciting updates with DH2i..
Watch the Show
$$SWYNK$$
Featured Article(s)
Staying on the right side of the friend-supervisor line
Most employers understand that to get the most out of their staff, the work climate needs to be friendly, flexible and somewhat accommodating. Having a friendly atmosphere makes coming into the office more comfortable and exciting. People work with folks they know, like and trust. Good manager create high-caliber teams that they enjoy and are proud of. But how can a manger be liked and trusted without sacrificing authority?
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Simplify encryption and key management on your SQL Server. Data thieves are targeting SMB companies because of their inadequa… (read more)