SQL - Write a query to find customers who have no orders yet
Upasana | May 27, 2019 | 2 min read | 107 views
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`)
)
id | name | |
---|---|---|
1 |
Customer 1 |
|
2 |
Customer 2 |
|
3 |
Customer 3 |
|
4 |
Customer 4 |
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`)
)
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;
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:
- Write a program to swap two numbers in Java
- Must have tool, technologies & skills for a Java Developer
- Cracking core java interviews - question bank
- Citibank Java developer interview questions
- SQL to find nth highest salary
- ION Trading Java Interview Questions
- There is no PasswordEncoder mapped for the id
Recommended books for interview preparation:
Book you may be interested in..
Book you may be interested in..