Evaluates to TRUE if the operand
is equal to one of a list of expressions.

LIKE

Evaluates to TRUE if the
operand matches a pattern.

NOT

Reverses the value of any other
Boolean operator.

OR

Evaluates to TRUE if either
Boolean expression is TRUE.

SOME

Evaluates to TRUE if some of a
set of comparisons are TRUE.

ALL

Compares a
scalar value with a single-column set of values. It is used in conjunction with
comparison operators and is sometimes classified as a comparison operator. It
returns TRUE when specified condition is TRUE for all pairs; otherwise it
returns FALSE. The example of its usage is given in
Chapter
8, the section "Using Subqueries in a WHERE clause."

ANY | SOME

Compares a scalar value with a
single-column set of values. The keywords ANY and SOME are completely
interchangeable. The operator returns TRUE if specified condition is valid for
any pair; otherwise it returns FALSE. The example of its usage is given in
Chapter
8, the section "Using Subqueries in a WHERE clause."

Note

For Microsoft SQL Server and IBM DB2 UDB, operators ANY | SOME
could only be used with a subquery; only Oracle allows for the list of scalar
values to be used with it.

BETWEEN <expression> AND
<expression>

The BETWEEN operator allows for
"approximate" matching of the selection criteria. It returns TRUE if the
expression evaluates to be greater or equal to the value of the start
expression, and is less or equal to the value of the end expression. Used with
negation operator NOT, the expression evaluates to TRUE only when its value is
less than that of the start expression, or
greater than the value of the end expression.

Note

AND keyword used in conjunction with BETWEEN operator is not the
same as the AND operator explained later in the chapter

The following query retrieves data about
product ID, product description and product price from the PRODUCT table, where
product price is in the range between 15 and 25 dollars.

Note that the border values are included
into the final result set. The operator works identically across all three
databases and could be used with a number of different data types: dates,
numbers, and strings.

Though rules for evaluation strings are
the same, the produced results are not that straightforward as those with the
numbers. The string are evaluated according to the characters in the value, and
unless full string is specified, the border limit values are not included. For
example, if one wants to get the product's information for a range of
descriptions starting with "C" and "S," the following query could be
used:

Note that the product with description
'SPRUCE LUMBER 30X40X50' was not included in spite that it does starts with
'S'; the cut-off criterion 'S' implies that only description consisting of a
single 'S' answers the condition; using wildcard characters (see later in the
chapter) does not help in this case. To include the product for the above
description, the full value must be used, or the first TWO characters of the
value next in line (see the query above), and so on

Since we do not have products priced
exactly at 10 or 15 dollars, only two matching records were returned.

Note

The data type of the expression evaluated against the list must
be correspond to the data type of the list values. Some RDBMS would implicitly
convert between compatible data types (e.g. MS SQL Server 2000 and Oracle 9i
both would accept the list like follows (10,15,'18.24', 16.03) — mixing numbers
with strings, while IBM DB2 UDB would generate an error SQL0415N, SQLSTATE
42825).

The operator IN behavior could be
emulated (to a certain extent) by using OR operator. The following query would
bring the result set identical to that returned by the query using a list of
literals

Using NOT operator in conjunction with
IN would return all records that are not within the specified list of values —
either predefined or generated from a subquery.

EXISTS

The EXISTS operator checks for the
existence of any rows with matched values in the subquery. The subquery could
query the same table, or different table(s), or a combination of both (see
Chapter
8 for information on correlated query). The operator acts identically
in all three RDBMS implementations

EXISTS usage resembles that of IN
operator (normally used with correlated query, discussed in
Chapter
8). The following SQL query produces results identical to those
produced by the queries in the above examples:

The use of an additional condition
(ordline_prodid_fn = product.prod_id_n) is necessary to
limit output to those records from PRODUCT to only those that have
corresponding records in ORDER_LINE table – i.e., only those products for which
there are orders. Without this condition, the query would bring all products
from the PRODUCTS table.

Note

While it is possible to specify a nonempty list of values with
the EXIST operator, it would always evaluate to TRUE. For example, the
following query would return all records from the table PRODUCT, because the
subquery always would evaluate to TRUE:

SELECT prod_description_s
FROM product WHERE EXISTS (SELECT * FROM DUAL)

The EXISTS operator produces results
identical to '= ANY' from the examples in the respective sections. Using
operator NOT in conjunction with EXISTS results in records when no rows are
returned by a subquery.

