SQL Server IN vs EXISTS (2024)

By: Koen Verbeeck |Comments (9) | Related: 1 | 2 | 3 | 4 | 5 | > TSQL

Problem

Is there a difference between using the T-SQL IN operator or the EXISTS operatorin a WHERE clause to filter for specific values in SQL queries and storedprocedures? Is there a logical difference,a performance difference or are they exactly the same? And what about NOT IN andNOT EXISTS?

Solution

In this SQL tutorial we'll investigate if there are any differences between theEXISTS and the IN operator. This can either be logical, i.e. they behave differentunder certain circ*mstances, or performance-wise, meaning if using one operatorhas a performance benefit over the other. We'll be using theAdventureWorks DW 2017 sample database for our test queries for theMicrosoft SQL Server DBMS.

SQL IN vs EXISTS Syntax

TheIN operator is typically used to filter a column for a certain list of values.For example, review this SELECT statement:

SELECT [ProductSubcategoryKey] ,[EnglishProductSubcategoryName] ,[ProductCategoryKey]FROM [AdventureWorksDW2017].[dbo].[DimProductSubcategory]WHERE [ProductCategoryKey] IN (1,2);

This query searches for all the product subcategories which belong to the productcategories Bikes and Categories (ProductCategoryKey 1 and 2).

You can also use the IN operator to search the values in the result set of asubquery with the following SQL commands:

SELECT [ProductSubcategoryKey] ,[EnglishProductSubcategoryName] ,[ProductCategoryKey]FROM [AdventureWorksDW2017].[dbo].[DimProductSubcategory]WHERE [ProductCategoryKey] IN ( SELECT [ProductCategoryKey] FROM [dbo].[DimProductCategory] WHERE [EnglishProductCategoryName] = 'Bikes' );

This query returns all subcategories linked to the Bikes category.

The benefit of using a subquery is that the query becomes less hard-coded; ifthe ProductCategoryKey changes for some reason, the second query will still work,while the first query might suddenly return incorrect results. It's importantthough the subquery returns exactly one column for the IN operator to work.

TheEXISTS operator doesn't check for values, but instead checks for the existenceof rows. Typically, a subquery is used in conjunction with EXISTS. It actually doesn'tmatter what the subquery returns, as long as rows are returned.

This query willreturn all rows from the ProductSubcategory table, because theinner subquery returns rows (which are not related to the outer query at all).

SELECT [ProductSubcategoryKey] ,[EnglishProductSubcategoryName] ,[ProductCategoryKey]FROM [AdventureWorksDW2017].[dbo].[DimProductSubcategory]WHERE EXISTS ( SELECT 1/0 FROM [dbo].[DimProductCategory] WHERE [EnglishProductCategoryName] = 'Bikes' );

As you might have noticed, the subquery has 1/0 in the SELECT clause. In a normalquery, this would return a divide by zero error, but inside an EXISTS clause it'sperfectly fine, since this division is never calculated. This demonstrates thatit's not important what the subquery returns, as long as rows are returned.

To use EXISTS in a more meaningful way, you can use a correlated subquery.In a correlated subquery, we pair values from the outer query with values from theinner (sub)query. This effectively checks if the value of the outer query existsin the table used in the inner query. For example, if we want to return a list ofall employees who made a sale, we can write the following query:

SELECT [EmployeeKey] ,[FirstName] ,[LastName] ,[Title]FROM [AdventureWorksDW2017].[dbo].[DimEmployee] eWHERE EXISTS ( SELECT 1 FROM dbo.[FactResellerSales] f WHERE e.[EmployeeKey] = f.[EmployeeKey] );

In the WHERE clause inside the EXISTS subquery, we correlate the employee keyof the outer table – DimEmployee – with the employee key of the innertable – FactResellerSales. If the employee key exists in both tables, a rowis returned and EXISTS will return true. If an employee key is not found in FactResellerSales,EXISTS returns false and the employee is omitted from the results:

We can implement the same logic using the IN operator with the following SQLstatement:

SELECT [EmployeeKey] ,[FirstName] ,[LastName] ,[Title]FROM [AdventureWorksDW2017].[dbo].[DimEmployee] eWHERE [EmployeeKey] IN ( SELECT [EmployeeKey] FROM dbo.[FactResellerSales] f );

Both queries return the same result set, but maybe there is an underlying performancedifference? Let's compare the execution plans.

This is the plan for EXISTS:

This is the plan for IN:

They look exactly the same. When executing both queries at the same time, youcan see they get assigned the same cost:

The top execution plan is for EXISTS, the bottom one for IN.

