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.