Amazon·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL join behavior question at Amazon for a BI Engineer role. Pretty focused on the mechanics of duplicate keys across tables, which is one of those things you think you know until someone asks you to actually show the row counts.

Questions Asked (1)

Given two tables where the same key value appears multiple times in both (3 rows in one, 5 in the other), walk through the exact result sets returned by a LEFT JOIN, RIGHT JOIN, and INNER JOIN on that key column. Explain why each join produces the row count it does.

Data ModelingTechnical Trade-offs

Author's notes

The Cartesian product part is what trips people up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining concrete sample tables with a common key column where one table has 3 rows and the other has 5 rows for that key. Then, for each join type, explain the matching logic and compute the result row count by multiplying the number of matching rows from each side (3×5=15 for INNER and LEFT, 5×3=15 for RIGHT). Finally, clarify that LEFT JOIN returns all rows from the left table plus unmatched right rows (but here all left rows match, so 15), RIGHT JOIN returns all rows from the right table plus unmatched left rows (15), and INNER JOIN returns only matches (15).

Pro tip: Mention that the row count is the product of matching rows, and explicitly state that if there were non-matching rows, the counts would differ; this shows you understand the nuance beyond the simple case.

1. Set up concrete examples

Create two small tables (e.g., Orders and OrderDetails) with a shared key column where one table has 3 rows with key 'A' and the other has 5 rows with key 'A'. This makes the explanation tangible.

2. Explain INNER JOIN

Describe that INNER JOIN returns only rows where the key matches in both tables. Since all 3 left rows match all 5 right rows, the result is 3×5 = 15 rows.

3. Explain LEFT JOIN

Explain that LEFT JOIN returns all rows from the left table plus matching rows from the right. Here, all left rows have matches, so the result is still 3×5 = 15 rows (no NULLs added).

4. Explain RIGHT JOIN

Explain that RIGHT JOIN returns all rows from the right table plus matching rows from the left. Since all right rows have matches, the result is 5×3 = 15 rows (no NULLs added).

5. Summarize and contrast

Conclude that in this specific case, all three joins produce 15 rows because every row on both sides has a match. Highlight that the row count is the Cartesian product of matching rows, and mention how non-matching rows would change the counts.

Key Points to Mention

The result of a join on a non-unique key is the Cartesian product of the matching rows from each table.
INNER JOIN returns only the intersection of matching rows (3×5=15).
LEFT JOIN returns all left rows plus matches; since all left rows match, it's still 15 rows.
RIGHT JOIN returns all right rows plus matches; since all right rows match, it's still 15 rows.
If there were non-matching rows, LEFT JOIN would include them with NULLs on the right, and RIGHT JOIN would include them with NULLs on the left, changing the row counts.
The order of tables in the join matters for LEFT vs RIGHT, but not for INNER.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.