TSQL Tutorial : Having

HAVING

The HAVING clause is used to limit the rows returned by a SELECT with GROUP BY. Its relationship to

GROUP BY is similar to the relationship between the WHERE clause and the SELECT itself. Like the WHERE

clause, it restricts the rows returned by a SELECT statement. Unlike WHERE, it operates on the rows in the

result set rather than the rows in the query's tables. Here's the previous query modified to include a

HAVING clause:

SELECT customers.CustomerNumber, customers.LastName, SUM(orders.Amount) AS

TotalOrders

FROM customers JOIN orders ON customers.CustomerNumber=orders.CustomerNumber

GROUP BY customers.CustomerNumber, customers.LastName

HAVING SUM(orders.Amount) > 700

CustomerNumber LastName TotalOrders

-------------- -------- -----------

3 Citizen 86753.09

1 Doe 802.35

There is often a better way of qualifying a query than by using a HAVING clause. In general, HAVING is

Less efficient than WHERE because it qualifies the result set after it's been organized into groups; WHERE

does so beforehand. Here's an example that improperly uses the HAVING clause:

-- Bad SQL - don't do this

SELECT customers.LastName, COUNT(*) AS NumberWithName

FROM customers

GROUP BY customers.LastName

HAVING customers.LastName<>'Citizen'

Properly written, this query's filter criteria should be in its WHERE clause, like so:

SELECT customers.LastName, COUNT(*) AS NumberWithName

FROM customers

WHERE customers.LastName<> 'Citizen'

GROUP BY customers.LastName

In fact, SQL Server recognizes this type of HAVING misuse and translates HAVING into WHERE during

Query execution. Regardless of whether SQL Server catches errors like these, it's always better to write

Optimal code in the first place.

TSQL Tutorial...