Write a SQL query to find the list of customers who never placed an order.

There are two tables.

• customers
• orders

Sample data:

USE TestDB;
GO

DROP TABLE IF EXISTS dbo.customers;
GO
CREATE TABLE dbo.customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
)
GO

DROP TABLE IF EXISTS dbo.orders;
GO
CREATE TABLE dbo.orders (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id)
)
GO

SET IDENTITY_INSERT dbo.customers ON; 
INSERT dbo.customers (customer_id, customer_name) VALUES (1, N'Sharon Salavaria');
INSERT dbo.customers (customer_id, customer_name) VALUES (2, N'Stephanie Conroy');
INSERT dbo.customers (customer_id, customer_name) VALUES (3, N'Christian Kleinerman');
INSERT dbo.customers (customer_id, customer_name) VALUES (4, N'Annette Hill');
INSERT dbo.customers (customer_id, customer_name) VALUES (5, N'Karen Berg');
INSERT dbo.customers (customer_id, customer_name) VALUES (6, N'Ramesh Meyyappan');
INSERT dbo.customers (customer_id, customer_name) VALUES (7, N'Chris Norred');
INSERT dbo.customers (customer_id, customer_name) VALUES (8, N'Linda Mitchell');
INSERT dbo.customers (customer_id, customer_name) VALUES (9, N'Zainal Arifin');
INSERT dbo.customers (customer_id, customer_name) VALUES (10, N'Kevin Brown');
SET IDENTITY_INSERT dbo.customers OFF;
GO

SET IDENTITY_INSERT dbo.orders ON; 
INSERT dbo.orders (order_id, customer_id) VALUES (1, 4);
INSERT dbo.orders (order_id, customer_id) VALUES (2, 5);
INSERT dbo.orders (order_id, customer_id) VALUES (3, 3);
INSERT dbo.orders (order_id, customer_id) VALUES (4, 6);
INSERT dbo.orders (order_id, customer_id) VALUES (5, 7);
INSERT dbo.orders (order_id, customer_id) VALUES (6, 3);
INSERT dbo.orders (order_id, customer_id) VALUES (7, 7);
INSERT dbo.orders (order_id, customer_id) VALUES (8, 8);
INSERT dbo.orders (order_id, customer_id) VALUES (9, 3);
SET IDENTITY_INSERT dbo.orders OFF;
GO

SELECT * FROM dbo.customers
SELECT * FROM dbo.orders

Solution

-- solution #1: using not exists
select c.customer_name as customers
from customers as c
where not exists (select 1 from orders as o where o.customer_id = c.customer_id)

-- solution #2: using left join
select c.customer_name as customers
from customers as c
left join orders as o on o.customer_id = c.customer_id
where o.customer_id is null;

-- solution #3: using not in
select c.customer_name as customers
from customers as c
where c.customer_id not in (select o.customer_id from orders as o)

The table contains very few records. Query execution time for all the three methods.

• not exists - 11ms
• left join - 82ms
• not in - 46ms