SQL: The difference between WHERE and HAVING

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 &quot;doc-spaming&quot; where everyone makes a blog writing walls of text explaining trivial things, crowding out the actual docs from search results.<p>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.

There&#x27;s no good reason for using two different keywords for filtering and it harms composability. But of course SQL isn&#x27;t very composable anyhow.<p>Preferably, SQL would look more like:<p><pre><code> FROM Foo f WHERE f.value &lt; 10 JOIN Bar b on b.id = f.bar_id WHERE b.other_value &gt; 20 SELECT f.group, f.value GROUP BY f.group the_group AGGREGATING SUM(f.value) the_sum WHERE the_sum &gt; 100 ORDER BY the_group </code></pre> 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.

In one interview I had, I was asked, &quot;What are you good at?&quot;. I said, &quot;Pretty good with SQL stuff&quot;. 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&#x27;s explanation. The thing that trips me up on specific databases, is whether aliases assigned in the &quot;select&quot; portion are available in the &quot;group by&quot; sections.

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&#x27;t have a GROUP BY clause (and even though it would possibly be less efficient)? Seems a bit redundant.

For me the aha moment was to understand the logical order of SQL operations: <a href="https:&#x2F;&#x2F;blog.jooq.org&#x2F;a-beginners-guide-to-the-true-order-of-sql-operations&#x2F;" rel="nofollow">https:&#x2F;&#x2F;blog.jooq.org&#x2F;a-beginners-guide-to-the-true-order-of...</a>. Never had a problem since.

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

<p><pre><code> select category, sum(amount) as total from mytable group by category HAVING total &gt; 100 </code></pre> is equal to (and potentially more performant than)<p><pre><code> select category, total from ( select category, sum(amount) as total from mytable group by category ) as mysubquery WHERE total &gt; 100</code></pre>

The article does a good job of explaining the difference between WHERE and HAVING. The simplest resource I&#x27;ve found for this is Julia Evans&#x27; &quot;SQL queries run in this order&quot; [0], which points out for example that SELECTs are one of the last things to run in a query.<p>I&#x27;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.<p>[0] <a href="https:&#x2F;&#x2F;jvns.ca&#x2F;blog&#x2F;2019&#x2F;10&#x2F;03&#x2F;sql-queries-don-t-start-with-select&#x2F;" rel="nofollow">https:&#x2F;&#x2F;jvns.ca&#x2F;blog&#x2F;2019&#x2F;10&#x2F;03&#x2F;sql-queries-don-t-start-with...</a>

WHERE folks HAVING difficulty with this concept? Sorry, couldn&#x27;t resist.<p>But seriously, this concept doesn&#x27;t seem to require as much attention as this post gives it.

HAVING is after aggregation, WHERE is before.<p>&#x2F;thread

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

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