(mysql.info.gz) Comparison Operators
Info Catalog
(mysql.info.gz) Parentheses
(mysql.info.gz) Non-typed Operators
(mysql.info.gz) 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 as described by the following rules.
MySQL compares values using the following rules:
* If one or both arguments are `NULL', the result of the comparison
is `NULL', except for the `NULL'-safe `<=>' equality comparison
operator.
* If both arguments in a comparison operation are strings, they are
compared as strings.
* If both arguments are integers, they are compared as integers.
* Hexadecimal values are treated as binary strings if not compared
to a number.
* If one of the arguments is a `TIMESTAMP' or `DATETIME' column and
the other argument is a constant, the constant is converted to a
timestamp before the comparison is performed. This is done to be
more ODBC-friendly. Note that this is not done for arguments in
`IN()'! To be safe, always use complete datetime/date/time
strings when doing comparisons.
* In all other cases, the arguments are compared as floating-point
(real) numbers.
By default, string comparisons are not case sensitive and use the
current character set (ISO-8859-1 Latin1 by default, which also works
excellently for English).
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()'. Cast Functions.
The following examples illustrate conversion of strings to numbers for
comparison operations:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
Note that when you are comparing a string column with a number, MySQL
can't use an index on the column to quickly look up the value. If
STR_COL is an indexed string column, the index cannot be used when
performing the lookup in the following statement:
SELECT * FROM TBL_NAME WHERE STR_COL=1;
The reason for this is that there are many different strings that may
convert to the value `1': `'1'', `' 1'', `'1a'', ...
`='
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
`<=>' was added in MySQL 3.23.0.
`<>'
`!='
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 whether 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 be able 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'.
`SET OPTION' 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 doesn't 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 at the
beginning of this section, but applied to all the three arguments.
* Before MySQL 4.0.5, arguments were converted to the type
of EXPR instead.
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.
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
`COALESCE()' was added in MySQL 3.23.3.
`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 3.22.5, you can use `MAX()' instead of `GREATEST()'.
`EXPR IN (VALUE,...)'
Returns `1' if EXPR is 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. If EXPR is
a case-sensitive string expression, the string comparison is
performed in case-sensitive fashion.
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, from MySQL 4.1 on `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'.
From MySQL 4.1 on, `IN()' syntax also is used to write certain
types of subqueries. `ANY IN SOME' subqueries 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
Note that a comparison of `NULL' values using `=' will always be
false!
`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 other cases, the arguments are compared as
case-insensitive strings.
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'
Before MySQL 3.22.5, you can use `MIN()' instead of `LEAST()'.
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.gz) Parentheses
(mysql.info.gz) Non-typed Operators
(mysql.info.gz) Logical Operators
automatically generated byinfo2html