The where clause in a select statement specifies the search conditions that determine which rows are retrieved. The general format is:
select select_list from table_list where search_conditions
Search conditions, or qualifications, in the where clause include:
Comparison operators (=, <, >, and so on)
where advance * 2 > total_sales * price
Ranges (between and not between)
where total_sales between 4095 and 12000
Lists (in, not in)
where state in ("CA", "IN", "MD")
Character matches (like and not like)
where phone not like "415%"
Unknown values (is null and is not null)
where advance is null
Combinations of search conditions (and, or)
where advance < 5000 or total_sales between 2000 and 2500
The where keyword can also introduce:
Join conditions (see Chapter 4, “Joins: Retrieving Data from Several Tables”)
Subqueries (see Chapter 5, “Subqueries: Using Queries Within Other Queries”)
The
only where condition that you can use on text columns
is like (or not like).
Adaptive Server does not necessarily evaluate and execute predicates in left-to-right order. Instead, Adaptive Server can evaluate and execute predicates in any order. For example, for this query:
where x != 0 and y = 10 or z = 100
Adaptive Server may not evaluate and execute x
!= 0 first.
For more information on search conditions, see the Reference Manual: Commands.
