Having, a less understood SQL clause

Post by hobs » Sat, Aug 06, 2022, 12:33 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)

Post by eatonphil » Sat, Aug 06, 2022, 12:55 PM UTC

nerdponx wrote: post #46

CodeIsTheEnd wrote: post #13

Earlier quoted context omitted.

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?

Post by debugnik » Sat, Aug 06, 2022, 1:09 PM UTC

samatman wrote: post #48

kevincox wrote: post #34

Earlier quoted context omitted.

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…

Post by qsort » Sat, Aug 06, 2022, 1:22 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)

   WITH A AS (
        SELECT x, sum(y) AS z
          FROM SomeTable
      GROUP BY x
   )

   SELECT * FROM A WHERE z > 10

Post by mwexler » Sat, Aug 06, 2022, 1:28 PM UTC

snidane wrote: post #49

occamrazor wrote: post #42

Earlier quoted context omitted.

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 price 10000 You can refer to the aliased price column before or after aggregation using where or having. Depending on the sql engine.