Table Dept(id, name) has rows (10,’HR’), (20,’IT’), (30,’Ops’). Table Emp(id, name, dept_id) has rows (1,’Ali’,10), (2,’Sara’,20), (3,’Omar’,20), (4,’Zara’,NULL). SELECT d.name, COUNT(*) FROM Dept d LEFT JOIN Emp e ON e.dept_id = d.id GROUP BY d.name; What count is shown for the Ops department?
Correct answer A. 1
Explanation
The LEFT JOIN produces one row for Ops, padded with NULLs on the Emp side. COUNT(*) counts rows, not values, so that padded row counts as 1. That is why, when counting matches in an outer join, you should count a column from the optional table, such as COUNT(e.id), which ignores the NULL and gives the expected 0.
Answer checked How we write and check questions
Discussion