SQL JOIN – Duplicate & NULL Handling

CREATE TABLE table1 (
    id INT
);

INSERT ALL
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (3)
    INTO table1 (id) VALUES (0)
    INTO table1 (id) VALUES (NULL)
SELECT 1 FROM DUAL;

table_1 contains 7 rows:

INSERT ALL

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (4)

INTO table2 (id) VALUES (5)

INTO table2 (id) VALUES (NULL)

INTO table2 (id) VALUES (NULL)

SELECT 1 FROM DUAL;

table_2 contains 6 rows:

2. Important Rule: Duplicate Values Multiply

For a normal equality join:

ON t1.id = t2.id

if a value occurs M times in Table 1 and N times in Table 2, the join produces:

M × N rows

For example, 0 occurs:

• Table 1 → 1 time
• Table 2 → 2 times

Therefore:

1 × 2 = 2 matching rows

Important: NULL = NULL is not TRUE in SQL. Therefore, NULLs do not match using t1.id = t2.id.

3. JOIN Results

INNER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id;

Only matching values are returned.

The only matching value is 0.

Table 1: 0 → 1 occurrence
Table 2: 0 → 2 occurrences

1 × 2 = 2

INNER JOIN = 2 rows

TABLE1_ID	TABLE2_ID
0	0
0	0

LEFT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

A LEFT JOIN preserves all rows from Table 1.

Table 1 Value	Table 1 Count	Matches in Table 2	Output
1	2	0	2
2	2	0	2
3	1	0	1
0	1	2	2
NULL	1	0	1
Total	7 - From Left table		8

Therefore:

LEFT OUTER JOIN = 8 rows

TABLE1_ID	TABLE2_ID
1	NULL
1	NULL
2	NULL
2	NULL
3	NULL
0	0
0	0
NULL	NULL

Notice that the NULL from Table 1 is still returned because a LEFT JOIN preserves every row from the left table.

RIGHT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
RIGHT JOIN table2 t2
    ON t1.id = t2.id;

A RIGHT JOIN preserves all rows from Table 2.

Table_2 Value	Table_2 Count	Matches in Table_1	Output
0	2	1	2
4	1	0	1
5	1	0	1
NULL	2	0	2
Total	6		6

Therefore:

RIGHT JOIN = 6 rows

4. FULL OUTER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id;

A FULL OUTER JOIN returns:

1. Matching rows
2. Unmatched rows from Table 1
3. Unmatched rows from Table 2

Matching rows

0:

1 × 2 = 2 rows

Unmatched table_1 rows

1
1
2
2
3
NULL

6 rows

Unmatched table_2 rows

4
5
NULL
NULL

4 rows

Therefore:

2 matching rows
+ 6 unmatched Table 1 rows
+ 4 unmatched Table 2 rows
--------------------------------
= 12 rows

FULL OUTER JOIN = 12 rows

8. Final Result

JOIN Type	Number of Records
INNER JOIN	2
LEFT JOIN	8
RIGHT JOIN	6
FULL OUTER JOIN	12

---

9. Important Interview Concept – Duplicate Values

A JOIN does not simply return one row for each matching value.

When duplicate values exist, the matching rows are multiplied.

For example:

Table 1       Table 2
0             0
              0

Table 1 has one 0.

Table 2 has two 0s.

Therefore:

1 × 2 = 2 rows

If the data were:

Table 1       Table 2
0             0
0             0
              0

Then:

2 × 3 = 6 rows

This is known as the many-to-many matching effect.

---

10. Important Point About NULL

Another important concept is that:

NULL = NULL

does not evaluate to TRUE in SQL.

Therefore, the NULL value in Table 1 does not match the NULL values in Table 2 when using:

ON t1.id = t2.id

This is why the NULL values are treated as unmatched rows in the LEFT, RIGHT, and FULL OUTER JOIN examples.

---

Summary

The key numbers to remember from this example are:

INNER JOIN       → 2 records
LEFT JOIN        → 8 records
RIGHT JOIN       → 6 records
FULL OUTER JOIN  → 12 records

The two most important interview concepts are:

1. Duplicate matching values multiply the number of JOIN records.

2. NULL does not match NULL using the = operator.