SQL Joins - INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

▶

Steps

setup

Start Sequential Scan on Table A

The query planner begins by scanning Table A to read all rows. This is the first data source for the join.

💡 Scanning Table A first sets up the left side of the join, so we know what rows we will try to match.

Line:

SELECT A.id, A.name, B.city FROM A INNER JOIN B ON A.id = B.id;

💡 Sequential scan reads all rows from Table A to feed into the join operation.

setup

Start Sequential Scan on Table B

Next, the planner scans Table B to read all rows. This is the right side input for the join.

💡 Reading Table B fully allows the join to compare each row from A against all rows from B.

Line:

SELECT A.id, A.name, B.city FROM A INNER JOIN B ON A.id = B.id;

💡 Both tables must be scanned to perform the join condition comparisons.

expand

Begin Nested Loop Join for INNER JOIN

The nested loop join operator starts by taking the first row from Table A and will compare it to each row in Table B to find matches on id.

💡 Nested loop join tries every pair of rows to find matching join keys.

Line:

SELECT A.id, A.name, B.city FROM A INNER JOIN B ON A.id = B.id;

💡 This step initiates the core matching process for the INNER JOIN.

compare

Compare A.id=1 with B.id=2 (INNER JOIN)

Compare the first row from A (id=1) with the first row from B (id=2). No match on id, so no output row.

💡 This comparison shows how rows without matching keys are excluded in INNER JOIN.

Line:

ON A.id = B.id

💡 INNER JOIN only outputs rows where join keys match.

compare

Compare A.id=1 with B.id=3 (INNER JOIN)

Compare the first row from A (id=1) with the second row from B (id=3). No match, so no output row.

💡 This confirms no match for Alice in Table B, so no INNER JOIN output for her.

Line:

ON A.id = B.id

💡 INNER JOIN excludes rows without matching keys on either side.

compare

Compare A.id=2 with B.id=2 (INNER JOIN)

Compare the second row from A (id=2) with the first row from B (id=2). Match found, output combined row.

💡 This match produces the only row in the INNER JOIN result.

Line:

ON A.id = B.id

💡 INNER JOIN outputs only rows where keys match in both tables.

compare

Compare A.id=2 with B.id=3 (INNER JOIN)

Compare the second row from A (id=2) with the second row from B (id=3). No match, no output.

💡 This confirms Bob's row only matches one row in B.

Line:

ON A.id = B.id

💡 INNER JOIN excludes non-matching pairs.

prune

Complete INNER JOIN and Start LEFT JOIN

INNER JOIN completes with one output row. Now LEFT JOIN begins, which outputs all rows from A, matching B rows or NULLs if no match.

💡 LEFT JOIN preserves all left table rows, showing how unmatched rows get NULLs.

Line:

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

💡 LEFT JOIN extends INNER JOIN by including unmatched left rows with NULLs.

expand

LEFT JOIN: Output A.id=1 with NULL for B.city

For the first row in A (id=1), no matching B row found, so output row with B.city as NULL.

💡 LEFT JOIN includes all left rows, even if no match, filling right columns with NULL.

Line:

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

💡 LEFT JOIN preserves unmatched left rows with NULL padding.

expand

LEFT JOIN: Output A.id=2 with B.city='NY'

For the second row in A (id=2), matching B row found (id=2), output combined row.

💡 LEFT JOIN outputs matched rows same as INNER JOIN for those keys.

Line:

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

💡 LEFT JOIN includes matched rows normally.

setup

Start RIGHT JOIN: Scan Tables and Prepare Join

RIGHT JOIN begins by scanning both tables again, but now preserving all rows from Table B and matching from A or NULLs.

💡 RIGHT JOIN is symmetric to LEFT JOIN but preserves right table rows.

Line:

SELECT A.id, A.name, B.city FROM A RIGHT JOIN B ON A.id = B.id;

💡 RIGHT JOIN outputs all right rows, matched or unmatched.

expand

RIGHT JOIN: Output B.id=2 with A.name='Bob'

For the first row in B (id=2), matching A row found (id=2), output combined row.

💡 RIGHT JOIN outputs matched rows normally.

Line:

SELECT A.id, A.name, B.city FROM A RIGHT JOIN B ON A.id = B.id;

💡 Matched rows appear in RIGHT JOIN output.

expand

RIGHT JOIN: Output B.id=3 with NULL for A.name

For the second row in B (id=3), no matching A row found, output row with NULL for A.name.

💡 RIGHT JOIN preserves all right rows, unmatched rows get NULLs on left side.

Line:

SELECT A.id, A.name, B.city FROM A RIGHT JOIN B ON A.id = B.id;

