Saturday, January 2, 2016

SQL EXISTS Example - Customers Who Never Order LeetCode Solution

Good morning Guys, Today we will learn how to use the EXISTS clause in SQL by picking up a nice SQL 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. This question is not as popular as the second largest salary query, but it is also one of the frequently asked SQL queries from programming interview, you can see the full list here.

SQL tables and data

Before looking at the query, let's see the schema, tables and data to understand the problem better. 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.

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 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.

SQL Exists example Customers who never order leetcode solution

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 = returns 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:


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.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

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 database in MySQL (solution)
  • Write a SQL query to copy or backup a table in MySQL (solution)
  • How do you find the duplicate rows in a table on database? (solution)
  • 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 difference between UNION and UNION ALL in SQL? (answer)
  • Difference between Self and Equi Join in SQL? (answer)
  • What is difference between View and Materialized View in Database? (answer)
  • Difference between clustered and non-clustered index in SQL? (answer)
  • Difference between Primary and Candidate key in table? (answer)
  • Difference between Unique and Primary key in table? (answer)
  • Difference between Primary and Foreign key in table? (answer)

1 comment :

manish said...

All the texts in right side is gone. Not able to understand any of the solution. Please correct alignment on right side.

Post a Comment