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 AdventureWorks2008R2; GO SELECT c.CustomerID, s.Name FROM AdventureWorks2008R2.Sales.Customer c JOIN AdventureWorks2008R2.Sales.Store s ON s.BusinessEntityID = 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 AdventureWorks2008R2; 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 AdventureWorks2008R2.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 AdventureWorks2008R2.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 AdventureWorks2008R2.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 AdventureWorks2008R2.Production.Product WHERE Name LIKE 'Ch%' ORDER BY ProductID;
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 AdventureWorks2008R2.Sales.Customer c JOIN AdventureWorks2008R2.Sales.Store s ON c.CustomerID = S.CustomerID WHERE c.CustomerID IS NOT NULL ORDER BY s.Name;
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 AdventureWorks2008R2; 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 AdventureWorks2008R2.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 BusinessEntityID FROM AdventureWorks2008R2.Sales.Store WHERE Name = N'Riders Company';