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(e.id) FROM Dept d LEFT JOIN Emp e ON e.dept_id = d.id GROUP BY d.name ORDER BY d.name; Which result is produced?
Correct answer C. (HR, 1), (IT, 2), (Ops, 0)
Explanation
A LEFT JOIN keeps every row of the left table, Dept. Ops has no matching employee, so it appears once with NULLs in the Emp columns. COUNT(e.id) ignores that NULL and reports 0. Zara does not appear, because she is on the right side and matches no department. Dropping Ops entirely would be the inner-join behaviour.
Answer checked How we write and check questions
Discussion