Having, a less understood SQL clause • Hacker News Board

Re: Having, a less understood SQL clause

#41

Post by mmsimanga » Sat, Aug 06, 2022, 11:41 AM UTC

beefield wrote: post #24

Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spen…

You are right about the LEFT join coupled with a filter in the WHERE clause sometimes makes the join a normal INNER join. Just when this is the case is difficult for me to work out. This is often a trick question in some SQL assessments I have seen. I say this as someone with over 20 years of SQL almost on a daily basis. I avoid such scenarios by using CTEs.

Re: Having, a less understood SQL clause

#42

Post by occamrazor » Sat, Aug 06, 2022, 11:49 AM UTC

ww520 wrote: post #19

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)

Re: Having, a less understood SQL clause

#43

Post by nerdponx » Sat, Aug 06, 2022, 11:52 AM UTC

teddyh wrote: post #3

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, 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-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)))

Re: Having, a less understood SQL clause

#44

Post by motoboi » Sat, Aug 06, 2022, 11:53 AM UTC

kevincox wrote: post #34

beefield wrote: post #24

Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spen…

I think left join is a bad name. Probably something like OPTIONAL JOIN or TRY JOIN would be more obvious. Of course the problem is then what do you call a right join? REVERSE OPTIONAL JOIN? But that is getting pretty confusing now. But maybe worth it because in my experience left is far more common.

It's actually pretty obvious name. You just need to visualize a Venn diagram and left, right and inner become obvious.

Re: Having, a less understood SQL clause

#45

Post by higeorge13 » Sat, Aug 06, 2022, 11:53 AM UTC

notimetorelax wrote: post #33

higeorge13 wrote: post #20

Having 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…

I would ask you more questions like window functions, cte, joins, etc. or how would you address certain questions, and tbh i would be really curious that you know good sql but haven’t heard of having.

In my hiring experience it always acted as my first sql impression of candidates and 100% filter of people who don’t actually know sql besides some basic inner joins and aggregations. There are always exceptions i guess. :-)

Re: Having, a less understood SQL clause

#46

Post by nerdponx » Sat, Aug 06, 2022, 12:03 PM UTC

CodeIsTheEnd wrote: post #13

galkk wrote: post #5

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…

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?

Re: Having, a less understood SQL clause

#47

Post by nerdponx » Sat, Aug 06, 2022, 12:04 PM UTC

rjbwork wrote: post #21

kmac_ wrote: post #17

Earlier quoted context omitted.

SQL opens up when used with OLAP schemas. Most devs are experienced in querying "object mapped" schemas where cube, roll up, etc. are not useful. Nothing bad per se, but it can give an impression that SQL is a bad language, when actually it clicks well with a proper data schema.

Indeed. I think your mind can really be opened by having to answer complex business questions with an expansive and well designed data warehouse schema. It's a shame it's such a relatively niche and unknown topic, especially in the startup world.

This is why the data engineers get paid the big bucks, and also why having a good data engineer is a lot more important than a good data scientist in the early stages of a company.

Re: Having, a less understood SQL clause

#48

Post by samatman » Sat, Aug 06, 2022, 12:19 PM UTC

kevincox wrote: post #34

beefield wrote: post #24

Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spen…

I think left join is a bad name. Probably something like OPTIONAL JOIN or TRY JOIN would be more obvious. Of course the problem is then what do you call a right join? REVERSE OPTIONAL JOIN? But that is getting pretty confusing now. But maybe worth it because in my experience left is far more common.

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 and matches from b" makes for a more consistent codebase.

I would hope a blue-sky relational query language wouldn't support two syntaxes for an operation which is non-commutative, when order is important it can and should be indicted by order.

Re: Having, a less understood SQL clause

#49

Post by snidane » Sat, Aug 06, 2022, 12:20 PM UTC

occamrazor wrote: post #42

ww520 wrote: post #19

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)

    select product
         , sum(price) as price 
      from table
     where price10000

You can refer to the aliased price column before or after aggregation using where or having. Depending on the sql engine.

Re: Having, a less understood SQL clause

#50

Post by samatman » Sat, Aug 06, 2022, 12:24 PM UTC

xivzgrev wrote: post #23

Can anyone explain why the query without having needs 14 separate queries? That seemed insane to me. It seems like the author is using one query per country. Where it seems like you’d just group by country and year, where country US You would need some unions to bolt on the additional aggregations, but it’s more like 4 queues, not 14 Eg select c.ctry_name, i.year_nbr, sum(i.item_cnt) as tot_cnt, sum(i.invoice_amt) as…

The generous reading, which I'm inclined to, is that it's illustrating OLAP techniques which one would actually apply to a relational database of high normalization.

It's tedious to construct an example database of, say, fifth normal form, which would show the actual utility of this kind of technique. So we're left with a highly detailed query, with some redundancies which wouldn't be redundant with more tables.