71–80 of 83 posts
Forget HAVING -- in my opinion RANK is the most useful SQL function that barely anyone knows about. Has gotten me out of more sticky query issues than I can count.
Earlier quoted context omitted.
The only “standard” feature id rather try not to understand is recursive CTEs lol
it's pretty useful when working with hierarchical data, but you do not to put some check for cyclical relations, I have seen those take an application down :D.
CTEs are a great feature for readability, but when using them be careful to test your work on data at least as large as you expect to see in production over the life of the statements you are working on, in all DBMSs you support if your project is multi-platform.
Earlier quoted context omitted.
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;
Why wouldn't SELECT * FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income FROM sales) WHERE nb_sales > 10 AND income work just like your example?
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.
Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)
I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together. Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP…
If I had a fancy DB I could use CUBE or GROUPING SETS and MATERIALIZED VIEWs to easily pre-calculate statistics for every combination of filter parameters that automatically get updated when the source data changed. But I had MariaDB so I made do. I ended up with something like this:
SELECT ... SUM(ABS(r.ilength)) AS distance, COUNT(*) AS intervals FROM r
GROUP BY average_retro_bucket, customer, `year`, lane_type, material_type, state, county, district WITH ROLLUP
HAVING average_retro_bucket IS NOT NULL AND customer IS NOT NULL;
So you can query like this to get stats for all districts in CA->Mendocino county:
SELECT * FROM stats_table WHERE state = 'CA' AND county = 'Mendocino' AND district IS NULL
SELECT * FROM stats_table WHERE state = 'CA' AND county IS NULL AND district IS NULL
SELECT * FROM stats_table WHERE year IS NULL AND state = 'CA'
I worked around not having materialized views by creating an EVENT that would re-create the stats tables every night. The stats don't really need to be real-time. Re-writing the multiple-GB statistics tables every night will wear out the SSDs in 20 years or so, oh well.
Having is the where-clause for Group By. It's easier to understand by thinking the SQL query as a pipeline. Stage 1: From returns the whole world of rows. Stage 2: Where filters down to the desired set of rows. Stage 3: Group By aggregates the filtered rows. Stage 4: Having filters again on the aggregated result. Stage 5: Select picks out the columns.
What I never understood is why HAVING and WHERE are different clauses. AFAIU, there are no cases where both could be used, so why can’t one simply use WHERE after a GROUP BY? (I know that I am probably missing some important technical points, I would like to learn about them)
Earlier quoted context omitted.
Why wouldn't SELECT * FROM (SELECT year, COUNT(*) as nb_sales, SUM(price) as income FROM sales) WHERE nb_sales > 10 AND income work just like your example?
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.
> Subselects can't do something like: (…)
which is wrong.
Forget HAVING -- in my opinion RANK is the most useful SQL function that barely anyone knows about. Has gotten me out of more sticky query issues than I can count.
Earlier quoted context omitted.
It doesn't irk me anything like as much as 'a Docker' (a Docker what? Usually container) for some reason. Although perhaps that shouldn't annoy me anyway, it ought to be better than being specific but inaccurate (which you could probably expect from someone unfamiliar enough to say 'a Docker') - mixing up image/container as people do.
Agree. Let me create a quick Jira for that.
Yeah, that really irritates me for some reason.
Earlier quoted context omitted.
It doesn't irk me anything like as much as 'a Docker' (a Docker what? Usually container) for some reason. Although perhaps that shouldn't annoy me anyway, it ought to be better than being specific but inaccurate (which you could probably expect from someone unfamiliar enough to say 'a Docker') - mixing up image/container as people do.
Agree. Let me create a quick Jira for that.
Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)
I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together. Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP…
MS SQL Server has native pivot support, and Postgres has a limited emulation of it through the crosstab(...) function. https://stackoverflow.com/a/11751905 https://www.postgresql.org/docs/current/tablefunc.html
For folks just learning about ROLLUP et al, I highly recommend this comparison chart for an overview of major features offered by modern relational databases. https://www.sql-workbench.eu/dbms_comparison.html
There's a whole constellation of advanced features out there that arguably most application developers are largely unaware of. (Which explains why most app devs still treat relational databases like dumb bit buckets at the far end of their ORMs.)