Useful TIP-Using <Where> and <Having> in the same query.

[raziel3]

Came across this little tip I never knew about

https://learn.microsoft.com/en-us/s...y-visual-database-tools?view=sql-server-ver16

very useful knowledge for people, like me, who uses aggregate queries a lot.

 

[plog]

Reader's Digested version:

WHERE - use when applying criteria to individual records
HAVING - use when applying criteria to an aggregation of records (MIN(), SUM(), COUNT(), AVG(), etc.)

...WHERE Table.FirstName = 'Jeff'
...WHERE Table.Quantity > 7

...HAVING SUM(Table.Quantity) > 100
...HAVING MAX(Table.Score) < 50

 

[ByteMyzer]

Here is a more concrete illustration of the differences between HAVING and WHERE clauses.

Table Name: StudentTests

StudentID | TestID | TestDate
----------+--------+----------
10123     | 1012   |  3/1/2022
10123     | 1023   |  9/6/2022
10123     | 1035   |11/17/2022
10123     | 1057   | 2/27/2023
10135     | 1012   |  3/1/2022
10135     | 1023   |  9/6/2022

SELECT ST.StudentID, MAX(ST.TestDate) AS TestDate
FROM StudentTests AS ST
GROUP BY ST.StudentID;

Result:
StudentID | TestDate
----------+----------
10123     | 2/27/2023
10135     |  9/6/2022

Task: Write a query that shows, for tests that took place in 2022, each Student ID with the latest test date.

SELECT ST.StudentID, MAX(ST.TestDate) AS TestDate
FROM StudentTests AS ST
WHERE ST.TestDate BETWEEN #1/1/2022# AND #12/31/2022#
GROUP BY ST.StudentID;

Result:
StudentID | TestDate
----------+----------
10123     |11/17/2022
10135     |  9/6/2022

Task: Write a query that shows each Student ID and the latest test date for whom the latest test date occurred in 2022.

SELECT ST.StudentID, MAX(ST.TestDate) AS TestDate
FROM StudentTests AS ST
GROUP BY ST.StudentID
HAVING MAX(ST.TestDate) BETWEEN #1/1/2022# AND #12/31/2022#;

Result:
StudentID | TestDate
----------+----------
10135     |  9/6/2022

 

[ebs17]

Without specifically testing @ByteMyzer's example, I'll say his statement is wrong, because different query logic was stored. Different logic, different results (depending on given data).

In such a simple design, with the same logic the result should always be the same, regardless of whether you use WHERE or HAVING.

The huge difference between the two lies in the order in which queries are evaluated and the resulting performance.

WHERE takes place immediately with the execution of FROM including JOINs and reduces the amount of data available. Only then does grouping take place. Grouping is a complex process because each record within the recordset under consideration must be compared with every other record to ensure that the content of the field or field combination is identical.
It makes a dramatic difference whether you use WHERE to reduce a set of, for example, 10,000 records to 300 and then group them or whether the grouping has to run across all 10,000 records; the number of comparison operations is significantly different.
When grouping, the support of an index would often be used and the comparison process mentioned would be accelerated again. Often, however, groupings are not just done using key fields or specifically indexed fields, but rather using a long list of fields where no index is guaranteed to be available.
A lot of work takes a lot of time.

HAVING then takes place after grouping and on aggregates formed.

Conclusion: With a simple operation (WHERE) you reduce the amount of data before executing a more complex operation and thus increase efficiency. This cannot happen when using HAVING.

 

[isladogs]

Adding on to the detailed explanation given by @ebs17, see my article comparing the efficiency (speed) of using WHERE vs HAVING

Speed Comparison Tests 4

This article compares the efficiency of using a WHERE clause or a HAVING clause in aggregate queries. It is the fourth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on...

www.isladogs.co.uk

For the differences to be significant, use indexing on the fields used in the filtering.

 

[ByteMyzer]

Without specifically testing @ByteMyzer's example, I'll say his statement is wrong, because different query logic was stored. Different logic, different results (depending on given data).

Why not test it, then, and prove me wrong?

In such a simple design, with the same logic the result should always be the same, regardless of whether you use WHERE or HAVING.

The result will not be the same between the two queries. As @Pat Hartman pointed out,

WHERE - Before aggregation
HAVING - After aggregation

In my example with:

