Problem Description –
Suppose that a website contains two tables, the
Customers table, and the
Orders table. Write a SQL query to find all customers who never order anything.
Table – Customers
Table – Orders
Using the above tables as examples, return the following.
Difficulty Level – Easy
SELECT Name Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders WHERE CustomerId IS NOT NULL)
In the subquery, we are selecting all the customer’s IDs who had made a purchase as the orders table will contain the IDs of customers who have made a purchase. Then in the outer query, we are using
WHERE Id NOT IN to exclude all the IDs from the subquery. This will give us all the customers ids who have never made a purchase.
One important thing to notice in this query is the use of WHERE CustomerId IS NOT NULL in the subquery. If you want you can exclude this where clause and your answer will be accepted in Leetcode. But You should include this condition because if for some reason, the CustomerId column in the second table contains NULL values, then the query will return nothing.
Here, I have included a NULL value in the second table.
SELECT * FROM Orders
You can see that there is a NULL value in the CustomersId column. And now, if I don’t include the Where clause condition in the subquery then look what we get.
SELECT Name Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders)
As I said before, you will get an empty result.