Why outer joins are needed in SQL - Visual Breakdown

Concept Flow - Why outer joins are needed

Start with two tables

↓

Perform INNER JOIN

↓

Check matching rows

Yes↓

Include matched rows

↓

Check unmatched rows

No↓

Exclude unmatched rows

↓

Result

↓

Outer Join

↓

Include matched rows

↓

Include unmatched rows with NULLs

↓

Result with all rows from one or both tables

This flow shows how inner joins only keep matched rows, while outer joins keep unmatched rows too by filling with NULLs.

Execution Sample

SQL

SELECT A.id, B.value
FROM A
LEFT JOIN B ON A.id = B.id;

This query returns all rows from table A, matching rows from B, and NULLs where no match exists.

Execution Table

Step	Action	Table A Row	Table B Row	Join Condition	Result Row
1	Check A.id=1 with B.id=1	A(1)	B(1)	1=1 True	(1, 'B1')
2	Check A.id=2 with B.id=2	A(2)	B(2)	2=2 True	(2, 'B2')
3	Check A.id=3 with B.id=3	A(3)	No B row	No match	(3, NULL)
4	No more rows in A	End	End	End	Query ends

💡 All rows from A processed; unmatched rows from A included with NULLs for B columns

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current A row	None	A(1)	A(2)	A(3)	End
Current B row	None	B(1)	B(2)	None	End
Result rows	Empty	(1, 'B1')	(1, 'B1'), (2, 'B2')	(1, 'B1'), (2, 'B2'), (3, NULL)	(1, 'B1'), (2, 'B2'), (3, NULL)

Key Moments - 2 Insights

Why does the third row from table A appear with NULLs in the result?

Because there is no matching row in table B for A.id=3, the LEFT JOIN includes it with NULLs for B columns, as shown in execution_table row 3.

Why wouldn't an INNER JOIN include the unmatched row from table A?

INNER JOIN only includes rows where the join condition is true. Since A.id=3 has no match in B, it is excluded, unlike the LEFT JOIN shown here.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result row at step 2?

A (3, 'B3')

B (2, NULL)

C (2, 'B2')

D (1, 'B1')

💡 Hint

Check the 'Result Row' column in execution_table at step 2

At which step does the join condition fail to find a matching row?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Join Condition' column in execution_table where it says 'No match'

If we changed the join to INNER JOIN, what would happen to the result rows?

A All rows from A would appear with NULLs for unmatched B rows

B Only rows with matching B rows would appear

C All rows from B would appear with NULLs for unmatched A rows

D No rows would appear

💡 Hint

Recall that INNER JOIN excludes unmatched rows, unlike LEFT JOIN shown in variable_tracker

Concept Snapshot

Outer joins include unmatched rows from one or both tables by filling missing columns with NULLs.
Syntax example: SELECT * FROM A LEFT JOIN B ON A.id = B.id;
Use outer joins when you want to keep all rows from a table even if no match exists.
Inner joins exclude unmatched rows, showing only matches.
Outer joins help answer questions like 'show me all customers, even those without orders.'

Full Transcript

This visual execution shows why outer joins are needed in SQL. When joining two tables, an inner join only keeps rows where the join condition matches in both tables. Rows without matches are excluded. Outer joins, like LEFT JOIN, keep all rows from one table and fill unmatched columns from the other table with NULLs. The example query selects all rows from table A and matches rows from table B by id. The execution table traces each row from A checked against B. For A.id=3, no matching B row exists, so the result includes (3, NULL). This shows how outer joins preserve unmatched rows, which inner joins do not. This is useful when you want to see all data from one table regardless of matches in the other.

Practice

1. Why do we need LEFT OUTER JOIN in SQL?

easy

A. To include all rows from the left table even if there is no matching row in the right table

B. To only show rows that have matching values in both tables

C. To delete rows from the left table

D. To update rows in the right table

Solution

1. Step 1: Understand the purpose of LEFT OUTER JOIN

   LEFT OUTER JOIN returns all rows from the left table and matched rows from the right table. If no match, NULLs appear for right table columns.

2. Step 2: Compare with INNER JOIN behavior

   INNER JOIN returns only rows with matching keys in both tables, excluding unmatched rows.

3. Final Answer:

   To include all rows from the left table even if there is no matching row in the right table -> Option A

4. Quick Check:

   LEFT OUTER JOIN shows all left rows [OK]

Hint: LEFT OUTER JOIN keeps all left rows, unmatched get NULLs [OK]

