Katkat
Back

Illustrate SQL Join Results with Duplicate Keys

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.