Having is where for after the collation.
Having, a less understood SQL clause
61–70 of 83 posts
Re: Having, a less understood SQL clause
#62Having 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've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately fo…
Your suggestion is incorrect. CUBE is not part of the expression.
Re: Having, a less understood SQL clause
#63Having vs where is my first question to filter candidates who have less experience in sql than they claim.
Well, I used SQL professionally for at least 7 years in different capacities and “having” was a new construct to me. You might say that I suck at SQL or you might realize that there’s more than one way to achieve a result in SQL. I usually prefer “with” statements. I hope this is not the only criteria you use to filter candidates. In my mind a better question would be to state the problem and see if the candidate can…
It’s also a hint that the candidate is not curious and doesn’t thoroughly read the docs which is a minus.
Re: Having, a less understood SQL clause
#64Having 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've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately fo…
Cube gets all possible combinations of grouping sets. It´s like showing all subtotals in a pivot table. That´s different than just grouping on the lowest level without totals.
Re: Having, a less understood SQL clause
#65Earlier quoted context omitted.
I would gladly buy a book of "SQL Recipes" ranging from beginner-level to advanced stuff that uses features like this, ideally with coverage of at least a few popular database systems, but at minimum Postgres. Is there such a book?
In fact, Yugabyte is giving it away for free - https://downloads.yugabyte.com/marketing-assets/O-Reilly-SQL...
This is great! Thanks for the link. :)
Re: Having, a less understood SQL clause
#66Calling each select "a sql" is really cute
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.
Re: Having, a less understood SQL clause
#67Earlier quoted context omitted.
It's best to pretend that RIGHT JOIN doesn't exist, imnsho. For one thing, in SQLite, it doesn't. Which is a weak argument for not using it on supported systems. The other weak argument is that a RIGHT JOIN is just the b, a version of a LEFT JOIN a, b. When you add them up it's an extra concept, SQL execution flow is already somewhat unintuitive, and a policy of using one of the two ways of saying "everything from a…
> For one thing, in SQLite, it doesn't. It does now, since the latest release 3.39, along with full join.
That's great news!
It'll be a long time before one can use it in portable SQLite queries, for those cases where that matters. I'll continue to eschew the right join for the clarity of only thinking about that relation in one way, but we statically link SQLite for several good reasons, including being able to use new features as they arrive.
Full join is certainly a welcome addition.
Re: Having, a less understood SQL clause
#68Earlier quoted context omitted.
I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately fo…
Your suggestion is incorrect. CUBE is not part of the expression.
Well, I did say I wasn't familiar with it, but it's correct for `coalesce` and I don't think `cube` is different: https://www.postgresql.org/docs/current/cube.html
In the context of `group by` it's treated as grouping sets, but that's not its only use. (Though that does seem to be special cased in terms of parsing, since afaict - I can't find the full query BNF on mobile - `grouping sets` is not optional.)
Re: Having, a less understood SQL clause
#69HAVING 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, equiva…
Snowflake SQL also has the interesting feature QUALIFY. Their docs go into more detail ( 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-comp…
> 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
}
Re: Having, a less understood SQL clause
#70Earlier quoted context omitted.
Your suggestion is incorrect. CUBE is not part of the expression.
Well, I did say I wasn't familiar with it, but it's correct for `coalesce` and I don't think `cube` is different: https://www.postgresql.org/docs/current/cube.html In the context of `group by` it's treated as grouping sets, but that's not its only use. (Though that does seem to be special cased in terms of parsing, since afaict - I can't find the full query BNF on mobile - `grouping sets` is not optional.)
https://www.postgresql.org/docs/14/sql-select.html
GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]
grouping_element can be one of:
( )
expression
( expression [, ...] )
ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
CUBE ( { expression | ( expression [, ...] ) } [, ...] )
GROUPING SETS ( grouping_element [, ...] )