SQL EXISTS or NOT EXISTS, That Is the Question

The article provides a detailed overview of the T-SQL IF EXISTS and NOT EXISTS statements. It also explains why EXISTS should be chosen over IN and NOT EXISTS over NOT IN when comparing data sets using subqueries.

SQL EXISTS is a logical operator that is used to check for the existence of rows in a database. It returns TRUE in case the subquery returns one or more records. SQL NOT EXISTS acts quite opposite to the EXISTS operator and is satisfied in case no rows are returned by the subquery.

Contents

• Using SQL EXISTS
  • SQL EXISTS syntax
  • SQL EXISTS example
• Using SQL NOT EXISTS
  • 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

Using SQL EXISTS

The result of the EXISTS condition is a boolean value—True or False. SQL Server EXISTS can be used in SELECT, UPDATE, INSERT, or DELETE statements.

SQL EXISTS syntax

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

SQL EXISTS example

Let’s consider the following example of SQL EXISTS usage. Suppose, our BicycleStoreDev database contains two tables: Customer and Order, that are linked with a one-to-many table relationship.

Let’s say we want to launch a remarketing campaign, and thus we need to get a list of customers who placed at least one order. So, how to check if a row exists in SQL? For this, we will run the following query:

SELECT
      *
FROM Sales.Customer c
WHERE EXISTS (SELECT
                    *
              FROM Sales.[Order] o
              WHERE o.CustomerId = o.OrderId)

The query lists the rows from the Customer table in which CustomerID fields equal the OrderID fields in the Order table.

Using SQL NOT EXISTS

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. In case a single record in a table matches the subquery, the NOT EXISTS returns FALSE, and the execution of the subquery is stopped. In plain English, NOT EXISTS allows locating records that don’t match the subquery.

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 value is then used to narrow down the rows from the outer select statement.

In simple words, 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 to use. In even simpler words, when you use SQL NOT EXISTS, the query returns all the rows that don’t satisfy the EXISTS condition. 

SQL NOT EXISTS example

To demonstrate NOT EXISTS usage in SQL Server, we will query the Customer table to find rows where the CustomerID doesn’t exist in the Order table. Thus, by using NOT EXISTS we will get the list of customers who haven’t placed any orders yet.

Difference between IN and EXISTS SQL Server

Logical operator IN in SQL returns TRUE if a specified value matches one of the values in a subquery or a list. Simply put, the IN operator compares a given value to a specified list of values. In case the given value matches at least one value from the list, it returns TRUE, otherwise, FALSE is thrown.

Suppose, we continue our remarketing campaign and need to get a list of customers living in New York to send them a special offer.

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

Remember our query with EXISTS? The one we 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 to choose?
It is obvious that 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.

We should also mention other major 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, however, it scans every row for the specified value.
• Null values returned by 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.

SQL Server NOT IN vs NOT EXISTS

SQL NOT IN consists of two logical operators: IN which we have described above and NOT which can be put before any conditional statement to find rows for which that statement is false.

How to use NOT IN in SQL

Let’s continue with our remarketing campaign. Suppose, now we need to get a list of customers that live in states other than New York.

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

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

SQL is a logical language, and everything works quite straightforwardly here. Its logical operators check the condition and return a Boolean data type. In certain cases, these logical operators can be used interchangeably, and the choice of the operator is up to the user. In the article, we provided a detailed overview and comparison of SQL EXISTS, NOT EXISTS, IN, and NOT IN for you to be well-versed enough to make the right choices in your work.

The screenshots in the article we made in the SQL Complete tool—an SSMS and Visual Studio add-in that vastly enhances and accelerates its 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.