Set the conditions for data retrieval or modification. Search conditions immediately follow the keyword WHERE in a SELECT, INSERT, UPDATE, or DELETE statement or follow the keyword HAVING in a SELECT statement. If you use more than one of the search conditions in a single statement, connect the conditions with AND or OR. Joins and subqueries can be specified in the search conditions.
{WHERE | HAVING} [NOT] Boolean_expression
{WHERE | HAVING} [NOT] Boolean_expression {AND | OR} Boolean_expression
{WHERE | HAVING} [NOT] column_name IS [NOT] NULL
{WHERE | HAVING} [NOT] column_name join_operator column_name
{WHERE | HAVING} [NOT] column_name [NOT] LIKE 'match_string'
{WHERE | HAVING} [NOT] EXISTS (subquery)
{WHERE | HAVING} [NOT] expression comparison_operator expression
{WHERE | HAVING} [NOT] expression [NOT] BETWEEN expression AND expression
{WHERE | HAVING} [NOT] expression [NOT] IN (value_list | subquery)
{WHERE | HAVING} [NOT] expression comparison_operator {ANY | ALL} (subquery)
{WHERE CURRENT OF cursor_name}
where
expression comparison_operator [ANY | ALL] (subquery)
expression [NOT] IN expression
[NOT] EXISTS expression
expression [NOT] BETWEEN expression AND expression
expression [NOT] LIKE expression
NOT expression LIKE expression
expression IS [NOT] NULL
Boolean_expression {AND | OR} Boolean_expression
[NOT] Boolean_function
where
If there are 16 or more values in an IN predicate, the values are sorted and binary search is used to evaluate the predicate. This can result in limiting the overall number of comparisons performed. For information about using IN with a subquery, see the Subqueries topic.
It is a good idea to use LIKE when you search for datetime values, since datetime entries can contain a variety of date parts. For example, if you insert the value 9:20 into a column named arrival_time, the clause WHERE arrival_time = '9:20' can't find it because SQL Server converts the entry into 'Jan 1, 1900 9:20AM'. However, the clause WHERE arrival_time LIKE '%9:20%' does find it.
Note When performing string comparisons with LIKE, all characters in the pattern string are significant, including every leading and/or trailing blank (space). If a comparison to return all rows with a string LIKE 'abc ' (abc followed by a single space) is requested, a row where the value of that column is 'abc' (abc without a space) will not be returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE 'abc' (abc without a space) is requested, all rows that start with 'abc' and have zero or more trailing blanks would be returned.
In this example, the WHERE clause affects only those titles that have year-to-date sales more than twice the advance given:
WHERE advance * 2 > ytd_sales
In this example, only phone numbers that do not begin with the 415 prefix are affected in the results set:
WHERE phone NOT LIKE '415%'
This example finds the rows for authors named Carson, Carsen, Karsen, and Karson:
WHERE au_lname LIKE '[CK]ars[eo]n'
This example finds titles that have an advance less than $5000 and includes those titles that have no specific advance entered (NULL values):
WHERE advance < $5000 OR advance IS NULL
Using parentheses always takes precedence over other operators. Without the parentheses, the following WHERE clause would have totally different meaning because AND takes a higher precedence than OR.
This example affects only books with an advance greater than $5500 and are either business or psychology books. If the parentheses were not included, the WHERE clause would affect all business books OR psychology books that have an advance greater than $5500.
WHERE (type = 'business' OR type = 'psychology') AND advance > $5500
This example returns all titles with year-to-date sales between and including 4095 through 12000.
WHERE ytd_sales BETWEEN 4095 AND 12000
This example finds the rows in which the state is California (CA), Indiana (IN), or Maryland (MD).
WHERE state IN ('CA', 'IN', 'MD')
DELETE | Operators |
EXECUTE | SELECT |
Expressions | UPDATE |
Functions | Wildcard Characters |
INSERT |