where clause

Description

Sets the search conditions in a select, insert, update, or delete statement.

Syntax

Search conditions immediately follow the keyword where in a select, insert, update, or delete statement. If you use more than one search condition in a single statement, connect the conditions with and or or.

where [not] expression comparison_operator expression
where {[not] expression comparison_operator expression} | {...}
where [not] expression [not] like "match_string"
	[escape "escape_character "]
where [not] expression is [not] null
where [not] expression [not] between expression and expression
where [not] expression [not] in ({value_list | subquery})
where [not] exists (subquery)
where [not] expression comparison_operator {any | all} (subquery)
where [not] column_name join_operator column_name
where [not] logical_expression
where [not] expression {and | or} [not] expression

Parameters

not

negates any logical expression or keywords such as like, null, between, in, and exists.

expression

is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions” on page 359 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.

comparison_operator

is one of the following:

Operator

Meaning

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

!=

Not equal to

<>

Not equal to

!>

Not greater than

!<

Not less than

In comparing char, nchar, unichar, varchar, univarchar, and nvarchar data, < means closer to the beginning of the alphabet and > means closer to the end of the alphabet.

Case and special character evaluations depend on the collating sequence of the operating system on the machine on which Adaptive Server is located. For example, lowercase letters may be greater than uppercase letters, and uppercase letters may be greater than numbers.

Trailing blanks are ignored for the purposes of comparison. For example, “Dirk” is the same as “Dirk  ”.

In comparing dates, < means earlier and > means later. Put quotes around all character and date data used with a comparison operator. For example:

 = "Bennet"
 > "94609"

See “User-defined datatypes” on page 44 in Chapter 1, “System and User-Defined Datatypes” of Reference Manual: Building Blocks for more information about data entry rules.

like

is a keyword indicating that the following character string (enclosed by single or double quotes) is a matching pattern. like is available for char, varchar, unichar, univarchar, nchar, nvarchar, datetime, date and time, text, and unitext columns, but not to search for seconds or milliseconds.

You can use the keyword like and wildcard characters with datetime and date data as well as with char and varchar. When you use like with datetime or date and time values, Adaptive Server converts the dates to standard datetime format, then to varchar. Since the standard storage format does not 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 date/ time values, since date/time entries may contain a variety of date parts. For example, if you insert the value “9:20” into a column named arrival_time, the following clause would not find it because Adaptive Server converts the entry into “Jan 1, 1900 9:20AM.”:

where arrival_time = '9:20'

However, the following clause would find it:

where arrival_time like '%9:20%'
match_string

is a string of characters and wildcard characters enclosed in quotes. Table 1-44 lists the wildcard characters.

Table 1-44: Wildcard characters

Wildcard character

Meaning

%

Any string of 0 or more characters

_

Any single character

[ ]

Any single character within the specified range ([a-f]) or set ([abcdef])

[^]

Any single character that is not within the specified range ([^a-f]) or set ([^abcdef])

escape

specifies an escape character with which you can search for literal occurrences of wildcard characters.

escape_character

is any single character. For more information, see “Using the escape clause” on page 382 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.

is null

searches for null values.

between

is the range-start keyword. Use and for the range-end value. The following range is inclusive:

where @val between x and y

The following range is not:

x and @val < y

Queries using between return no rows if the first value specified is greater than the second value.

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 usually evaluated first. However, you can change the order of execution with parentheses.

in

allows you to select values that match any one of a list of values. The comparator can be a constant or a column name, and the list can be a set of constants or, more commonly, a subquery. For information on using in with a subquery, see the Transact-SQL User’s Guide. Enclose the list of values in parentheses.

value_list

is a list of values. Put single or double quotes around character values, and separate each value from the following one with a comma (see example 7). The list can be a list of variables, for example:

in (@a, @b, @c)

However, you cannot use a variable containing a list, such as the following, for a values list:

@a = "'1', '2', '3'"
exists

is used with a subquery to test for the existence of some result from the subquery. For more information, see the Transact-SQL User’s Guide.

subquery

is a restricted select statement (order by and compute clauses and the keyword into are not allowed) inside the where or having clause of a select, insert, delete, or update statement, or a subquery. For more information, see the Transact-SQL User’s Guide.

any

is used with >, <, or = and a subquery. It returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement. For more information, see the Transact-SQL User’s Guide.

all

is used with > or < and a subquery. It returns results when all values retrieved in the subquery match the value in the where or having clause of the outer statement. For more information, see the Transact-SQL User’s Guide.

column_name

is the name of the column used in the comparison. Qualify the column name with its table or view name if there is any ambiguity. For columns with the IDENTITY property, you can specify the syb_identity keyword, qualified by a table name where necessary, rather than the actual column name.

join_operator

is a comparison operator or one of the join operators =* or *=. For more information, see the Transact-SQL User’s Guide.

logical_expression

is an expression that returns TRUE or FALSE.

or

joins two conditions and returns results 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 execution with parentheses.

Examples

Example 1

where advance * $2 > total_sales * price

Example 2

Finds all the rows in which the phone number does not begin with 415:

where phone not like '415%'

Example 3

Finds the rows for authors named Carson, Carsen, Karsen, and Karson:

where au_lname like "[CK]ars[eo]n"

Example 4

Finds the row of the sales_east table in which the IDENTITY column has a value of 4:

where sales_east.syb_identity = 4

Example 5

where advance < $5000 or advance is null

Example 6

where (type = "business" or type = "psychology") and advance > $5500

Example 7

where total_sales between 4095 and 12000

Example 8

Finds the rows in which the state is one of the three in the list:

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

Usage

Standards

ANSI SQL – Compliance level: Entry-level compliant.

See also

Commands delete, execute, group by and having clauses, insert, select, update

Datatypes Date and time datatypes

System procedures sp_helpjoins