Movatterモバイル変換

Table 14.4 Comparison Operators

In previous versions of MySQL, when evaluating an expression containingLEAST() orGREATEST(), the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments toLEAST("11", "45", "2") are evaluated and sorted as strings, so that this expression returns"11".

The function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means thatLEAST("11", "45", "2") + 0 evaluates to"11" + 0 and thus to integer 11.

• =

  Equal:

  mysql> SELECT 1 = 0;        -> 0mysql> SELECT '0' = 0;        -> 1mysql> SELECT '0.0' = 0;        -> 1mysql> SELECT '0.01' = 0;        -> 0mysql> 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 returns1 rather thanNULL if both operands areNULL, and0 rather thanNULL if one operand isNULL.

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

  mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;        -> 1, 1, 0mysql> 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';        -> 1mysql> SELECT .01 <> '0.01';        -> 0mysql> 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))

• expr BETWEENmin ANDmax

  Ifexpr is greater than or equal tomin andexpr is less than or equal tomax,BETWEEN returns1, otherwise it returns0. This is equivalent to the expression(min <=expr ANDexpr <=max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described inSection 14.3, “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, 0mysql> SELECT 1 BETWEEN 2 AND 3;        -> 0mysql> SELECT 'b' BETWEEN 'a' AND 'c';        -> 1mysql> SELECT 2 BETWEEN 2 AND '3';        -> 1mysql> SELECT 2 BETWEEN 2 AND 'x-3';        -> 0

  For best results when usingBETWEEN with date or time values, useCAST() to explicitly convert the values to the desired data type. Examples: If you compare aDATETIME to twoDATE values, convert theDATE values toDATETIME values. If you use a string constant such as'2001-1-1' in a comparison to aDATE, cast the string to aDATE.

• expr NOT BETWEENmin ANDmax

  This is the same asNOT (expr BETWEENmin ANDmax).

• COALESCE(value,...)

  Returns the first non-NULL value in the list, orNULL if there are no non-NULL values.

  The return type ofCOALESCE() is the aggregated type of the argument types.

  mysql> SELECT COALESCE(NULL,1);        -> 1mysql> SELECT COALESCE(NULL,NULL,NULL);        -> NULL

• EXISTS(query)

  Whether the result of a query contains any rows.

  CREATE TABLE t (col VARCHAR(3));INSERT INTO t VALUES ('aaa', 'bbb', 'ccc', 'eee');SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');        -> 1SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');        -> 0

• NOT EXISTS(query)

  Whether the result of a query contains no rows:

  SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');        -> 0SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');        -> 1

• GREATEST(value1,value2,...)

  With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as forLEAST().

  mysql> SELECT GREATEST(2,0);        -> 2mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);        -> 767.0mysql> SELECT GREATEST('B','A','C');        -> 'C'

  GREATEST() returnsNULL if any argument isNULL.

• expr IN (value,...)

  Returns1 (true) ifexpr is equal to any of the values in theIN() list, else returns0 (false).

  Type conversion takes place according to the rules described inSection 14.3, “Type Conversion in Expression Evaluation”, applied to all the arguments. If no type conversion is needed for the values in theIN() list, they are all non-JSON constants of the same type, andexpr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search forexpr is done using a binary search, which makes theIN() operation very quick.

  mysql> SELECT 2 IN (0,3,5,7);        -> 0mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');        -> 1

  IN() can be used to compare row constructors:

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

  You should never mix quoted and unquoted values in anIN() 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 anIN() 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');

  Implicit type conversion may produce nonintuitive results:

  mysql> SELECT 'a' IN (0), 0 IN ('b');        -> 1, 1

  In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).

  The number of values in theIN() list is only limited by themax_allowed_packet value.

  To comply with the SQL standard,IN() returnsNULL not only if the expression on the left hand side isNULL, but also if no match is found in the list and one of the expressions in the list isNULL.

  IN() syntax can also be used to write certain types of subqueries. SeeSection 15.2.15.3, “Subqueries with ANY, IN, or SOME”.

• expr NOT IN (value,...)

  This is the same asNOT (expr IN (value,...)).

• INTERVAL(N,N1,N2,N3,...)

  Returns0 ifN ≤N1,1 ifN ≤N2 and so on, or-1 ifN isNULL. All arguments are treated as integers. It is required thatN1 ≤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);        -> 3mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);        -> 2mysql> SELECT INTERVAL(22, 23, 30, 44, 200);        -> 0

• ISboolean_value

  Tests a value against a boolean value, whereboolean_value can beTRUE,FALSE, orUNKNOWN.

  mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;        -> 1, 1, 1

• IS NOTboolean_value

  Tests a value against a boolean value, whereboolean_value can beTRUE,FALSE, orUNKNOWN.

  mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;        -> 1, 1, 0

• IS NULL

  Tests whether a value isNULL.

  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 usingIS NULL:

  • Ifsql_auto_is_null variable is set to 1, then after a statement that successfully inserts an automatically generatedAUTO_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 theLAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, seeSection 14.15, “Information Functions”. If noAUTO_INCREMENT value was successfully inserted, theSELECT statement returns no row.

    The behavior of retrieving anAUTO_INCREMENT value by using anIS NULL comparison can be disabled by settingsql_auto_is_null = 0. SeeSection 7.1.8, “Server System Variables”.

    The default value ofsql_auto_is_null is 0.

  • ForDATE andDATETIME columns that are declared asNOT 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.

    SeeObtaining Auto-Increment Values, and the description for theFLAG_AUTO_IS_NULL option atConnector/ODBC Connection Parameters.

• IS NOT NULL

  Tests whether a value is notNULL.

  mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;        -> 1, 1, 0

• ISNULL(expr)

  Ifexpr isNULL,ISNULL() returns1, otherwise it returns0.

  mysql> SELECT ISNULL(1+1);        -> 0mysql> SELECT ISNULL(1/0);        -> 1

  ISNULL() can be used instead of= to test whether a value isNULL. (Comparing a value toNULL using= always yieldsNULL.)

  TheISNULL() function shares some special behaviors with theIS NULL comparison operator. See the description ofIS NULL.

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

  • If all arguments are integer-valued, they are compared as integers.

  • If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is aDECIMAL value, they are compared asDECIMAL values.

  • If the arguments comprise a mix of numbers and strings, they are compared as strings.

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

  The return type ofLEAST() is the aggregated type of the comparison argument types.

  mysql> SELECT LEAST(2,0);        -> 0mysql> SELECT LEAST(34.0,3.0,5.0,767.0);        -> 3.0mysql> SELECT LEAST('B','A','C');        -> 'A'