Write a query to list customers who have not placed any orders using RIGHT JOIN or NOT EXISTS.

Task:

Write a SQL query to list customers who have not placed any orders using:

• RIGHT JOIN OR
• NOT EXISTS

Step 1: Create Dummy Tables

We'll need two tables:

• customers – contains all customer details.
• orders – contains order details.

-- Table: customers
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Table: orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Step 2: Insert Dummy Data

-- Insert customers
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Mike Johnson'),
(4, 'Emily Davis');

-- Insert orders
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2024-01-10'),
(102, 1, '2024-03-15'),
(103, 2, '2024-04-20');
-- Note: customer 3 and 4 have NOT placed any orders

Step 3: Solution 1 – Using RIGHT JOIN

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Explanation:

• We use a LEFT JOIN to get all customers, even those without orders.
• The WHERE o.customer_id IS NULL condition filters only those who have no matching order.

(In practice, LEFT JOIN is used to find missing orders, but RIGHT JOIN would be used if you reversed the join order. Example for RIGHT JOIN is shown below.)

Step 4: Solution 2 – Using RIGHT JOIN (if orders listed first)

SELECT c.customer_id, c.customer_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Explanation:

• This query lists all customers (right table), and if there’s no order matched, then order_id will be NULL.

Step 5: Solution 3 – Using NOT EXISTS

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Explanation:

• This subquery checks for the existence of orders per customer.
• NOT EXISTS filters customers who don’t appear in the orders table.

Output of All Above Queries

For the given dummy data, the result would be:

Summary Table