✅ SQL JOINS — Scenario-Based Interview Questions with Answers

Scenario 1: Find customers who have never placed an order

Tables: customers(customer_id, name) orders(order_id, customer_id);

Question: Business wants a list of customers with zero orders.

Answer:

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

Why this works
• LEFT JOIN keeps all customers
• Orders missing → NULL
• WHERE filters only non-ordering customers

Scenario 2: Get total revenue per customer, including customers with no orders

Question: Show every customer and their total spend. If no orders, show 0.

Answer:

SELECT c.customer_id, c.name, COALESCE(SUM(o.amount), 0) AS total_spend FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• LEFT JOIN keeps all customers
• SUM aggregates orders
• COALESCE converts NULL to 0

Scenario 3: Find orders that don’t have a matching customer

Question: Audit data to find orphan orders.

Answer:

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

Explanation
• LEFT JOIN from orders
• Missing customers become NULL
• Filters invalid data

Scenario 4: Get only customers who have placed at least one order

Question: Marketing wants only active customers.

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

Explanation
• INNER JOIN keeps only matching rows
• Customers without orders are excluded

Scenario 5: Find customers with more than 3 orders

Answer:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) > 3;

Explanation
• JOIN combines data
• GROUP BY customer
• HAVING filters aggregated count

Scenario 6: Show latest order for each customer

Answer:

SELECT c.customer_id, c.name, MAX(o.order_date) AS last_order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• JOIN connects customers and orders
• MAX finds latest order per customer

Scenario 7: Find customers who ordered in 2024 but not in 2025

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o2024 ON c.customer_id = o2024.customer_id LEFT JOIN orders o2025 ON c.customer_id = o2025.customer_id AND o2025.order_date >= '2025-01-01' WHERE o2024.order_date BETWEEN '2024-01-01' AND '2024-12-31' AND o2025.customer_id IS NULL;

Explanation
• INNER JOIN ensures 2024 orders
• LEFT JOIN checks absence in 2025
• NULL filter removes 2025 buyers

Scenario 8: Employee-Manager hierarchy (SELF JOIN)

Table: employees(employee_id, name, manager_id);

Answer:

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

Explanation
• Same table joined twice
• Shows reporting structure

Scenario 9: Revenue by city

Answer:

SELECT c.city, SUM(o.amount) AS revenue FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.city;

Explanation
• JOIN links customers to orders
• GROUP BY city
• SUM calculates revenue

Scenario 10: Duplicate explosion after JOIN (classic trap)

Question: Why does this query show inflated revenue?

SELECT SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

Answer:
• Customer table may have duplicates
• JOIN multiplies rows
• Revenue gets inflated

Fix:

SELECT SUM(amount) FROM orders; or deduplicate customers before joining.

Interview golden rule for JOINS
Always explain:
1️⃣ Which table is LEFT
2️⃣ Which table is RIGHT
3️⃣ What rows are kept
4️⃣ Where NULLs appear

Double Tap ♥️ For More