SQL EXISTS or NOT EXISTS, That Is the Question

SQL EXISTS and SQL NOT EXISTS are two logical operators commonly used in T-SQL to check for the existence of rows in a database. The SQL EXISTS returns TRUE if a subquery returns one or more rows, while the SQL NOT EXISTS returns TRUE if a subquery returns no rows. These two operators check for the presence or absence of related data in subqueries.

In this article, we’ll explore how the SQL EXISTS and NOT EXISTS work, when to use them, and why they are often preferred over alternatives like IN and NOT IN for reliable and efficient query performance when you need SQL to compare with the previous row or other comparison tasks.

Table of contents

• Using SQL EXISTS
  • SQL EXISTS syntax
  • SQL EXISTS example
• Using SQL NOT EXISTS
  • What is the difference between EXISTS and NOT EXISTS in SQL?
  • SQL NOT EXISTS syntax
  • SQL NOT EXISTS in a subquery
  • SQL NOT EXISTS example
• Difference between IN and EXISTS SQL Server
• SQL Server NOT IN vs NOT EXISTS
  • How to use NOT IN in SQL 
  • NOT IN vs NOT EXISTS performance in SQL Server
• Conclusion
  • Key takeaways

Using SQL EXISTS

SQL Server EXISTS can be used in SELECT, UPDATE, INSERT, or DELETE statements, and the result is a Boolean value, which means either True or False. Here is the syntax for this operator.  

SQL EXISTS syntax 

SELECT 
      column_name 
FROM Table_Name 
WHERE EXISTS (SELECT 
                    column_name 
              FROM Table_Name 
              WHERE condition); 

SQL EXISTS example 

Suppose you have a BicycleStoreDev database containing two tables, Customer and Order, that are linked with a one-to-many table relationship. 

Let’s say you want to launch a remarketing campaign and need to get a list of customers who have placed at least one order. So, how do you check if this row exists in your SQL database?  

Here is how to find customers who have placed at least one order: 

SELECT 
    c.CustomerID, 
    c.FirstName, 
    c.LastName, 
    c.Email 
FROM Sales.Customer c 
WHERE EXISTS ( 
    SELECT 1 
    FROM Sales.[Order] o 
    WHERE o.CustomerID = c.CustomerID 
); 

Step-by-step annotation 

1. SELECT c.CustomerID, c.FirstName, c.LastName, c.Email choose the customer columns you want to return from the outer query. 

2. FROM Sales.Customer c: c is an alias for the Sales.Customer table (the outer query). 

3. WHERE EXISTS (...): the EXISTS clause is a Boolean test. It returns TRUE for a given outer row when the subquery returns at least one row. If TRUE, that outer row is kept. 

4. SELECT 1 inside the subquery: using SELECT 1 is conventional because EXISTS only cares whether a row exists, not the row contents. It is slightly more efficient than selecting full columns. 

5. FROM Sales.[Order] o: o is an alias for the Order table (the inner/subquery table). 

6. WHERE o.CustomerID = c.CustomerID: this subquery checks for any order whose CustomerID matches the current Customer row from the outer query. If any such order exists, the subquery returns rows and EXISTS becomes TRUE. 

Expected result and why the logic works 

• Expected result: the query returns one row per customer who has placed at least one order. Customers with no matching records in Sales.[Order] are excluded.
• Why it works: for every row in Sales.Customer, SQL evaluates the correlated subquery. If the subquery finds any matching Order row, EXISTS evaluates to TRUE, and that customer is included. SQL Server typically short-circuits the subquery (stops after the first match) and may optimize this into a semi-join, so EXISTS is efficient. 

Sample output  

Using SQL NOT EXISTS

The SQL Server NOT EXISTS condition consists of two logical operators: EXISTS, which was described above, and NOT, which is used to negate a Boolean input. 

What is the difference between EXISTS and NOT EXISTS in SQL? 

Unlike EXISTS, NOT EXISTS returns TRUE if the result of the subquery does not contain any rows. However, if a single record in a table matches the subquery, the NOT EXISTS returns FALSE, and the execution of the subquery is stopped. Put better, NOT EXISTS allows locating records that don’t match the subquery. Here is the syntax for this operator. 

SQL NOT EXISTS syntax

SELECT 
      column_name 
