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'

Filtering Rows with WHERE and HAVING

See Also