Selecting Rows: The WHERE Clause

The WHERE clause in a SELECT statement specifies the criteria for which rows to retrieve. The WHERE clause has the following syntax:

SELECT select_list
FROM {table_name | viewname list} [(optimizer_hints)]
WHERE search_conditions

Search conditions, or qualifications, in the WHERE clause include:

• Comparison operators (such as =, < >, <, and >)

  WHERE advance * 2 > ytd_sales * price

• Ranges (BETWEEN and NOT BETWEEN)

  WHERE ytd_sales between 4095 and 12000

• Lists (IN, NOT IN)

  WHERE state in ('CA', 'IN', 'MD')

• Pattern matches (LIKE and NOT LIKE)

  WHERE phone not like '415%'

  Note The only WHERE conditions that you can use on text columns are LIKE and NOT LIKE or with certain string functions such as patindex().

• Unknown values (IS NULL and IS NOT NULL)

  where advance is null

• Combinations of these conditions (AND, OR)

  where advance < $5000 or (ytd_sales > 2000 and ytd_sales < 2500)

In addition, WHERE can introduce join conditions and subqueries. For more information, see Using Joins to Create Multitable Queries and Using Subqueries.

For a complete list of search conditions, see Search Conditions in the Microsoft SQL Server Transact-SQL Reference.