(mysql.info) comparison-operators
Info Catalog
(mysql.info) type-conversion
(mysql.info) non-typed-operators
(mysql.info) logical-operators
12.1.3 Comparison Functions and Operators
-----------------------------------------
Comparison operations result in a value of `1' (`TRUE'), `0' (`FALSE'),
or `NULL'. These operations work for both numbers and strings. Strings
are automatically converted to numbers and numbers to strings as
necessary.
Some of the functions in this section (such as `LEAST()' and
`GREATEST()') return values other than `1' (`TRUE'), `0' (`FALSE'), or
`NULL'. However, the value they return is based on comparison operations
performed according to the rules described in type-conversion.
To convert a value to a specific type for comparison purposes, you can
use the `CAST()' function. String values can be converted to a
different character set using `CONVERT()'. See cast-functions.
By default, string comparisons are not case sensitive and use the
current character set. The default is `latin1' (cp1252 West European),
which also works well for English.
* `='
Equal:
mysql> SELECT 1 = 0;
-> 0
mysql> SELECT '0' = 0;
-> 1
mysql> SELECT '0.0' = 0;
-> 1
mysql> SELECT '0.01' = 0;
-> 0
mysql> SELECT '.01' = 0.01;
-> 1
* `<=>'
`NULL'-safe equal. This operator performs an equality comparison
like the `=' operator, but returns `1' rather than `NULL' if both
operands are `NULL', and `0' rather than `NULL' if one operand is
`NULL'.
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
* `<>', `!='
Not equal:
mysql> SELECT '.01' <> '0.01';
-> 1
mysql> SELECT .01 <> '0.01';
-> 0
mysql> SELECT 'zapp' <> 'zappp';
-> 1
* `<='
Less than or equal:
mysql> SELECT 0.1 <= 2;
-> 1
* `<'
Less than:
mysql> SELECT 2 < 2;
-> 0
* `>='
Greater than or equal:
mysql> SELECT 2 >= 2;
-> 1
* `>'
Greater than:
mysql> SELECT 2 > 2;
-> 0
* `IS BOOLEAN_VALUE', `IS NOT BOOLEAN_VALUE'
Tests a value against a boolean value, where BOOLEAN_VALUE can be
`TRUE', `FALSE', or `UNKNOWN'.
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
-> 1, 1, 1
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
-> 1, 1, 0
`IS [NOT] BOOLEAN_VALUE' syntax was added in MySQL 5.0.2.
* `IS NULL', `IS NOT NULL'
Tests whether a value is or is not `NULL'.
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0, 0, 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1, 1, 0
To work well with ODBC programs, MySQL supports the following
extra features when using `IS NULL':
* You can find the row that contains the most recent
`AUTO_INCREMENT' value by issuing a statement of the
following form immediately after generating the value:
SELECT * FROM TBL_NAME WHERE AUTO_COL IS NULL
This behavior can be disabled by setting
`SQL_AUTO_IS_NULL=0'. See set-option.
* For `DATE' and `DATETIME' columns that are declared as `NOT
NULL', you can find the special date `'0000-00-00'' by using a
statement like this:
SELECT * FROM TBL_NAME WHERE DATE_COLUMN IS NULL
This is needed to get some ODBC applications to work because
ODBC does not support a `'0000-00-00'' date value.
* `EXPR BETWEEN MIN AND MAX'
If EXPR is greater than or equal to MIN and EXPR is less than or
equal to MAX, `BETWEEN' returns `1', otherwise it returns `0'.
This is equivalent to the expression `(MIN <= EXPR AND EXPR <=
MAX)' if all the arguments are of the same type. Otherwise type
conversion takes place according to the rules described in
type-conversion, but applied to all the three arguments.
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
* `EXPR NOT BETWEEN MIN AND MAX'
This is the same as `NOT (EXPR BETWEEN MIN AND MAX)'.
* `COALESCE(VALUE,...)'
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,...)'
With two or more arguments, returns the largest (maximum-valued)
argument. The arguments are compared using the same rules as for
`LEAST()'.
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()' returns `NULL' only if all
arguments are `NULL'. As of 5.0.13, it returns `NULL' if any
argument is `NULL'.
* `EXPR IN (VALUE,...)'
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 type-conversion, but applied to all the
arguments.
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1
The number of values in the `IN' list is only limited by the
`max_allowed_packet' 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 any-in-some-subqueries.
* `EXPR NOT IN (VALUE,...)'
This is the same as `NOT (EXPR IN (VALUE,...))'.
* `ISNULL(EXPR)'
If EXPR is `NULL', `ISNULL()' returns `1', otherwise it returns
`0'.
mysql> SELECT ISNULL(1+1);
-> 0
mysql> SELECT ISNULL(1/0);
-> 1
`ISNULL()' can be used instead of `=' to test whether a value is
`NULL'. (Comparing a value to `NULL' using `=' always yields
false.)
The `ISNULL()' function shares some special behaviors with the `IS
NULL' comparison operator. See the description of `IS NULL'.
* `INTERVAL(N,N1,N2,N3,...)'
Returns `0' if N < N1, `1' if N < N2 and so on or `-1' if N is
`NULL'. All arguments are treated as integers. It is required that
N1 < N2 < N3 < `...' < NN for this function to work correctly.
This is because a binary search is used (very fast).
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0
* `LEAST(VALUE1,VALUE2,...)'
With two or more arguments, returns the smallest (minimum-valued)
argument. The arguments are compared using the following rules:
* If the return value is used in an `INTEGER' context or all
arguments are integer-valued, they are compared as integers.
* If the return value is used in a `REAL' context or all
arguments are real-valued, they are compared as reals.
* If any argument is a case-sensitive string, the arguments are
compared as case-sensitive strings.
* In all other cases, the arguments are compared as
case-insensitive strings.
Before MySQL 5.0.13, `LEAST()' returns `NULL' only if all
arguments are `NULL'. As of 5.0.13, it returns `NULL' if any
argument is `NULL'.
mysql> SELECT LEAST(2,0);
-> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> SELECT LEAST('B','A','C');
-> 'A'
Note that the preceding conversion rules can produce strange
results in some borderline cases:
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
-> -9223372036854775808
This happens because MySQL reads `9223372036854775808.0' in an
integer context. The integer representation is not good enough to
hold the value, so it wraps to a signed integer.
Info Catalog
(mysql.info) type-conversion
(mysql.info) non-typed-operators
(mysql.info) logical-operators
automatically generated byinfo2html