SQL Coding Exercise – Customers Who Never Order

This SQL coding exercise is from oj-online-judge: https://oj.leetcode.com/problems/customers-who-never-order/

Two tables: the Customers table and the Orders table. Write a SQL query to find all customers who does not order anything.

Table: Customers.
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Mario |
| 3  | Sam   |
| 4  | Bob   |
+----+-------+

Table: Orders.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Mario     |
| Bob       |
+-----------+

Using sub-query, checking if CustomerId appears in the Orders table.

# 717ms
select `Name` from `Customers` where `Id` not in (select `CustomerId` from `Orders`)

We can also extend the nested select but it tends to be a bit slower.

# 794ms
SELECT `Name` FROM `Customers` WHERE `Customers`.`Id` NOT IN (
  SELECT `c`.`Id` FROM `Customers` as `c` 
  INNER JOIN `Orders` as `o` ON `c`.`Id` = `o`.`CustomerId`) 

Using exists keyword on sub query also works:

# 707ms
SELECT `Name`
FROM `Customers`
WHERE NOT EXISTS (
        SELECT 1 FROM `Orders`
        WHERE `Customers`.`id` = `Orders`.`CustomerId`);

–EOF (The Ultimate Computing & Technology Blog) —