Joins in SQL in DBMS Theory - Step-by-Step Execution

Concept Flow - Joins in SQL

Start with two tables

↓

Choose Join Type

↓

INNER JOIN

↓

Match rows where keys are equal

↓

Output matched rows

↓

LEFT JOIN

↓

All rows from left table

↓

Match rows from right table or NULL

↓

Output all left rows + matches

↓

RIGHT JOIN

↓

All rows from right table

↓

Match rows from left table or NULL

↓

Output all right rows + matches

↓

FULL OUTER JOIN

↓

All rows from both tables

↓

Match rows or NULL where no match

↓

Output all rows combined

Joins combine rows from two tables based on a related column, choosing which rows to keep depending on the join type.

Execution Sample

DBMS Theory

SELECT A.id, A.name, B.salary
FROM Employees A
INNER JOIN Salaries B ON A.id = B.emp_id;

This query joins Employees and Salaries tables, showing only employees who have salary records.

Analysis Table

Step	Action	Employees Row	Salaries Row	Join Condition (A.id = B.emp_id)	Result Row
1	Compare Employees.id=1 with Salaries.emp_id=1	id=1, name=Alice	emp_id=1, salary=5000	True	id=1, name=Alice, salary=5000
2	Compare Employees.id=1 with Salaries.emp_id=2	id=1, name=Alice	emp_id=2, salary=6000	False
3	Compare Employees.id=2 with Salaries.emp_id=1	id=2, name=Bob	emp_id=1, salary=5000	False
4	Compare Employees.id=2 with Salaries.emp_id=2	id=2, name=Bob	emp_id=2, salary=6000	True	id=2, name=Bob, salary=6000
5	Compare Employees.id=3 with Salaries.emp_id=1	id=3, name=Carol	emp_id=1, salary=5000	False
6	Compare Employees.id=3 with Salaries.emp_id=2	id=3, name=Carol	emp_id=2, salary=6000	False
7	No more rows to compare

💡 All rows compared; only matched rows included in INNER JOIN result.

State Tracker

Variable	Start	After Step 1	After Step 4	Final
Current Employees Row	None	id=1, name=Alice	id=2, name=Bob	id=3, name=Carol
Current Salaries Row	None	emp_id=1, salary=5000	emp_id=2, salary=6000	emp_id=2, salary=6000
Result Rows	Empty	[(1, Alice, 5000)]	[(1, Alice, 5000), (2, Bob, 6000)]	[(1, Alice, 5000), (2, Bob, 6000)]

Key Insights - 3 Insights

Why does Carol (id=3) not appear in the result?

Because Carol's id=3 does not match any emp_id in Salaries, so INNER JOIN excludes her (see execution_table rows 5 and 6).

What happens if we use LEFT JOIN instead of INNER JOIN?

All Employees rows appear; unmatched Salaries columns show NULL. This differs from INNER JOIN which only shows matched rows.

Why do we compare every Employees row with every Salaries row?

Because the join checks all possible pairs to find matching keys, as shown in execution_table steps 1 to 6.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result row at Step 4?

A id=3, name=Carol, salary=NULL

B id=2, name=Bob, salary=6000

C id=1, name=Alice, salary=5000

D No result row

💡 Hint

Check the Result Row column at Step 4 in execution_table.

At which step does the join condition first become True?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the Join Condition column in execution_table for the first True value.

If we changed the join to LEFT JOIN, what would happen to Carol's row?

A It would appear with salary NULL

B It would be excluded

C It would appear with salary 0

D It would cause an error

💡 Hint

Recall LEFT JOIN keeps all left table rows even if no match (see key_moments).

Concept Snapshot

Joins in SQL combine rows from two tables based on matching columns.
INNER JOIN returns only matching rows.
LEFT JOIN returns all left table rows plus matches or NULLs.
RIGHT JOIN returns all right table rows plus matches or NULLs.
FULL OUTER JOIN returns all rows from both tables with matches or NULLs.
Use ON clause to specify join condition.

Full Transcript

Joins in SQL let you combine rows from two tables by matching values in related columns. The process starts with two tables and a chosen join type. For INNER JOIN, only rows with matching keys in both tables appear in the result. LEFT JOIN keeps all rows from the left table and adds matching rows from the right or NULL if no match. RIGHT JOIN is similar but keeps all right table rows. FULL OUTER JOIN keeps all rows from both tables, matching where possible and filling NULLs where not. The example query joins Employees and Salaries on employee ID, showing only employees with salary records. The execution table shows step-by-step comparisons of rows and which results are included. Variables track current rows and accumulated results. Key moments clarify why some rows appear or not and how join types differ. The visual quiz tests understanding of the join process and results. The snapshot summarizes join types and behavior for quick reference.