When to use EXISTS and NOT EXISTS in SQL? SQL Server Example

 Hello guys, if you are wondering how to use the IF EXISTS and NOT EXISTS in SQL then you are at the right place. Earlier, I have shared how to use GROUP BY, WHERE, and HAVING clause and in this tutorial I will share how to use exists and not exists clause in SQL. The IF EXISTS and NOT EXISTS commands in T-SQL are covered in depth in this article. When comparing data sets using subqueries, it also illustrates why EXISTS should be preferred over IN and NOT EXISTS over NOT IN. 

If you don't know, EXISTS is a logical operator in SQL that is used to check if rows in a database exist. If the subquery produces one or more records, it returns TRUE. 

The NOT EXISTS operator in SQL is the polar opposite of the EXISTS operator, and it is fulfilled if the subquery returns no results.

Using EXISTS in SQL

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

Let's understand this by example using a customer database.

you guys can feel free to create your own tables. create two tables. table 1 should have a one-to-many relationship with table 2. I have created two sample tables Customer and Orders. A customer can have multiple Orders, thus, creating a one-to-many relationship.

Now, Let's imagine we want to start a remarketing campaign and require a list of consumers who have made at least one purchase. So, with SQL, how do you see whether a row exists? We'll use the following query to do this:

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

The query returns rows from the Customer table whose CustomerID columns are the same as the OrderID fields in the Order table.

Using NOT EXISTS in SQL

The NOT EXISTS condition in SQL Server is made up of two logical operators: EXISTS (which was previously discussed) and NOT (which is used to negate a Boolean input).

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

NOT EXISTS, unlike EXISTS, returns TRUE if the subquery's result contains no records. If a single entry in a table fits the subquery, the NOT EXISTS function returns FALSE, and the subquery's execution is halted. NOT EXISTS, in plain English, allows you to find records that don't match the subquery.

SYNTAX

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

NOT EXISTS is used in the WHERE clause with a subquery to see if the result of the subquery is TRUE or FALSE. The outer select statement's rows are subsequently narrowed down using the Boolean value.

To put it another way, the NOT EXISTS subquery checks every entry from the outer query, returns TRUE or FALSE, and then passes the value to the outer query to use. In plain English, SQL NOT EXISTS returns all entries that do not fulfill the EXISTS condition.

SQL Exists and Not Exists Example

We'll query the Customer table to locate entries where the CustomerID doesn't exist in the Order table to show how NOT EXISTS works in SQL Server. As a result, we'll obtain a list of clients who haven't placed any orders yet if we use NOT EXISTS.

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

Result

That's all about how to use EXISTS and NOT EXISTS clause in SQL. Because SQL is a logical language, everything works fairly simply. Its logical operators examine the situation and return a Boolean value. In some situations, these logical operators can be used interchangeably, and the user can choose which one to employ. We gave a complete review and comparison of SQL EXISTS, NOT EXISTS, IN in this post so that you may make the best decisions possible in your work.

Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• 10 Frequently asked SQL Query interview questions (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 4 ways to find the Nth highest salary in SQL (solution)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• Difference between Self and Equi Join in SQL? (answer)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Advanced SQL books to level up your SQL skills (books)
• 5 Courses to learn Database and SQL Better (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Write a SQL query to copy or backup a table in MySQL (solution)
• Difference between Primary and Candidate key in table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Unique and Primary key in table? (answer)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• Difference between Primary and Foreign key in table? (answer)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How do you find the duplicate rows in a table on a database? (solution)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Free Courses to learn Database and SQL (free courses)

Thanks for reading this article, if you like this SQL EXISTS tutorial, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning.