9 ms·

SQL: The difference between WHERE and HAVING

whatever1 5y ago
Is there any book that has the exact specifications of this dumpster fire that is called sql? With no other language I have faced so many gotchas with implicit conversions and unexpected behaviors.
- brigandish 5y ago
  Surely you've used Javascript?
- ziggus 5y ago
  I've found that most traditional developers have a rough time with SQL, because they're trying to use it like a procedural language, which it isn't - it's relational algebra/calculus based. Cornell has a good explanation: https://www.cs.cornell.edu/projects/btr/bioinformaticsschool/slides/gehrke.pdf https://www.cs.cornell.edu/projects/btr/bioinformaticsschool...
  - ttfkam 5y ago
    Exactly! It's a DSL for set theory. A true 4th-gen language. Perhaps the only truly successful 4GL.
- em500 5y ago
  https://www.iso.org/committee/45342/x/catalogue/p/1/u/0/w/0/d/0 https://www.iso.org/committee/45342/x/catalogue/p/1/u/0/w/0/... If you want to take a peek at an older revision: http://web.cecs.pdx.edu/~len/sql1999.pdf http://web.cecs.pdx.edu/~len/sql1999.pdf (I'm guessing this will be another case of be careful what you wish for).
gpvos 5y ago
Huh, I always thought HAVING was the equivalent for GROUP BY that WHERE is for SELECT, i.e. that it happened during aggregation, just as WHERE happens during data retrieval from the table. But apparently you can use HAVING instead of WHERE and it would work the same even if you don't have a GROUP BY clause (and even though it would possibly be less efficient)? Seems a bit redundant.
- maweki 5y ago
  It's not redundant. As you point out: selecting rows _before_ aggregation and _after_ are different beasts. You can't exclude rows from aggregation with HAVING.
  - dspillett 5y ago
    > You can't exclude rows from aggregation with HAVING. In theory¹ a DB that supports skip-scanning could use feedback from some HAVING clauses to short-circuit such a scan significantly, for instance if HAVING COUNT(*)<4 is present and a fourth row for that grouping comes up it can discard the row and skip to the next value. This would likely only be useful for indexes containing large numbers of the same few values though. [1] I'm not aware of any engine that does this. It might be that the time cost of assessing if the optimisation is worthwhile² far outways the sum benefit over time of the saving when it is actually found to be worthwhile. [2] Similarly, writing+testing+maintaining the code to do so might not be good use of dev time compared to other improvements
    - namibj 5y ago
      I believe I sorta did something like this recently in differential reasoner. See this code here: <insert link to the code range in differential reasoner in the file where I use the count trace to throw out ineligible ones>. It's only _kinda_ like this, though. I will post the link later, if necessary in a comment.
  - tgv 5y ago
    I don't think HAVING is strictly needed, but you need to use a sub-query. SELECT x ... HAVING y => SELECT * FROM (x ...) t WHERE y
    - throwawayffffas 5y ago
      Yeah, you are correct it's really just syntactic sugar for that. As another comment points out, WHERE and HAVING are just σ in relational algebra.
CPLX 5y ago
SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING employees_no < 10; This phrase returns departments where the number of female employees is <10 right, rather than listing female employees from departments that have <10 employees in total?
- deleted 5y ago
  [deleted]
- cm2187 5y ago
  should return an error, the select part is read last by the DB, so whatever you define in the select part (employees_no) is not available to the having clause. SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10; The logical order in which the DB processes the query is: FROM Employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10 SELECT department_id, count(*) as employees_no so you apply count(*) < 10 after you filtered by gender and after you grouped by department_id
  - magicalhippo 5y ago
    > should return an error Except some databases allow for that syntax. The one we use, SQLAnywhere, does.
  - totoglazer 5y ago
    Depends on the db. Plenty make such aliases available for having & order by.
  - deleted 5y ago
    [deleted]
- wccrawford 5y ago
  That sounds right to me. It creates counts of female employees in each department, and then only outputs the departments that have 10 or more.
