Boolean expressions are allowed in WHERE clauses and in check constraints. Boolean expressions in check constraints have limitations not noted here; see CONSTRAINT clause for more information. Boolean expressions in a WHERE clause have a highly liberal syntax; see WHERE clause, for example.
A boolean expression can include a boolean operator or operators. These operators are listed in the following table.
| Operator | Explanation and Example | Syntax |
|---|---|---|
| AND, OR, NOT | Evaluate any operand(s) that are boolean expressions (orig_airport = 'SFO') OR
(dest_airport = 'GRU')
-- returns true
|
{
Expression AND
Expression |
Expression OR
Expression |
NOT Expression
}
|
| Comparisons | <, =, >, <=, >=, <> are applicable to
all of the built-in types. DATE('1998-02-26') <
DATE('1998-03-01')
-- returns true
Note: Derby also accepts
the != operator, which is not included in the SQL standard.
|
Expression
{
< |
= |
> |
<= |
>= |
<>
}
Expression
|
| IS NULL, IS NOT NULL | Test whether the result of an expression is null or not.
WHERE MiddleName IS NULL |
Expression IS [ NOT ] NULL |
| LIKE | Attempts to match a character expression to a character
pattern, which is a character string that includes one or more wildcards.
% matches any number (zero or more) of characters in the corresponding position in first character expression. _ matches one character in the corresponding position in the character expression. Any other
character matches only that character in the corresponding position in the
character expression.
city LIKE 'Sant_' To
treat % or _ as constant characters, escape the character with an optional
escape character, which you specify with the ESCAPE clause.
SELECT a FROM tabA WHERE a LIKE '%=_' ESCAPE '=' Note: When LIKE comparisons are used, Derby compares one character
at a time for non-metacharacters. This is different than the way Derby processes
= comparisons. The comparisons with the = operator compare the entire character
string on left side of the = operator with the entire character string on
the right side of the = operator. For more information, see Character-based
collation in Derby in
the Derby Developer's Guide.
|
CharacterExpression [ NOT ] LIKE CharacterExpression WithWildCard [ ESCAPE 'escapeCharacter'] |
| BETWEEN | Tests whether the first operand is between the second and
third operands. The second operand must be less than the third operand. Applicable
only to types to which <= and >= can be applied. WHERE booking_date BETWEEN
DATE('1998-02-26') AND
DATE('1998-03-01')
|
Expression [ NOT ] BETWEEN Expression AND Expression |
| IN | Operates on table subquery or list of values. Returns TRUE
if the left expression's value is in the result of the table subquery or in
the list of values. Table subquery can return multiple rows but must return
a single column. WHERE booking_date NOT IN
(SELECT booking_date FROM
HotelBookings WHERE rooms_available = 0)
|
{
Expression [ NOT ]
IN
TableSubquery |
Expression [ NOT ]
IN ( Expression
[, Expression ]* )
}
|
| EXISTS | Operates on a table subquery. Returns TRUE if the table
subquery returns any rows, and FALSE if it returns no rows. A table subquery
can return multiple columns and rows. WHERE EXISTS (SELECT * FROM Flights WHERE dest_airport = 'SFO' AND orig_airport = 'GRU') |
[NOT] EXISTS TableSubquery |
| Quantified comparison | A quantified comparison is a comparison operator (<,
=, >, <=, >=, <>) with ALL or ANY or SOME applied. Operates on table subqueries, which can return multiple rows but must return a single column. If ALL is used, the comparison must be true for all values
returned by the table subquery. If ANY or SOME is used, the comparison must
be true for at least one value of the table subquery. ANY and SOME are equivalent.
WHERE normal_rate < ALL (SELECT budget/550 FROM Groups) |
Expression
ComparisonOperator
{
ALL |
ANY |
SOME
}
TableSubquery
|