Last Minute SQL Interview Prep Kit: Conditional Data Manipulation with CASE WHEN & Join Mechanics

Understanding how SQL joins calculate record counts is a critical skill frequently tested in technical interviews. This chapter explores the exact mathematical mechanics of Inner, Left, Right, and Full Outer Joins, focusing on duplicate keys, non-matching records, and the unique, often misunderstood behavior of NULL values during join operations.

1. The Core Mechanics of SQL Joins & Record Multiplication

When joining two tables on a specific key, SQL does not simply perform a 1-to-1 lookup. Instead, it evaluates matches on a row-by-row basis. If a join key contains duplicate values in both tables, SQL performs a local Cartesian product for that specific key.

The $M \times N$ Multiplication Rule

If Table A contains $M$ rows with a join key value of $X$, and Table B contains $N$ rows with the same join key value of $X$, the resulting join set will contain exactly $M \times N$ rows for that key.

This multiplication rule applies to all join types (Inner, Left, Right, and Full Outer) for the subset of rows that satisfy the join condition.

2. Mental Model: The Matchmaking Dance

To intuitively understand how duplicate keys multiply, consider the following real-world analogy:

Real-Life Analogy: The Numbered Shirt Dance

Imagine a dance hall where people are split into two groups: Group A (Left Table) and Group B (Right Table). Everyone wears a shirt with a number printed on it.

The coordinator announces: "Everyone must find a partner from the opposite group who is wearing the exact same number!"

• If Group A has 3 people wearing the number 1, and Group B has 2 people wearing the number 1, every single person wearing 1 in Group A must dance with every single person wearing 1 in Group B.
• This results in $3 \times 2 = 6$ unique dancing pairs.
• If someone in Group A is wearing the number 4 and there is no one in Group B wearing a 4, they have no matching partner.
  • In an Inner Join, they are escorted out of the room (discarded).
  • In a Left Join, they are allowed to stand on the dance floor alone, with an empty space (represented by NULL) where their partner should be.

3. Step-by-Step Evolution: Initial Dataset Setup

To trace these calculations systematically, let us define the initial state of our tables as described in the lecture.

Table A (Left Table)

• Key 1 count ($M_1$): 3 rows
• Key 2 count ($M_2$): 1 row

Table B (Right Table)

• Key 1 count ($N_1$): 2 rows
• Key 2 count ($N_2$): 2 rows
• Key 3 count ($N_3$): 1 row (Non-matching)

4. Inner Join Record Calculation

An Inner Join returns only the rows that have matching values in both tables. Non-matching keys (such as Key 3 in Table B) are completely excluded.

Mathematical Calculation

Using our initial dataset:

• For Key 1: $3 \text{ (from Table A)} \times 2 \text{ (from Table B)} = 6 \text{ rows}$
• For Key 2: $1 \text{ (from Table A)} \times 2 \text{ (from Table B)} = 2 \text{ rows}$

Visualizing the Matching Flow

5. Left Join vs. Right Join with Non-Matching Keys

Outer joins preserve rows from one or both tables even if they do not find a matching row in the other table.

Left Join Calculation

A Left Join preserves all rows from the left table (Table A) plus the matched rows from the right table (Table B). If there is no match, the columns from the right table are populated with NULL.

In our initial dataset, Table A contains only keys 1 and 2. Both of these keys find matches in Table B. There are no non-matching keys residing in the left table.

In this specific scenario, the Left Join output count is identical to the Inner Join output count.

Right Join Calculation

A Right Join preserves all rows from the right table (Table B) plus the matched rows from the left table (Table A).

In our dataset, Table B contains Key 3, which does not exist in Table A.

• Matching Rows: $6 \text{ (for Key 1)} + 2 \text{ (for Key 2)} = 8 \text{ rows}$
• Right Non-Matching Rows (Key 3): 1 row

6. Full Outer Join: The Comprehensive Union

A Full Outer Join combines the behavior of both Left and Right Joins. It returns all matching records, plus all non-matching records from the left table, plus all non-matching records from the right table.

Dataset Modification (Adding Key 4 and another Key 2)

To illustrate a complete Full Outer Join, let us update our tables by adding:

1. Key 4 to Table A (Left non-matching key).
2. An additional row of Key 2 to Table A.

Updated Table A (Left)

• Key 1: 3 rows
• Key 2: 2 rows (increased from 1)
• Key 4: 1 row (non-matching)

Updated Table B (Right)

• Key 1: 2 rows
• Key 2: 2 rows
• Key 3: 1 row (non-matching)

Full Outer Join Calculation

1. Calculate Matching Keys:
   • Key 1: $3 \times 2 = 6 \text{ rows}$
   • Key 2: $2 \times 2 = 4 \text{ rows}$
2. Identify Non-Matching Keys:
   • Key 4 (Left only): 1 row
   • Key 3 (Right only): 1 row

7. The Golden Rule of NULLs in SQL Joins

One of the most common traps in SQL interviews is understanding how NULL values behave during a join.

Crucial Concept: In SQL, NULL represents an unknown or missing value. It is not equal to anything, not even another NULL.

Because NULL = NULL evaluates to UNKNOWN, rows containing NULL in the join column of Table A will never match rows containing NULL in the join column of Table B. They are treated strictly as non-matching values.

Visualizing NULL Join Behavior

8. Step-by-Step Walkthrough: The Complete Dataset with NULLs

Let us apply this golden rule to our final dataset, which now includes a NULL value in both tables.

