Illustrate SQL Join Results with Duplicate Keys
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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
3) 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.
Overview: This question evaluates understanding of SQL join semantics and the effect of duplicate key values on result cardinality, assessing competency in relational data manipulation and reasoning about result sets.
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.
Tables
TABLE1(col1 INTEGER)
TABLE2(col1 INTEGER)
Hints
- 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.
- 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.