How to use EXISTS and NOT Exists in SQL? Example Query and Tutorial

Now the second half is how do you check if there is a record or not for a particular customer?  That’s where EXISTS and NOT EXISTS clause will help you, and that’s what you will learn in this article.

This is actually also a popular SQL Interview question, and you might have seen it already, but if you are not, that’s fine. This question is not as popular as the second-largest salary query, but it is also one of the frequently asked SQL queries from a programming interview; you can see the full list here.

While I agree that this problem can be solved in a different way, but it is also a perfect example of how you can use the SQL EXISTS clause.

But, if you are new to the SQL world, it’s better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

SQL tables and Data for Query Example

Before looking at the query, let’s see the schema, tables, and data to understand the problem better. We have two tables Customers and Orders. Customers contain two columns, Id and Name. The id is a numeric column while the Name is a text column, let’s assume of type VARCHAR.

If any customer has ordered anything, then their CustomerId will exist in the Orders table, we will take advantage of this knowledge to solve this problem.

We have four customers with Id ranging from 1 to 4. Our second table, Orders, contains Id, which is a unique id for order, and CustomerId, which is the Id of the Customer who makes that order. If any Customer will place an order, then their Id will exist in the Orders table.

Table: Customers.

+—-+——-+
| Id | Name  |
+—-+——-+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+—-+——-+

Table: Orders.

+—-+————+
| Id | CustomerId |
+—-+————+
| 1  | 3          |
| 2  | 1          |
+—-+————+

Using the above tables as an example, return the following:

+———–+
| Customers |
+———–+
| Henry     |
| Max       |
+———–+

If you looking for some more SQL challenges, then you can try solving problems given in Joe Celko’s classic book, SQL Puzzles, and Answers, the 2nd Edition. One of the best books to improve your SQL query skills.

How to Find Customers Who Never Order using EXISTS in SQL

One of the most common solutions to this problem is by using the SQL JOIN clause. You can use the LEFT OUTER JOIN to solve this problem, as shown below:

SELECT C.Name FROM Customers C
LEFT JOIN Orders O ON  C.Id = O.CustomerId
WHERE O.CustomerId is NULL

When you join two tables in SQL using a LEFT OUTER JOIN, then a big table will be created with NULL values in the column which don’t exist in another table.

For example, the big table will have four columns C.Id, C.Name, O.Id, and O.CustomerId, for Customers who have never ordered anything, the O.CustomerId will be NULL.

Many programmers make the mistake of using != in the JOIN condition to solve this problem, with the assumption that if = returns matching rows, then != will return those ids which are not present in another table. So beware of that.

If you struggle to understand join, then I suggest you take a look at Jose Portilla’s excellent SQL course The Complete SQL Bootcamp on Udemy. His teaching style is amazing and you will understand joins in no time.

Anyway, this problem is actually an excellent example of how and when to use EXISTS clause:

SELECT C.Name FROM Customers C 
WHERE NOT EXISTS (SELECT 1 FROM Orders O WHERE C.Id = O.CustomerId)

This is a correlated subquery, where the inner query will execute for each row of the outer query, and only those customers will be returned who have not ordered anything.

Btw, the most simple solution is by using the NOT IN Clause.

SELECT A.Name FROM Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId FROMs Orders B)

And, here is a nice screenshot to remember how and when to use the EXISTS clause in SQL query:

That’s all about how to use the EXISTS clause in SQL to find all customers who have never ordered. If you like to improve your SQL query skills, then you can also problems given in Joe Celko’s classical book SQL Puzzles and Answers, the 2nd Edition.