GNU Info

(mysql.info)Comparison Operators

---

Next: Logical Operators Prev: Parenthesis Up: Non-typed Operators

---

Enter node , (file) or (file)node

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

The examples below 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 or not 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

`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.  The
     first argument (`expr') determines how the comparison is performed
     as follows:

        * If `expr' is a `TIMESTAMP', `DATE', or `DATETIME' column,
          `MIN()' and `MAX()' are formatted to the same format if they
          are constants.

        * If `expr' is a case-insensitive string expression, a
          case-insensitive string comparison is done.

        * If `expr' is a case-sensitive string expression, a
          case-sensitive string comparison is done.

        * If `expr' is an integer expression, an integer comparison is
          done.

        * Otherwise, a floating-point (real) comparison is done.

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

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

If you are comparing case sensitive string with any of the standard
operators (`=', `<>'..., but not `LIKE') end space will be ignored.

     mysql> select "a" ="A ";
             -> 1