Final Table A (Left)

• Key 1: 3 rows
• Key 2: 2 rows
• Key 4: 1 row
• Key NULL: 1 row

Final Table B (Right)

• Key 1: 2 rows
• Key 2: 2 rows
• Key 3: 1 row
• Key NULL: 1 row

Scenario A: Inner Join with NULLs

Because NULL cannot match NULL, the NULL rows are excluded from the inner join. Only the valid matching keys (1 and 2) are processed.

• Key 1 matches: $3 \times 2 = 6 \text{ rows}$
• Key 2 matches: $2 \times 2 = 4 \text{ rows}$

Scenario B: Left Join with NULLs

The Left Join must preserve all rows from Table A.

• Matching Rows: 10 rows (from keys 1 and 2)
• Non-Matching Key 4: 1 row (preserved with right-side columns as NULL)
• Non-Matching Key NULL: 1 row (preserved with right-side columns as NULL)

Scenario C: Full Outer Join with NULLs

The Full Outer Join preserves all matching rows, plus all non-matching rows from both sides.

• Matching Rows: 10 rows (keys 1 and 2)
• Left Non-Matching Rows:
  • Key 4: 1 row
  • Left NULL: 1 row
• Right Non-Matching Rows:
  • Key 3: 1 row
  • Right NULL: 1 row

9. Tabular Synthesis: Join Record Counts Across Scenarios

The following table summarizes the record counts across the different stages of dataset modifications discussed in the lecture:

10. Realistic SQL Implementation & Verification

To verify these mathematical calculations, you can run the following standard SQL script. This script creates the tables, inserts the final state of the data (including NULL values), and executes the joins.

-- 1. Create Temporary Tables
CREATE TABLE Table_A (
    id INT,
    row_desc VARCHAR(50)
);

CREATE TABLE Table_B (
    id INT,
    row_desc VARCHAR(50)
);

-- 2. Populate Table A (Left Table)
INSERT INTO Table_A (id, row_desc) VALUES 
(1, 'Row_A1'),
(1, 'Row_A2'),
(1, 'Row_A3'),
(2, 'Row_A4'),
(2, 'Row_A5'),
(4, 'Row_A6'),
(NULL, 'Row_A7_Null');

-- 3. Populate Table B (Right Table)
INSERT INTO Table_B (id, row_desc) VALUES 
(1, 'Row_B1'),
(1, 'Row_B2'),
(2, 'Row_B3'),
(2, 'Row_B4'),
(3, 'Row_B5'),
(NULL, 'Row_B6_Null');

-- 4. Verify Inner Join (Expected: 10 Rows)
SELECT a.id AS a_id, b.id AS b_id, a.row_desc, b.row_desc
FROM Table_A a
INNER JOIN Table_B b ON a.id = b.id;

-- 5. Verify Left Join (Expected: 12 Rows)
SELECT a.id AS a_id, b.id AS b_id, a.row_desc, b.row_desc
FROM Table_A a
LEFT JOIN Table_B b ON a.id = b.id;

-- 6. Verify Full Outer Join (Expected: 14 Rows)
-- Note: Using UNION for databases that do not support FULL OUTER JOIN natively (e.g., MySQL)
SELECT a.id AS a_id, b.id AS b_id, a.row_desc, b.row_desc
FROM Table_A a
LEFT JOIN Table_B b ON a.id = b.id
UNION ALL
SELECT a.id AS a_id, b.id AS b_id, a.row_desc, b.row_desc
FROM Table_A a
RIGHT JOIN Table_B b ON a.id = b.id
WHERE a.id IS NULL AND b.id IS NOT NULL;

11. Common Interview Pitfalls & Debugging Workflows

During high-pressure SQL interviews, candidates frequently make predictable mistakes. Use this structured checklist to systematically calculate join outputs:

The 4-Step Join Calculation Checklist

Common Pitfalls to Avoid

• The NULL Match Trap: Never match a NULL key from Table A with a NULL key from Table B. Treat them as completely distinct, non-matching entities.
• The Addition Mistake: Do not add the frequencies of duplicate keys (e.g., $3 + 2 = 5$). You must multiply them ($3 \times 2 = 6$) because SQL generates a Cartesian product for matching keys.
• Missing Left/Right Nulls: In Left or Right joins, remember that the non-matching keys (including NULL rows) from the preserved table will appear in the output exactly once.

Summary and Key Takeaways

• The $M \times N$ Rule: Duplicate keys in joined tables multiply. If Key $X$ appears $M$ times on the left and $N$ times on the right, it contributes $M \times N$ rows to the output.
• Inner Join Exclusions: Inner joins only return rows where the join condition evaluates to TRUE. All non-matching keys and NULL keys are discarded.
• Left Join Preservation: Left joins return all matching rows plus all non-matching rows from the left table. Non-matching rows are padded with NULL values for the right table's columns.
• Right Join Preservation: Right joins return all matching rows plus all non-matching rows from the right table. Non-matching rows are padded with NULL values for the left table's columns.
• Full Outer Join Union: Full Outer Joins return all matching rows, plus all non-matching rows from both tables.
• NULL Non-Equality: In SQL, NULL = NULL is evaluated as UNKNOWN. Consequently, NULL values never join with other NULL values.
• NULLs as Non-Matching Keys: In outer joins, NULL values behave exactly like any other non-matching key (such as a key that exists in only one table).
• Systematic Calculation: To avoid mistakes in interviews, always list the frequency of each key in both tables before calculating the final join record counts.