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;
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 = NULLis not TRUE in SQL. Therefore, NULLs do not match usingt1.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,
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,
A FULL OUTER JOIN returns:
- Matching rows
- Unmatched rows from Table 1
- Unmatched rows from Table 2
Matching rows
0:
1 × 2 = 2 rows
Unmatched table_1 rows
1
6 rows
Unmatched table_2 rows
4
4 rows
Therefore:
2 matching 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
Table 1 has one 0.
Table 2 has two 0s.
Therefore:
1 × 2 = 2 rowsIf the data were:
Table 1 Table 2
Then:
2 × 3 = 6 rowsThis is known as the many-to-many matching effect.
10. Important Point About NULL
Another important concept is that:
NULL = NULLdoes 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.idThis 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
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.
