Rows from a left outer join — Aptitude Question
Rows from a left outer join: Two tables: students | id | name | |----|-------| | 1 | Asha | | 2 | Ravi | | 3 |… Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Medium
- Time target: 105 seconds
Question
Two tables:
students
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Meena |
marks
| student_id | score |
|---|---|
| 1 | 80 |
| 1 | 90 |
| 3 | 70 |
How many rows does this query return?
SELECT * FROM students s LEFT JOIN marks m ON s.id = m.student_id;Options
- 3
- 4
- 5
- 6
Show the answer and the worked solution
Correct answer: B. 4
Worked solution
- A left outer join emits every matching pair, and for a left row with no match it emits one row padded with NULLs.
- Student 1 matches the two mark rows 80 and 90, contributing 2 rows.
- Student 3 matches the single mark row 70, contributing 1 row.
- Student 2 matches nothing but is still kept, contributing 1 row of NULLs.
- The result has 2 + 1 + 1 = 4 rows.
Answer: 4.
The idea
Row counts from a join are driven by matching multiplicities, not by table sizes. An inner join here would return 3 rows, since student 2 would disappear.
Shortcut. Rows = the sum over students of max(matches, 1): Asha 2, Ravi 1 padded with NULLs, Meena 1, which makes 4.
Common trap. 3 assumes a left join returns exactly one row per left-table row, but Asha matches two marks and appears twice.