SQL Challenges-1: Customers without any Order

SQL Challenges-1: Exercise-6 with Solution

From the following tables, write a SQL query to find those customers who never ordered anything. Return customer name.

Input:

Table: customers

Structure:

Data:

Table: orders

structure:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE IF NOT EXISTS customers (customer_id int, customer_name varchar(255));
TRUNCATE TABLE customers;
INSERT INTO customers (customer_id, customer_name) VALUES ('101', 'Liam');
INSERT INTO customers (customer_id, customer_name) VALUES ('102', 'Josh');
INSERT INTO customers (customer_id, customer_name) VALUES ('103', 'Sean');
INSERT INTO customers (customer_id, customer_name) VALUES ('104', 'Evan');
INSERT INTO customers (customer_id, customer_name) VALUES ('105', 'Toby');	
CREATE TABLE IF NOT EXISTS orders (order_id int, customer_id int, order_date Date, order_amount int);
TRUNCATE TABLE orders;
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('401', '103','2012-03-08','4500');
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('402', '101','2012-09-15','3650');
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('403', '102','2012-06-27','4800');
SELECT * FROM customers;
SELECT * FROM orders;

SELECT customer_name as customers
FROM customers
WHERE customer_id NOT IN
(
SELECT customer_id FROM orders
);

Sample Output:

Customers|
---------|
Evan     |
Toby     |

Solution-1:

SELECT customer_name AS customers
FROM customers cus
LEFT JOIN orders ord
ON cus.customer_id = ord.customer_id
WHERE ord.customer_id IS NULL;

Solution-2:

SELECT customer_name AS Customers 
FROM customers WHERE 0 = 
(
SELECT COUNT(*) FROM orders 
WHERE customers.customer_id = orders.customer_id
);

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Duplicate Emails.
Next: Remove Duplicate Emails.