PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is a HAVING condition with no aggregate always exactly equivalent to WHERE?

I recently ran across an interesting comment in the PostgreSQL test suite:

-- HAVING is effectively equivalent to WHERE in this case
SELECT b, c FROM test_having
    GROUP BY b, c HAVING b = 3 ORDER BY b, c;

It makes me think. When a HAVING clause (or sub-clause) is referencing a non-aggregated value, is it always exactly functionally equivalent to WHERE? Are there any cases where that is not true? Given that the only non-aggregated references that are valid to appear in a HAVING clause are the GROUP BY key columns, it doesn’t seem at first glance like it could filter out anything that would change the final result of the query. But are there any possibilities I’m missing?

Tags:

2

Answers


  1. 0

    The result will be the same – so you might argue that it’s semantically the same.

    But unless the DBMS has a very clever optimiser, using HAVING on a grouping column is a waste of resources:

    In this query (re-formatting yours):

    SELECT             
    b
  , c
FROM test_having
GROUP BY b, c 
HAVING b = 3 
ORDER BY b, c;
    • if b can assume a million values, the query will most probably group 999,999 buckets too many, only to discard them at the end; which it will not if you filter with WHERE before the GROUP BY .
    • ORDER BY b, c is a waste of time; how do you order one result row?
    Login or Signup to reply.
  2. 0

    When the HAVING clause references non-aggregated values that are also present in the GROUP BY clause, it behaves similarly to a WHERE clause. In your example, HAVING b = 3 could indeed be rewritten as WHERE b = 3, as it filters rows based on a non-aggregated value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search