[Solved] You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?Customers(id, name)Orders(id, customer_id, order_date) | SQL

SQL - LEFT and RIGHT JOIN

You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?

Customers(id, name)
Orders(id, customer_id, order_date)

ASELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

BSELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders);

CSELECT c.name, o.order_date FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id;

DSELECT c.name, MAX(o.order_date) FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

Step-by-Step Solution

Solution:

Step 1: Understand requirement for all customers
We want all customers listed, even those without orders, so LEFT JOIN is needed.
Step 2: Aggregate last order date per customer
Using MAX(o.order_date) with GROUP BY c.name gives last order date or NULL if no orders.
Step 3: Check options
SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; uses LEFT JOIN and GROUP BY correctly. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders); filters in WHERE, excluding customers without orders. Options A and C use INNER JOIN, excluding customers without orders.
Final Answer:
SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; -> Option A
Quick Check:
LEFT JOIN + GROUP BY + MAX gets last order date including customers without orders [OK]

Quick Trick: Use LEFT JOIN with GROUP BY and MAX to include all left rows [OK]

Common Mistakes:

MISTAKES

Master "LEFT and RIGHT JOIN" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?