12.4.2 Comparison Functions and Operators (original) (raw)

12.4.2 Comparison Functions and Operators

Table 12.4 Comparison Operators

Name Description
> Greater than operator
>= Greater than or equal operator
< Less than operator
<>, != Not equal operator
<= Less than or equal operator
<=> NULL-safe equal to operator
= Equal operator
BETWEEN ... AND ... Whether a value is within a range of values
COALESCE() Return the first non-NULL argument
EXISTS() Whether the result of a query contains any rows
GREATEST() Return the largest argument
IN() 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
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Whether a value is not within a range of values
NOT EXISTS() Whether the result of a query contains no rows
NOT IN() 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, seeSection 13.2.10.5, “Row Subqueries”.

Some of the functions in this section return values other than1 (TRUE),0 (FALSE), orNULL. LEAST() and GREATEST() are examples of such functions; Section 12.3, “Type Conversion in Expression Evaluation”, describes the rules for comparison operations performed by these and similar functions for determining their return values.

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(). SeeSection 12.10, “Cast Functions and Operators”.

By default, string comparisons are not case-sensitive and use the current character set. The default islatin1 (cp1252 West European), which also works well for English.

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)  
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)  
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)  
mysql> SELECT 0.1 <= 2;  
        -> 1

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

(a < x) OR ((a = x) AND (b <= y))  
mysql> SELECT 2 < 2;  
        -> 0

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

(a < x) OR ((a = x) AND (b < y))  
mysql> SELECT 2 >= 2;  
        -> 1

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

(a > x) OR ((a = x) AND (b >= y))  
mysql> SELECT 2 > 2;  
        -> 0

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

(a > x) OR ((a = x) AND (b > y))  
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 usingBETWEEN with date or time values, use CAST() 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 a DATE, cast the string to a DATE.

mysql> SELECT COALESCE(NULL,1);  
        -> 1  
mysql> SELECT COALESCE(NULL,NULL,NULL);  
        -> NULL  
CREATE TABLE t (col VARCHAR(3));  
INSERT INTO t VALUES ('aaa', 'bbb', 'ccc', 'eee');  
SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');  
        -> 1  
SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');  
        -> 0  
SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');  
        -> 0  
SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');  
        -> 1  
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() returnsNULL if any argument isNULL.

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 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 the IN() list is only limited by themax_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. SeeSection 13.2.10.3, “Subqueries with ANY, IN, or SOME”.

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

SELECT * FROM tbl_name WHERE auto_col IS NULL  
If the statement returns a row, the value returned is the same as if you invoked the[LAST\_INSERT\_ID()](information-functions.html#function%5Flast-insert-id) function. For details, including the return value after a multiple-row insert, see[Section 12.15, “Information Functions”](information-functions.html "12.15 Information Functions"). If no`AUTO_INCREMENT` value was successfully inserted, the [SELECT](select.html "13.2.9 SELECT Statement") statement returns no row.  
The behavior of retrieving an`AUTO_INCREMENT` value by using an[IS NULL](comparison-operators.html#operator%5Fis-null) comparison can be disabled by setting[sql\_auto\_is\_null = 0](server-system-variables.html#sysvar%5Fsql%5Fauto%5Fis%5Fnull). See [Section 5.1.7, “Server System Variables”](server-system-variables.html "5.1.7 Server System Variables").  
The default value of[sql\_auto\_is\_null](server-system-variables.html#sysvar%5Fsql%5Fauto%5Fis%5Fnull) is 0.  
SELECT * FROM tbl_name WHERE date_column IS NULL  
This is needed to get some ODBC applications to work because ODBC does not support a`'0000-00-00'` date value.  
See[Obtaining Auto-Increment Values](/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html), and the description for the`FLAG_AUTO_IS_NULL` option at[Connector/ODBC Connection Parameters](/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html).
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;  
        -> 1, 1, 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 NULL.)
The ISNULL() function shares some special behaviors with theIS NULL comparison operator. See the description ofIS NULL.

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'