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

Carvia Tech | May 27, 2019 | 2 min read | 1 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. Top 20 Java Concurrency Interview Questions and Answers
  2. Cracking core java interviews - question bank
  3. ION Trading Java Interview Questions
  4. Citibank Java developer interview questions
  5. Sapient Global Market Java Interview Questions and Coding Exercise
  6. Morgan Stanley Investment Banking Java Interview Questions
  7. Top 50 Multi-threading Java Interview Questions for Investment Bank



Find more on this topic:
Java Interviews image
Java Interviews

Interview - Product Companies, eCommerce Companies, Investment Banking, Healthcare Industry, Service Companies and Startups.

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info