Search Conditions

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.

Syntax

{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

Boolean_expression

Is an expression that returns TRUE or FALSE. Boolean_expressions have the following format:

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

Boolean_function

Is a function that returns TRUE or FALSE.

expression

Is a column name, a constant, a function, a variable, a subquery, any combination of column names, constants, and functions connected by an operator(s) or a subquery. The expression may also contain the CASE expression. For details, see the CASE Expression, Operators, and the Subqueries topics.

column_name

Is the name of a column used in the comparison. If there is any ambiguity, qualify the column name with its table or view name.

match_string

Is a string of characters and wildcard characters enclosed in quotation marks. For details, see the Wildcard Characters topic.

comparison_operator

Is a symbol used to contrast two expressions. For details, see the Operators topics.

value_list

Is a list of values. Place quotation marks around char, varchar, and datetime values, and separate values with commas.

subquery

Is a restricted SELECT statement (the ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed). For more information, see the Subqueries topic.

cursor_name

Specifies the name of the cursor that points to the row where the statement should be applied. The WHERE CURRENT OF clause is available with the DELETE and UPDATE statements. For details, see the Cursors topic.

AND

Joins two conditions and returns results when both of the conditions are true. When more than one logical operator is used in a statement, AND operators are evaluated first. You can change the order of evaluation by using parentheses.

ALL

Is used with <, >, <>, >=, or <= and a subquery. It evaluates to TRUE or FALSE when all values retrieved in the subquery match the value in the WHERE or HAVING clause or when the subquery returns no rows of the outer statement. For details, see the Subqueries topic.

ANY

Is used with <, >, <>, >=, <=, or = and a subquery. It evaluates to TRUE when any value retrieved in the subquery satisfies the comparison predicate in the WHERE or HAVING clause of the outer statement. For details, see the Subqueries topic.

BETWEEN

Is the range-start keyword. Use AND for the range-end value. A range of expression BETWEEN x AND y, unlike a range of expression > x and expression < y, is inclusive.

EXISTS

Is used with a subquery to test for the existence of rows returned by the subquery. For details, see the Subqueries topic.

IN

Allows you to select values that match any one of a list of values. The expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a subquery. Enclose the list of values in parentheses.

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.

IS [NOT] NULL

Are keywords that search for null values (or all values except null values). An expression with a bitwise or arithmetic operator evaluates to NULL if any of the operands is NULL.

LIKE

Indicates that the following character string (enclosed by quotation marks) is to be used with pattern matching. You can use the LIKE keyword and wildcard characters with datetime data as well as with char and varchar. When you use LIKE with datetime values, SQL Server converts the dates to the standard datetime format and then to varchar. Since the standard storage format doesn't include seconds or milliseconds, you cannot search for seconds or milliseconds with LIKE and a pattern.

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.

NOT

Negates any Boolean expression (which can include keywords, such as LIKE, NULL, BETWEEN, IN, and EXISTS).

OR

Joins two conditions and evaluates to TRUE when either of the conditions is true. When more than one logical operator is used in a statement, OR operators are normally evaluated after AND operators. However, you can change the order of evaluation by using parentheses.

Examples

A.    Arithmetic Operators

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

B.    Wildcard with the NOT LIKE Condition

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%'

C.    Specific Character Wildcards with the LIKE Condition

This example finds the rows for authors named Carson, Carsen, Karsen, and Karson:

WHERE au_lname LIKE '[CK]ars[eo]n'

D.    Boolean (OR) and Comparison Operators (< and IS NULL)

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

E.    Grouping Boolean Expressions

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

F.    Between Condition

This example returns all titles with year-to-date sales between and including 4095 through 12000.

WHERE ytd_sales BETWEEN 4095 AND 12000

G.    IN Condition

This example finds the rows in which the state is California (CA), Indiana (IN), or Maryland (MD).

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

See Also