WHERE

The WHERE clause specifies the search condition that determines whether a row should be returned in the result set. Rows will be returned only if the entire WHERE clause evaluates to TRUE. Each condition within the WHERE clause is referred to as a predicate. There is no limit to the number of predicates that can appear in a WHERE clause, and predicates are combined using the AND, OR, and NOT logical operators.

For example, the AdventureWorks sample database has a Product table that contains the Name and ProductID columns, a ProductInventory table that contains the Quantity, LocationID, and ProductID columns, and a Location table that contains the LocationID and Name columns. A query that returns the current product inventory per location, for the entire Touring line of products would look like the following code block:

SELECT P.Name AS ProductName, [PI].Quantity, L.Name AS LocationName
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS [PI] ON P.ProductID = [PI].ProductID
INNER JOIN Production.Location AS L ON [PI].LocationID = L.LocationID
WHERE P.Name LIKE 'Touring%';

The following screenshot shows that all the ProductName values in the result set begin with the word Touring: