What is the difference between WHERE clause and HAVING clause?

Links to the original text: https://www.codeproject.com/Articles/5164738/What-is-the-difference-between-WHERE-and-HAVING-cl

Catalog

What's the difference between Where and Having?

The WHERE Clause

HAVING clause

Combining the two: WHERE and HAVING

In this article, learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes!

All the examples in this article are based on Microsoft SQL Server Management Studio and AdventureWorks 2012 database.

What's the difference between Where and Having?

When using more advanced SQL, it may not be clear when it makes sense to use WHERE and HAVING clauses.

Although both clauses seem to do the same thing, they do it in different ways. In fact, their functions complement each other.

  • Using the WHERE clause is the filter record in the result. The filter proceeds before any grouping.
  • The HAVING clause is used to filter values in groups.

Before we move on, let's review the format of the SQL statement. It is

SELECT
FROM
WHERE
GROUP BY
HAVING

To help keep it right, I like to think about the execution order of SQL statements from top to bottom. This means that the WHERE clause is first applied to the result, and then the remaining rows are summarized according to GROUP BY.

The WHERE Clause

The WHERE clause is used to filter rows from results. for example

SELECT   COUNT(SalesOrderID)
FROM     Sales.SalesOrderDetail

Return 121,317 as count and query

SELECT   COUNT(SalesOrderID)
FROM     Sales.SalesOrderDetail
WHERE    UnitPrice > 200

Return 48,159 as count. This is because the WHERE clause filters out 73,158 Sales OrderDetails whose UnitPrice is less than or equal to 200 from the results.

HAVING clause

The HAVING clause is used to filter values in GROUP BY. You can use them to filter out groupings, as follows

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING   SalesOrderID > 50000

But their real strength lies in their ability to compare and filter aggregate function results. For example, you can choose all orders totaling more than $10,000.

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING   SUM(UnitPrice * OrderQty) > 10000

Because the visibility of the WHERE clause is one line at a time, SUM cannot be evaluated in all Sales Order IDs. Evaluate the HAVING clause after creating the grouping.

Combining the two: WHERE and HAVING

When the SQL statement has both WHERE clause and HAVING clause, remember to first apply the WHERE clause, then group the results, and finally filter the groups according to the HAVING clause.

In many cases, you can place WHERE conditions in the HAVING clause, such as

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING   SUM(UnitPrice * OrderQty) > 10000 
         AND SalesOrderID > 50000

Vs

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
WHERE    SalesOrderID > 50000
GROUP BY SalesOrderID
HAVING   SUM(UnitPrice * OrderQty) > 10000

If you can add conditions from where clause in the having clause, why even worry about WHERE? Can I use this query?

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING   SUM(UnitPrice * OrderQty) > 10000 AND LineTotal > 10

In fact, the query generates errors. The LineTotal column is not part of the list by field, nor is it the result of the total.

To be effective, the having clause can only compare the result of an aggregation function or the column part of a group.

For validity, the query must be rewritten to

SELECT   SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM     Sales.SalesOrderDetail
WHERE    LineTotal > 100
GROUP BY SalesOrderID
HAVING   SUM(UnitPrice * OrderQty) > 10000

Summarize the differences between WHERE and HAVING:

  • WHERE is used to filter records before any packet occurs.
  • HAVING is used to filter values after composition into groups. Only columns or expressions in a group can be included in the condition of the HAVING clause.

Tags: SQL Database less

Posted on Thu, 12 Sep 2019 06:51:12 -0700 by mattlatos