Oracle 1Z0-084: The Outer Join That Returns Fewer Rows Than an Inner Join

The Outer Join That Returns Fewer Rows Than an Inner Join

The HR warehouse has DEPARTMENTS (DEPT_ID primary key, DEPT_NAME) with 27 rows, and EMPLOYEES (EMP_ID primary key, LAST_NAME, DEPT_ID nullable, SALARY NOT NULL, COMMISSION_PCT nullable). Three departments currently have no employees, and two contractors have DEPT_ID null because they are on loan and not assigned anywhere.

The requirement is one row per department showing how many employees earn more than 5,000 and the total salary of those employees; departments with nobody above the threshold must show 0 and 0.

The developer writes:

SELECT d.dept_name, COUNT(e.emp_id), SUM(e.salary) FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.salary > 5000 GROUP BY d.dept_name;

It returns 22 rows. A second, older query uses the comma join with the same predicate in the WHERE clause and returns 24 rows with inflated counts. Neither result is accepted, and the contractors must appear on a single 'Unassigned' line without disturbing department totals.

1. Explain precisely why the LEFT JOIN query returns only 22 of the 27 departments, and why the comma-join version returns 24 rows with wrong counts.

Read the worked answer

Why the LEFT OUTER JOIN collapses to an inner join. A left outer join preserves the left row by padding the right side with NULLs when no match exists. That padding happens before WHERE. For the five departments that lose their row, e.salary is NULL, and NULL > 5000 evaluates to UNKNOWN, which is not TRUE, so the preserved row is discarded. A predicate on the null-supplying side of an outer join placed in WHERE silently converts the outer join into an inner join. The correct row count (27) minus the two departments lost for having only sub-5,000 staff, minus the three empty ones, gives the 22 observed.

Why the comma join is worse. FROM departments d, employees e WHERE d.dept_id = e.dept_id is an inner join: the three empty departments disappear immediately, leaving 24. The inflated counts come from COUNT(*), which counts the joined row, not the employee. For an empty department that survived an outer join, COUNT(*) returns 1 (the padded row) while COUNT(e.emp_id) returns 0 - the classic off-by-one in outer-join reporting. SUM(e.salary) over padded rows returns NULL, not 0, which is the second half of the '0 and 0' requirement.

Rule. Null-supplying-side predicates belong in the ON clause; only predicates that intentionally filter the result belong in WHERE.

2. Write the query that satisfies the requirement, and state which aggregate choices are forced by the outer join.

Read the worked answer

SELECT d.dept_name,
       COUNT(e.emp_id)              AS high_earners,
       NVL(SUM(e.salary), 0)        AS high_earner_salary
FROM   departments d
LEFT   JOIN employees e
       ON e.dept_id = d.dept_id
      AND e.salary  > 5000
GROUP  BY d.dept_name
ORDER  BY d.dept_name;

The only change from the failing query is moving e.salary > 5000 into the ON clause. Semantics: join each department to the employees who both belong to it and earn more than 5,000; departments with none keep their row with NULL employee columns.

Two aggregate details are forced by that shape:

COUNT(e.emp_id), not COUNT(*). The padded row is a real row in the result set; COUNT(*) would report 1 high earner for an empty department.
NVL(SUM(e.salary), 0). SUM over only-null input returns NULL, and the requirement is a literal 0. COUNT needs no NVL because it already returns 0.

If the business also wants the total headcount including low earners, add a second LEFT JOIN under a different alias rather than trying to serve both populations from one joined row set - joining the same table twice for two different filters is cheaper to reason about than one CASE-laden aggregate, and avoids multiplying rows.

3. The two contractors with a null DEPT_ID must appear on one 'Unassigned' line. Why can the current query not produce that line, and what are the two workable shapes?

Read the worked answer

The join condition is e.dept_id = d.dept_id. For a contractor, e.dept_id is NULL, and NULL = anything is UNKNOWN, never TRUE, so no department row ever matches them - including under LEFT JOIN. Even under FULL OUTER JOIN they would produce a row with a NULL department, but a LEFT JOIN with DEPARTMENTS as the preserved side simply drops them. Their salaries therefore never enter any group.

Option 1 - FULL OUTER JOIN with a label.

SELECT NVL(d.dept_name, 'Unassigned') AS dept,
       COUNT(e.emp_id)                AS high_earners,
       NVL(SUM(e.salary), 0)          AS high_earner_salary
FROM   departments d
FULL   OUTER JOIN employees e
       ON e.dept_id = d.dept_id
      AND e.salary  > 5000
GROUP  BY NVL(d.dept_name, 'Unassigned');

The full outer join preserves unmatched rows on both sides: empty departments and unassigned employees. Note that employees failing the salary test still generate a padded row on the department side, but those contribute 0 and NULL to the aggregates, so totals are unaffected. The label must be repeated in GROUP BY or the statement fails with ORA-00979.

Option 2 - UNION ALL of two purpose-built queries: one grouped by d.dept_id for departments (including empty ones), one selecting the NULL-dept bucket from EMPLOYEES. More code, but each branch has one obvious population and neither depends on NVL grouping semantics.

A third option that should be rejected: fixing the data by inventing a department row for the contractors. It changes the master data to solve a report, and every future headcount query inherits the fiction.