Oracle 5.0, Справочное руководство

Страница: 898 / 3232

Comparison Functions and Operators
878
For best results when using
BETWEEN
[877] with date or time values, use
CAST()
[948] to
explicitly convert the values to the desired data type. Examples: If you compare a
DATETIME
to two
DATE
values, convert the
DATE
values to
DATETIME
values. If you use a string constant such as
'2001-1-1'
in a comparison to a
DATE
, cast the string to a
DATE
.
•
expr NOT BETWEEN min AND max
[878]
This is the same as
NOT (expr BETWEEN min AND max)
.
•
COALESCE(value,...)
[878]
Returns the first non-
NULL
value in the list, or
NULL
if there are no non-
NULL
values.
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
•
GREATEST(value1,value2,...)
[878]
With two or more arguments, returns the largest (maximum-valued) argument. The arguments are
compared using the same rules as for
LEAST()
[879] .
mysql> SELECT GREATEST(2,0);
-> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
-> 767.0
mysql> SELECT GREATEST('B','A','C');
-> 'C'
Before MySQL 5.0.13,
GREATEST()
[878] returns
NULL
only if all arguments are
NULL
. As of
5.0.13, it returns
NULL
if any argument is
NULL
.
•
expr IN (value,...)
[878]
Returns
1
if
expr
is equal to any of the values in the
IN
list, else returns
0
. If all values are
constants, they are evaluated according to the type of
expr
and sorted. The search for the item then
is done using a binary search. This means
IN
is very quick if the
IN
value list consists entirely of
constants. Otherwise, type conversion takes place according to the rules described in Section 12.2,
“Type Conversion in Expression Evaluation” , but applied to all the arguments.
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
You should never mix quoted and unquoted values in an
IN
list because the comparison rules for
quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may
therefore lead to inconsistent results. For example, do not write an
IN
expression like this:
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
Instead, write it like this:
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
The number of values in the
IN
list is only limited by the
max_allowed_packet
[466] value.
To comply with the SQL standard,
IN
returns
NULL
not only if the expression on the left hand side is
NULL
, but also if no match is found in the list and one of the expressions in the list is
NULL
.
IN()
syntax can also be used to write certain types of subqueries. See Section 13.2.9.3,
“Subqueries with
ANY
,
IN
, or
SOME
” .