LeetCode 183. Customers Who Never Order SQL Solution
Problem
LeetCode SQL Problem
- Customers Who Never Order
Customers table
Id | Name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
5 | Bob |
Orders table
Id | CustomerId |
---|---|
1 | 3 |
2 | 1 |
3 | 2 |
4 | 1 |
Solution - LEFT JOIN
- MySQL
- TSQL
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using LEFT JOIN
SELECT C.Name AS Customers
FROM Customers AS C
LEFT JOIN Orders AS O ON C.Id = O.CustomerId
WHERE O.Id IS NULL;
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using LEFT JOIN
SELECT C.Name AS Customers
FROM Customers AS C
LEFT JOIN Orders AS O ON C.Id = O.CustomerId
WHERE O.Id IS NULL;
Solution - EXISTS + Correlated Subquery
- MySQL
- TSQL
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using EXISTS and correlated subquery
SELECT C.Name AS Customers
FROM Customers AS C
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O
WHERE O.CustomerId = C.Id
)
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using EXISTS and correlated subquery
SELECT C.Name AS Customers
FROM Customers AS C
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O
WHERE O.CustomerId = C.Id
)
Solution - IN + Subquery
- MySQL
- TSQL
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using IN and Subquery
SELECT C.Name AS Customers
FROM Customers AS C
WHERE C.Id NOT IN (
SELECT O.CustomerId
FROM Orders AS O
)
Customers Who Never Order
-- Find Customer Id that does not appear in Orders table at all using IN and Subquery
SELECT C.Name AS Customers
FROM Customers AS C
WHERE C.Id NOT IN (
SELECT O.CustomerId
FROM Orders AS O
)
Query Output
Customers |
---|
Max |
Bob |