LEFT JOIN in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this LEFT JOIN query?

Given two tables, Employees and Departments, what will be the result of the following query?

SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.id
ORDER BY Employees.id;

Tables:

Employees
id | name | department_id
1 | Alice | 1
2 | Bob | 2
3 | Charlie | NULL

Departments
id | department_name
1 | HR
2 | IT
3 | Marketing

MySQL

SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.id
ORDER BY Employees.id;

A [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": "IT"}, {"name": "Charlie", "department_name": null}]

B [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": "IT"}]

C [{"name": "Alice", "department_name": "HR"}, {"name": "Bob", "department_name": "IT"}, {"name": "Charlie", "department_name": "Marketing"}]

D [{"name": "Alice", "department_name": null}, {"name": "Bob", "department_name": null}, {"name": "Charlie", "department_name": null}]

💡 Hint

Remember, LEFT JOIN keeps all rows from the left table and matches rows from the right table if possible.

The LEFT JOIN returns all employees. For those with a matching department_id, it shows the department name. For Charlie, who has NULL department_id, the department_name is NULL.

🧠 Conceptual

intermediate

Which statement best describes a LEFT JOIN?

Choose the correct description of what a LEFT JOIN does in SQL.

A Returns all rows from the right table and matching rows from the left table; unmatched left table rows are excluded.

B Returns only rows that have matching values in both tables.

C Returns all rows from the left table and matching rows from the right table; unmatched right table rows show NULL values.

D Returns all rows from the left table and matching rows from the right table; unmatched right table rows are excluded.

💡 Hint

Think about which table's rows are always kept and what happens when there is no match.

LEFT JOIN keeps all rows from the left table. If there is no matching row in the right table, the right table columns show NULL.

📝 Syntax

advanced

Identify the syntax error in this LEFT JOIN query

Which option contains a syntax error in the LEFT JOIN SQL query?

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.id;

A SELECT e.name, d.department_name FROM Employees e LEFT JOIN Departments d e.department_id = d.id;

B ;di.d = di_tnemtraped.e NO d stnemtrapeD NIOJ TFEL e seeyolpmE MORF eman_tnemtraped.d ,eman.e TCELES

C SELECT e.name, d.department_name FROM Employees e LEFT JOIN Departments d ON e.department_id = d.id;

D SELECT e.name, d.department_name FROM Employees e LEFT JOIN Departments d ON e.department_id = d.id

💡 Hint

Look for missing keywords or punctuation in the JOIN clause.

Option A is missing the ON keyword before the join condition, causing a syntax error.

❓ optimization

advanced

How to optimize a LEFT JOIN query with large tables?

You have two large tables, Orders and Customers. You want to LEFT JOIN them on customer_id. Which option will improve query performance?

A Use SELECT * to get all columns from both tables.

B Add an index on Orders.customer_id and Customers.id columns.

C Remove the ON clause and use WHERE Orders.customer_id = Customers.id.

D Use a CROSS JOIN instead of LEFT JOIN.

💡 Hint

Indexes help speed up lookups in join conditions.

Adding indexes on the join columns helps the database find matching rows faster, improving LEFT JOIN performance.

🔧 Debug

expert

Why does this LEFT JOIN query return fewer rows than expected?

Consider these tables:

Products
id | name
1 | Pen
2 | Pencil
3 | Eraser

Sales
id | product_id | quantity
1 | 1 | 10
2 | 2 | 5

Query:

SELECT Products.name, Sales.quantity
FROM Products
LEFT JOIN Sales ON Products.id = Sales.product_id
WHERE Sales.quantity > 0;

Why does this query return only 2 rows instead of 3?

A The ON condition is incorrect and excludes some rows.

B LEFT JOIN does not include unmatched rows from the left table.

C The Products table has missing rows.

D The WHERE clause filters out rows where Sales.quantity is NULL, excluding products without sales.

💡 Hint

Think about how WHERE affects rows with NULL values after a LEFT JOIN.

The WHERE clause filters rows after the join. Since unmatched Sales rows have NULL quantity, the condition Sales.quantity > 0 excludes them, reducing rows.

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Deletes rows from the left table that do not have matches in the right table.

B. Returns only rows that have matching values in both tables.

C. Returns all rows from the right table and matching rows from the left table.

D. Returns all rows from the left table and matching rows from the right table, NULL if no match.

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN keeps all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens with unmatched rows

   If no matching row exists in the right table, the result shows NULL for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, NULL if no match. -> Option D

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

Hint: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN
• Thinking it returns all right table rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a LEFT JOIN in MySQL?

easy

A. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id;

Solution

1. Step 1: Review standard LEFT JOIN syntax

   The correct syntax uses LEFT JOIN followed by ON condition to specify matching columns.

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; uses correct syntax: LEFT JOIN with ON clause. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has JOIN LEFT which is invalid. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, which is incorrect for JOIN condition. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, invalid syntax.

3. Final Answer:

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   LEFT JOIN syntax = LEFT JOIN ... ON ... [OK]

Hint: Use LEFT JOIN ... ON ... for correct syntax [OK]

Common Mistakes:

• Using JOIN LEFT instead of LEFT JOIN
• Using WHERE instead of ON for join condition
• Putting join condition directly in WHERE after table

3. Given these tables:

Employees(id, name)
Departments(id, dept_name, emp_id)

What is the output of this query?

SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.id = d.emp_id ORDER BY e.id;

Assuming:
Employees: (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
Departments: (10, 'HR', 1), (20, 'IT', 3)

medium

A. [('Alice', NULL), ('Bob', NULL), ('Carol', NULL)]

B. [('Alice', 'HR'), ('Bob', 'IT'), ('Carol', NULL)]

C. [('Alice', 'HR'), ('Bob', NULL), ('Carol', 'IT')]

D. [('Alice', 'HR'), ('Carol', 'IT')]

Solution

1. Step 1: Match Employees with Departments using LEFT JOIN

   LEFT JOIN keeps all Employees. For each employee, it tries to find a matching department where e.id = d.emp_id.

2. Step 2: Check matches and NULLs

   'Alice' (id=1) matches department 'HR' (emp_id=1). 'Bob' (id=2) has no matching department, so dept_name is NULL. 'Carol' (id=3) matches 'IT' (emp_id=3).

3. Final Answer:

   [('Alice', 'HR'), ('Bob', NULL), ('Carol', 'IT')] -> Option C

4. Quick Check:

   LEFT JOIN keeps all left rows, unmatched right columns NULL [OK]

Hint: LEFT JOIN shows all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are dropped
• Mixing up emp_id and id columns
• Ignoring ORDER BY effect

4. Identify the error in this LEFT JOIN query:

SELECT a.id, b.value FROM tableA a LEFT JOIN tableB b ON a.id = b.id WHERE b.value > 10;

medium

A. The WHERE clause filters out rows where b.value is NULL, defeating LEFT JOIN purpose.

B. The ON clause is missing a join condition.

C. LEFT JOIN should be replaced with INNER JOIN for this query.

D. The SELECT statement is missing table aliases.

Solution

1. Step 1: Understand effect of WHERE on LEFT JOIN

   LEFT JOIN keeps all rows from tableA, but WHERE b.value > 10 excludes rows where b.value is NULL (no match), effectively turning it into INNER JOIN.

2. Step 2: Identify the error

   The error is using WHERE to filter on right table columns after LEFT JOIN, which removes unmatched rows.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, defeating LEFT JOIN purpose. -> Option A

4. Quick Check:

   WHERE on right table column after LEFT JOIN removes unmatched rows [OK]

Hint: Use ON for filtering right table, not WHERE, to keep unmatched rows [OK]

Common Mistakes:

• Using WHERE instead of ON for right table filters
• Confusing LEFT JOIN with INNER JOIN
• Ignoring NULLs in join columns

5. You have two tables:

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

You want to list all products with total quantity sold, including products with zero sales.
Which query correctly uses LEFT JOIN to achieve this?

hard

A. SELECT p.name, SUM(s.quantity) FROM Products p INNER JOIN Sales s ON p.product_id = s.product_id GROUP BY p.product_id;

B. SELECT p.name, SUM(s.quantity) FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id GROUP BY p.product_id;

C. SELECT p.name, SUM(s.quantity) FROM Sales s LEFT JOIN Products p ON p.product_id = s.product_id GROUP BY p.product_id;

D. SELECT p.name, COUNT(s.sale_id) FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id WHERE s.quantity > 0 GROUP BY p.product_id;

Solution

1. Step 1: Use LEFT JOIN from Products to Sales

   To include all products even with no sales, LEFT JOIN Sales to Products on product_id.

2. Step 2: Aggregate sales quantity and group by product

   Use SUM(s.quantity) to total sales per product. Group by p.product_id to get one row per product.

3. Final Answer:

   SELECT p.name, SUM(s.quantity) FROM Products p LEFT JOIN Sales s ON p.product_id = s.product_id GROUP BY p.product_id; -> Option B

4. Quick Check:

   LEFT JOIN + SUM + GROUP BY lists all products with total sales [OK]

Hint: LEFT JOIN products to sales, SUM quantity, GROUP BY product [OK]

Common Mistakes:

• Using INNER JOIN excludes products with zero sales
• Joining Sales to Products reverses LEFT JOIN logic
• Filtering in WHERE removes unmatched rows