Saturday, November 08, 2014

TSQL Tutorial : 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
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...

No comments:

Post a Comment