Finding unmatched rows with LEFT JOIN in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find customers without orders

Given two tables, Customers and Orders, which query returns all customers who have never placed an order?

SQL

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

A SELECT c.id, c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.id IS NULL;

B SELECT c.id, c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE c.id IS NULL;

C SELECT c.id, c.name FROM Customers c RIGHT JOIN Orders o ON c.id = o.customer_id WHERE o.id IS NULL;

D SELECT c.id, c.name FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id WHERE o.id IS NULL;

💡 Hint

Use LEFT JOIN to keep all customers and check for missing orders by filtering NULLs in the joined table.

LEFT JOIN keeps all rows from Customers. If a customer has no matching order, the Orders columns are NULL. Filtering WHERE o.id IS NULL finds those customers without orders.

❓ query_result

intermediate

Identify products never sold

Which SQL query lists all products that have never been sold, given tables Products and Sales?

SQL

Products(product_id, product_name)
Sales(sale_id, product_id, quantity)

A SELECT p.product_id, p.product_name FROM Products p INNER JOIN Sales s ON p.product_id = s.product_id WHERE s.sale_id IS NULL;

B SELECT p.product_id, p.product_name FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id WHERE s.sale_id IS NULL;

C SELECT p.product_id, p.product_name FROM Products p RIGHT JOIN Sales s ON p.product_id = s.product_id WHERE s.sale_id IS NULL;

D SELECT p.product_id, p.product_name FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id WHERE p.product_id IS NULL;

💡 Hint

Use LEFT JOIN and check for NULL in the Sales table to find products without sales.

LEFT JOIN keeps all products. If a product has no matching sale, s.sale_id is NULL. Filtering WHERE s.sale_id IS NULL returns products never sold.

📝 Syntax

advanced

Identify syntax error in LEFT JOIN query

Which option contains a syntax error when trying to find unmatched rows using LEFT JOIN?

SQL

Tables: Employees(emp_id, name), Projects(proj_id, emp_id)

A ELECT e.emp_id, e.name FROM Employees e LEFT JOIN Projects p ON e.emp_id = p.emp_id WHERE p.proj_id IS NULL;

B SELECT e.emp_id, e.name FROM Employees e LEFT JOIN Projects p ON e.emp_id = p.emp_id WHERE p.proj_id IS NULL;

C ;LLUN SI di_jorp.p EREHW di_pme.p = di_pme.e NO p stcejorP NIOJ TFEL e seeyolpmE MORF eman.e ,di_pme.e TCELES

D SELECT e.emp_id, e.name FROM Employees e LEFT JOIN Projects p ON e.emp_id = p.emp_id WHERE p IS NULL;

💡 Hint

Check if the WHERE clause references a valid column or table alias.

In option D, 'WHERE p IS NULL' is invalid because 'p' is a table alias, not a column. You must check a column like p.proj_id for NULL.

❓ optimization

advanced

Optimize query to find unmatched rows

Which query is the most efficient way to find customers without orders, assuming indexes on customer_id?

SQL

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

A SELECT c.id, c.name FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id WHERE o.id IS NULL;

B SELECT c.id, c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.id IS NULL;

C SELECT c.id, c.name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id);

D SELECT c.id, c.name FROM Customers c WHERE c.id NOT IN (SELECT customer_id FROM Orders);

💡 Hint

NOT EXISTS often performs better than LEFT JOIN with NULL check for unmatched rows.

Option C uses NOT EXISTS which can be optimized by the database engine using indexes efficiently. Option C works but may be slower. Option C can be slower and has issues with NULLs. Option C is incorrect logic.

🧠 Conceptual

expert

Understanding LEFT JOIN behavior with unmatched rows

What will be the output of this query?

Tables:
Authors(author_id, name)
Books(book_id, author_id, title)

Query:
SELECT a.name, b.title FROM Authors a LEFT JOIN Books b ON a.author_id = b.author_id WHERE b.book_id IS NULL;

SQL

Authors:
1, 'Alice'
2, 'Bob'
3, 'Carol'

Books:
10, 1, 'Book A'
11, 1, 'Book B'
12, 3, 'Book C'

A [{'name': 'Bob', 'title': NULL}]

B [{'name': 'Carol', 'title': NULL}]

C [{'name': 'Alice', 'title': NULL}]

D []

💡 Hint

LEFT JOIN keeps all authors. WHERE b.book_id IS NULL filters authors with no books.

Bob (author_id 2) has no books in Books table, so LEFT JOIN produces NULL for b.book_id. The query returns Bob with title NULL.