LEFT JOIN execution behavior in SQL - Step-by-Step Execution

Concept Flow - LEFT JOIN execution behavior

Start with Left Table

↓

For each row in Left Table

↓

Find matching rows in Right Table

Yes No↓

Combine rows

↓

Add combined row to result

↓

Repeat for next Left row

↓

End

The LEFT JOIN goes through each row in the left table, tries to find matching rows in the right table, and combines them. If no match is found, it fills the right side with NULLs and still includes the left row.

Execution Sample

SQL

SELECT A.id, A.name, B.score
FROM Students A
LEFT JOIN Scores B ON A.id = B.student_id;

This query lists all students and their scores if available; if no score exists, it shows NULL.

Execution Table

Step	Left Table Row	Matching Right Rows Found?	Action	Output Row
1	id=1, name='Alice'	Yes (score=85)	Combine left and right rows	1, 'Alice', 85
2	id=2, name='Bob'	No	Combine left row with NULLs for right	2, 'Bob', NULL
3	id=3, name='Charlie'	Yes (score=92)	Combine left and right rows	3, 'Charlie', 92
4	id=4, name='Diana'	No	Combine left row with NULLs for right	4, 'Diana', NULL
5	No more rows	N/A	Stop execution	End of result set

💡 All rows from left table processed; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Current Left Row	None	id=1, name='Alice'	id=2, name='Bob'	id=3, name='Charlie'	id=4, name='Diana'	None
Matching Right Rows	None	score=85	None	score=92	None	None
Output Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

Why do some rows have NULL values in the right table columns?

When no matching row is found in the right table for a left table row, the right side columns are filled with NULLs to keep the left row in the result (see execution_table rows 2 and 4).

Does LEFT JOIN exclude any rows from the left table?

No, LEFT JOIN always includes all rows from the left table regardless of matches in the right table, as shown by all left rows appearing in the output (execution_table all steps).

Visual Quiz - 3 Questions

Look at the execution table, what is the output row at step 2?

A 2, 'Bob', 85

B NULL, NULL, NULL

C 2, 'Bob', NULL

D 2, 'Bob', 92

💡 Hint

Check the 'Output Row' column at step 2 in the execution_table.

At which step does the LEFT JOIN find no matching right table row for the left row?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Matching Right Rows Found?' column in the execution_table.

If the right table had a matching row for 'Diana', how would the output change at step 4?

A Output row would include Diana's score instead of NULL

B Output row would be removed

C Output row would have NULLs on both sides

D No change, still NULL for right columns

💡 Hint

Refer to how matched rows are combined in execution_table steps 1 and 3.

Concept Snapshot

LEFT JOIN syntax:
SELECT columns FROM LeftTable
LEFT JOIN RightTable ON condition;

Behavior:
- Includes all rows from LeftTable
- Matches rows from RightTable if condition met
- If no match, RightTable columns are NULL

Use to keep all left rows with optional right data.

Full Transcript

The LEFT JOIN starts by taking each row from the left table. For each left row, it looks for matching rows in the right table based on the join condition. If it finds matches, it combines the left and right rows into one output row. If no match is found, it still outputs the left row but fills the right side columns with NULL values. This process repeats for every row in the left table until all are processed. The result is a table that always includes all rows from the left table, with matching data from the right table when available, or NULLs otherwise.

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