Monday, September 21, 2026

Joins in Interview

 CREATE TABLE table1 (
    id INT
);


CREATE TABLE table2 (
    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;



SELECT * FROM hr.table1





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;

SELECT * FROM hr.table2;








Below is a clean, Blogger/Blogspot-friendly version. I’ve removed the broken formatting, normalized the SQL, and converted the tables so they render cleanly in a blog post.

SQL JOIN Examples – INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN

One of the most important concepts to understand when working with SQL JOINs is that duplicate matching values can multiply the number of records returned.

In this example, we will create two tables and understand the number of records returned by:

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL OUTER JOIN

1. Create the Tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

2. Insert Data into Table 1

For Oracle, we can use INSERT ALL:

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;

Check the data:

SELECT *
FROM table1;

Table 1

ID
1
1
2
2
3
0
NULL

Total records = 7

3. Insert Data into Table 2

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;

Check the data:

SELECT *
FROM table2;

Table 2

ID
0
0
4
5
NULL
NULL

Total records = 6

4. 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;

An INNER JOIN returns only the records where there is a matching value in both tables.

The only matching value between the two tables is:

0

Table 1 contains one 0.

Table 2 contains two 0s.

Therefore:

1 × 2 = 2 records

Result

TABLE1_IDTABLE2_ID
00
00

INNER JOIN = 2 records

5. 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 returns:

  1. All records from Table 1

  2. Matching records from Table 2

  3. NULL for Table 2 columns when there is no match

Let's calculate:

Table 1 ValueCount in Table 1Matching Rows in Table 2Output Rows
1202
2202
3101
0122
NULL101
Total7
8

Therefore:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result will conceptually look like:

TABLE1_IDTABLE2_ID
1NULL
1NULL
2NULL
2NULL
3NULL
00
00
NULLNULL

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

6. 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 returns:

  1. All records from Table 2

  2. Matching records from Table 1

  3. NULL for Table 1 columns when there is no match

Let's calculate:

Table 2 ValueCount in Table 2Matching Rows in Table 1Output Rows
0212
4101
5101
NULL202
Total6
6

Therefore:

2 + 1 + 1 + 2 = 6

RIGHT JOIN = 6 records

The result will conceptually look like:

TABLE1_IDTABLE2_ID
00
00
NULL4
NULL5
NULLNULL
NULLNULL

7. 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:

  • All matching records

  • All unmatched records from Table 1

  • All unmatched records from Table 2

Let's calculate it step by step.

Matching Records

The only matching value is 0.

Table 1:

0 → 1 occurrence

Table 2:

0 → 2 occurrences

Therefore:

1 × 2 = 2 matching records

Unmatched Records from Table 1

The following records don't have a match in Table 2:

1
1
2
2
3
NULL

Total:

6 unmatched records

Unmatched Records from Table 2

The following records don't have a match in Table 1:

4
5
NULL
NULL

Total:

4 unmatched records

Therefore:

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

FULL OUTER JOIN = 12 records

8. Final Result

JOIN TypeNumber of Records
INNER JOIN2
LEFT JOIN8
RIGHT JOIN6
FULL OUTER JOIN12

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.

For Blogspot, I’d recommend keeping the SQL inside <pre><code> blocks if you are using the HTML view, because that preserves indentation and makes the code much easier to read.





Absolutely. The important point here is that duplicate matching values multiply the number of rows in a join.

Let's use exactly your data.

1. Create the tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

2. Insert the data

Table 1

INSERT INTO table1 (id) VALUES
(1),
(1),
(2),
(2),
(3),
(0),
(NULL);

Table 2

INSERT INTO table2 (id) VALUES
(0),
(0),
(4),
(5),
(NULL),
(NULL);

So the data is:

Table 1Table 2
10
10
24
25
3NULL
0NULL
NULLNULL
7 rows6 rows

3. INNER JOIN

SELECT t1.id, t2.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 has:

0 → 1 occurrence

Table 2 has:

0 → 2 occurrences

Therefore:

1 × 2 = 2 rows

Result:

T1.idT2.id
00
00

INNER JOIN = 2 records

NULL does not match NULL with =.

4. LEFT JOIN

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

A LEFT JOIN returns every row from Table 1, plus matching rows from Table 2.

Let's calculate:

Table 1 valueCount in T1Matching T2 rowsOutput
1202
2202
3101
0122
NULL101

Total:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result conceptually looks like:

T1.idT2.id
1NULL
1NULL
2NULL
2NULL
3NULL
00
00
NULLNULL

Notice that Table 1's NULL still appears because LEFT JOIN preserves all T1 rows.

5. RIGHT JOIN

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

A RIGHT JOIN returns every row from Table 2, plus matching rows from Table 1.

Calculate:

Table 2 valueCount in T2Matching T1 rowsOutput
0212
4101
5101
NULL202

Total:

2 + 1 + 1 + 2 = 6

RIGHT JOIN = 6 records

Result:

T1.idT2.id
00
00
NULL4
NULL5
NULLNULL
NULLNULL

6. FULL OUTER JOIN

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

FULL OUTER JOIN returns:

  • All matching rows

  • All unmatched rows from Table 1

  • All unmatched rows from Table 2

Let's break it down.

Matching records

Only 0 matches:

T1 has 1 zero
T2 has 2 zeros

1 × 2 = 2

Unmatched Table 1 records

Table 1 has:

1
1
2
2
3
NULL

That's 6 unmatched rows.

Unmatched Table 2 records

Table 2 has:

4
5
NULL
NULL

That's 4 unmatched rows.

Therefore:

2 matching
+ 6 unmatched T1
+ 4 unmatched T2
----------------
= 12 rows

FULL OUTER JOIN = 12 records

Final Answer

Join TypeNumber of Records
INNER JOIN2
LEFT JOIN8
RIGHT JOIN6
FULL OUTER JOIN12

The key interview concept

The biggest thing to remember is:

JOIN doesn't simply return the number of matching values. Duplicate values can multiply the rows.

For example:

Table 1          Table 2

0                0
                 0

The single 0 in Table 1 matches both zeros in Table 2:

1 × 2 = 2 rows

If you had:

Table 1: 0, 0
Table 2: 0, 0, 0

then the join would produce:

2 × 3 = 6 rows

One more critical point about NULL

This condition:

ON t1.id = t2.id

does not consider:

NULL = NULL

as TRUE.

Therefore, the two NULLs in Table 2 do not match the NULL in Table 1.

This is a very common SQL interview question.





Absolutely. The important point here is that duplicate matching values multiply the number of rows in a join.

Let's use exactly your data.

1. Create the tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

2. Insert the data

Table 1

INSERT INTO table1 (id) VALUES
(1),
(1),
(2),
(2),
(3),
(0),
(NULL);

Table 2

INSERT INTO table2 (id) VALUES
(0),
(0),
(4),
(5),
(NULL),
(NULL);

So the data is:

Table 1Table 2
10
10
24
25
3NULL
0NULL
NULLNULL
7 rows6 rows

3. INNER JOIN

SELECT t1.id, t2.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 has:

0 → 1 occurrence

Table 2 has:

0 → 2 occurrences

Therefore:

1 × 2 = 2 rows

 

 

 

 

Result:

T1.idT2.id
00
00

INNER JOIN = 2 records

NULL does not match NULL with =.

4. LEFT JOIN

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

A LEFT JOIN returns every row from Table 1, plus matching rows from Table 2.

Let's calculate:

Table 1 valueCount in T1Matching T2 rowsOutput
1202
2202
3101
0122
NULL101

 

 

 

Total:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result conceptually looks like:

T1.idT2.id
1NULL
1NULL
2NULL
2NULL
3NULL
00
00
NULLNULL

Notice that Table 1's NULL still appears because LEFT JOIN preserves all T1 rows.

5. RIGHT JOIN

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

A RIGHT JOIN returns every row from Table 2, plus matching rows from Table 1.

Calculate:

Table 2 valueCount in T2Matching T1 rowsOutput
0212
4101
5101
NULL202

Total:

2 + 1 + 1 + 2 = 6

 

 

 

 

RIGHT JOIN = 6 records

Result:

T1.idT2.id
00
00
NULL4
NULL5
NULLNULL
NULLNULL

6. FULL OUTER JOIN

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

FULL OUTER JOIN returns:

  • All matching rows

  • All unmatched rows from Table 1

  • All unmatched rows from Table 2

Let's break it down.

Matching records

Only 0 matches:

T1 has 1 zero
T2 has 2 zeros

1 × 2 = 2

Unmatched Table 1 records

Table 1 has:

1
1
2
2
3
NULL

That's 6 unmatched rows.

Unmatched Table 2 records

Table 2 has:

4
5
NULL
NULL

That's 4 unmatched rows.

Therefore:

2 matching
+ 6 unmatched T1
+ 4 unmatched T2
----------------
= 12 rows

 

 

 

FULL OUTER JOIN = 12 records

Final Answer

Join TypeNumber of Records
INNER JOIN2
LEFT JOIN8
RIGHT JOIN6
FULL OUTER JOIN12

The key interview concept

The biggest thing to remember is:

JOIN doesn't simply return the number of matching values. Duplicate values can multiply the rows.

For example:

Table 1          Table 2

0                0
                 0

The single 0 in Table 1 matches both zeros in Table 2:

1 × 2 = 2 rows

If you had:

Table 1: 0, 0
Table 2: 0, 0, 0

then the join would produce:

2 × 3 = 6 rows

One more critical point about NULL

This condition:

ON t1.id = t2.id

does not consider:

NULL = NULL

as TRUE.

Therefore, the two NULLs in Table 2 do not match the NULL in Table 1.

This is a very common SQL interview question.










Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)