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'