• Article

Filtering Rows by Using WHERE and HAVING

The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. For example, the WHERE clause in the following SELECT statement qualifies the rows only to a specific sales territory.

USE AdventureWorks2008R2;
GO
SELECT c.CustomerID, s.Name
FROM AdventureWorks2008R2.Sales.Customer c
JOIN AdventureWorks2008R2.Sales.Store s
ON s.BusinessEntityID = c.CustomerID
WHERE c.TerritoryID = 1;

The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. However, HAVING can be specified without GROUP BY. The HAVING clause specifies additional filters that are applied after the WHERE clause filters. These filters can be applied to an aggregate function used in the select list. In the following example the WHERE clause only qualifies orders selling a product with a unit price exceeding $100, and the HAVING clause additionally restricts the result to only those orders that include more than 100 units.

USE AdventureWorks2008R2;
GO
SELECT OrdD1.SalesOrderID AS OrderID,
       SUM(OrdD1.OrderQty) AS "Units Sold",
       SUM(OrdD1.UnitPrice * OrdD1.OrderQty) AS Revenue
FROM Sales.SalesOrderDetail AS OrdD1
WHERE OrdD1.SalesOrderID in (SELECT OrdD2.SalesOrderID
    FROM Sales.SalesOrderDetail AS OrdD2
    WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.SalesOrderID
HAVING SUM(OrdD1.OrderQty) > 100;

Search Conditions in the WHERE and HAVING Clauses

The search conditions, or qualifications, in the WHERE and HAVING clauses can include the following:

  • Comparison operators, such as: =, < >, <, and >

    For example, the following query retrieves the rows from the Product table for the products that are in class H.

    SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE Class = 'H'
ORDER BY ProductID;

  • Ranges (BETWEEN and NOT BETWEEN)

    For example, the following query retrieves rows from the Product table with list prices from $100 to $500.

    SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE ListPrice BETWEEN 100 and 500
ORDER BY ListPrice;

  • Lists (IN, NOT IN)

    For example, the following query retrieves products that fall in a list of colors.

    SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE Color IN ('Multi', 'Silver')
ORDER BY ProductID;

  • Pattern matches (LIKE and NOT LIKE)

    For example, the following query retrieves rows from the Product table in which the product name starts with the letters Ch.

    SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE Name LIKE 'Ch%'
ORDER BY ProductID;

    Note

    The only WHERE conditions that you can use on text columns are functions that return another data type, such as PATINDEX(); or the operators, such as IS NULL, IS NOT NULL, LIKE, and NOT LIKE.

  • Null values (IS NULL and IS NOT NULL)

    For example, the following query retrieves rows from the Customer table in which the salesperson IDs of the customers are not NULL.

    SELECT s.Name
FROM AdventureWorks2008R2.Sales.Customer c
JOIN AdventureWorks2008R2.Sales.Store s
ON c.CustomerID = S.CustomerID
WHERE c.CustomerID IS NOT NULL
ORDER BY s.Name;

    Note

    Use caution when comparing null values. For example, specifying = NULL is not the same as specifying IS NULL. For more information, see Null Values.

  • All records (=ALL, >ALL, <= ALL, ANY)

    For example, the following query retrieves order and product IDs from the SalesOrderDetail table in which the quantity of the product shipped is larger than the quantity shipped for any product in class H.

    USE AdventureWorks2008R2;
GO
SELECT OrdD1.SalesOrderID, OrdD1.ProductID
FROM Sales.SalesOrderDetail OrdD1
WHERE OrdD1.OrderQty > ALL
      (SELECT OrdD2.OrderQty
       FROM Sales.SalesOrderDetail OrdD2 JOIN Production.Product Prd
             ON OrdD2.ProductID = Prd.ProductID
       WHERE Prd.Class = 'H');
GO

  • Combinations of these conditions (AND, OR, NOT)

    For example, the following query retrieves all products for which either the list price is less than $500, or the product class is L and the product line is S.

    SELECT ProductID, Name
FROM AdventureWorks2008R2.Production.Product
WHERE ListPrice < 500
   OR (Class = 'L' AND ProductLine = 'S');

    Note that when you search for a Unicode string in a WHERE clause, put the N character before the search string:

    SELECT BusinessEntityID 
FROM AdventureWorks2008R2.Sales.Store 
WHERE Name = N'Riders Company';