Filtering Rows with 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 this SELECT statement qualifies the rows only where the region is Washington State:

SELECT CustomerID, CompanyName

FROM Northwind.dbo.Customers

WHERE Region = 'WA'

The HAVING clause is typically used in conjunction with the GROUP BY clause, although it can be specified without GROUP BY. The HAVING clause specifies further filters that are applied after the WHERE clause filters. For example, this WHERE clause only qualifies orders selling a product with a unit price exceeding $100, and the HAVING clause further restricts the result to only the orders that includes 100 or more units:

SELECT OrdD1.OrderID AS OrderID,

       SUM(OrdD1.Quantity) AS "Units Sold",

       SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue

FROM [Order Details] AS OrdD1

WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID

                        FROM [Order Details] AS OrdD2

                        WHERE OrdD2.UnitPrice > $100)

GROUP BY OrdD1.OrderID

HAVING SUM(OrdD1.Quantity) > 100

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

• Comparison operators (such as =, < >, <, and >). For example, this query retrieves the rows from the Products table for the products that are in product category 2:

  SELECT ProductID, ProductName

  FROM Northwind.dbo.Products

  WHERE CategoryID = 2

  ORDER BY ProductID

    

• Ranges (BETWEEN and NOT BETWEEN). For example, this query retrieves rows from the Products table with categories from 2 to 4:

  SELECT CategoryID, ProductID, ProductName

  FROM Northwind.dbo.Products

  WHERE CategoryID BETWEEN 2 and 4

  ORDER BY CategoryID, ProductID

    

• Lists (IN, NOT IN). For example, this query retrieves rows from the Products table in which the Category ID matches one in a list of IDs:

  SELECT CategoryID, ProductID, ProductName

  FROM Northwind.dbo.Products

  WHERE CategoryID IN (1,4,5,7)

  ORDER BY CategoryID, ProductID

    

• Pattern matches (LIKE and NOT LIKE). For example, this query retrieves rows from the Products table in which the product name starts with the letters Ch:

  SELECT CategoryID, ProductID, ProductName

  FROM Northwind.dbo.Products

  WHERE ProductName LIKE 'Ch%'

  ORDER BY CategoryID, 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, this query retrieves rows from the Customers table in which the customer’s region is not NULL:

  SELECT CompanyName, City, Region, Country

  FROM Northwind.dbo.Customers

  WHERE Region IS NOT NULL

  ORDER BY CompanyName

    

  ---

  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, this query retrieves order and product IDs from the Order Details table in which the quantity of the product shipped is larger than the quantity shipped for any product in category 1:

  USE Northwind

  GO

  SELECT OrdD1.OrderID, OrdD1.ProductID

  FROM "Order Details" OrdD1

  WHERE OrdD1.Quantity > ALL

        (SELECT OrdD2.Quantity

         FROM "Order Details" OrdD2 JOIN Products Prd

               ON OrdD2.ProductID = Prd.ProductID

         WHERE Prd.CategoryID = 1)

  GO

    

• Combinations of these conditions (AND, OR, NOT). For example, this query retrieves all products for which either the stock level is lower than the reorder point or the product comes from supplier 15 and is in category 4:

  SELECT ProductID, ProductName

  FROM Northwind.dbo.Products

  WHERE UnitsInStock < ReorderLevel

     OR (SupplierID = 15 AND CategoryID = 4)

    

Note When you search for a Unicode string in a WHERE clause, place the N character before the search string, for example:

SELECT CompanyName, ContactName, Phone, Fax

FROM Northwind.dbo.Customers

WHERE CompanyName = N'Berglunds snabbköp'

See Also