pfarrell 5y ago
In one interview I had, I was asked, "What are you good at?". I said, "Pretty good with SQL stuff". This was _literally_ the question they asked. It was a good jumping off point for them to probe how much I knew. I like this article's explanation. The thing that trips me up on specific databases, is whether aliases assigned in the "select" portion are available in the "group by" sections.
- cm2187 5y ago
  Are there databases where the alias defined in the select part of the query is available in the group by/having section?
  - simondotau 5y ago
    MySQL/MariaDB certainly permits their use in GROUP BY, HAVING and ORDER BY.
  - MrDOS 5y ago
    Postgres reference SELECTed aliases in ORDER BY/GROUP BY clauses, but _not_ in HAVING: test=# create table event (who text not null, test(# what text not null, test(# at timestamptz not null default now()); CREATE TABLE test=# insert into event (who, what) test-# values ('Bob', 'sent a message'), test-# ('Alice', 'received a message'), test-# ('Alice', 'sent a message receipt'), test-# ('Bob', 'received the message receipt'), test-# ('Bob', 'waited patiently for a response'), test-# ('Bob', 'worried whether his message had been well-received'), test-# ('Bob', 'paced'), test-# ('Alice', 'forgot to reply for two weeks'), test-# ('Bob', 'began to panic'), test-# ('Bob', 'fled the country for Moldova in a fit of panic'), test-# ('Alice', 'finally remembered to reply'), test-# ('Bob', 'never got it'); INSERT 0 12 test=# select who as "The poor sod", test-# count(what) as "Events" test-# from event test-# group by "The poor sod" test-# order by "Events" desc; The poor sod | Events --------------+-------- Bob | 8 Alice | 4 (2 rows)
throwawayffffas 5y ago
Nothing of essence in this post. I had a look at the other posts they all look similar. I greatly dislike this new trend of "doc-spaming" where everyone makes a blog writing walls of text explaining trivial things, crowding out the actual docs from search results. I swear to god whenever I search for something in the python standard library I have to scroll past a bunch of sites like this before i find the actual docs.
- throwawayffffas 5y ago
  A much better resource if you are new and want to understand the difference between where and having is here near the bottom. https://www.postgresql.org/docs/14/tutorial-agg.html https://www.postgresql.org/docs/14/tutorial-agg.html
  - RobertRoberts 5y ago
    Relevant snippet: > It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.) Thank you for linking this, it's very concise and actually helpful.
- bgro 5y ago
  The recipe writing community is leaking into the developer community.
  - dspillett 5y ago
    Coming soon to a dev recipes site near you: post after post starting with a four paragraph retelling of a trip to the countryside, the packing for which made the writer think of custom aggregation functions...
barrkel 5y ago
There's no good reason for using two different keywords for filtering and it harms composability. But of course SQL isn't very composable anyhow. Preferably, SQL would look more like: FROM Foo f WHERE f.value < 10 JOIN Bar b on b.id = f.bar_id WHERE b.other_value > 20 SELECT f.group, f.value GROUP BY f.group the_group AGGREGATING SUM(f.value) the_sum WHERE the_sum > 100 ORDER BY the_group And so on. FROM introduces a source of data which flows into the next line. SELECT does a projection, GROUP BY applies aggregation operators on groups and also does a projection. WHERE could be inserted pretty much anywhere.
- goto11 5y ago
  This is basically how Linq (in .net) does it. It is much more composable than SQL but still implement the relational algebra.
  - wongarsu 5y ago
    And LINQ is converted into SQL if you do LINQ queries against a database. The queries are sometimes far from optimal, but the developer experience is quite nice.
- Pxtl 5y ago
  Yup. To be blunt: SQL is a flawed old language that should've been replaced decades ago, but every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too. It was a fine language for its time. So was Pascal, and FORTRAN. We don't use those anymore generally for a variety of good reasons. SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. edit: I'm getting "You're posting too fast" so I can't continue this conversation, but no being able to express "unknown" is not worth throwing out the reflexive property of equality. That is a bad trade-off and I will die on this hill.
  - mrbungie 5y ago
    It may be flawed, it may be old, but it clearly continues to be sufficiently expressive and useful. Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.
