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 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
where column_name is [not] null
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” on page 337 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.
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 47 in Chapter 1, “System and User-Defined Datatypes” of Reference Manual: Building Blocks 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, 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%'
is a string of characters and wildcard characters enclosed in quotes. Table 1-36 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. See “Using the escape clause” on page 363 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.
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. 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. 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. 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. 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.
column_name allows text, unitext, or image datatypes.
is a comparison operator or one of the join operators =* or *=. 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')
Select data based on null values of text, unitext, and image columns:
create table temp1(c1 int, c2 text null, c3 unitext null, c4 image null)
insert into temp1 values(1, null, replicate("u",5), null)
insert into temp1 values(2, replicate("x",3), null, null)
go
select * from temp1 where c2 is null
go
c1 c2 c3 c4 ----- ---------- -------------------------- ------------ 1 NULL 0x75007500750075007500 NULL (1 row affected)
select * from temp1 where c2 is not null and c3 is null and c4 is null go
c1 c2 c3 c4 ----- ---------- ---------- ---------- 2 xxx NULL NULL
Update data based on non-null values of text column:
insert into temp1 values(3, replicate("y", 3), null, 0x858585847474)
insert into temp1 values(4, replicate("z",3),"aaa", 0x75)
go
update temp1 set c2 = "updated" where c2 is not null
select * from temp1 where c2 is not null
go
(3 rows affected) c1 c2 c3 c4 ----- ---------- ---------- ---------- 2 updated NULL NULL 3 updated NULL 0x858585847474 4 updated 0x610061006100 0x75
Select data into table temp2 based on null values of text column in temp1:
select c1, c2 into temp2 from temp1 where c2 is null select * from temp2 go
(1 row affected) c1 c2 ----- ---------- 1 NULL
Insert data into table temp2, selecting from temp1, based on non-null values of text column in temp1:
insert into temp2 select c1, c2 from temp1 where c2 is not null select * from temp2 go
(3 rows affected) c1 c2 ----- ---------- 1 NULL 2 updated 3 updated 4 updated (4 rows affected)
Select data in a sub-query based on null value of text column:
select count(*) from temp2 where c1 in (select c1 from temp1 where c2 is null and c3 is not null)
----------- 1 (1 row affected)
Delete data based on null value of unitext column:
delete from temp1 where c3 is null go
(2 rows affected)
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” in Reference Manual: Building Blocks 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.
You can use the keyword like to search a unitext column for a specific pattern. However, the like clause is not optimized when it is used with a unitext column. like pattern matching for unitext depends on the default Unicode sort order, which is also used for like pattern matching for unichar and univarchar datatypes.
The where clause accepts text and unitext LOB locators, but not image LOB locators, for the variables expression and match_string.
... where expression like ‘match_string’ ...
When match_string is a locator, Adaptive Server uses only up to 16KB of the corresponding LOB.
Specifying the null condition selects only those rows with a null value in the specified LOB column. The LOB value may be null either because it was explicitly assigned a null value, or because the LOB was not initialized.
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.
ANSI SQL – 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
