LEFT JOIN and RIGHT JOIN in PostgreSQL - Step-by-Step Execution

Concept Flow - LEFT JOIN and RIGHT JOIN

Start with Table A

↓

Join with Table B

↓

LEFT JOIN: Keep all rows from A

↓

Match rows from B or NULL if no match

↓

RIGHT JOIN: Keep all rows from B

↓

Match rows from A or NULL if no match

↓

Result Table with combined rows

LEFT JOIN keeps all rows from the left table and matches rows from the right table or fills NULL if no match. RIGHT JOIN keeps all rows from the right table and matches rows from the left table or fills NULL if no match.

Execution Sample

PostgreSQL

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

This query returns all rows from table A with matching scores from table B or NULL if no match.

Execution Table

Step	Table A Row	Table B Row Matched?	Output Row	Notes
1	A.id=1, name='Alice'	B.id=1, score=85	1, Alice, 85	Match found, output combined row
2	A.id=2, name='Bob'	No match	2, Bob, NULL	No match in B, output NULL for B columns
3	A.id=3, name='Carol'	B.id=3, score=92	3, Carol, 92	Match found, output combined row
4	End of A rows			LEFT JOIN ends after all A rows processed

💡 All rows from Table A processed; LEFT JOIN includes unmatched rows with NULLs

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current A row	None	id=1, name='Alice'	id=2, name='Bob'	id=3, name='Carol'	End
Matched B row	None	id=1, score=85	None	id=3, score=92	End
Output row	None	1, Alice, 85	2, Bob, NULL	3, Carol, 92	End

Key Moments - 2 Insights

Why does the output include a row with NULL values for B columns?

Because LEFT JOIN keeps all rows from the left table (A). When there is no matching row in B (see step 2 in execution_table), it fills B columns with NULL.

What happens if we switch to RIGHT JOIN?

RIGHT JOIN keeps all rows from the right table (B). Rows in B without matching A rows will appear with NULLs in A columns. This is the opposite of LEFT JOIN.

Visual Quiz - 3 Questions

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

A 2, Bob, 85

B 2, Bob, NULL

C NULL, Bob, NULL

D 2, NULL, NULL

💡 Hint

Check the Output Row column at step 2 in execution_table

At which step does the LEFT JOIN output a row with no matching B row?

A Step 2

B Step 3

C Step 1

D No such step

💡 Hint

Look for 'No match' in Table B Row Matched? column in execution_table

If we change the query to RIGHT JOIN, which table's rows will all appear in the output?

A All rows from Table A

B Only matching rows from both tables

C All rows from Table B

D No rows at all

💡 Hint

Refer to concept_flow description about RIGHT JOIN behavior

Concept Snapshot

LEFT JOIN: Returns all rows from left table + matched rows from right table or NULL if no match.
RIGHT JOIN: Returns all rows from right table + matched rows from left table or NULL if no match.
Use ON clause to specify join condition.
Unmatched rows fill missing side columns with NULL.
LEFT and RIGHT JOIN differ by which table's rows are fully kept.

Full Transcript

This visual execution shows how LEFT JOIN and RIGHT JOIN work in SQL. LEFT JOIN keeps all rows from the left table and matches rows from the right table. If no match is found, it fills the right table columns with NULL. RIGHT JOIN does the opposite, keeping all rows from the right table and matching left table rows or filling NULL if no match. The example query selects from table A LEFT JOIN table B on id. The execution table traces each row of A, shows if a matching B row exists, and the output row. For example, at step 2, A row with id=2 has no matching B row, so output includes NULL for B columns. Variable tracker shows how current rows and output change step by step. Key moments clarify why NULLs appear and the difference between LEFT and RIGHT JOIN. The quiz tests understanding of output rows and join behavior. The snapshot summarizes the main points for quick reference.

Practice

1. What does a LEFT JOIN do in PostgreSQL?

easy

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

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

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

D. Returns all rows from both tables, matching where possible.

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 with unmatched rows

   If there is no matching row in the right table, the result includes NULLs for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, or NULL if no match. -> Option A

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 RIGHT JOIN
• Thinking LEFT JOIN returns only matched rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a RIGHT JOIN in PostgreSQL?

easy

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

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

C. SELECT * FROM table1 RIGHT OUTER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 RIGHT JOIN ON table1.id = table2.id table2;

Solution

1. Step 1: Recall correct RIGHT JOIN syntax

   The correct syntax is: SELECT ... FROM left_table RIGHT JOIN right_table ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   RIGHT JOIN syntax = FROM left_table RIGHT JOIN right_table ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left_table RIGHT JOIN right_table ON condition [OK]

Common Mistakes:

• Placing JOIN keyword incorrectly
• Using WHERE instead of ON for join condition
• Misordering table names

3. Given these tables:

employees
id | name
1 | Alice
2 | Bob
3 | Carol

departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.id = departments.id ORDER BY employees.id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B. [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Understand LEFT JOIN with given tables

   LEFT JOIN keeps all employees. For each employee, it tries to find a department with matching id.

2. Step 2: Match rows and fill NULLs

   Alice (id=1) has no matching department, so dept_name is NULL. Bob (id=2) matches Sales. Carol (id=3) matches HR.

3. Final Answer:

   [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}] -> Option C

4. Quick Check:

   LEFT JOIN keeps all employees, unmatched dept_name is NULL [OK]

Hint: LEFT JOIN keeps all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Mixing up LEFT and RIGHT JOIN results
• Ignoring NULL values for unmatched rows

4. You wrote this query but it gives an error:

SELECT * FROM employees RIGHT JOIN departments employees.id = departments.id;

What is the error and how to fix it?

medium

A. RIGHT JOIN is not supported; use LEFT JOIN instead

B. Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id

C. Table names are reversed; switch employees and departments

D. Use WHERE instead of ON for join condition

Solution

1. Step 1: Identify syntax error in JOIN clause

   The query is missing the ON keyword before the join condition, which is required syntax.

2. Step 2: Correct the syntax

   Add ON before employees.id = departments.id to fix the error.

3. Final Answer:

   Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id -> Option B

4. Quick Check:

   JOIN condition must use ON keyword [OK]

Hint: JOIN conditions always need ON keyword before condition [OK]

Common Mistakes:

• Omitting ON keyword
• Using WHERE instead of ON for join condition
• Confusing table order in JOIN

5. You want to list all departments and their employees, including departments with no employees. Which query achieves this?

hard

A. SELECT departments.dept_name, employees.name FROM employees RIGHT JOIN departments WHERE employees.id = departments.id;

B. SELECT departments.dept_name, employees.name FROM employees LEFT JOIN departments ON employees.id = departments.id;

C. SELECT departments.dept_name, employees.name FROM departments RIGHT JOIN employees ON departments.id = employees.id;

D. SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id;

Solution

1. Step 1: Identify which table to keep all rows from

   We want all departments, even those without employees, so departments is the left table in LEFT JOIN.

2. Step 2: Use LEFT JOIN from departments to employees

   LEFT JOIN keeps all departments and adds matching employees or NULL if none.

3. Final Answer:

   SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id; -> Option D

4. Quick Check:

   LEFT JOIN from departments keeps all departments [OK]

Hint: LEFT JOIN from main table keeps all its rows, unmatched get NULLs [OK]

Common Mistakes:

• Using employees as left table instead of departments
• Using RIGHT JOIN incorrectly
• Confusing which table to keep all rows from