💡 RIGHT JOIN includes unmatched right rows with NULL padding.

setup

Start FULL OUTER JOIN: Scan Tables

FULL OUTER JOIN begins by scanning both tables to preserve all rows from both sides, matching where possible.

💡 FULL OUTER JOIN combines LEFT and RIGHT JOIN behaviors, including all rows from both tables.

Line:

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id;

💡 FULL OUTER JOIN outputs all rows, matched or unmatched, from both tables.

expand

FULL OUTER JOIN: Output A.id=1 with NULL for B.city

Output unmatched row from A (id=1) with NULL for B.city.

💡 FULL OUTER JOIN includes unmatched rows from left with NULLs on right.

Line:

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id;

💡 Unmatched left rows appear with NULLs on right side.

expand

FULL OUTER JOIN: Output A.id=2 with B.city='NY'

Output matched row with A.id=2 and B.city='NY'.

💡 Matched rows appear normally in FULL OUTER JOIN.

Line:

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id;

💡 Matched rows included as usual.

expand

FULL OUTER JOIN: Output B.id=3 with NULL for A.name

Output unmatched row from B (id=3) with NULL for A.name.

💡 FULL OUTER JOIN includes unmatched right rows with NULLs on left.

Line:

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id;

💡 Unmatched right rows appear with NULLs on left side.

expand

Start CROSS JOIN: Cartesian Product

CROSS JOIN produces all combinations of rows from A and B without any join condition.

💡 CROSS JOIN outputs every pair of rows from both tables.

Line:

SELECT A.id, A.name, B.city FROM A CROSS JOIN B;

💡 CROSS JOIN results in a Cartesian product of rows.

expand

CROSS JOIN: Output all combinations of A and B rows

Output all 4 combinations: (Alice, NY), (Alice, LA), (Bob, NY), (Bob, LA).

💡 Every row from A pairs with every row from B in CROSS JOIN.

Line:

SELECT A.id, A.name, B.city FROM A CROSS JOIN B;

💡 CROSS JOIN outputs full Cartesian product.

setup

SELF JOIN: Alias Table A and Join on manager_id

SELF JOIN aliases Table A as A1 and A2, joining on A1.manager_id = A2.id to find managers.

💡 Self join lets a table relate to itself, useful for hierarchical data.

Line:

SELECT A1.id, A1.name, A2.name AS manager_name FROM A AS A1 JOIN A AS A2 ON A1.manager_id = A2.id;

💡 Self join uses aliases to distinguish table instances.

def print_sql_queries():
    queries = {
        'INNER JOIN': "SELECT A.id, A.name, B.city FROM A INNER JOIN B ON A.id = B.id;",  # STEP 1-7
        'LEFT JOIN': "SELECT A.id, A.name, B.city FROM A LEFT JOIN B ON A.id = B.id;",    # STEP 8-10
        'RIGHT JOIN': "SELECT A.id, A.name, B.city FROM A RIGHT JOIN B ON A.id = B.id;", # STEP 11-13
        'FULL OUTER JOIN': "SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id;", # STEP 14-17
        'CROSS JOIN': "SELECT A.id, A.name, B.city FROM A CROSS JOIN B;",               # STEP 18-19
        'SELF JOIN': "SELECT A1.id, A1.name, A2.name AS manager_name FROM A AS A1 JOIN A AS A2 ON A1.manager_id = A2.id;" # STEP 20
    }
    for join_type, query in queries.items():
        print(f"-- {join_type} --")
        print(query)
        print()

if __name__ == '__main__':
    print_sql_queries()

📊

SQL Joins - INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF - Watch the Algorithm Execute, Step by Step

Watching the join operations unfold visually helps you understand how rows are matched, preserved, or padded with NULLs, which is often confusing from code or text alone.

Scanning Table A to read all rows for join.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 0

SeqScan chosen for simplicity; no indexes used.

Scanning Table B to read all rows for join.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 0

SeqScan chosen for Table B as well.

Starting nested loop join: comparing first row of A with all rows of B.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 0

Nested loop join chosen for clarity in this example.

Comparing A.id=1 with B.id=2: no match, no output.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 1

Join condition evaluated for each pair.

Comparing A.id=1 with B.id=3: no match, no output.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 2

Join condition evaluated for each pair.

Comparing A.id=2 with B.id=2: match found, output row.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 3

Join condition true, row emitted.

Comparing A.id=2 with B.id=3: no match, no output.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 4

Join condition false, no output.

INNER JOIN done; preparing for LEFT JOIN execution.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 4

Reusing nested loop join with LEFT JOIN semantics.

