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

Question

Problem You are given two tables. Table A ID 1 1 1 1 2 3 Table B ID 1 1 NULL 3 NULL Task Without executing the query, determine the output of the following SQL statement. SELECT A.ID, B.ID FROM TableA A LEFT JOIN TableB B ON A.ID = B.ID; Questions How many rows will the query return? Why don't NULL values match? Explain why duplicate values produce additional rows.

Answer & Explanation

**Result of the query**

The query will return **10 rows**.

**How we get that number**

1. **Rows with ID = 1**
*Table A* has four rows whose ID is 1.
*Table B* has two rows whose ID is 1.

In a join every row from the left side is paired with **every** matching row from the right side.
4 × 2 = **8 rows** are produced for the value 1.

2. **Rows with ID = 2**
*Table A* has one row with ID 2.
*Table B* has **no** row with ID 2.

Because it is a **LEFT** join, the A‑row is kept and the B‑columns are filled with NULL.
That contributes **1 row**.

3. **Rows with ID = 3**
*Table A* has one row with ID 3.
*Table B* has one row with ID 3.

They match one‑to‑one, giving **1 row**.

Add them together: 8 + 1 + 1 = **10 rows**.

---

**Why NULL values don’t match**

In SQL the equality operator (`=`) follows three‑valued logic:

* `value = value` → TRUE
* `value = NULL` → UNKNOWN
* `NULL = NULL` → UNKNOWN

`UNKNOWN` is treated as FALSE in a join condition, so a row where `B.ID` is NULL never satisfies `A.ID = B.ID`. (Even if `A.ID` were NULL, the comparison would still be UNKNOWN.) Therefore the two NULL rows in Table B never join to any row in Table A.

---

**Why duplicate values create extra rows**

A join works row‑by‑row. When the join key appears more than once on either side, the database pairs **each** occurrence on the left with **each** occurrence on the right.

For the key 1 we have:

| A.ID | (four rows) |
|------|-------------|
| 1 |
| 1 |
| 1 |
| 1 |

and

| B.ID | (two rows) |
|------|-------------|
| 1 |
| 1 |

The result set contains the Cartesian product of those two sets: 4 × 2 = 8 rows. The same principle applies to any duplicated key – duplicates multiply the number of result rows.

---

**Summary you should remember**

* A LEFT JOIN keeps every row from the left table; if no right‑hand row matches, the right columns are NULL.
* `NULL = NULL` is **not** true; NULL never matches anything in an equality join.
* When the join key is duplicated, the result contains every possible pairing of the matching rows, so duplicates increase the row count multiplicatively.

With the data given, the query therefore returns 10 rows, 8 of them coming from the four × two matches on ID = 1, one row for the unmatched ID = 2, and one row for the matched ID = 3.