LeetCode 183. Customers Who Never Order SQL Solution

Problem

LeetCode SQL Problem

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

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;

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;

Solution - EXISTS + Correlated Subquery

MySQL

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
		)

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
		)

Solution - IN + Subquery

MySQL

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
		)

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
		)

Query Output

Customers
Max
Bob