How SQL Handles No Matching Rows in Aggregation

When working with SQL aggregation queries, it’s important to understand how the query is processed and how the result is returned, whether using a GROUP BY clause or not, especially when there are no matching rows.

Although this topic may seem basic, I’ve encountered many errors or unexpected function results due to this behavior, so I considered it worth writing about.

Let’s explore this behavior with an example:

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.

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

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
_______________

Behavior:

Query 1: The GROUP BY clause groups the results by department_id. If there are no employees in department 99, the query returns no rows.

Query 2: Without the GROUP BY clause, the query treats all matching rows as a single group. If no employees match the condition, the query returns a single row with a NULL value for the SUM(salary).

In real-world scenarios, when asked for the total salaries of a department with no employees, the expected answer is typically 0, not NULL or an empty result. However, SQL’s default behavior doesn’t always align with this expectation. The key difference lies in how SQL handles grouping:

With GROUP BY: SQL creates a group for each unique value in the specified column(s). If no rows match the WHERE condition, no groups are created, and thus no rows are returned.

Without GROUP BY: SQL treats all matching rows as a single group. If no rows match, SQL still returns a single row with NULL values for aggregate functions.

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); 

Conclusion:

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