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 thos orders that include more than 100 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 customers' 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

IS [NOT] NULL

Operators

ISNULL

WHERE