jastr 5y ago
The article does a good job of explaining the difference between WHERE and HAVING. The simplest resource I've found for this is Julia Evans' "SQL queries run in this order" [0], which points out for example that SELECTs are one of the last things to run in a query. I've managed software teams and data engineering teams, and both teams get tripped up with even moderate SQL queries. To simplify, we encouraged teams to use a clearer subset of SQL. Most HAVING can be replaced with a WHERE inside a more readable and explicit subquery. Similarly, we got rid of most RIGHT JOINS. [0] https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/ https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
rubyist5eva 5y ago
HAVING is after aggregation, WHERE is before. /thread
JanecekPetr 5y ago
For me the aha moment was to understand the logical order of SQL operations: https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/a-beginners-guide-to-the-true-order-of.... Never had a problem since.
simondotau 5y ago
select category, sum(amount) as total from mytable group by category HAVING total > 100 is equal to (and potentially more performant than) select category, total from ( select category, sum(amount) as total from mytable group by category ) as mysubquery WHERE total > 100
- MrDOS 5y ago
  On my local Postgres 13 instance, the EXPLAINation of both of those queries looks identical – which is a trap, because the path of the query planner's understanding is narrow. It's quite easy to write a subquery which must be evaluated over and over again for each row of the parent query. Grouping (either inside or outside of the subquery) is usually a good way to trigger this pathological behaviour (which sucks, because grouping is often where you want to use a subquery!). I usually find CTEs to be a relatively tolerable middle ground of readability and performance (although I don't run from HAVING, either): with category_totals as (select category, sum(amount) as total from mytable group by category) select category, total from category_totals where total > 100; Either way, subqueries are a code smell for me.
  - simondotau 5y ago
    My fully custom website (thousands of pages per minute) has an authentication query that runs on every page—and it has a subquery inside a subquery inside a subquery. That's four layers of SQL inception. And it executes in under ten milliseconds. Subqueries are indeed a performance red flag when the query is returning more than a few rows AND the subquery references fields outside of the subquery. Or put more simply, a subquery that will need to be executed an unreasonable number of times per run. But so long as you aren't doing anything like that, and your database engine has a competent query planner, there should be zero difference in performance. For this reason I don't consider subqueries to be a code smell. For me a query is "smelly" when it does things which aren't obvious to a human parsing the query with their brain.
mwexler 5y ago
WHERE folks HAVING difficulty with this concept? Sorry, couldn't resist. But seriously, this concept doesn't seem to require as much attention as this post gives it.
crateless 5y ago
Are there any resources e.g books, blogs etc. that have a low-level description of the data structures/algos involved when SQL queries are run?
- defaultcompany 5y ago
  You might look at SQLToy which is a DB and blog I wrote to teach how SQL processing works internally: https://github.com/weinberg/SQLToy https://github.com/weinberg/SQLToy

Is there any book that has the exact specifications of this dumpster fire that is called sql? With no other language I have faced so many gotchas with implicit conversions and unexpected behaviors.

brigandish 5y ago
Surely you've used Javascript?
ziggus 5y ago
I've found that most traditional developers have a rough time with SQL, because they're trying to use it like a procedural language, which it isn't - it's relational algebra/calculus based. Cornell has a good explanation: https://www.cs.cornell.edu/projects/btr/bioinformaticsschool/slides/gehrke.pdf https://www.cs.cornell.edu/projects/btr/bioinformaticsschool...
- ttfkam 5y ago
  Exactly! It's a DSL for set theory. A true 4th-gen language. Perhaps the only truly successful 4GL.
em500 5y ago
https://www.iso.org/committee/45342/x/catalogue/p/1/u/0/w/0/d/0 https://www.iso.org/committee/45342/x/catalogue/p/1/u/0/w/0/... If you want to take a peek at an older revision: http://web.cecs.pdx.edu/~len/sql1999.pdf http://web.cecs.pdx.edu/~len/sql1999.pdf (I'm guessing this will be another case of be careful what you wish for).

gpvos 5y ago

Huh, I always thought HAVING was the equivalent for GROUP BY that WHERE is for SELECT, i.e. that it happened during aggregation, just as WHERE happens during data retrieval from the table. But apparently you can use HAVING instead of WHERE and it would work the same even if you don't have a GROUP BY clause (and even though it would possibly be less efficient)? Seems a bit redundant.

maweki 5y ago
It's not redundant. As you point out: selecting rows _before_ aggregation and _after_ are different beasts. You can't exclude rows from aggregation with HAVING.
- dspillett 5y ago
  > You can't exclude rows from aggregation with HAVING. In theory¹ a DB that supports skip-scanning could use feedback from some HAVING clauses to short-circuit such a scan significantly, for instance if HAVING COUNT(*)<4 is present and a fourth row for that grouping comes up it can discard the row and skip to the next value. This would likely only be useful for indexes containing large numbers of the same few values though. [1] I'm not aware of any engine that does this. It might be that the time cost of assessing if the optimisation is worthwhile² far outways the sum benefit over time of the saving when it is actually found to be worthwhile. [2] Similarly, writing+testing+maintaining the code to do so might not be good use of dev time compared to other improvements
  - namibj 5y ago
    I believe I sorta did something like this recently in differential reasoner. See this code here: <insert link to the code range in differential reasoner in the file where I use the count trace to throw out ineligible ones>. It's only _kinda_ like this, though. I will post the link later, if necessary in a comment.
- tgv 5y ago
  I don't think HAVING is strictly needed, but you need to use a sub-query. SELECT x ... HAVING y => SELECT * FROM (x ...) t WHERE y
  - throwawayffffas 5y ago
    Yeah, you are correct it's really just syntactic sugar for that. As another comment points out, WHERE and HAVING are just σ in relational algebra.

CPLX 5y ago

SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING employees_no < 10; This phrase returns departments where the number of female employees is <10 right, rather than listing female employees from departments that have <10 employees in total?

deleted 5y ago
[deleted]
cm2187 5y ago
should return an error, the select part is read last by the DB, so whatever you define in the select part (employees_no) is not available to the having clause. SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10; The logical order in which the DB processes the query is: FROM Employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10 SELECT department_id, count(*) as employees_no so you apply count(*) < 10 after you filtered by gender and after you grouped by department_id
- magicalhippo 5y ago
  > should return an error Except some databases allow for that syntax. The one we use, SQLAnywhere, does.
- totoglazer 5y ago
  Depends on the db. Plenty make such aliases available for having & order by.
- deleted 5y ago
  [deleted]
wccrawford 5y ago
That sounds right to me. It creates counts of female employees in each department, and then only outputs the departments that have 10 or more.

pfarrell 5y ago

In one interview I had, I was asked, "What are you good at?". I said, "Pretty good with SQL stuff". This was _literally_ the question they asked. It was a good jumping off point for them to probe how much I knew. I like this article's explanation. The thing that trips me up on specific databases, is whether aliases assigned in the "select" portion are available in the "group by" sections.

cm2187 5y ago
Are there databases where the alias defined in the select part of the query is available in the group by/having section?
- simondotau 5y ago
  MySQL/MariaDB certainly permits their use in GROUP BY, HAVING and ORDER BY.
- MrDOS 5y ago
  Postgres reference SELECTed aliases in ORDER BY/GROUP BY clauses, but _not_ in HAVING: test=# create table event (who text not null, test(# what text not null, test(# at timestamptz not null default now()); CREATE TABLE test=# insert into event (who, what) test-# values ('Bob', 'sent a message'), test-# ('Alice', 'received a message'), test-# ('Alice', 'sent a message receipt'), test-# ('Bob', 'received the message receipt'), test-# ('Bob', 'waited patiently for a response'), test-# ('Bob', 'worried whether his message had been well-received'), test-# ('Bob', 'paced'), test-# ('Alice', 'forgot to reply for two weeks'), test-# ('Bob', 'began to panic'), test-# ('Bob', 'fled the country for Moldova in a fit of panic'), test-# ('Alice', 'finally remembered to reply'), test-# ('Bob', 'never got it'); INSERT 0 12 test=# select who as "The poor sod", test-# count(what) as "Events" test-# from event test-# group by "The poor sod" test-# order by "Events" desc; The poor sod | Events --------------+-------- Bob | 8 Alice | 4 (2 rows)

throwawayffffas 5y ago

Nothing of essence in this post. I had a look at the other posts they all look similar. I greatly dislike this new trend of "doc-spaming" where everyone makes a blog writing walls of text explaining trivial things, crowding out the actual docs from search results. I swear to god whenever I search for something in the python standard library I have to scroll past a bunch of sites like this before i find the actual docs.

throwawayffffas 5y ago
A much better resource if you are new and want to understand the difference between where and having is here near the bottom. https://www.postgresql.org/docs/14/tutorial-agg.html https://www.postgresql.org/docs/14/tutorial-agg.html
- RobertRoberts 5y ago
  Relevant snippet: > It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.) Thank you for linking this, it's very concise and actually helpful.