LIKE

Operator LIKE belongs to the "fuzzy
logic" domain. It is used any time when criteria in the WHERE clause of the
SELECT query are only partially known. It utilizes a variety of wildcard
characters to specify the missing parts of the value (Table 11-3). The
pattern must follow the LIKE keyword.

Table 11-3: Wildcard Characters for use with Operator
LIKE

Character

Description

Implementation

%

Matches any string of zero or
more characters

Oracle, IBM DB2 UDB, MS SQL
Server 2000

_
(underscore)

Matches any single character
within a string

Oracle, IBM DB2 UDB, MS SQL
Server 2000

[ ]

Matches any single character
within the specified range or set of characters

Microsoft SQL 2000
only

[ ^ ]

Matches any single character
NOT within specified range or set of characters

Microsoft SQL 2000
only

The following query requests information
from the table CUSTOMER of the ACME database, where customer name (field
CUST_NAME_S) starts with 'WILE'

In plane English, this query translates
as "all records from the table CUSTOMER where field CUST_NAME_S contains the
following sequence of characters: the value starts with WILE followed by
unspecified number of characters, then blank space, and the second part of the
value starts with some letter or number followed by combination EAL; the rest
of the characters is unspecified".

In Microsoft SQL Server (and Sybase, as
well), you also may use matching pattern that specifies range of characters.
The following query retrieves records for the customer whose second part of the
name starts with either 'S' or 'B':

ESCAPE clause in conjunction with the
LIKE operator allows for inclusion wildcard characters themselves to be
included in the search string. It allows you to specify an escape character to
be used to identify special characters within the search string that should be
treated as "regular" ones. Virtually any character could be designated as an
escape character in a query, though caution must be exercised in order not to
use characters that might be encountered in the values themselves (e.g., use of
the '%' or 'L' as an escape character would result in erroneous results to be
returned). The clause is supported by all three major databases and is part of
SQL standard.

The following example uses an underscore
sign (_) as one of the search characters; it queries INFORMATION_SCHEMA view
(Microsoft SQL Server 2000 specific view for accessing information about
objects present in the current database; covered in detail in
Chapter
13):

The query requests records from the view
where table name starts with 'ORD' followed by unspecified number of
characters, has an underscore "_" as part of its name, followed by 'L' and,
again, ending with an unspecified number of characters. Since the underscore
character has a special meaning as a wildcard character it has to be preceded
by an escape character '/'. As you can see, the table name ORDER_LINE uniquely
fits these requirements.

Note

Oracle 9i specifies four types of LIKE
operator: LIKE, LIKEC, LIKE2, and LIKE4. The first evaluates a string as
defined by the input value character set, the second (LIKEC) assumes UNICODE
complete set, while LIKE2 and LIKE4 subsets use USC2 (fixed-width, 2
bytes/16-bit encoding of the UNICODE characters) and USC4 (4 bytes/32-bit
encoding) codepoints, respectively. The term USC refers to Universal
Multiple-Octet Coded Character Set.

With a bit of practice one could
construct quite sophisticated pattern matching queries. Here is an example: the
query that specifies exactly three characters preceding 'E' in the first part
of the name, followed by unspecified number of characters, exactly one
character preceding letters 'ES' in the second part, followed by unspecified
number of characters

The results might be surprising at first
glance: why does the resultset include 'INTEGRATED POWER DESIGNS'? To
understand the results one should recall that '%' stands for any character,
blank spaces included; therefore 'GRATED POWER' string fits the criteria; it is
followed by a blank space and a word that includes 'ES' as the second and first
characters.

Note

In a search for similarly sounding names use SOUNDEX function,
described in
Chapter
10.

AND

AND
combines two Boolean expressions and returns TRUE when both expressions are
TRUE. The following query returns records for the product with a unit price
over $20 and whose description starts with 'S':

The query returned information for the
products whose price does not match any on the supplied list, i.e., where
operator IN returns TRUE (match) it becomes FALSE, while FALSE (no match)
translates into TRUE.

OR

Combines two conditions according to the
rules of Boolean logic (see
Appendix
L for more information on Boolean logic). When more than one logical
operator is used in a statement, OR operators are evaluated after AND
operators. However, you can change the order of evaluation by using
parentheses. The example of the usage of the OR operator is given earlier in
the chapter, in a paragraph discussing operator IN.