6.3.1.2 Comparison Operators

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These functions work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as needed (as in Perl).

MySQL performs comparisons using the following rules:

• If one or both arguments are NULL, the result of the comparison is NULL, except for the <=> 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.
• In all other cases, the arguments are compared as floating-point (real) numbers.

By default, string comparisons are done in case-independent fashion using the current character set (ISO-8859-1 Latin1 by default, which also works excellently for English).

If you are comparing case-insensitive strings with any of the standard operators (=, <>..., but not LIKE) trailing whitespace (spaces, tabs and newlines) will be ignored.

mysql> SELECT "a" ="A \n";
        -> 1

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

=

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

<>

!=

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

<=>

NULL-safe equal:

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

IS NULL

IS NOT NULL

Test 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 other programs, MySQL supports the following extra features when using IS NULL:

• You can find the last inserted row with:

  SELECT * FROM tbl_name WHERE auto_col IS NULL
  

  This can be disabled by setting SQL_AUTO_IS_NULL=0. See section 5.5.6 SET Syntax.
• For NOT NULL DATE and DATETIME columns you can find the special date 0000-00-00 by using:

  SELECT * FROM tbl_name WHERE date_column IS NULL
  

  This is needed to get some ODBC applications to work (as ODBC doesn't support a 0000-00-00 date)

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 above, but applied to all the three arguments. Note that before 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

Same as NOT (expr BETWEEN min AND max).

expr IN (value,...)

Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then 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. From 4.1 (to comply with the SQL-99 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.

expr NOT IN (value,...)

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!

COALESCE(list)

Returns first non-NULL element in list:

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> 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

User Comments

Is there a null-safe version of the "!=" operator?
It's not terribly clear from TFM. It's also not
terribly clear if you can "NOT" anything, or only
the things specificially listed above.

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;

Ahmed Al-Adaileh, what you're trying to do is a
subselect, which mysql doesn't support right now.

the only "subselect" type thing you can do is use
IN(), but only with values:

SELECT bar FROM foo WHERE column IN
('value','whee','anothervalue',11,53,'value2')

you cannot put another SELECT statement in there.

On using ranges & BETWEEN for selecting data:

Selecting item_name FROM item WHERE item_name
BETWEEN 'A' and 'D' will not return all of 'D' items.
To ensure all lower-bound items are included set
the lower-bound to 'Dz'

All the upper bound items are returned as one would
expect.

Not sure why one wouldn't make the change to
upper bound as well.

I solve the issue of select * from table where col in
(select col from table2 where [condition]) by using a
JOIN; i made something like SELECT * FROM TABLE
JOIN TABLE2 WHERE TABLE.COL=TABLE2.COL AND
[CONDITION] (te condition of the subset); wich had
the same results of the IN() sentence avoiding a
process to add commas to the subset.

I want to audit my database and find records which have no or wrong reference into another table.
How to use exists clause?
like
select * from table_a where field_id not in (select field_id from table_b)?

Using BETWEEN can cause problems if you are not careful about types. For instance, the Perl DBD module for MySQL wraps integers in quotes when it replaces parameters. But "SELECT * FROM foo WHERE '1' BETWEEN a AND b" does not necessarily yield the same results as "SELECT * FROM foo WHERE (a <= '1') AND ('1' <= b)". It seems like where BETWEEN is used a string comparison is made, while an integer comparison is made using the <= symbols.

To respond to the question about a table date less than one of 0,3,5,10 days from the current date, the following solution is provided almost verbatim in the section on Date Time Functions, substituting the IN clause as follows:

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) IN (0,3,5,10)

The IN operator seems to completely disregard case-sensitivity enforced by BINARY in table creation.
Example, the queries:
(i) SELECT * FROM mytable WHERE myfield NOT IN ('F', 'd');
(ii) SELECT * FROM mytable WHERE (myfield != 'F') OR (myfield != 'd');
return different sets even though 'myfield' has been declared a BINARY field.
Hmmm! Is this a bug or am I doing something wrong here?
Any thoughts?

Gerard

Peter,

As you mentioned, SELECT item_name FROM item WHERE item_name BETWEEN 'A' AND 'D' does not return all the D items. This is the logical behavior, based on how the BETWEEN operator works. It compares strings alphabetically. In a dictionary, the entry for 'A' comes at the beginning of the A section, and the entry for 'D' comes at the beginning of the D section. So the entry for 'dog', for example, does not come between the entries for 'A' and 'D'. Changing 'D' to 'Dz' will work for most D items, but it will still fail for strings like 'Dzungaria'.

Maybe you want to do something like SELECT item_name FROM item WHERE LEFT(item_name, 1) BETWEEN 'A' AND 'D'.

Keep in mind that the column type (BLOB vs. TEXT, for example) will determine whether these comparisons are case-sensitive or not.

Add your own comment.