SQL implementation of LeetCode (183. Customers who have never placed an order)

[LeetCode] 183.Customers Who Never Order

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.

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

This question gives us a Customers table and an Orders table. Let us find customers who have never placed an order. Then our most direct method is to find the customer ID that does not appear in the Orders table, using the Not in keyword, as shown below:

Solution 1:

SELECT Name AS Customers FROM Customers 
WHERE Id NOT IN (SELECT CustomerId FROM Orders);

Or we can also use a left join to join the two tables. We just need to find out the customers whose CustomerId on the right is Null, which means they have not placed an order:

Solution 2:

SELECT Name AS Customers FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;

We can also use the Not exists keyword, which works similarly to Not in. See the following code:

Solution 3:

SELECT Name AS Customers FROM Customers c
WHERE NOT EXISTS (SELECT * FROM Orders o WHERE o.CustomerId = c.Id);

References:

https://leetcode.com/discuss/22624/three-accepted-solutions

https://leetcode.com/discuss/53213/a-solution-using-not-in-and-another-one-using-left-join

This is the end of the article about SQL implementation of LeetCode (182. Customers who have never placed an order). For more relevant content about SQL implementation of customers who have never placed an order, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!