Let's take a look at the IO statistics (you can show these by running thestatement SET STATISTICS IO ON). Again, everything is exactly the same:

So, there's no performance difference that we can prove and both returnthe same result sets. When would you choose to use one or the other? Here aresome guidelines:

• If you have a small list of static values (and the values are not presentin some table), the IN operator is preferred.
• If you need to check for existence of values in another table, the EXISTSoperator is preferred as it clearly demonstrates the intent of the query.
• If you need to check against more than one single column, you canonly use EXISTS since the IN operator only allows you to check for onesingle column.

Let’s illustrate the last point with an example. In the AdventureWorks datawarehouse, we have an Employee dimension. Some employees manage a specific salesterritory:

Now, it’s possible that a sales person also makes sales in other territories.For example, Michael Blythe – responsible for the Northeast region – has sold in4 distinct regions:

Let’s suppose we now only want to find the sales amounts for the salesterritory managers, but only for their own region. A possible SQL query could be:

SELECT f.[EmployeeKey] ,f.[SalesTerritoryKey] ,SUM([SalesAmount])FROM [dbo].[FactResellerSales] fWHERE EXISTS ( SELECT 1 FROM [dbo].[DimEmployee] e WHERE f.[EmployeeKey] = e.[EmployeeKey] AND f.[SalesTerritoryKey] = e.[SalesTerritoryKey] AND e.[SalesTerritoryKey] <> 11 -- the NA region )GROUP BY f.[EmployeeKey] ,f.[SalesTerritoryKey];

The result is as follows:

Inside the EXISTS clause, we retrieve the sales territories managers byfiltering out all employees linked to the NA region. In the outer query, we getall sales per sales territory and employee, where the employee and territory isfound in the inner query. As you can see, EXISTS allows us to easily check onmultiple columns, which is not possible with IN.

SQL Server NOT IN vs NOT EXISTS

By prefixing the operators with the NOT operator, we negate the Boolean outputof those operators. Using NOT IN for example will return all rows with a value thatcannot be found in a list.

There is one special case though: when NULL values come into the picture. Ifa NULL value is present in the list, the result set is empty!

This means that NOT IN can return unexpected results if suddenly a NULL valuepops up in the result set of the subquery. NOT EXISTS doesn't have thisissue, since it doesn't matter what is returned. If an empty result set isreturned, NOT EXISTS will negate this, meaning the current record isn't filteredout:

The query above returns all employees who haven't made a sale. Logically,NOT IN and NOT EXISTS are the same – meaning they return the same result sets –as long as NULLS aren't involved. Is there a performance difference? Again,both query plans are the same:

The same goes for the IO statistics:

There is one gotcha though. The EmployeeKey is not-nullable in FactResellerSales.As demonstrated before, NOT IN can have issues when NULLs are involved. If we changeEmployeeKey to be nullable, we get the following execution plans:

Quite a difference this time! Because SQL Server now has to take NULL valuesinto account, the execution plan changes. The same can be seen in the IO statistics:

Now there's an actual performance difference between NOT IN and NOT EXISTS.When to use which operator? Some guidelines:

• The same guidelines as for IN and EXISTS can be applied. For checking againsta small static list, NOT IN is preferred. Checking for existence in anothertable? NOT EXISTS is the better choice. Checking against multiple columns, againNOT EXISTS.
• If one of the columns is nullable, NOT EXISTS is preferred.

Using Joins Instead of IN or EXISTS

The same logic can be implemented with joins as well. An alternative for IN andEXISTS is anINNER JOIN, whileaLEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as analternative for NOT IN and NOT EXISTS. The reason they are not included in thistip – even though they might return the exact same result set and executionplan – is because the intent is different. With IN and EXISTS, you check forthe existence of values in another record set. With joins you merge the result sets,which means you have access to all columns of the other table. Checking for existenceis more of a "side-effect". When you use (NOT) IN and (NOT) EXISTS,it's really clear what the intent of your query is. Joins on the other handcan have multiple purposes.

Using an INNER JOIN, you can also have multiple rows returned for the same valueif there are multiple matches in the second table. If you want to check for existenceand if a value exist you need a column from the other table, joins are preferred.

Next Steps

• You can find more T-SQL tips in thisoverview.
• Long-time MVP Gail Shaw has a nice series on EXISTS vs IN vs SQL JOINS. If you'reinterested in comparing EXISTS/IN versus the JOINS, you can read the followingblog posts:
  • IN vs INNER JOIN
  • LEFT OUTER JOIN vs NOT EXISTS
• SQL Server Join Tips
• Tip:SQL Server Join Example
• Learn more about theSQL SELECT statement
• Learn more about the SQL LIKEcommand

About the author