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.

Practice

1. What does a LEFT JOIN combined with WHERE right_table.key IS NULL do in SQL?

easy

A. Finds rows in the right table that have no matching rows in the left table

B. Finds rows in the left table that have no matching rows in the right table

C. Returns all rows from both tables regardless of matches

D. Deletes unmatched rows from the left table

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, right table columns are NULL.

2. Step 2: Apply WHERE condition to filter unmatched rows

   Filtering with WHERE right_table.key IS NULL selects only those left table rows without a match in the right table.

3. Final Answer:

   Finds rows in the left table that have no matching rows in the right table -> Option B

4. Quick Check:

   LEFT JOIN + IS NULL = unmatched left rows [OK]

Hint: LEFT JOIN + IS NULL filters unmatched left table rows [OK]

Common Mistakes:

• Confusing unmatched rows as from right table
• Using INNER JOIN instead of LEFT JOIN
• Not checking for NULL in right table columns

2. Which of the following SQL queries correctly finds customers without orders using LEFT JOIN?

easy

A. SELECT c.id FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id WHERE c.id IS NULL;

B. SELECT c.id FROM customers c INNER JOIN orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL;

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

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

Solution

1. Step 1: Identify correct JOIN type

   LEFT JOIN keeps all customers and matches orders; unmatched orders will be NULL.

2. Step 2: Filter unmatched orders

   WHERE o.customer_id IS NULL selects customers without orders.

3. Final Answer:

   SELECT c.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL; -> Option D

4. Quick Check:

   LEFT JOIN + right table NULL = unmatched left rows [OK]

Hint: Use LEFT JOIN and check right table key IS NULL [OK]

Common Mistakes:

• Using INNER JOIN which excludes unmatched rows
• Checking NULL on left table columns
• Using RIGHT JOIN incorrectly for this case

3. Given tables employees(id, name) and tasks(employee_id, task_name), what does this query return?

SELECT e.name FROM employees e LEFT JOIN tasks t ON e.id = t.employee_id WHERE t.employee_id IS NULL;

medium

A. Names of employees who have no tasks assigned

B. Names of employees who have at least one task

C. All employee names regardless of tasks

D. Names of tasks without employees

Solution

1. Step 1: Analyze LEFT JOIN and ON condition

   The query joins employees with tasks on employee ID, keeping all employees.

2. Step 2: Filter rows where tasks are missing

   WHERE t.employee_id IS NULL selects employees with no matching tasks.

3. Final Answer:

   Names of employees who have no tasks assigned -> Option A

4. Quick Check:

   LEFT JOIN + NULL in right table = unmatched left rows [OK]

Hint: LEFT JOIN + right key NULL = unmatched left rows [OK]

Common Mistakes:

• Thinking it returns employees with tasks
• Confusing NULL check on left table
• Assuming INNER JOIN behavior

4. Identify the error in this query intended to find products without sales:

SELECT p.product_id FROM products p LEFT JOIN sales s ON p.product_id = s.product_id WHERE p.product_id IS NULL;

medium

A. ON condition is incorrect, should join on sales_id

B. LEFT JOIN should be INNER JOIN

C. The WHERE clause should check s.product_id IS NULL, not p.product_id

D. Query is correct and will return unmatched products

Solution

1. Step 1: Understand LEFT JOIN result

   LEFT JOIN keeps all products; unmatched sales columns are NULL.

2. Step 2: Check WHERE clause correctness

   Filtering on p.product_id IS NULL is wrong because left table columns are never NULL in LEFT JOIN; should check s.product_id IS NULL.

3. Final Answer:

   The WHERE clause should check s.product_id IS NULL, not p.product_id -> Option C

4. Quick Check:

   Filter NULL on right table columns, not left [OK]

Hint: Check NULL on right table columns after LEFT JOIN [OK]

Common Mistakes:

• Checking NULL on left table columns
• Using INNER JOIN instead of LEFT JOIN
• Incorrect ON join condition

5. You have tables students(id, name) and enrollments(student_id, course_id). Write a query to find students not enrolled in any course, considering some students may have NULL IDs. Which query correctly handles this?

hard

A. SELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL AND s.id IS NOT NULL;

B. SELECT s.name FROM students s RIGHT JOIN enrollments e ON s.id = e.student_id WHERE s.id IS NULL;

C. SELECT s.name FROM students s INNER JOIN enrollments e ON s.id = e.student_id WHERE s.id IS NULL;

D. SELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL;

Solution

1. Step 1: Use LEFT JOIN to find unmatched students

   LEFT JOIN students with enrollments keeps all students; unmatched enrollments are NULL.

2. Step 2: Filter students without enrollments and exclude NULL student IDs

   WHERE e.student_id IS NULL finds students without courses; adding s.id IS NOT NULL excludes students with NULL IDs to avoid incorrect matches.

3. Final Answer:

   SELECT s.name FROM students s LEFT JOIN enrollments e ON s.id = e.student_id WHERE e.student_id IS NULL AND s.id IS NOT NULL; -> Option A

4. Quick Check:

   LEFT JOIN + right NULL + exclude NULL left keys = correct unmatched [OK]

Hint: Exclude NULL keys on left table when filtering unmatched [OK]

Common Mistakes:

• Ignoring NULL IDs in left table
• Using INNER JOIN which excludes unmatched rows
• Using RIGHT JOIN incorrectly