Outer join that silently became an inner join, and a total inflated by a fan-out
DEPARTMENTS has 27 rows; EMPLOYEES has 107, one of which has a null DEPARTMENT_ID, and department 120 has no employees at all. Audit needs every department - including empty ones - with headcount and total salary for staff earning more than 5,000:
SELECT d.department_name, COUNT(e.employee_id) AS headcount, SUM(e.salary) AS total FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.salary > 5000 GROUP BY d.department_name;
It returns 12 rows. Empty departments are missing, and so are departments whose staff all earn 5,000 or less. A second audit query joins EMPLOYEES to JOB_HISTORY (several rows per employee) and sums SALARY; the total came out about 2.4 times the payroll figure.
1. Explain why the outer join degraded into an inner join and rewrite it correctly, including the treatment of empty departments.
Read the worked answer
WHERE is evaluated after the join. For a department with no employees, the outer join produces nulls in all e.* columns, and e.salary > 5000 on null evaluates to UNKNOWN - the row is discarded. The LEFT JOIN has been filtered back to an inner join, and the null-extended rows that the outer join existed to produce are exactly the rows the predicate removes.
The filter must live in the ON clause, so it applies while matching, not after:
SELECT d.department_name,
COUNT(e.employee_id) AS headcount,
NVL(SUM(e.salary), 0) AS total
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
AND e.salary > 5000
GROUP BY d.department_id, d.department_name;
Two details: COUNT(e.employee_id) returns 0 for the empty departments while COUNT(*) would return 1 (counting the null-extended row); and SUM over no rows returns null, so NVL(...,0) is required for a report that should show a zero.
2. The payroll figure from the JOB_HISTORY query is roughly 2.4 times the true total. Diagnose it and give two ways to fix it.
Read the worked answer
This is fan-out: EMPLOYEES.JOB_HISTORY is one-to-many, so each employee's salary is repeated once per job-history row and added that many times. The 2.4 factor is the average number of history rows per employee. The same defect appears whenever a fact row is joined to a one-to-many child before being summed.
Fix 1 - aggregate each table at its own grain, then join:
SELECT d.department_name, NVL(SUM(x.salary),0) AS total
FROM departments d
LEFT JOIN (SELECT department_id, SUM(salary) AS salary
FROM employees
WHERE salary > 5000
GROUP BY department_id) x
ON x.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
Fix 2 - keep the salary roll-up in a scalar subquery or a pre-aggregated inline view and join the history only for counts that are computed with COUNT(DISTINCT ...) or from its own grouped view.
SUM(DISTINCT salary) is not a fix: two employees on the same salary would collapse into one.
3. The report groups by DEPARTMENT_NAME. What can go wrong, and where does the employee with a null DEPARTMENT_ID appear?
Read the worked answer
Grouping by name instead of key merges departments that share a name - with 27 departments, a duplicate name (e.g. two 'Sales' rows) silently produces one row with a combined headcount, and the reconciliation against 107 employees fails. Group by department_id and carry the name along (either in the GROUP BY or as MIN(d.department_name) when it is functionally dependent).
The null-department employee belongs to no department. A departments-driven outer join cannot produce them: they are not in any of the 27 groups, and they never appear as an orphan because the join starts from DEPARTMENTS. To include them, either drive from EMPLOYEES with a (+)-style outer join or add an explicit UNION ALL 'Unassigned' row - and reconcile 107 = sum of department headcounts + 1 to prove the report is complete.