Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.8Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Comparison Functions and Operators

12.3.2 Comparison Functions and Operators

Table 12.3 Comparison Operators

NameDescription
BETWEEN ... AND ... Check whether a value is within a range of values
COALESCE() Return the first non-NULL argument
= Equal operator
<=> NULL-safe equal to operator
> Greater than operator
>= Greater than or equal 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 Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
< Less than operator
<= Less than or equal 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:

=  >  <  >=  <=  <>  !=

The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.10.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 12.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 12.10, “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
    

    For row comparisons, (a, b) = (x, y) is equivalent to:

    (a = x) AND (b = y)
    
  • <=>

    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.

    The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
    

    For row comparisons, (a, b) <=> (x, y) is equivalent to:

    (a <=> x) AND (b <=> y)
    
  • <>, !=

    Not equal:

    mysql> SELECT '.01' <> '0.01';
            -> 1
    mysql> SELECT .01 <> '0.01';
            -> 0
    mysql> SELECT 'zapp' <> 'zappp';
            -> 1
    

    For row comparisons, (a, b) <> (x, y) and (a, b) != (x, y) are equivalent to:

    (a <> x) OR (b <> y)
    
  • <=

    Less than or equal:

    mysql> SELECT 0.1 <= 2;
            -> 1
    

    For row comparisons, (a, b) <= (x, y) is equivalent to:

    (a < x) OR ((a = x) AND (b <= y))
    
  • <

    Less than:

    mysql> SELECT 2 < 2;
            -> 0
    

    For row comparisons, (a, b) < (x, y) is equivalent to:

    (a < x) OR ((a = x) AND (b < y))
    
  • >=

    Greater than or equal:

    mysql> SELECT 2 >= 2;
            -> 1
    

    For row comparisons, (a, b) >= (x, y) is equivalent to:

    (a > x) OR ((a = x) AND (b >= y))
    
  • >

    Greater than:

    mysql> SELECT 2 > 2;
            -> 0
    

    For row comparisons, (a, b) > (x, y) is equivalent to:

    (a > x) OR ((a = x) AND (b > y))
    
  • IS 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
    
  • IS NOT boolean_value

    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
    
  • IS NULL

    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:

  • IS NOT NULL

    Tests whether a value is not NULL.

    mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
            -> 1, 1, 0
    
  • 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 Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

    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, 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.

  • 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'
    

    GREATEST() 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 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
    

    IN can be used to compare row constructors:

    mysql> SELECT (3,4) IN ((1,2), (3,4));
            -> 1
    mysql> SELECT (3,4) IN ((1,2), (3,5));
            -> 0
    

    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 13.2.10.3, “Subqueries with ANY, IN, or SOME”.

  • 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 any argument is NULL, the result is NULL. No comparison is needed.

    • 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 the arguments comprise a mix of numbers and strings, they are compared as numbers.

    • If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

    • In all other cases, the arguments are compared as binary 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'
    

    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
  Posted by Peter on May 17, 2002
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!
  Posted by on May 17, 2002
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;
  Posted by Per Persson on January 28, 2005
The IN operator also works with tuples, at least in version 4.1:

mysql> select (3,4) in ((2,3),(3,4));
+------------------------+
| (3,4) in ((2,3),(3,4)) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.15 sec)

mysql> select (3,5) in ((2,3),(3,4));
+------------------------+
| (3,5) in ((2,3),(3,4)) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)

  Posted by DRB on April 4, 2006
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.
  Posted by James Alday on January 5, 2007
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!
  Posted by Bill Wilkinson on November 29, 2007
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.

  Posted by Axel Axel on December 14, 2007
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.

  Posted by Thaylor Harmor on December 21, 2007
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 ).
  Posted by Michal Borychowski on February 16, 2009
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/

  Posted by Landon Springer on June 13, 2010
When working with the "SET" datatype, it's useful to use the "COALESCE" function to add items to the set:

COALESCE(CONCAT(my_set, ",new_value"), "new_value")

This helped me to stop triggering data truncated errors. Cheers!
  Posted by Ilan Hazan on October 17, 2010
I found a way to optimize the MySQL IN() Comparison Operation in the case the field which the IN clause refers to, is part of the index used to execute the query. In this case, using the IN() operator will perform badly, as it first evaluate a full and unlimited sub query for each value in the IN clause, then, at completion, will merge the results and limit the returned result set.
My solution is to use a union of limited sub-queries for each value at the IN() clause.
Please read more about it: http://www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/
  Posted by Dmitrii Tisnek on May 2, 2012
Mixing data types in the in operator will make your queries slow, e.g.:

select * from account where account_id in ('','aaa'); 0 rows, 0.00 sec
select * from account where account_id in (123,124); 0 rows, 0.00 sec
select * from account where account_id in ('',123); 0 rows, 1 min 10.87 sec

table in questions has 4M rows, account_id is numeric, primary key
  Posted by Mike Jonsson on February 28, 2014
Well this works for me on my old 5.1 setup of community server.

SELECT COUNT(id) OR 0 FROM tblName
WHERE fldName = 1;

Mitigating all NULL errors in return by defaulting to a returned ZERO on NULL.

Perhaps not a standard SQL or ANSII format but we are all using MySQL and not JohnnyDoe's Home Brew SQL.

  Posted by Michael Muryn on March 5, 2014
When building a query dynamically, if you have to verify if a string value is in a given list of string and use the "IN" statement like this:

SELECT
....r.id AS recipe_id,
....GROUP_CONCAT(
..........DISTINCT concat('"', ra.code, '"')
..........ORDER BY ra.code
..........SEPARATOR ', '
.....) AS recipe_attribute_list
FROM recipe r
LEFT JOIN recipe_attribute ra ON
....ra.recipe_id = r.id
....AND ra.code IN ('ALCOHOL_FREE', 'NUTS_FREE', 'VEGETARIAN');

And then, if your list is empty, you still want your recipes, it just won't have any attributes. If you have the brilliant idea to replace the IN content with false like this

...
AND ra.code IN (false);
...

You'll get a major surprise as potentially all records will be sent.
Why? Because "code" is a string value and false is an alias to 0, which is an int. And when comparing string to int, the string is converted to an int, and unless the string begin by a number (e.g., "3abc"), then it will be converted to 0. So 0 IN (0), will of course evaluate to true.

So what to do? For my example:

a) you can use null instead of false, i.e.:
IN (null)

b) you can replace the whole condition with false, i.e.:
AND ra.code IN ('ALCOHOL_FREE', 'NUTS_FREE', 'VEGETARIAN');
will become:
AND false
this is clean because it give the result of the evaluation if SQL was supporting empty list.

c) you can of course remove the whole JOIN if it is logical, but it is a less straightforward approach and you won't be able to always do this.

I prefer 'b' here, but solutions might differ depending on the need of the query, or your style.
Sign Up Login You must be logged in to post a comment.