Alternative SQL queries-CodePudding

This query shown here is used to find an alternative way still need to do other two way too.

Consider the following SQL code:

SELECT building, SUM(budget) AS totalbudget
FROM department 
GROUP BY building
HAVING SUM(budget) > 10000;

Create three alternative ways to get the same exact result eliminating the HAVING clause. And for each alternative explain the impact of the query on performance?

First way:

SELECT building, totalbudget
FROM 
    (SELECT building, SUM(budget) AS totalbudget
     FROM department 
     GROUP BY building)
WHERE totalbudget > 10000;

There are still two other ways...

CodePudding user response：

Option 1:

SELECT *
FROM   department
MATCH_RECOGNIZE(
  PARTITION BY building
  MEASURES
    SUM(budget) AS totalBudget
  PATTERN (^ all_rows  $)
  DEFINE
    all_rows AS 1 = 1
)
WHERE totalBudget > 10000

Option 2:

SELECT building,
       SUM(budget) AS totalbudget
FROM   department d
WHERE  10000 < (
  SELECT SUM(budget)
  FROM   department t
  WHERE  d.building = t.building
)
GROUP BY building

Option 3:

SELECT building,
       SUM(budget) AS totalbudget
FROM   department
GROUP BY building
ORDER BY totalbudget DESC
FETCH FIRST ( SELECT COUNT(*)
              FROM   (
                SELECT SUM(budget) AS total
                FROM   department
                GROUP BY building
              )
              WHERE total > 10000            ) ROWS ONLY;

And for each alternative explain the impact of the query on performance?

They'll all have worse performance than using HAVING.

db<>fiddle here