How to Filter data with predicates

The simplest SELECT statements with only SELECT and FROM clauses will evaluate every row in a table. By using a WHERE clause, you define conditions that determine which rows will be processed and potentially reduce result set.

The structure of the WHERE clause

The WHERE clause is made up of one or more search conditions, each of which must evaluate to TRUE, FALSE, or ‘unknown’ for each row of the table. Rows will only be returned when the WHERE clause evaluates as TRUE. The individual conditions act as filters on the data, and are referred to as ‘predicates’. Each predicate includes a condition that is being tested, usually using the basic comparison operators:

• = (equals)
• <> (not equals)
• > (greater than)
• >= (greater than or equal to)
• < (less than)
• <= (less than or equal to)

For example, the following query returns all products with a ProductCategoryID value of 2:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2;

Similarly, the following query returns all products with a ListPrice less than 10.00:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ListPrice < 10.00;

IS NULL / IS NOT NULL

You can also easily filter to allow or exclude the ‘unknown’ or NULL values using IS NULL or IS NOT NULL.

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductName IS NOT NULL;

Multiple conditions

Multiple predicates can be combined with the AND and OR operators and with parentheses. However SQL Server will only process two conditions at a time. All conditions must be TRUE when connecting multiple conditions with AND operator. When using OR operator to connect two conditions, one or both may be TRUE for the result set.

For example, the following query returns product in category 2 that cost less than 10.00:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
    AND ListPrice < 10.00;

AND operators are processed before OR operators, unless parentheses are used. For best practice, use parentheses when using more than two predicates. The following query returns products in category 2 OR 3 AND cost less than 10.00:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE (ProductCategoryID = 2 OR ProductCategoryID = 3)
    AND (ListPrice < 10.00);