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.

Practice

1. What does an INNER JOIN do in SQL?

easy

A. Returns all rows from the right table, and matched rows from the left

B. Returns all rows from the left table, and matched rows from the right

C. Returns only rows with matching values in both tables

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

Solution

1. Step 1: Understand INNER JOIN behavior

   An INNER JOIN returns rows where there is a match in both tables based on the join condition.

2. Step 2: Compare with other join types

   LEFT JOIN returns all from left, RIGHT JOIN all from right, FULL JOIN all from both. INNER JOIN only matched rows.

3. Final Answer:

   Returns only rows with matching values in both tables -> Option C

4. Quick Check:

   INNER JOIN = matched rows only [OK]

Hint: INNER JOIN keeps only matching rows from both tables [OK]

Common Mistakes:

• Confusing INNER JOIN with LEFT JOIN
• Thinking INNER JOIN returns unmatched rows
• Mixing up LEFT and RIGHT JOIN definitions

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

easy

A. SELECT * FROM table1 LEFT JOIN table2 USING (id);

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

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

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

Solution

1. Step 1: Recall LEFT JOIN syntax

   The correct syntax is: SELECT columns FROM table1 LEFT JOIN table2 ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; matches correct syntax. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has wrong order. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 LEFT JOIN table2 USING (id); uses correct syntax with parentheses.

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN syntax = FROM table1 LEFT JOIN table2 ON condition [OK]

Hint: LEFT JOIN uses ON, not WHERE, for join condition [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Incorrect order of JOIN keywords
• Confusing USING with ON without matching columns

3. Given two tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id;

medium

A. All employees with their department names, only if dept_id matches

B. All employees, even if they have no department

C. All departments with employees, including departments with no employees

D. All employees and departments, matched or not

Solution

1. Step 1: Analyze INNER JOIN condition

   The INNER JOIN returns rows where Employees.dept_id matches Departments.dept_id.

2. Step 2: Understand result rows

   Only employees with a matching department will appear, showing employee name and department name.

3. Final Answer:

   All employees with their department names, only if dept_id matches -> Option A

4. Quick Check:

   INNER JOIN filters to matching dept_id rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Assuming employees without departments appear
• Confusing INNER JOIN with LEFT JOIN
• Thinking unmatched departments appear

4. Identify the error in this SQL join query:

SELECT * FROM Orders o LEFT JOIN Customers c ON o.customer_id = c.id WHERE c.country = 'USA';

medium

A. The WHERE clause filters out unmatched rows, defeating LEFT JOIN purpose

B. The ON clause should use = instead of LIKE

C. LEFT JOIN should be RIGHT JOIN here

D. Missing GROUP BY clause for aggregation

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   LEFT JOIN keeps all rows from Orders, matching Customers or NULL if no match.

2. Step 2: Effect of WHERE on joined table

   WHERE c.country = 'USA' removes rows where c is NULL (no match), so unmatched rows are excluded, negating LEFT JOIN effect.

3. Final Answer:

   The WHERE clause filters out unmatched rows, defeating LEFT JOIN purpose -> Option A

4. Quick Check:

   WHERE on joined table filters NULLs, breaks LEFT JOIN [OK]

Hint: Use ON for join filters, WHERE filters after join [OK]

Common Mistakes:

• Filtering joined table in WHERE instead of ON
• Confusing LEFT JOIN with RIGHT JOIN
• Adding unnecessary GROUP BY

5. You want to list all products and their suppliers, including products without suppliers and suppliers without products. Which join should you use?

hard

A. LEFT JOIN

B. FULL OUTER JOIN

C. RIGHT JOIN

D. INNER JOIN

Solution

1. Step 1: Understand join requirements

   We want all products and all suppliers, even if no matching row exists in the other table.

2. Step 2: Match join type to requirement

   INNER JOIN excludes unmatched rows. LEFT or RIGHT JOIN include unmatched rows from one side only. FULL OUTER JOIN includes unmatched rows from both tables.

3. Final Answer:

   FULL OUTER JOIN -> Option B

4. Quick Check:

   FULL OUTER JOIN returns all rows from both tables [OK]

Hint: Use FULL OUTER JOIN to include unmatched rows from both tables [OK]

Common Mistakes:

• Choosing INNER JOIN which excludes unmatched rows
• Using LEFT or RIGHT JOIN which include only one side unmatched
• Confusing FULL OUTER JOIN syntax