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.

Practice

1. What does a LEFT JOIN do in SQL?

easy

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

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

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

D. Returns rows only from the left table without any matching

Solution

1. Step 1: Understand LEFT JOIN behavior

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

2. Step 2: Check what happens when no match exists

   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 B

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 LEFT JOIN returns only matching rows
• Assuming NULLs never appear in results

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

easy

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

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

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

D. SELECT * FROM table1 LEFT JOIN table2 USING id;

Solution

1. Step 1: Recall correct LEFT JOIN syntax

   The correct syntax uses LEFT JOIN followed by ON clause to specify join condition.

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; uses correct syntax: LEFT JOIN with ON condition. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has incorrect order. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 LEFT JOIN table2 USING id; uses USING without parentheses, which is invalid syntax.

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN ... ON condition is standard syntax [OK]

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

Common Mistakes:

• Using WHERE instead of ON for join condition
• Swapping JOIN and LEFT keywords
• Confusing USING with ON without proper column names

3. Given these tables:
Employees(id, name)
Departments(id, dept_name, manager_id)
What will this query return?

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

medium

A. All employees with their department names if they are managers, NULL otherwise

B. Only employees who are managers with their department names

C. All departments with their managers' names

D. Only departments with managers matching employee ids

Solution

1. Step 1: Analyze LEFT JOIN condition

   The query LEFT JOINs Employees (left) with Departments (right) on employee id matching department manager_id.

2. Step 2: Understand output rows

   All employees appear. If an employee is a manager (id matches manager_id), department name shows; else department columns are NULL.

3. Final Answer:

   All employees with their department names if they are managers, NULL otherwise -> Option A

4. Quick Check:

   LEFT JOIN keeps all employees, adds department if manager [OK]

Hint: LEFT JOIN keeps all left rows, adds right data if matched [OK]

Common Mistakes:

• Thinking only managers appear in result
• Confusing which table is left or right
• Expecting departments without managers to appear

4. Identify the error in this SQL query:

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

medium

A. The ON clause is missing a join condition

B. The SELECT clause must include all columns from both tables

C. LEFT JOIN should be INNER JOIN for this query

D. The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   The WHERE clause filters rows after join. Filtering on b.value > 10 excludes rows where b.value is NULL.

2. Step 2: Effect on LEFT JOIN

   This filtering removes rows without matches in B, making LEFT JOIN behave like INNER JOIN.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect -> Option D

4. Quick Check:

   Filtering on right table in WHERE breaks LEFT JOIN [OK]

Hint: Use ON for right table filters, not WHERE, to keep LEFT JOIN effect [OK]

Common Mistakes:

• Filtering right table columns in WHERE after LEFT JOIN
• Confusing ON and WHERE clauses
• Assuming LEFT JOIN always keeps all left rows regardless of WHERE

5. You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?

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

hard

A. SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

B. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders);

C. SELECT c.name, o.order_date FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id;

D. SELECT c.name, MAX(o.order_date) FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand requirement for all customers

   We want all customers listed, even those without orders, so LEFT JOIN is needed.

2. Step 2: Aggregate last order date per customer

   Using MAX(o.order_date) with GROUP BY c.name gives last order date or NULL if no orders.

3. Step 3: Check options

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; uses LEFT JOIN and GROUP BY correctly. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders); filters in WHERE, excluding customers without orders. Options A and C use INNER JOIN, excluding customers without orders.

4. Final Answer:

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; -> Option A

5. Quick Check:

   LEFT JOIN + GROUP BY + MAX gets last order date including customers without orders [OK]

Hint: Use LEFT JOIN with GROUP BY and MAX to include all left rows [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Filtering right table in WHERE removes unmatched rows
• Not grouping when using aggregate functions