Illustrate SQL Join Results with Duplicate Keys

TABLE1

+------+
| col1 |
+------+
| 1    |
| 1    |
| 1    |
+------+

TABLE2

+------+
| col1 |
+------+
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
+------+

Scenario

Demonstrate how SQL join types behave when duplicate key values exist in both tables.

Question

Using the two tables below, illustrate the exact result sets (show at least the first few rows) returned by

1. LEFT JOIN, 2) RIGHT JOIN, and
2. INNER JOIN on TABLE1.col1 = TABLE2.col1, and explain why each join produces that specific row count.

Hints

Same key value appears multiple times in both tables; think Cartesian product of matching rows: n_left * n_right.

Join Behavior with Duplicate Keys

You are given two tables:

TABLE1 col1

1 1 1

TABLE2 col1

1 1 1 1 1

Using these tables, write SQL to:

1. Show the result set produced by an INNER JOIN on TABLE1.col1 = TABLE2.col1.
2. Show the result set produced by a LEFT JOIN on TABLE1.col1 = TABLE2.col1.
3. Show the result set produced by a RIGHT JOIN on TABLE1.col1 = TABLE2.col1.

Also determine how many rows each join returns and be ready to explain why those row counts occur when there are duplicate key values in both tables.

Table TABLE1

column	type	constraints
col1	INTEGER

Sample data:

col1
1
1
1

Table TABLE2

column	type	constraints
col1	INTEGER

Sample data:

col1
1
1
1
1
1

Expected output

Both tables contain only the key value 1, duplicated 3 times in TABLE1 and 5 times in TABLE2. For an equality join, every matching pair of rows is produced, so each of the 3 left rows matches each of the 5 right rows, giving 3 × 5 = 15 joined rows. Because there are no non-matching keys on either side, INNER JOIN, LEFT JOIN, and RIGHT JOIN all return the same 15 rows, and no NULL-padded rows appear.

If you SELECT t1.col1 and t2.col1 for any of the three join types, the full result set consists of 15 identical rows: (t1_col1 = 1, t2_col1 = 1). The summary below shows the row counts per join type.

join_type	row_count
INNER JOIN	15
LEFT JOIN	15
RIGHT JOIN	15

Hints

1. When the join keys are equal, every duplicate on the left matches every duplicate on the right: m duplicates × n duplicates = m×n joined rows.
2. Since all rows in both tables have col1 = 1, there are no unmatched rows; INNER, LEFT, and RIGHT joins all produce the same row count.
3. LEFT JOIN keeps all left rows; RIGHT JOIN keeps all right rows. Here every row matches, so no NULL-padded rows appear.