Export (0) Print
Expand All

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 AdventureWorks;
GO
SELECT c.CustomerID, s.Name
FROM AdventureWorks.Sales.Customer c
JOIN AdventureWorks.Sales.Store s
ON s.CustomerID = 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 AdventureWorks;
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

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 AdventureWorks.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 AdventureWorks.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 AdventureWorks.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 AdventureWorks.Production.Product
    WHERE Name LIKE 'Ch%'
    ORDER BY ProductID
    
    NoteNote

    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 AdventureWorks.Sales.Customer c
    JOIN AdventureWorks.Sales.Store s
    ON c.CustomerID = S.CustomerID
    WHERE c.SalesPersonID IS NOT NULL
    ORDER BY s.Name
    
    NoteNote

    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 AdventureWorks;
    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 AdventureWorks.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 CustomerID 
    FROM AdventureWorks.Sales.Store 
    WHERE Name = N'Berglunds snabbkp'
    

Community Additions

ADD
Show:
© 2014 Microsoft