bgro 5y ago
The recipe writing community is leaking into the developer community.
- dspillett 5y ago
  Coming soon to a dev recipes site near you: post after post starting with a four paragraph retelling of a trip to the countryside, the packing for which made the writer think of custom aggregation functions...

barrkel 5y ago

There's no good reason for using two different keywords for filtering and it harms composability. But of course SQL isn't very composable anyhow. Preferably, SQL would look more like: FROM Foo f WHERE f.value < 10 JOIN Bar b on b.id = f.bar_id WHERE b.other_value > 20 SELECT f.group, f.value GROUP BY f.group the_group AGGREGATING SUM(f.value) the_sum WHERE the_sum > 100 ORDER BY the_group And so on. FROM introduces a source of data which flows into the next line. SELECT does a projection, GROUP BY applies aggregation operators on groups and also does a projection. WHERE could be inserted pretty much anywhere.

goto11 5y ago
This is basically how Linq (in .net) does it. It is much more composable than SQL but still implement the relational algebra.
- wongarsu 5y ago
  And LINQ is converted into SQL if you do LINQ queries against a database. The queries are sometimes far from optimal, but the developer experience is quite nice.
Pxtl 5y ago
Yup. To be blunt: SQL is a flawed old language that should've been replaced decades ago, but every time somebody tries to replace it they throw the baby out with the bathwater and throw out the relational theory too. It was a fine language for its time. So was Pascal, and FORTRAN. We don't use those anymore generally for a variety of good reasons. SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken. edit: I'm getting "You're posting too fast" so I can't continue this conversation, but no being able to express "unknown" is not worth throwing out the reflexive property of equality. That is a bad trade-off and I will die on this hill.
- mrbungie 5y ago
  It may be flawed, it may be old, but it clearly continues to be sufficiently expressive and useful. Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.

