DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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