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

Hello Guys, you might have heard about how useful the EXISTS clause is helpful in writing sophisticated queries. Still, at the same time, I have also seen that many programmers struggle to understand and use EXISTS and NOT EXISTS clauses while writing SQL queries.  If you are one of them, then you have come to the right place. Today you will learn how to use the EXISTS clause in SQL by picking up a real-world example and an excellent SQL exercise from the LeetCode. Suppose that a website contains two tables, the Customers table, and the Orders table. Can you write an SQL query to find all customers who have never ordered anything?

To solve this problem, you need to first think about data. For example, if a customer has never ordered anything means there won't be any record for him on the Orders table. Good job, half of the task is done.

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. 

One of the best books with lots of challenging questions to test your SQL skill, and if you need some online courses to learn SQL in-depth or fill the gaps in your SQL knowledge, the following courses are a good place to start with.



Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Difference between Self and Equi Join in SQL? (answer)
• 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)
• 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)
• Difference between Primary and Foreign key in table? (answer)

Thanks for reading this article, if you like this SQL EXISTS tutorial and example, 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 this list of Free SQL Courses for Beginners to kick-start your learning.