Monday, May 13, 2019

How to use EXISTS Clause in SQL? A Real World SQL Query Example using EXISTS

Hello Guys, you might have heard about how useful EXISTS clause in useful for writing sophisticated query but at the same time I have also seen that many programmers struggle to understand and use EXISTS and NOT EXISTS clause while writing SQL query.  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 a nice 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?

In order 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 programming interview, you can see the full list here.

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

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




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. The id is a numeric column while 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 Id of Customer who makes 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 an 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 classic 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



The solution of Customers Who Never Order

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

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.


Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners
SQL Puzzles and Answers, the 2nd Edition


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 index 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 article then please share 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 resource 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.

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