Good morning Guys, Today we will learn how to use the EXISTS clause in SQL by picking up a niceSQL exercise from LeetCode. Suppose that a website contains two tables, the Customers table and the Orders table. Write an SQL query to find all customers who have never ordered anything . This problem can be solved from different way but it is also a very good example of how you can use the SQL EXISTS clause. If any customer has ordered anything then their CustomerId will exist in Order table, we will take advantage of this knowledge to solve this problem. Before that' let see the schema, tables and data to understand the problem better.
SQL tables and data
We have two table Customers and Orders. Customers contain two columns, Id and Name. Id is a numeric column while Name is a text column, let's assume of type VARCHAR. We have four customers with Id ranging from 1 to 4. Our second table, Orders contains Id, which is unique id for order and CustomerId, which is Id of Customer who make that order. If any Customer will place an order than their Id will exist in the Orders table.
| Id | Name |
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
| Id | CustomerId |
| 1 | 3 |
| 2 | 1 |
Using the above tables as example, return the following:
| Customers |
| Henry |
| Max |
If you looking for some more SQL challenges then you can try solving problems given on Joe Celko's classical book, SQL Puzzles and Answers, the 2nd Edition . One of the best book to improve your SQL query skills.
Solution of Customers Who Never Order
One of the most common solution of this problem is by using 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 LEFT OUTER JOIN than 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 Customer who have never ordered anything, the O.CustomerId will be NULL.
Many programmers make the mistake of using != in JOIN condition to solve this problem, with the assumption that if = return matching rows then != will return those ids which are not present in another table. So beware of that.
This problem is actually a very good 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 NOT IN Clause
SELECT A.Name FROM Customers A WHERE A.Id NOT IN (SELECT B.CustomerId FROMs Orders B)
That's all about how to use EXISTS clause in SQL to find all customers who have never ordered. If you like to improve SQL query skill then you can also problems given in Joe Celko's classical book SQL Puzzles and Answers, the 2nd Edition. One of the best book with lots of challenging problems to test your SQL skill.