Oracle SUM() with No Matching Rows: NULL vs No Rows

When an Oracle SUM() query finds no matching rows, the result depends on whether you use GROUP BY.

Without GROUP BY: SUM() returns one row containing NULL.
With GROUP BY: Oracle returns no rows, because there is no group to return.

This distinction is easy to miss and can cause unexpected results in reports, applications, and PL/SQL code.

For example:

-- Returns one row with NULL
SELECT SUM(salary)
FROM hr.employees
WHERE department_id = 99;
-- Returns no rows
SELECT SUM(salary)
FROM hr.employees
WHERE department_id = 99
GROUP BY department_id;

Scenario: Finding Total Salaries by Department:

We need the total salaries for department 99. Since that department is closed, there are no employees working in it.

SUM() With GROUP BY Returns No Rows

Query 1: Query the sum of the salaries with grouping by department for department 99

SQL> SELECT SUM(salary) as total_salary
FROM hr.employees
WHERE department_id = 99
GROUP BY department_id;
no rows selected

SUM() Without GROUP BY Returns NULL

Query 2: Query the sum of the salaries without using group by for department 99

SQL> SELECT SUM(salary)
FROM hr.employees
WHERE department_id = 99;
   TOTAL_SALARY
_______________

Comparison Table

Why Does GROUP BY Behave Differently?:

The difference comes from how Oracle handles aggregation.

Without GROUP BY, Oracle treats the entire result set as a single group, even when no rows match the WHERE condition. Consequently, SUM() returns NULL for that empty input.

With GROUP BY, Oracle creates groups only from rows that satisfy the query conditions. If no rows match, no groups are formed, and the query returns no rows.

The key distinction is that NULL is a value returned within a result row, whereas “no rows selected” means no result row exists at all.This behavior is consistent across most relational database management systems, including Oracle.

Aligning SQL Results with Real-World Expectations

To ensure that your SQL queries return 0 instead of NULL or no rows, you can use the COALESCE() function, which substitutes a specified value when encountering NULL.

SELECT COALESCE(SUM(salary), 0) FROM employees WHERE department_id = :p_department_id;

Since the GROUP BY clause returns no rows, a simple UNION ALL section can be added to return a zero total, ensuring that this section is included only when the department does not exist.

SELECT :p_department_id AS department_id, COALESCE(SUM(e.salary), 0) AS total_salary
FROM hr.employees e
WHERE e.department_id = :p_department_id
GROUP BY e.department_id
UNION ALL
SELECT :p_department_id, 0
WHERE NOT EXISTS (SELECT 1 FROM hr.employees WHERE department_id = :p_department_id); 

FAQ Section:

Does SUM() return NULL when no rows are found?

Yes. An aggregate such as SUM() without GROUP BY returns one result row, with NULL for SUM() when no input rows match.

Why does GROUP BY return no rows?

Because no matching rows means there is no group to produce. Therefore the grouped query has no result row.

How do I make SUM() return 0 instead of NULL?

SELECT COALESCE(SUM(salary), 0)
FROM hr.employees
WHERE department_id = 99;

What is the difference between SUM() returning NULL and returning no rows?

Without GROUP BY, COUNT(*) returns 0 when no rows match, whereas SUM() returns NULL.

SELECT COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employees
WHERE department_id = 99;

The result contains one row: EMPLOYEE_COUNT = 0 and TOTAL_SALARY = NULL.

With GROUP BY, neither aggregate produces a result row if no groups are formed.

Conclusion:

NULL means a result row exists but its aggregate value is unknown/NULL. No rows means the query produced no result row at all.

Even though it seems basic, SQL handles aggregation differently with and without GROUP BY when no rows match. A GROUP BY may return no rows, while the same query without it returns NULL. This subtle difference can lead to confusion and unexpected results when the nuances aren’t well understood by developers.

Hope it helps. See you on the next post – Part 5 : Avoid Misusing LEFT JOIN in SQL Queries

Also my other Common SQL/PLSQL Pitfalls – Blog Posts:

Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience

NVL and DECODE: Lazy vs Eager Evaluation (Part 1)

Scalar Subquery Caching Behavior in a SQL Statement (Part 2)

Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL (Part 3)

Oracle SUM() Returns NULL or No Rows? GROUP BY Explained (Part 4)

Avoid Misusing LEFT JOIN in SQL Queries (Part 5)