LEFT JOIN: output A.id=1 with NULL for B.city (no match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 1

LEFT JOIN outputs unmatched left rows with NULLs.

LEFT JOIN: output A.id=2 with B.city='NY' (match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 2

Matched rows output normally.

Preparing RIGHT JOIN: scanning tables.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 0

RIGHT JOIN uses nested loop join with right preservation.

RIGHT JOIN: output B.id=2 with A.name='Bob' (match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 1

Matched rows output.

RIGHT JOIN: output B.id=3 with NULL for A.name (no match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 2

Unmatched right rows padded with NULLs.

Preparing FULL OUTER JOIN: scanning tables.

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 0

FULL OUTER JOIN requires preserving unmatched rows from both sides.

FULL OUTER JOIN: output A.id=1 with NULL for B.city (no match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 1

Unmatched left rows padded with NULLs.

FULL OUTER JOIN: output A.id=2 with B.city='NY' (match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 2

Matched rows output.

FULL OUTER JOIN: output B.id=3 with NULL for A.name (no match).

SeqScanArows≈2

SeqScanBrows≈2

NestedLoopA.id = B.id

Rows processed: 3

Unmatched right rows padded with NULLs.

CROSS JOIN: producing Cartesian product of all rows.

SeqScanArows≈2

SeqScanBrows≈2

NestedLooprows≈4

Rows processed: 0

No join condition; all pairs output.

CROSS JOIN complete: all combinations output.

SeqScanArows≈2

SeqScanBrows≈2

NestedLooprows≈4

Rows processed: 4

Cartesian product fully enumerated.

Starting SELF JOIN: scanning aliased tables A1 and A2.

SeqScanA AS A1rows≈2

SeqScanA AS A2rows≈2

NestedLoopA1.manager_id = A2.id

Rows processed: 0

Aliases distinguish table instances for self join.

Key Takeaways

✓ INNER JOIN outputs only rows with matching keys in both tables.

This is hard to see from code alone because it requires understanding which rows get filtered out.

✓ LEFT and RIGHT JOIN preserve all rows from one table, padding unmatched rows with NULLs.

Visualizing this shows how unmatched rows are retained, which is often confusing in text explanations.

✓ CROSS JOIN produces a Cartesian product, combining every row from both tables.

Seeing all pairs explicitly helps grasp the difference from conditional joins.

Practice

1. Given a relation R(A,B,C) with functional dependencies F = {A -> B, B -> C}, trace the step-by-step reasoning to check if the decomposition into R1(A,B) and R2(B,C) is lossless and dependency preserving.

easy

A. The decomposition is lossless because B is common and a key in R2; it preserves all dependencies since both FDs appear in R1 or R2

B. The decomposition is lossless because A is a key in R1; it does not preserve B -> C since B is not a key in R1

C. The decomposition is not lossless because the common attribute B is not a key in either relation; it preserves all dependencies

D. The decomposition is lossless and preserves dependencies only if A is a key for the entire relation

Solution

1. Step 1: Identify common attributes

   R1 and R2 share attribute B.

2. Step 2: Check if common attribute is a key in either relation

   B is a key in R2 (since B -> C), so lossless join condition holds.

3. Step 3: Check dependency preservation

   A -> B is in R1, B -> C is in R2, so all dependencies are preserved.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   The decomposition is lossless because A is a key in R1; it does not preserve B -> C since B is not a key in R1 incorrectly states lossless join depends on A in R1; The decomposition is not lossless because the common attribute B is not a key in either relation; it preserves all dependencies wrongly claims lossless join fails; The decomposition is lossless and preserves dependencies only if A is a key for the entire relation adds unnecessary condition.

Hint: Lossless join if common attribute is key in one decomposed relation; preserve dependencies if each FD is in some relation.

Common Mistakes:

• Assuming common attribute must be key in both relations
• Confusing which relation must contain which FD
• Believing lossless join requires the original key in all decomposed relations

2. In which scenario is MVCC most beneficial compared to traditional locking mechanisms in a database system?

easy

A. When strict serializability is required and no stale reads are allowed

B. When transactions are short-lived and mostly write-heavy with minimal reads

C. When many concurrent transactions perform mostly read operations with occasional writes

D. When the database workload consists primarily of batch updates with no concurrent reads

Solution

1. Step 1: Understand MVCC's strength in concurrency

   MVCC allows readers to access snapshot versions without blocking writers, making it ideal for workloads with many reads and fewer writes.

2. Step 2: Analyze write-heavy workloads

   Write-heavy workloads with minimal reads benefit less from MVCC since version maintenance overhead can be costly.

3. Step 3: Analyze batch updates without concurrent reads

   Batch updates without concurrent reads do not leverage MVCC's snapshot isolation benefits.

4. Step 4: Analyze strict serializability requirements

   MVCC provides snapshot isolation, which may allow some anomalies; strict serializability requires stronger mechanisms.

5. Final Answer:

   Option C -> Option C

6. Quick Check:

   MVCC shines in read-heavy concurrent environments [OK]

Hint: MVCC excels when many reads coexist with some writes

Common Mistakes:

• Assuming MVCC is best for write-heavy workloads
• Confusing snapshot isolation with strict serializability
• Believing MVCC is unnecessary without concurrent reads

3. Trace the sequence of lock acquisitions and releases when two transactions T1 and T2 both request shared locks on the same data item, followed by T1 requesting an exclusive lock on that item. What happens step-by-step?

easy

A. T1 acquires exclusive lock first; T2 waits for T1 to release before acquiring shared lock

B. T1 and T2 acquire shared locks simultaneously; T1 upgrades to exclusive lock immediately without waiting

C. T1 acquires shared lock; T2 waits until T1 releases its lock before acquiring shared lock

D. T1 and T2 acquire shared locks; T1 must wait to upgrade to exclusive lock until T2 releases its shared lock

Solution

1. Step 1: Both transactions request shared locks

   Shared locks are compatible, so T1 and T2 both acquire shared locks simultaneously.

2. Step 2: T1 requests exclusive lock (lock upgrade)

   Exclusive lock is incompatible with shared locks held by others, so T1 must wait until T2 releases its shared lock.

3. Step 3: T1 waits; T2 continues holding shared lock

   T1 cannot upgrade immediately; it must wait for T2 to release.

4. Final Answer:

   Option D -> Option D

5. Quick Check:

   Lock compatibility rules prevent immediate exclusive lock upgrade when others hold shared locks.

Hint: Exclusive lock upgrade waits for all shared locks to release [OK]

Common Mistakes:

• Assuming lock upgrade is immediate despite other shared locks
• Believing shared locks are exclusive
• Ignoring lock compatibility rules

4. Which of the following is a common misconception about the trade-offs when choosing SQL over NoSQL in distributed systems?

medium

A. NoSQL databases never support ACID transactions and thus cannot guarantee any consistency

B. SQL databases always guarantee strong consistency but may sacrifice availability during network partitions

C. NoSQL databases often sacrifice immediate consistency to achieve higher availability and partition tolerance

D. SQL databases are better suited for complex queries and relational data integrity

Solution

1. Step 1: Analyze each statement

   SQL databases always guarantee strong consistency but may sacrifice availability during network partitions correctly states SQL prioritizes consistency over availability during partitions.

2. Step 2: Evaluate NoSQL and ACID

   NoSQL databases never support ACID transactions and thus cannot guarantee any consistency is incorrect because some NoSQL databases support ACID transactions, though often limited or scoped.

3. Step 3: Confirm other options

   NoSQL databases often sacrifice immediate consistency to achieve higher availability and partition tolerance correctly describes NoSQL trade-offs. SQL databases are better suited for complex queries and relational data integrity is true about SQL strengths.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   NoSQL can support ACID in some cases, so 'never' is false [OK]

Hint: NoSQL can support ACID in limited scopes; 'never' is false

Common Mistakes:

• Believing NoSQL never supports ACID
• Assuming SQL always maintains availability during partitions
• Confusing consistency guarantees with query complexity

5. Which of the following statements about deadlocks in Two-Phase Locking (2PL) is INCORRECT?

medium

A. Strict 2PL completely prevents deadlocks by ordering lock acquisition

B. Deadlock detection algorithms can resolve deadlocks by aborting one of the transactions

C. Deadlocks occur when two or more transactions wait indefinitely for locks held by each other

D. Deadlocks can arise even if transactions follow the 2PL protocol

Solution

1. Step 1: Understand deadlock conditions

   Deadlocks happen when transactions cyclically wait for locks held by each other, which can occur under 2PL.

2. Step 2: Analyze deadlock prevention

   Strict 2PL does not guarantee deadlock prevention; it only ensures serializability.

3. Step 3: Evaluate options

   A, B, and D are correct statements. C is incorrect because strict 2PL does not prevent deadlocks by ordering lock acquisition.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   Deadlocks require detection and resolution even under strict 2PL.

Hint: Strict 2PL ensures serializability but not deadlock prevention [OK]

Common Mistakes:

• Assuming strict 2PL prevents deadlocks
• Confusing deadlock detection with prevention
• Believing 2PL eliminates all concurrency issues