COUNT and NULL — OS, DBMS & Networks Aptitude Question
COUNT and NULL: Table employee: | empid | name | managerid | |--------|-------|------------| | 1 | Asha | NULL | |… Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Medium
- Time target: 90 seconds
Question
Table employee:
| emp_id | name | manager_id |
|---|---|---|
| 1 | Asha | NULL |
| 2 | Ravi | 1 |
| 3 | Meena | 1 |
| 4 | Sunil | NULL |
| 5 | Kiran | 2 |
What does this query return?
SELECT COUNT(*), COUNT(manager_id) FROM employee;Options
- 3 and 3
- 3 and 5
- 5 and 3
- 5 and 5
Show the answer and the worked solution
Correct answer: C. 5 and 3
Worked solution
- COUNT(*) counts rows whatever they contain, and the table has 5 rows.
- COUNT(manager_id) counts only the rows where that column is not NULL.
- Two rows, for Asha and Sunil, hold NULL there, leaving 3.
- The query therefore returns 5 and 3.
Answer: 5 and 3.
The idea
Every aggregate except COUNT(*) ignores NULLs, which also makes AVG(col) divide by the non-null count rather than the row count — a favourite source of surprising averages.
Shortcut. COUNT(column) = rows − NULLs in that column = 5 − 2 = 3, beside COUNT(*) = 5.
Common trap. "5 and 5" assumes COUNT(manager_id) counts every row, NULL included.