Table 11.3. Comparison Operators
Name | Description |
---|---|
BETWEEN ... AND ... |
Check whether a value is within a range of values |
COALESCE() |
Return the first non-NULL argument |
<=> |
NULL-safe equal to operator |
= |
Equal operator |
>= |
Greater than or equal operator |
> |
Greater than operator |
GREATEST() |
Return the largest argument |
IN() |
Check whether a value is within a set of values |
INTERVAL() |
Return the index of the argument that is less than the first argument |
IS NOT NULL |
NOT NULL value test |
IS NOT |
Test a value against a boolean |
IS NULL |
NULL value test |
IS |
Test a value against a boolean |
ISNULL() |
Test whether the argument is NULL |
LEAST() |
Return the smallest argument |
<= |
Less than or equal operator |
< |
Less than operator |
LIKE |
Simple pattern matching |
NOT BETWEEN ... AND ... |
Check whether a value is not within a range of values |
!= , <> |
Not equal operator |
NOT IN() |
Check whether a value is not within a set of values |
NOT LIKE |
Negation of simple pattern matching |
STRCMP() |
Compare two strings |
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.
The following relational comparison operators can be used to compare not only scalar operands, but row operands:
= > < >= <= <> !=
For examples of row comparisons, see Section 12.2.9.5, “Row Subqueries”.
Some of the functions in this section return values other than
1
(TRUE
),
0
(FALSE
), or
NULL
. For example,
LEAST()
and
GREATEST()
. However, the value
they return is based on comparison operations performed
according to the rules described in
Section 11.2.2, “Type Conversion in Expression Evaluation”.
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
Section 11.9, “Cast Functions and Operators”.
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
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
Tests a value against a boolean value, where
boolean_value
can be
TRUE
, FALSE
, or
UNKNOWN
.
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
-> 1, 1, 0
Tests whether a value is NULL
.
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0, 0, 1
To work well with ODBC programs, MySQL supports the
following extra features when using IS
NULL
:
If sql_auto_is_null
variable is set to 1 (the default), then after a
statement that successfully inserts an automatically
generated AUTO_INCREMENT
value, you
can find that value by issuing a statement of the
following form:
SELECT * FROMtbl_name
WHEREauto_col
IS NULL
If the statement returns a row, the value returned is
the same as if you invoked the
LAST_INSERT_ID()
function. For details, including the return value after
a multiple-row insert, see
Section 11.11.3, “Information Functions”. If no
AUTO_INCREMENT
value was successfully
inserted, the SELECT
statement returns no row.
The behavior of retrieving an
AUTO_INCREMENT
value by using an
IS NULL
comparison can be
disabled by setting
sql_auto_is_null = 0
.
See Section 5.1.5, “Session System Variables”.
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 * FROMtbl_name
WHEREdate_column
IS NULL
This is needed to get some ODBC applications to work
because ODBC does not support a
'0000-00-00'
date value.
See
Section 20.1.7.1.1, “Obtaining Auto-Increment Values”,
and the description for the
FLAG_AUTO_IS_NULL
option at
Section 20.1.4.2, “Connector/ODBC Connection Parameters”.
Tests whether a value is not NULL
.
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1, 1, 0
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
(
if all the
arguments are of the same type. Otherwise type conversion
takes place according to the rules described in
Section 11.2.2, “Type Conversion in Expression Evaluation”, but applied to all the
three arguments.
min
<=
expr
AND
expr
<=
max
)
mysql>SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
-> 1, 0 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
For best results when using
BETWEEN
with date or time
values, you should use CAST()
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
.
This is the same as NOT
(
.
expr
BETWEEN
min
AND
max
)
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
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'
GREATEST()
returns
NULL
if any argument is
NULL
.
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 11.2.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
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 12.2.9.3, “Subqueries with ANY
, IN
, and
SOME
”.
This is the same as NOT
(
.
expr
IN
(value
,...))
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
.
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
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.
LEAST()
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.
User Comments
ASP users: if you're getting empty recordset
returned when using COALESCE, add "OPTION=16384"
to your connectionstring, or check "Change Bigint
to Int" in the DSN manager!
If you are looking for something like:
SELECT id,name,perm_list FROM users WHERE 'write'
IN perm_list
where 'perm_list' contains a comma separated list
of privileges, you would try to use:
SELECT id,name FROM users WHERE FIND_IN_SET
('write',perm_list)>0;
The IN operator also works with tuples, at least in version 4.1:
1 row in set (0.15 sec)mysql> select (3,4) in ((2,3),(3,4));
mysql> select (3,5) in ((2,3),(3,4));
1 row in set (0.00 sec)
... WHERE t1.mydate IN t2.datelist
but suddenly it stopped working, so i use the STRING only function...
... WHERE FIND_IN_DATE(t1.mydate,t2.datelist)
and it works again.
Hope this helps - Jon
If you want to do a case sensitive string comparision (for ex. username/password) then simply add BINARY to your statement.
SELECT * FROM sometable WHERE BINARY somecolumn='somestring';
For more information see Section 12.8, “Cast Functions and Operators” as mentioned above.
MSSQL users: If you're looking for ISNULL(field, 0), it is IFNULL(field, 0) in MySQL.
When you compare two strings field with < or > you get a strange result. For example:
SELECT '1'<'2'
return 1
SELECT '1'<'10'
return 0!
I thinks that this is becouse of diffrent lenght of two operand. If you want to compare str_field1 with str_field2 you need to use CAST operator:
SELECT CAST('1' AS SIGNED INTEGER)<CAST('10' AS SIGNED INTEGER)
return 1
I hope this will help
I use PHP with MySQL and was expecting similar behaviour out of its operators, which led me to mess up a program when they didn't behave similarly... I post this as a warning to others who may experience the same problem (as there is no mention in the code examples above).
In our DB there is a column that was added after creation and defaults to null. All of the old records are thus marked null . Some newer records are marked with a source of where the record came from, so during a check for duplicates I put a clause like this in my query:
SOURCE != 'external'
!= (or <>) doesn't see NULL as something that can be compared to and thus doesn't compare itself to those records that have the field marked null! Thus hundreds of duplicate records were added because the comparison was failing.
Changing it to this fixed the problem:
SOURCE != 'external' || SOURCE IS NULL
Hope that helps someone!
It would be nice to also mention the need of the less obvious functions such as interval(), etc.
I am not able to guess in the first place, why the need arise to use such function??!!
Also there should be suggestions from MySQL team, not merely an explaination of the features of MySQL.
(cite: There is no proper suggestion of the best practices while choosing between char and varchar).
James Alday could have solved his problem a couple of other ways, at least one of which is almost surely faster.
He ended up doing
. . . SOURCE != 'external' || SOURCE IS NULL
so that the comparison operator (that is, the !=) would be effectively ignored if the field SOURCE is null.
Other ways:
. . . WHERE IFNULL( SOURCE, '' ) != 'external'
or
. . . WHERE ( SOURCE <=> 'external' ) = 0
or
. . . WHERE NOT ( SOURCE <=> 'external' )
Almost surely the last of those will perform better.
If you want to compare an empty string to a numeric value or an integer field, you'll have to CAST the integer field or value to a string, due to the fact that for mysql, a zero (the integer one) equals to an empty string
Example :
SELECT 0 = '';
==> 1
SELECT '0' = '';
==> 0
SELECT CAST(0 AS CHAR) = '';
==> 0
This is common when you want to check user input : if a user inputs a "0" for a field, the check without cast will fail because mysql thinks this is an empty string.
If you're playing with GREATEST and Dates you should not use NULL when comparing.
Example (MYSQL 5.0.44):
SELECT GREATEST('2007-12-31 23:59:59', '');
==> 2007-12-31 23:59:59
SELECT GREATEST('2007-12-31 23:59:59', '2037-01-01 00:00:00');
==> 2037-01-01 00:00:00
SELECT GREATEST('2007-12-31 23:59:59', NULL);
==> NULL
Work around is to test the data for NULL using ISNULL.
Here is an example with <columnB> that may or may not be NULL:
SELECT IF( ISNULL( columnB ), columnA, GREATEST( columnA, columnB) );
If columnB is NULL then the output will be columnA, otherwise its the result of GREATEST( columnA, columnB ).
The documentation above states that:
"For best results when using BETWEEN with date or time values, you should use CAST()..."
But in reality, if you were dealing with DATETIME or DATE fields, you HAVE to use CAST() - it is NOT for BEST results; rather for correct results.
I've explained this further with the CAST function. Just look up the function for a full example.
Khalid
Parity: Logical even / odd
I couldn't find a way to do this another way, so I leave this for others (and myself) to find.
The operators I'm looking for are "IS ODD" or "IS EVEN"
e.g., to select every record where id is an odd number, I was hoping for something like this:
>SELECT * FROM mytable WHERE id IS ODD
Here's the solution:
>SELECT * FROM mytable WHERE id % 2 = 1
Similarly, for even
>SELECT * FROM mytable WHERE id % 2 = 0
Happy Trails,
Loye Young
COALESCE() function is very useful if you need to calculate the average value of items stored in a row (not in a column where you would use AVG())
Exemplary usage when we have three items per row would be:
SELECT *,
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V
I talk about it in more detail on my blog post at:
http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/
Add your own comment.