Common Mistakes:

• Confusing LEFT OUTER JOIN with INNER JOIN
• Thinking it deletes or updates rows
• Assuming it only shows matched rows

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

easy

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

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

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

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

Solution

1. Step 1: Recall correct LEFT OUTER JOIN syntax

   The correct syntax is: SELECT ... FROM table1 LEFT JOIN table2 ON condition; LEFT JOIN is shorthand for LEFT OUTER JOIN.

2. Step 2: Identify syntax errors in other options

   SELECT * FROM table1 JOIN LEFT OUTER table2 ON table1.id = table2.id; has JOIN LEFT OUTER which is invalid order. SELECT * FROM table1 OUTER LEFT JOIN table2 ON table1.id = table2.id; uses OUTER LEFT JOIN which is incorrect. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON for join condition.

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN ... ON ... is correct syntax [OK]

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

Common Mistakes:

• Swapping JOIN and LEFT keywords
• Using WHERE instead of ON for join condition
• Writing OUTER LEFT JOIN instead of LEFT OUTER JOIN

3. Given tables Employees and Departments where some employees have no department, what will this query return?

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

medium

A. All departments with employee names; NULL for departments without employees

B. Only employees who have a matching department

C. All employees with their department names; NULL for employees without a department

D. Only departments with employees

Solution

1. Step 1: Analyze LEFT JOIN behavior on Employees and Departments

   LEFT JOIN keeps all rows from Employees (left table). For employees without matching department, department columns show NULL.

2. Step 2: Understand output columns

   Query selects employee name and department name. Employees without department show NULL in department name.

3. Final Answer:

   All employees with their department names; NULL for employees without a department -> 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 side NULL [OK]

Common Mistakes:

• Thinking only matched rows appear
• Confusing left and right tables
• Expecting departments without employees to appear

4. What is wrong with this query if we want to list all customers and their orders, including customers with no orders?

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

medium

A. INNER JOIN excludes customers without orders; should use LEFT OUTER JOIN

B. The ON clause is missing

C. The SELECT statement is missing table aliases

D. Orders table should be first in the FROM clause

Solution

1. Step 1: Understand INNER JOIN behavior

   INNER JOIN returns only rows with matching keys in both tables, so customers without orders are excluded.

2. Step 2: Identify correct join for including all customers

   LEFT OUTER JOIN keeps all customers even if no matching orders exist, showing NULL for order columns.

3. Final Answer:

   INNER JOIN excludes customers without orders; should use LEFT OUTER JOIN -> Option A

4. Quick Check:

   Use LEFT OUTER JOIN to include all left table rows [OK]

Hint: Use LEFT OUTER JOIN to include unmatched left table rows [OK]

Common Mistakes:

• Using INNER JOIN when outer join is needed
• Forgetting ON clause (though present here)
• Thinking table order in FROM matters for join type

5. You have two tables: Students and Enrollments. Some students are not enrolled in any course. Which query correctly lists all students and their courses, showing NULL for students without enrollments?

hard

A. SELECT s.name, e.course FROM Students s INNER JOIN Enrollments e ON s.id = e.student_id;

B. SELECT s.name, e.course FROM Students s RIGHT OUTER JOIN Enrollments e ON s.id = e.student_id;

C. SELECT s.name, e.course FROM Enrollments e LEFT OUTER JOIN Students s ON s.id = e.student_id;

D. SELECT s.name, e.course FROM Students s LEFT OUTER JOIN Enrollments e ON s.id = e.student_id;

Solution

1. Step 1: Identify which table has all students

   Students table contains all students, including those without enrollments.

2. Step 2: Choose join to keep all students

   LEFT OUTER JOIN with Students as left table keeps all students, adding course info or NULL if no enrollment.

3. Step 3: Evaluate other options

   INNER JOIN excludes students without enrollments. RIGHT OUTER JOIN with Enrollments left excludes students without enrollments. LEFT OUTER JOIN with Enrollments left table excludes students without enrollments.

4. Final Answer:

   SELECT s.name, e.course FROM Students s LEFT OUTER JOIN Enrollments e ON s.id = e.student_id; -> Option D

5. Quick Check:

   LEFT OUTER JOIN keeps all left table rows [OK]

Hint: LEFT OUTER JOIN with Students on left keeps all students [OK]

Common Mistakes:

• Using INNER JOIN excludes students without courses
• Swapping left and right tables in join
• Using RIGHT OUTER JOIN incorrectly