FROM Table_Name 
WHERE NOT EXISTS (SELECT 
          column_name 
                  FROM Table_Name 
                  WHERE condition); 

SQL NOT EXISTS in a subquery 

NOT EXISTS is used with a subquery in the WHERE clause to check if the result of the subquery returns TRUE or FALSE. The Boolean result is then used to narrow down the rows from the outer select statement. 

Therefore, the subquery with NOT EXISTS checks every row from the outer query, returns TRUE or FALSE, and then sends the value to the outer query for use. In even simpler words, when you use SQL NOT EXISTS, the query returns all the rows that don’t satisfy the EXISTS condition. Here is an example. 

Example: Customers without orders 

Let’s assume you want to find all customers who have never placed an order; you can use NOT EXISTS like this: 

SELECT  
    c.CustomerID, 
    c.FirstName, 
    c.LastName 
FROM Sales.Customer c 
WHERE NOT EXISTS ( 
    SELECT 1 
    FROM Sales.[Order] o 
    WHERE o.CustomerID = c.CustomerID 
); 

Output: 

CustomerID	FirstName	LastName
8	Linda	Park
9	Adam	Brody
10	Maria	Gomez

In this query, SQL Server checks each customer in the Sales.Customer table. For every customer, it runs the subquery to see if a matching order exists in the Sales.Order table. If no matching rows are found, the customer is returned in the result set. NOT EXISTS is used with a subquery in the WHERE clause to check if the result of the subquery returns TRUE or FALSE. The Boolean value is then used to narrow down the rows from the outer select statement.

SQL NOT EXISTS example 

Let’s say you want to find all customers who have never placed an order. For example, identifying inactive customers for a re-engagement campaign. 

You can achieve this by checking which customer IDs exist in the Order table and then returning only those customers who are not present there using the NOT EXISTS operator. Here’s the query for this scenario: 

SELECT  
    c.CustomerID, 
    c.FirstName, 
    c.LastName, 
    c.Email 
FROM Sales.Customer c 
WHERE NOT EXISTS ( 
    SELECT 1 
    FROM Sales.[Order] o 
    WHERE o.CustomerID = c.CustomerID 
); 

Explanation: 

• The outer query selects customer details from the Sales.Customer table.
• For each customer, the subquery checks the Sales.Order table to see if there is a matching CustomerID.
• If no rows are returned by the subquery, the condition evaluates to TRUE, and the customer is included in the result set. 

As a result, this query returns a list of customers who do not have any corresponding records in the Order table. Meaning, customers who have not placed any orders yet. 

Difference between IN and EXISTS in SQL Server 

Which is better: IN or EXISTS?  

Both operators can return the same results, but they differ in efficiency, handling of NULLs, and how the SQL engine processes them. 

• Use EXISTS when working with large, indexed tables, since it can stop scanning as soon as it finds the first match.
• Use IN when the list of values is small, static, or hard-coded. It’s simpler and more intuitive.
• Be cautious with NOT IN if there’s a possibility of NULLs in the subquery, as it can cause unexpected results. 

Example with IN 

Continuing with the remarketing campaign, let’s assume you need to get a list of customers living in New York to send them a special offer. Here is the query you will use for this scenario: 

SELECT 
     c.FirstName 
    ,c.LastName 
    ,c.Email 
    ,c.City 
FROM Sales.Customer c 
WHERE City IN ('New York'); 

Remember the query with EXISTS? The one used to find the list of customers who placed orders? Let’s now rewrite it using IN. As you can see, the result is the same. 

Then which one should you choose? 
 
No doubt, subqueries with IN are more intuitive; however, they’re slower and less efficient than the same queries written with EXISTS when it comes to large datasets. 

In addition to this, here are other significant differences between the two operators: 

• The output of the subquery with EXISTS can be either TRUE or FALSE, whereas the output of IN can be TRUE, FALSE, or NULL.
• The database engine stops checking rows for the EXISTS once it finds at least one matching row; for the IN, it scans every row for the specified value.
• Null values returned by a subquery that is compared to the outer statement using IN or NOT IN return UNKNOWN. Thus, using NULL values with IN can lead to unexpected results.

Comparison table 

