LEFT JOIN execution behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEFT JOIN with no matching rows

Given two tables Employees and Departments, what is the output of the following query?

SELECT Employees.Name, Departments.Name AS DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.ID
WHERE Departments.Name = 'Marketing';

Assume Employees has 3 rows with DeptID values 1, 2, and NULL, and Departments has IDs 1 and 2 but no department named 'Marketing'.

SQL

SELECT Employees.Name, Departments.Name AS DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.ID
WHERE Departments.Name = 'Marketing';

A The query returns an error because Departments.Name is NULL for some rows.

B No rows are returned because the WHERE clause filters out all rows where Departments.Name is NULL.

C Only employees with DeptID matching a department named 'Marketing' are returned.

D All employees are returned with DeptName as NULL because LEFT JOIN keeps all Employees rows.

💡 Hint

Remember that WHERE filters after the join, so conditions on the right table can exclude rows.

The LEFT JOIN keeps all Employees rows, but the WHERE clause filters rows where Departments.Name is 'Marketing'. Since no department has that name, Departments.Name is NULL for all rows, so the WHERE clause excludes all rows, resulting in no output.

❓ query_result

intermediate

LEFT JOIN with condition in ON clause vs WHERE clause

Consider the tables Orders and Customers. What is the difference in output between these two queries?

Query 1:

SELECT Orders.ID, Customers.Name
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.ID AND Customers.Status = 'Active';

Query 2:

SELECT Orders.ID, Customers.Name
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.ID
WHERE Customers.Status = 'Active';

SQL

SELECT Orders.ID, Customers.Name
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.ID AND Customers.Status = 'Active';

A Query 1 returns all orders with NULL for inactive customers; Query 2 returns only orders with active customers.

B Both queries return the same rows because the condition is applied in both cases.

C Query 1 returns only orders with active customers; Query 2 returns all orders with NULL for inactive customers.

D Query 1 returns an error due to condition in ON clause; Query 2 runs correctly.

💡 Hint

Think about when the condition is applied: during join or after join.

In Query 1, the condition on Customers.Status is part of the join condition, so unmatched or inactive customers cause NULLs but orders still appear. In Query 2, the WHERE clause filters after the join, removing rows where Customers.Status is not 'Active', so only orders with active customers appear.

📝 Syntax

advanced

Identify the syntax error in LEFT JOIN query

Which of the following LEFT JOIN queries will cause a syntax error?

A SELECT a.id, b.value FROM tableA a LEFT JOIN tableB b ON a.id = b.a_id;

B SELECT a.id, b.value FROM tableA a LEFT JOIN tableB b ON a.id = b.a_id WHERE b.value IS NOT NULL;

C SELECT * FROM tableA LEFT JOIN tableB ON tableA.id = tableB.a_id;

D SELECT * FROM tableA LEFT JOIN tableB ON tableA.id = tableB.a_id WHERE;

💡 Hint

Look carefully at the WHERE clause syntax.

Option D has a WHERE clause with no condition after it, which is a syntax error. The other queries have correct syntax.

❓ optimization

advanced

Optimizing LEFT JOIN with large tables

You have two large tables: Sales (millions of rows) and Products. You want to list all sales with product names, including sales with missing product info. Which approach is more efficient?

A Use LEFT JOIN with condition on Products in ON clause to filter products.

B Use INNER JOIN to only get sales with matching products.

C Use LEFT JOIN with condition on Products in WHERE clause to filter products.

D Use CROSS JOIN and filter in WHERE clause.

💡 Hint

Filtering in ON clause can reduce rows early in join.

Filtering conditions on the right table should be in the ON clause for LEFT JOIN to avoid turning it into an INNER JOIN and to optimize performance by reducing rows during join.

🧠 Conceptual

expert

Understanding NULL propagation in LEFT JOIN

In a LEFT JOIN between Table1 and Table2, if a row in Table1 has no matching row in Table2, what will be the value of columns from Table2 in the result?

A They will contain zero or empty string depending on data type.

B They will contain default values defined in Table2 schema.

C They will contain NULL values.

D They will contain values from the previous matched row.

💡 Hint

Think about what LEFT JOIN means for unmatched rows.

LEFT JOIN returns all rows from the left table and fills columns from the right table with NULL when there is no match.