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. Citibank Java developer interview questions
  4. Sapient Global Market Java Interview Questions and Coding Exercise
  5. Morgan Stanley Investment Banking Java Interview Questions
  6. Top 50 Multi-threading Java Interview Questions for Investment Bank
  7. RBS Java Programming Interview Questions



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 month ago


Recommended books for interview preparation:

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