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