Operator	Returns TRUE if…	Performance notes	Pros	Cons
IN	A value matches one in a list or subquery	Simple for small sets, but scans entire list/subquery	Easy to read and intuitive	Slower on large datasets, affected by NULLs
EXISTS	A subquery returns at least one row	Optimized; stops after the first match (semi-join)	Fast on large, indexed tables; ignores NULLs	Slightly less intuitive for beginners
NOT IN	A value does not match any in a list or subquery	Full scan; poor performance with large subqueries	Simple syntax	Fails if subquery returns NULL (returns no rows)
NOT EXISTS	A subquery returns no rows	Stops when a match is found; avoids NULL pitfalls	Reliable, efficient for large datasets	Slightly more verbose

Key takeaways 

• IN: best for small, static lists.
• EXISTS: best for large datasets and correlated subqueries.
• NOT IN: avoid if the subquery may return NULLs.
• NOT EXISTS: a reliable alternative to NOT IN for large, real-world queries.

SQL Server NOT IN vs NOT EXISTS

Which is faster: NOT IN or NOT EXISTS in SQL Server? 

In large datasets, NOT EXISTS is generally faster than NOT IN due to the way SQL Server processes subqueries and evaluates conditions against potentially large result sets. 

The NOT IN operator is built from two logical components: 

• IN, which checks whether a value matches any value in a list or subquery.
• NOT, which reverses the condition to return rows that do not match. 

On the one hand, while NOT IN can be useful, it has limitations, especially when dealing with NULL values. If a subquery returns even one NULL, the entire NOT IN condition fails, which can lead to unexpected results. On the other hand, NOT EXISTS avoids this pitfall and handles such cases more reliably. 

From both a correctness and performance standpoint, NOT EXISTS is often the safer and faster choice, particularly when querying large tables or complex subqueries. 

How to use NOT IN in SQL 

Let’s continue with the remarketing campaign example. Suppose now you need to get a list of customers that live in other states aside from New York. 

To exclude rows that match certain values, use NOT IN like this:

SELECT 
     c.FirstName, 
     c.LastName, 
     c.Email, 
     c.City 
FROM Sales.Customer c 
WHERE c.State NOT IN ('NY'); 

This query returns all customers who do not live in New York. The condition c.State NOT IN (‘NY’) filters out rows where the State column equals ‘NY.’Here is an example of what your before and after results will look like. 

Before (full dataset): 

FirstName	LastName	Email	City
Kasha	Todd	[email protected]	Campbell
Tameka	Fisher	[email protected]	Redondo Beach
Charolette	Rice	[email protected]	Sacramento
Genoveva	Tyler	[email protected]	New York
Sharie	Alvarez	[email protected]	New York

After applying NOT IN (‘NY’): 

FirstName	LastName	Email	City
Kasha	Todd	[email protected]	Campbell
Tameka	Fisher	[email protected]	Redondo Beach

NOT IN vs NOT EXISTS performance in SQL Server

First, we should mention that NOT EXISTS and NOT IN unlike EXISTS and IN are not interchangeable in all situations. Namely, when NULLs are involved, they return different results. In this case, when the subquery returns even one null, NOT IN will not match any rows.

Regarding performance aspects, SQL NOT EXISTS would be a better choice over SQL NOT IN. NOT EXISTS is significantly faster than NOT IN especially when the subquery result is very large.

Conclusion

In summary, use EXISTS when working with large datasets where performance matters, and use NOT EXISTS when you need to check for the absence of related rows. While all four logical operators—IN, EXISTS, NOT IN, and NOT EXISTS—return Boolean results, their behavior and efficiency differ in important ways. 

Key takeaways 

• Use EXISTS for performance on large, indexed tables. It stops checking once a match is found.
• Avoid NOT IN if there’s any chance of NULL values in the subquery, as it can return no results unexpectedly.
• Prefer NOT EXISTS for absence logic. It is both reliable and efficient.
• Use IN only for small, static lists where readability matters more than performance.

This way, you can choose the right operator depending on your data and ensure your SQL queries are both accurate and optimized. 

The screenshots in the article were made in the SQL Complete tool—an SSMS and Visual Studio add-in that vastly enhances and accelerates users’ SQL coding capabilities. Want to try it yourself? Download a 14-day free trial of SQL Complete and test-drive all the advanced features it delivers.