jastr 5y ago

The article does a good job of explaining the difference between WHERE and HAVING. The simplest resource I've found for this is Julia Evans' "SQL queries run in this order" [0], which points out for example that SELECTs are one of the last things to run in a query. I've managed software teams and data engineering teams, and both teams get tripped up with even moderate SQL queries. To simplify, we encouraged teams to use a clearer subset of SQL. Most HAVING can be replaced with a WHERE inside a more readable and explicit subquery. Similarly, we got rid of most RIGHT JOINS. [0] https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/ https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...

rubyist5eva 5y ago

HAVING is after aggregation, WHERE is before. /thread

JanecekPetr 5y ago

For me the aha moment was to understand the logical order of SQL operations: https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/a-beginners-guide-to-the-true-order-of.... Never had a problem since.

simondotau 5y ago

select category, sum(amount) as total from mytable group by category HAVING total > 100 is equal to (and potentially more performant than) select category, total from ( select category, sum(amount) as total from mytable group by category ) as mysubquery WHERE total > 100

MrDOS 5y ago
On my local Postgres 13 instance, the EXPLAINation of both of those queries looks identical – which is a trap, because the path of the query planner's understanding is narrow. It's quite easy to write a subquery which must be evaluated over and over again for each row of the parent query. Grouping (either inside or outside of the subquery) is usually a good way to trigger this pathological behaviour (which sucks, because grouping is often where you want to use a subquery!). I usually find CTEs to be a relatively tolerable middle ground of readability and performance (although I don't run from HAVING, either): with category_totals as (select category, sum(amount) as total from mytable group by category) select category, total from category_totals where total > 100; Either way, subqueries are a code smell for me.
- simondotau 5y ago
  My fully custom website (thousands of pages per minute) has an authentication query that runs on every page—and it has a subquery inside a subquery inside a subquery. That's four layers of SQL inception. And it executes in under ten milliseconds. Subqueries are indeed a performance red flag when the query is returning more than a few rows AND the subquery references fields outside of the subquery. Or put more simply, a subquery that will need to be executed an unreasonable number of times per run. But so long as you aren't doing anything like that, and your database engine has a competent query planner, there should be zero difference in performance. For this reason I don't consider subqueries to be a code smell. For me a query is "smelly" when it does things which aren't obvious to a human parsing the query with their brain.

mwexler 5y ago

WHERE folks HAVING difficulty with this concept? Sorry, couldn't resist. But seriously, this concept doesn't seem to require as much attention as this post gives it.

crateless 5y ago

Are there any resources e.g books, blogs etc. that have a low-level description of the data structures/algos involved when SQL queries are run?

defaultcompany 5y ago
You might look at SQLToy which is a DB and blog I wrote to teach how SQL processing works internally: https://github.com/weinberg/SQLToy https://github.com/weinberg/SQLToy