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...
No comments:
Post a Comment