SQL - Write a query to find customers who have no orders yet

Carvia Tech | May 27, 2019 | 2 min read | 107 views


left outer join 3
Customers without Orders

Below are the table definitions for Customer and Order Table.

Customer Table DDL
CREATE TABLE `t_customer` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
Select * from t_customer;
id name email

1

Customer 1

cust-1@mail.com

2

Customer 2

cust-2@mail.com

3

Customer 3

cust-3@mail.com

4

Customer 4

cust-4@mail.com

Order Table DDL
CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL,
  `cust_id` bigint(20) NOT NULL,
  `amount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK3oltq1wdwn8hskut0cgb0nd4x` FOREIGN KEY (`cust_id`) REFERENCES `t_customer` (`id`)
)
Select * from t_order;
id cust_id amount

1

1

10

2

3

20

Now we can easily find all those customers who do not have any orders yet using the below SQL query.

SQL - Customers who have no orders yet
SELECT
    c.id, c.name
FROM
    Customer c
        LEFT OUTER JOIN
    Orders o ON c.id = o.cust_id
WHERE
    o.cust_id IS NULL;
Result
c.id c.name

2

Customer 2

4

Customer 4

Customer with id 2 and 4 have not placed any orders yet.

The same could be fetched using the below SQL query but in an inefficient manner:

SQL - Inefficient query to find customers without orders
SELECT
    *
FROM
    Customer c
WHERE
    c.id NOT IN (SELECT
            o.cust_id
        FROM
            Orders o);

That’s all.


Top articles in this category:
  1. Write a program to swap two numbers in Java
  2. Must have tool, technologies & skills for a Java Developer
  3. Cracking core java interviews - question bank
  4. Citibank Java developer interview questions
  5. SQL to find nth highest salary
  6. ION Trading Java Interview Questions
  7. There is no PasswordEncoder mapped for the id

Find more on this topic:

Recommended books for interview preparation: