This is a common SQL (Joins) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem Without executing the query, determine the output of the following INNER JOIN. Pay close attention to duplicate values in both tables. Table A ID 1 1 2 3 Table B ID 1 1 1 2 4 SQL Query SELECT A.ID, B.ID FROM TableA A INNER JOIN TableB B ON A.ID = B.ID; Task Without executing the query, How many rows will the query return? Explain why duplicate values increase the number of rows.

Answer & Explanation

**Answer – 7 rows**

**Why the result is 7**

An `INNER JOIN` returns a row for **every combination** of rows from the two tables that satisfy the join condition (`A.ID = B.ID`).
Think of the join as a “pair‑up” process:

1. **Find a value that exists in both tables.**
2. **Count how many times that value appears in each table.**
3. **Multiply those two counts** – that gives the number of result rows contributed by that value.

Now apply that to the data you have.

| ID value | Count in Table A | Count in Table B | Rows produced (A × B) |
|----------|------------------|------------------|----------------------|
| 1 | 2 | 3 | 2 × 3 = 6 |
| 2 | 1 | 1 | 1 × 1 = 1 |
| 3 | 1 | 0 | 1 × 0 = 0 (no match) |
| 4 | 0 | 1 | 0 × 1 = 0 (no match) |

Add the rows contributed by each ID:

6 (for ID = 1) + 1 (for ID = 2) = **7 rows**.

**Explanation of the duplicate effect**

When a value appears more than once in a table, each occurrence is treated as a separate row. During the join, every row from Table A that has the matching value is paired with **every** row from Table B that has the same value. This is why the counts are multiplied:

- For ID = 1, Table A has two rows (`A1`, `A2`). Table B has three rows (`B1`, `B2`, `B3`).
The join pairs `A1` with each of `B1`, `B2`, `B3` (3 rows) and also pairs `A2` with each of `B1`, `B2`, `B3` (another 3 rows). Total = 6 rows.

- For ID = 2, there is only one row in each table, so only one pair is produced.

If there were no duplicates, each matching value would contribute just a single row. Duplicates “inflate” the result because the join does not collapse them; it creates a Cartesian product for each matching key value.

**Take‑away**

- An inner join’s cardinality is the sum over all join keys of (count in left table × count in right table).
- Duplicate keys in either side increase the result size multiplicatively, not additively.

So, without running the query, you can confidently say the query will return **7 rows**, and you understand that the extra rows come from the 2 × 3 pairing of the duplicate “1” values.