How many rows result from left, right, full outer, and inner joins?…

Why This Question Matters

This medium-level SQL question appears frequently in data engineering interviews at companies like Gartner. While less common, it tests deeper understanding that distinguishes strong candidates. Mastering the underlying concepts (join) will help you answer variations of this question confidently.

How to Approach This

Break this problem into components. Identify the core trade-offs involved, then walk the interviewer through your reasoning step by step. Demonstrate awareness of edge cases and production considerations - this is what separates good answers from great ones.

You cannot state a number without knowing the data, and saying so is the correct answer. Row counts depend on how many rows match on each side, not on the table sizes. What you can give is the formula.

Let M be the number of rows produced by matching keys, Lu the left rows with no match, and Ru the right rows with no match.

INNER JOIN returns M.

LEFT JOIN returns M + Lu.

RIGHT JOIN returns M + Ru.

FULL OUTER JOIN returns M + Lu + Ru.

Every outer join returns at least as many rows as the inner join, never fewer.

The part that trips people up

M is not "the number of matching keys". When a key appears multiple times on both sides, the join multiplies. If key A appears 3 times on the left and 2 times on the right, that key alone produces 6 rows.

Concretely, with a left table of 10 rows and a right table of 4:

If the join key is unique on both sides and 3 keys match: inner returns 3, left returns 3 + 7 = 10, right returns 3 + 1 = 4, full returns 3 + 7 + 1 = 11.

If the right table has 4 rows all carrying the same key, and 2 left rows share that key, the inner join alone returns 8 rows — more than the right table has.

A LEFT JOIN preserving "all left rows" is therefore a common misconception. It preserves every left row at least once, but duplicate right-side keys will fan those rows out.

Why interviewers ask

They want to know whether you will notice an accidental fan-out. Unexpected row growth after a join is one of the most frequent data quality bugs in production pipelines, and it usually means the join key is not as unique as assumed.

In the interview, also mention that you would verify uniqueness with a quick GROUP BY key HAVING COUNT(*) > 1 before trusting a join's grain.

Let M be the number of rows produced by matching keys, Lu the left rows with no match, and Ru the right rows with no match.

INNER JOIN returns M.

LEFT JOIN returns M + Lu.

RIGHT JOIN returns M + Ru.

FULL OUTER JOIN returns M + Lu + Ru.

Every outer join returns at least as many rows as the inner join, never fewer.

The part that trips people up

Concretely, with a left table of 10 rows and a right table of 4:

If the join key is unique on both sides and 3 keys match: inner returns 3, left returns 3 + 7 = 10, right returns 3 + 1 = 4, full returns 3 + 7 + 1 = 11.

If the right table has 4 rows all carrying the same key, and 2 left rows share that key, the inner join alone returns 8 rows — more than the right table has.

A LEFT JOIN preserving "all left rows" is therefore a common misconception. It preserves every left row at least once, but duplicate right-side keys will fan those rows out.

Why interviewers ask

In the interview, also mention that you would verify uniqueness with a quick GROUP BY key HAVING COUNT(*) > 1 before trusting a join's grain.

How many rows result from left, right, full outer, and inner joins?

Why This Question Matters

How to Approach This

The part that trips people up

Why interviewers ask

Related SQL Questions

Level up your prep

How many rows result from left, right, full outer, and inner joins?

Why This Question Matters

How to Approach This

The part that trips people up

Why interviewers ask

Related SQL Questions

Level up your prep