4 ms·

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grou

by teddyh 4y ago

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equivalent to SELECT * FROM … WHERE … GROUP BY … HAVING …;)

syntheticcdo 4y ago
FWIW I just tested a somewhat complex query using HAVING vs a sub-select as you indicated and Postgres generated the same query plan (and naturally, results) for both.
- djbusby 4y ago
  A DB wizard I used to work for showed me HAVING after looking at this nasty sub-sub select I did, with some app-layer-loop after. Caesar if you see this thanks for being a great mentor.
larsrc 4y ago
But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like. SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;
- p4bl0 4y ago
  Why wouldn't SELECT * FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income FROM sales) WHERE nb_sales > 10 AND income < 1000; work just like your example?
  - dspillett 4y ago
    For more complicated examples, HAVING can produce easier to read/understand/maintain statements. There may also be performance differences depending on the rest of the query, but for simple examples like this exactly the same plan will be used, so the performance will be identical. Unfortunately, the simplest examples do not always illustrate the potential benefits of less common syntax.
    - p4bl0 4y ago
      I totally agree with that, but I was responding to this statement: > Subselects can't do something like: (…) which is wrong.
- Linosaurus 4y ago
  This should work, right? Just a bit more unnecessary text. SELECT year, sales, income FROM ( SELECT year, COUNT(*) sales, SUM(price) income FROM sales ) AS innerquery WHERE sales > 10 AND income < 1000;
nerdponx 4y ago
Snowflake SQL also has the interesting feature QUALIFY. Their docs go into more detail (https://docs.snowflake.com/en/sql-reference/constructs/qualify.html https://docs.snowflake.com/en/sql-reference/constructs/quali...), but the short version is that typically SELECT is evaluated in the order FROM, WHERE, GROUP BY, HAVING, WINDOW, DISTINCT, ORDER BY, LIMIT. But what happens if you want to filter on the result of a WINDOW? Sorry, time to write a nested query and bemoan the non-composability of SQL. Snowflake adds QUALIFY, which is executed after WINDOW and before DISTINCT. Therefore you can write some pretty interesting tidy queries, like this one I've been using at work: SELECT *, row_number() OVER ( PARTITION BY quux.record_time ORDER BY quux.projection_time DESC ) AS record_version FROM foo.bar.quux AS quux WHERE (NOT quux._bad OR quux._bad IS NULL) QUALIFY record_version = 1 Without QUALIFY, you'd have to nest queries (ugh): SELECT * FROM ( SELECT *, row_number() OVER ( PARTITION BY quux.record_time ORDER BY quux.projection_time DESC ) AS record_version FROM foo.bar.quux AS quux WHERE (NOT quux._bad OR quux._bad IS NULL) ) AS quux_versioned WHERE quux_versioned.record_version = 1 or use a CTE (depending on whether your database inlines CTEs). I definitely pine for some kind of optimizing Blub-to-SQL compiler that would let me write my SQL like this instead: (query (from foo.bar.quux :as quux) (select *) (where (or (not quux._bad) (null quux._bad)) (select (row-number :over (:partition-by quux.record-time :order-by (:desc quux.projection-time)) :as record-version) (where (= 1 record-version)))
- someforevery 4y ago
  > I definitely pine for some kind of optimizing Blub-to-SQL compiler I've been playing with malloy[1] that lets you pipeline/nest queries like you are describing here. source: quux as from_sql(..) { where: record_version = 1 where: _bad != null } 1. https://looker-open-source.github.io/malloy/documentation/language/query.html https://looker-open-source.github.io/malloy/documentation/la...

by teddyh 4y ago

syntheticcdo 4y ago

FWIW I just tested a somewhat complex query using HAVING vs a sub-select as you indicated and Postgres generated the same query plan (and naturally, results) for both.

djbusby 4y ago
A DB wizard I used to work for showed me HAVING after looking at this nasty sub-sub select I did, with some app-layer-loop after. Caesar if you see this thanks for being a great mentor.

larsrc 4y ago

But HAVING can also act on aggregate results. In fact, the example in the article is not the most important use for HAVING. Subselects can't do something like. SELECT year, COUNT(*) sales, SUM(price) income FROM sales HAVING sales > 10 AND income < 1000;

p4bl0 4y ago
Why wouldn't SELECT * FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income FROM sales) WHERE nb_sales > 10 AND income < 1000; work just like your example?
- dspillett 4y ago
  For more complicated examples, HAVING can produce easier to read/understand/maintain statements. There may also be performance differences depending on the rest of the query, but for simple examples like this exactly the same plan will be used, so the performance will be identical. Unfortunately, the simplest examples do not always illustrate the potential benefits of less common syntax.
  - p4bl0 4y ago
    I totally agree with that, but I was responding to this statement: > Subselects can't do something like: (…) which is wrong.
Linosaurus 4y ago
This should work, right? Just a bit more unnecessary text. SELECT year, sales, income FROM ( SELECT year, COUNT(*) sales, SUM(price) income FROM sales ) AS innerquery WHERE sales > 10 AND income < 1000;

nerdponx 4y ago

Snowflake SQL also has the interesting feature QUALIFY. Their docs go into more detail (https://docs.snowflake.com/en/sql-reference/constructs/qualify.html https://docs.snowflake.com/en/sql-reference/constructs/quali...), but the short version is that typically SELECT is evaluated in the order FROM, WHERE, GROUP BY, HAVING, WINDOW, DISTINCT, ORDER BY, LIMIT. But what happens if you want to filter on the result of a WINDOW? Sorry, time to write a nested query and bemoan the non-composability of SQL. Snowflake adds QUALIFY, which is executed after WINDOW and before DISTINCT. Therefore you can write some pretty interesting tidy queries, like this one I've been using at work: SELECT *, row_number() OVER ( PARTITION BY quux.record_time ORDER BY quux.projection_time DESC ) AS record_version FROM foo.bar.quux AS quux WHERE (NOT quux._bad OR quux._bad IS NULL) QUALIFY record_version = 1 Without QUALIFY, you'd have to nest queries (ugh): SELECT * FROM ( SELECT *, row_number() OVER ( PARTITION BY quux.record_time ORDER BY quux.projection_time DESC ) AS record_version FROM foo.bar.quux AS quux WHERE (NOT quux._bad OR quux._bad IS NULL) ) AS quux_versioned WHERE quux_versioned.record_version = 1 or use a CTE (depending on whether your database inlines CTEs). I definitely pine for some kind of optimizing Blub-to-SQL compiler that would let me write my SQL like this instead: (query (from foo.bar.quux :as quux) (select *) (where (or (not quux._bad) (null quux._bad)) (select (row-number :over (:partition-by quux.record-time :order-by (:desc quux.projection-time)) :as record-version) (where (= 1 record-version)))

someforevery 4y ago
> I definitely pine for some kind of optimizing Blub-to-SQL compiler I've been playing with malloy[1] that lets you pipeline/nest queries like you are describing here. source: quux as from_sql(..) { where: record_version = 1 where: _bad != null } 1. https://looker-open-source.github.io/malloy/documentation/language/query.html https://looker-open-source.github.io/malloy/documentation/la...