Sets the search conditions in a select, insert, update, or delete statement.
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 [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
negates any logical expression or keywords such as like, null, between, in, and exists.
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”.
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” for more information about data entry rules.
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, and datetime columns, but not to search for seconds or milliseconds.
You can use the keyword like and wildcard characters with datetime data as well as with char and varchar. When you use like with datetime 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 datetime values, since datetime 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%'
is a string of characters and wildcard characters enclosed in quotes. Table 7-38 lists the 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]) |
specifies an escape character with which you can search for literal occurrences of wildcard characters.
is any single character. For more information, see “Using the escape clause”.
searches for null values.
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.
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.
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.
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'"
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.
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.
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.
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.
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.
is a comparison operator or one of the join operators =* or *=. For more information, see the Transact-SQL User’s Guide.
is an expression that returns TRUE or FALSE.
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.
where advance * $2 > total_sales * price
Finds all the rows in which the phone number does not begin with 415:
where phone not like '415%'
Finds the rows for authors named Carson, Carsen, Karsen, and Karson:
where au_lname like "[CK]ars[eo]n"
Finds the row of the sales_east table in which the IDENTITY column has a value of 4:
where sales_east.syb_identity = 4
where advance < $5000 or advance is null
where (type = "business" or type = "psychology") and advance > $5500
where total_sales between 4095 and 12000
Finds the rows in which the state is one of the three in the list:
where state in ('CA', 'IN', 'MD')
where and having search conditions are identical, except that aggregate functions are not permitted in where clauses. For example, this clause is legal:
having avg(price) > 20
This clause is not legal:
where avg(price) > 20
For examples, see Chapter 2, “Transact-SQL Functions” for information on the use of aggregate functions, and group by and having clauses.
Joins and subqueries are specified in the search conditions: see the Transact-SQL User’s Guide for full details.
The number of and and or conditions in a where clause is limited only by the amount of memory available to run the query.
The pattern string included in the like predicate is limited only by the size of string that can be placed in a varchar.
There are two ways to specify literal quotes within a char or varchar entry. The first method is to use two quotes. For example, if you began a character entry with a single quote, and you want to include a single quote as part of the entry, use two single quotes:
'I don''t understand.'
Or use double quotes:
"He said, ""It's not really confusing."""
The second method is to enclose a quote in the opposite kind of quotation mark. In other words, surround an entry containing double quotes with single quotes (or vice versa). Here are some examples:
'George said, "There must be a better way."' "Isn't there a better way?" 'George asked, "Isn"t there a better way?"'
To enter a character string that is longer than the width of your screen, enter a backslash (\) before going to the next line.
If a column is compared to a constant or variable in a where clause, Adaptive Server converts the constant or variable into the datatype of the column so that the optimizer can use the index for data retrieval. For example, float expressions are converted to int when compared to an int column. For example:
where int_column = 2
selects rows where int_column = 2.
When Adaptive Server optimizes queries, it evaluates the search conditions in where and having clauses, and determines which conditions are search arguments (SARGs) that can be used to choose the best indexes and query plan. All of the search conditions are used to qualify the rows. For more information on search arguments, see the Performance and Tuning Guide.
SQL92 – Compliance level: Entry-level compliant.
Commands – delete, execute, group by and having clauses, insert, select, update
Datatypes – Date and time datatypes
System procedures – sp_helpjoins
