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.