SQL Query – Find customer ID that are present in customers table and not in order tables

One of the very common business scenario in SQL is to find the record that exists in one table and does not exists in the other table. You can solve such questions in 4 different manners.

Problem Statement

You are running an online business in which customers can register on your website. As soon as they register, an entry is made into the "customer" table.
Whenever the customer places an order, an entry is made into the "orders" table.

Now the business team wishes to propose a discount coupon for the first time buyers. For this, they want the list of customer ID those have registered on the website but have not placed any order till date.

Below is the customer and orders table with sample records.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_price DECIMAL(10,2),
  order_date DATE
);

INSERT INTO customers VALUES (1, 'John', 'john@example.com');
INSERT INTO customers VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO customers VALUES (3, 'Bob', 'bob@example.com');
INSERT INTO customers VALUES (4, 'Emma', 'emma@example.com');
INSERT INTO customers VALUES (5, 'Olivia', 'olivia@example.com');
INSERT INTO customers VALUES (6, 'Thomas', 'thomas@example.com');
INSERT INTO customers VALUES (7, 'Noah', 'noah@example.com');
INSERT INTO customers VALUES (8, 'Benjamin', 'benjamin@example.com');
INSERT INTO customers VALUES (9, 'Sarah', 'sarah@example.com');
INSERT INTO customers VALUES (10, 'Rahul', 'rahul@example.com');
INSERT INTO customers VALUES (11, 'Dev', 'dev@example.com');

INSERT INTO orders VALUES (1, 1, 100.00, '2023-01-01');
INSERT INTO orders VALUES (2, 1, 50.00, '2023-01-01');
INSERT INTO orders VALUES (3, 2, 75.00, '2023-01-01');
INSERT INTO orders VALUES (4, 2, 25.00, '2023-01-01');
INSERT INTO orders VALUES (5, 2, 45.00, '2023-01-01');
INSERT INTO orders VALUES (6, 3, 15.00, '2023-01-01');
INSERT INTO orders VALUES (7, 4, 65.00, '2023-01-01');
INSERT INTO orders VALUES (8, 6, 5.00, '2023-01-01');
INSERT INTO orders VALUES (9, 6, 7.00, '2023-01-01');
INSERT INTO orders VALUES (10, 7, 15.00, '2023-01-01');
INSERT INTO orders VALUES (11, 8, 55.00, '2023-01-01');
INSERT INTO orders VALUES (12, 8, 75.00, '2023-01-01');
INSERT INTO orders VALUES (13, 10, 15.00, '2023-01-01');
INSERT INTO orders VALUES (14, 10, 70.00, '2023-01-01');
INSERT INTO orders VALUES (15, 10, 20.00, '2023-01-01');
INSERT INTO orders VALUES (16, 10, 45.00, '2023-01-01');

SQL Question

Find customers that are present in customers table and not in order tables.

There can be 4 common solutions for this problem. You can go with any of the solution explained below:

Solution 1

/*
Solution 1
Category: JOINS
Pattern: LEFT with NULL
*/

SELECT c.id
from customers c
left outer join
orders o
on
c.id = o.customer_id
where o.customer_id is null;

You can use the LEFT OUTER JOIN with NULL condition to get the required result.

Solution 2

/*
Solution 2
Category: Miscellaneous
Pattern: SET Operators
*/

select c.id from customers c
except
select o.customer_id from orders o;

You can also use SET OPERATORS like EXCEPT/MINUS to solve the SQL questions where you wish to find out records present in one table but missing in other table.

Solution 3

/*
Solution 3
Category: WHERE
Pattern: NOT IN
*/

select c.id from customers c
where c.id not in (
select o.customer_id from orders o
  );

You can use SUBQUERY using NOT IN to identify records missing in one table depending on the other table records.

Solution 4

/*
Solution 4
Category: WHERE
Pattern: NOT EXISTS
*/

select c.id from customers c
where not exists (
select 1 from orders o where o.customer_id = c.id
  );

You can also use NOT EXISTS to identify the records not present in one table by doing a lookup into other table records.

If you would have noticed I have categorised the solutions into "Category" & "Pattern". This is a very novel way of solving SQL queries. You just have to understand the Categories & Patterns in it. Then you can related to SQL problems easily and solve it using applicable pattern.
I have created an online course that teaches exactly this approach and how you can gain confidence in solving SQL queries using this sql pattern system.
If you are interested in upgrading your SQL skills, do check out the free course now.

Join the FREE Course Now