WHERE ST.TestDate BETWEEN #1/1/2022# AND #12/31/2022#

...the criterion is applied to the dataset before the grouping takes place, so you will see the results for the two students, ONLY from the tests with the dates taking place in 2022.

In my example with:

HAVING MAX(ST.TestDate) BETWEEN #1/1/2022# AND #12/31/2022#;

The grouping takes place, with the MAX derivations on the TestDate field, and THEN the criterion is applied to the resulting dataset. So, because the maximum test date for Student ID 10123 is 2/27/2023, no result is returned in the HAVING query for this student.

 

[Josef P.]

WHERE - Before aggregation
HAVING - After aggregation

I would extend this with:
Having only for conditions with aggregate functions - everything else belongs in the Where section.

And now I'll add some confusion or clarity, depending on how you want to look at it
The DBEngine (Jet/ACE) makes a Where out of Having when creating the show plan, if the condition allows it, such as a filter on a certain value.

Example:
Query TestTab_GroupBy_Where:

SELECT T, Count(*) AS Cnt
FROM TestTab
WHERE N = 1
GROUP BY T

ShowPlan output:

--- TestTab_GroupBy_Where ---

- Inputs to Query -
Table 'TestTab'
- End inputs to Query -

01) Restrict rows of table TestTab
      using rushmore
      for expression "N=1"
02) Group result of '01)'

Query TestTab_GroupBy_Having:

SELECT T, Count(*) AS Cnt
FROM TestTab
GROUP BY T, N
HAVING N=1;

ShowPlan output:

--- TestTab_GroupBy_Having ---

- Inputs to Query -
Table 'TestTab'
- End inputs to Query -

01) Restrict rows of table TestTab
      using rushmore
      for expression "N=1"
02) Group result of '01)'

Nevertheless, I always recommend using Where for "normal" conditions, as this makes the query logic more visible.

/edit: Colin shows this well in his article. (Link in #6)

 

[ByteMyzer]

@Josef P.
What does the plan show for the following query?

SELECT T, Count(*) AS Cnt
FROM TestTab
GROUP BY T, N
HAVING Count(*) > 1;

 

[Josef P.]

Unfortunately only Group table ... Showplan does not show the use of Having Count(*)>1.

- Inputs to Query -
Table 'TestTab'
- End inputs to Query -

01) Group table 'TestTab'

You can see this better in the SQL server:

SELECT T, Count(*) AS Cnt
FROM TestTab
WHERE N = 1
GROUP BY T
HAVING count(*)>1;


SELECT T, Count(*) AS Cnt
FROM TestTab
GROUP BY T, N
HAVING N=1 and count(*)>1;

=>

|--Filter(WHERE:([Expr1002]>(1)))
       |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
            |--Stream Aggregate(GROUP BY:([TestDb].[dbo].[TestTab].[T]) DEFINE:([Expr1005]=Count(*)))
                 |--Index Seek(OBJECT:([TestDb].[dbo].[TestTab].[IX_N]), SEEK:([TestDb].[dbo].[TestTab].[N]=(1)) ORDERED FORWARD)

  |--Filter(WHERE:([Expr1002]>(1)))
       |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
            |--Stream Aggregate(GROUP BY:([TestDb].[dbo].[TestTab].[T]) DEFINE:([Expr1005]=Count(*)))
                 |--Index Seek(OBJECT:([TestDb].[dbo].[TestTab].[IX_N]), SEEK:([TestDb].[dbo].[TestTab].[N]=(1)) ORDERED FORWARD)

 

[ebs17]

The QBE is not smart enough

As Josef showed, the SQL optimizer is correspondingly smarter and can in many cases clean up the mess that the QBE and thus another Access component creates.
But I also wouldn't want to rely on errors being compensated for internally.

That means YOU, the developer, MUST make a conscious determination that a WHERE is called for and change the HAVING to a WHERE.

But that doesn't show any help or operating instructions. The real problem is that many believe and are encouraged to believe that the QBE produces decent SQL code that can be adopted without criticism - and there is no need to really bother with the actual SQL programming language.
I can only shake my head.

 

[ebs17]

so what's the problem with using the QBE?

1) An error is first generated that needs to be corrected.
2) The belief of many that the QBE does everything right, along with a quasi-encouragement to rely on this assistant and not really bother with the native SQL programming language. This “habit” is then carried on over and over again.