Database Reference
In-Depth Information
Note:
Nested tables are covered in Chapter 16.
7.2.7
User-Defined Operators
User-defined operators can be created using the DDL CREATE OPERA-
TOR command.
The next thing to look at is conditions.
7.3
Conditions
A
is a condition or state of the result of an expression. Because a
state is implied, a condition will return a Boolean result of TRUE or
FALSE, indicating something being on or off. Conditions can be divided
into the following listed groups:
condition
Comparison
compares expressions as shown (see Chapter 5):
<expression> condition <expression>
Set membership using IN and EXISTS is a type of comparison in
that it verifies membership of an expression in a set of values. Once
again, examples are in Chapter 5.
<expression> member (<expression>, …, <expression>)
The
floating-point condition
allows checking for a number as
being defined or undefined. The syntax is as follows such that NAN
represents Not A Number and INFINITE is undefined.
<expression> IS [ NOT ] { INFINITE | NAN }
A NULL can be tested for using the NULL conditional comparison.
<expression> IS [ NOT ] NULL
In the example shown following, three different counts are made
counting songs with playing times not yet entered into the database
and not entered as zero or a space character. The sum of the row
counts returned by the second and third queries is identical to the
first query's row count. The result is shown in Figure 7.6.
SELECT COUNT(*) FROM SONG;
SELECT COUNT(*) FROM SONG WHERE PLAYING_TIME IS NULL;
