Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about comparison operators in MariaDB Server SQL. This section details operators like =, >, <, and LIKE used to compare values in conditions, essential for filtering and joining data.
NULLThe function is useful when substituting a default value for null values when displaying data.
See also NULL Values in MariaDB.
When two arguments are given, COALESCE() is the same as IFNULL():
Hex type confusion:
The reason for the differing results above is that when 0x31 is inserted directly to the column, it's treated as a number (see Hexadecimal Literals), while when 0x31 is passed to COALESCE(), it's treated as a string, because:
HEX values have a string data type by default.
COALESCE() has the same data type as the argument.
Substituting zero for NULL (in this case when the aggregate function returns NULL after finding no rows):
This page is licensed: GPLv2, originally from fill_help_tables.sql
exprmaxBETWEEN10min <= expr AND expr <= maxNULL:
DATE, DATETIME and TIMESTAMP examples. Omitting the time component compares against 00:00, so later times on the same date are not returned:
The following query doesn't show countries whose name starts with 'D':
When using WHERE name BETWEEN 'B' AND 'D', the condition includes all names that start with 'B' because 'B' is the lower bound and is inclusive. However, names starting with 'D' are excluded because the upper bound 'D' is treated as a string, and the comparison stops at the character level. For example, a name like 'D' is included if it matches exactly, but a name like 'Denmark' is not included because 'Denmark' is lexicographically greater than 'D'.
This page is licensed: GPLv2, originally from fill_help_tables.sql
FALSEUNKNOWNThis page is licensed: GPLv2, originally from fill_help_tables.sql
IS NOT boolean_valueSELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
+------------------+------------------+---------------------+
| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |
+------------------+------------------+---------------------+
| 1 | 1 | 0 |
+------------------+------------------+---------------------+NULLNULLWhen used in row comparisons these two queries return the same results:
This page is licensed: GPLv2, originally from fill_help_tables.sql
>SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+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().
This page is licensed: GPLv2, originally from
Tests whether a value is NULL. See also NULL Values in MariaDB.
Some ODBC applications use the syntax auto_increment_field IS NOT NULL to find the latest row that was inserted with an autogenerated key value. If your applications need this, you can set the variable to 1.
This page is licensed: GPLv2, originally from
Returns 1 if expr is equal to 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. Otherwise, type conversion takes place according to the rules described at , but
applied to all the arguments.
If expr is NULL, IN always returns NULL. If at least one of the values in the list is NULL, and one of the comparisons is true, the result is 1. If at least one of the values in the list is NULL and none of the comparisons is true, the result is NULL.
Type conversion:
This page is licensed: GPLv2, originally from
<>, !=Not equal operator. Evaluates both SQL expressions and returns 1 if they are not equal, and 0 if they are equal, or NULL if either expression is NULL. If the expressions return different data types, (for instance, a number and a string), performs type conversion.
When used in row comparisons these two queries return the same results:
This page is licensed: GPLv2, originally from
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 all other cases, the arguments are compared as case-insensitive strings.
LEAST() returns NULL if any argument is NULL.
This page is licensed: GPLv2, originally from
<=>NULL-safe equal operator. It 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.
a <=> b is equivalent to a = b OR (a IS NULL AND b IS NULL).
When used in row comparisons, these two queries return the same results:
See also .
This page is licensed: GPLv2, originally from
<=Less than or equal operator. Evaluates both SQL expressions and returns 1 if the left value is less than or equal to the right value and 0 if it is not, or NULL if either expression is NULL. If the expressions return different data types, (for instance, a number and a string), performs type conversion.
When used in row comparisons these two queries return the same results:
This page is licensed: GPLv2, originally from
COALESCE(value,...)SELECT COALESCE(NULL,1);
+------------------+
| COALESCE(NULL,1) |
+------------------+
| 1 |
+------------------+SELECT COALESCE(NULL,NULL,NULL);
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
| NULL |
+--------------------------+SET @a=NULL, @b=1;
SELECT COALESCE(@a, @b), IFNULL(@a, @b);
+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
| 1 | 1 |
+------------------+----------------+CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));
SELECT * FROM t1;
+------+------+
| a | b |
+------+------+
| 49 | a |
| 1 | a |
+------+------+SELECT SUM(score) FROM student;
+------------+
| SUM(score) |
+------------+
| NULL |
+------------+
SELECT COALESCE(SUM(score),0) FROM student;
+------------------------+
| COALESCE(SUM(score),0) |
+------------------------+
| 0 |
+------------------------+expr BETWEEN min AND maxSELECT 1 BETWEEN 2 AND 3;
+-------------------+
| 1 BETWEEN 2 AND 3 |
+-------------------+
| 0 |
+-------------------+SELECT 'b' BETWEEN 'a' AND 'c';
+-------------------------+
| 'b' BETWEEN 'a' AND 'c' |
+-------------------------+
| 1 |
+-------------------------+SELECT 2 BETWEEN 2 AND '3';
+---------------------+
| 2 BETWEEN 2 AND '3' |
+---------------------+
| 1 |
+---------------------+SELECT 2 BETWEEN 2 AND 'x-3';
+-----------------------+
| 2 BETWEEN 2 AND 'x-3' |
+-----------------------+
| 0 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Truncated incorrect DOUBLE value: 'x-3'SELECT 1 BETWEEN 1 AND NULL;
+----------------------+
| 1 BETWEEN 1 AND NULL |
+----------------------+
| NULL |
+----------------------+CREATE TABLE `x` (
a date ,
b datetime,
c timestamp
)
INSERT INTO x VALUES
('2018-11-11', '2018-11-11 05:15', '2018-11-11 05:15'),
('2018-11-12', '2018-11-12 05:15', '2018-11-12 05:15');
SELECT * FROM x WHERE a BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a | b | c |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
| 2018-11-12 | 2018-11-12 05:15:00 | 2018-11-12 05:15:00 |
+------------+---------------------+---------------------+
SELECT * FROM x WHERE b BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a | b | c |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
+------------+---------------------+---------------------+
SELECT * FROM x WHERE c BETWEEN '2018-11-11' AND '2018-11-12';
+------------+---------------------+---------------------+
| a | b | c |
+------------+---------------------+---------------------+
| 2018-11-11 | 2018-11-11 05:15:00 | 2018-11-11 05:15:00 |
+------------+---------------------+---------------------+SELECT name
FROM countries
WHERE name BETWEEN "B" AND "D"
ORDER BY name ASC;
+--------------------------------+
| name |
+--------------------------------+
| Bahamas |
| Bahrain |
| Bangladesh |
...
| Cuba |
| Cyprus |
| Czech Republic |
+--------------------------------+
41 rows in set (0.001 sec)SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
+------------------+-------------------+
| NULL IS NOT TRUE | NULL IS NOT FALSE |
+------------------+-------------------+
| 1 | 1 |
+------------------+-------------------+SELECT (t1.a, t1.b) > (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a > t2.x) OR ((t1.a = t2.x) AND (t1.b > t2.y))
FROM t1 INNER JOIN t2;SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
| 0 |
+-------+
SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
| 1 |
+-----------+IS NULLexpr IN (value,...)N0 < N11N1 <= N0 < N22N2 <= N0 < N3-1N0NULLN1 <= N2 <= N3 <= ... <= NnThis page is licensed: GPLv2, originally from fill_help_tables.sql
INTERVAL(N0,N1,N2,N3,...)SELECT INTERVAL(22, 24, 26, 28);NULL. If the expressions return different data types (for example, a number and a string), a type conversion is performed.When used in row comparisons these two queries are synonymous and return the same results:
To perform a NULL-safe comparison, use the <=> operator.
= can also be used as an assignment operator.
This page is licensed: GPLv2, originally from fill_help_tables.sql
NULL:
This page is licensed: GPLv2, originally from fill_help_tables.sql
expr NOT IN (value,...)SELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
| 1 |
+--------------------+min AND max).Note that the meaning of the alternative form NOT expr BETWEEN min AND max is affected by the HIGH_NOT_PRECEDENCE SQL_MODE flag.
NULL:
This page is licensed: GPLv2, originally from fill_help_tables.sql
expr NOT BETWEEN min AND maxNULLNULLWhen used in row comparisons, these two queries return the same results:
Type conversion:
Case insensitivity - see Character Sets and Collations:
This page is licensed: GPLv2, originally from fill_help_tables.sql
SELECT GREATEST(2,0);
+---------------+
| GREATEST(2,0) |
+---------------+
| 2 |
+---------------+SELECT GREATEST(34.0,3.0,5.0,767.0);
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
| 767.0 |
+------------------------------+SELECT (t1.a, t1.b) != (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a != t2.x) OR (t1.b != t2.y)
FROM t1 INNER JOIN t2;SELECT LEAST(2,0);
+------------+
| LEAST(2,0) |
+------------+
| 0 |
+------------+SELECT (t1.a, t1.b) <= (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a < t2.x) OR ((t1.a = t2.x) AND (t1.b <= t2.y))
FROM t1 INNER JOIN t2;SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
| 0 | 0 | 1 |
+-----------+-----------+--------------+SET @@sql_auto_is_null=1;
CREATE TABLE t1 (auto_increment_column INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 WHERE auto_increment_column IS NULL;
+-----------------------+
| auto_increment_column |
+-----------------------+
| 1 |
+-----------------------++--------------------------+
| INTERVAL(22, 24, 26, 28) |
+--------------------------+
| 0 |
+--------------------------+SELECT INTERVAL(22, 22, 22, 22, 23);+------------------------------+
| INTERVAL(22, 22, 22, 22, 23) |
+------------------------------+
| 3 |
+------------------------------+SELECT INTERVAL(25, 24, 26, 28);+--------------------------+
| interval(25, 24, 26, 28) |
+--------------------------+
| 1 |
+--------------------------+SELECT INTERVAL(27, 24, 26, 28);+--------------------------+
| interval(27, 24, 26, 28) |
+--------------------------+
| 2 |
+--------------------------+SELECT INTERVAL(27, 25, 26, 27);+--------------------------+
| interval(27, 25, 26, 27) |
+--------------------------+
| 3 |
+--------------------------+SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);+--------------------------------------+
| INTERVAL(23, 1, 15, 17, 30, 44, 200) |
+--------------------------------------+
| 3 |
+--------------------------------------+SELECT INTERVAL(10, 1, 10, 100, 1000);+--------------------------------+
| INTERVAL(10, 1, 10, 100, 1000) |
+--------------------------------+
| 2 |
+--------------------------------+SELECT INTERVAL(22, 23, 30, 44, 200);+-------------------------------+
| INTERVAL(22, 23, 30, 44, 200) |
+-------------------------------+
| 0 |
+-------------------------------+SELECT INTERVAL(10, 2, NULL);+-----------------------+
| INTERVAL(10, 2, NULL) |
+-----------------------+
| 2 |
+-----------------------+left_expr = right_exprSELECT (t1.a, t1.b) = (t2.x, t2.y) FROM t1 INNER JOIN t2;
SELECT (t1.a = t2.x) AND (t1.b = t2.y) FROM t1 INNER JOIN t2;SELECT 1 = 0;
+-------+
| 1 = 0 |
+-------+
| 0 |
+-------+
SELECT '0' = 0;
+---------+
| '0' = 0 |
+---------+
| 1 |
+---------+
SELECT '0.0' = 0;
+-----------+
| '0.0' = 0 |
+-----------+
| 1 |
+-----------+
SELECT '0.01' = 0;
+------------+
| '0.01' = 0 |
+------------+
| 0 |
+------------+
SELECT '.01' = 0.01;
+--------------+
| '.01' = 0.01 |
+--------------+
| 1 |
+--------------+
SELECT (5 * 2) = CONCAT('1', '0');
+----------------------------+
| (5 * 2) = CONCAT('1', '0') |
+----------------------------+
| 1 |
+----------------------------+
SELECT 1 = NULL;
+----------+
| 1 = NULL |
+----------+
| NULL |
+----------+
SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
| NULL |
+-------------+SELECT 'wefwf' NOT IN ('wee','wefwf','weg');
+--------------------------------------+
| 'wefwf' NOT IN ('wee','wefwf','weg') |
+--------------------------------------+
| 0 |
+--------------------------------------+SELECT 1 NOT IN ('1', '2', '3');
+--------------------------+
| 1 NOT IN ('1', '2', '3') |
+--------------------------+
| 0 |
+--------------------------+SELECT NULL NOT IN (1, 2, 3);
+-----------------------+
| NULL NOT IN (1, 2, 3) |
+-----------------------+
| NULL |
+-----------------------+
SELECT 1 NOT IN (1, 2, NULL);
+-----------------------+
| 1 NOT IN (1, 2, NULL) |
+-----------------------+
| 0 |
+-----------------------+
SELECT 5 NOT IN (1, 2, NULL);
+-----------------------+
| 5 NOT IN (1, 2, NULL) |
+-----------------------+
| NULL |
+-----------------------+SELECT 1 NOT BETWEEN 2 AND 3;
+-----------------------+
| 1 NOT BETWEEN 2 AND 3 |
+-----------------------+
| 1 |
+-----------------------+SELECT 'b' NOT BETWEEN 'a' AND 'c';
+-----------------------------+
| 'b' NOT BETWEEN 'a' AND 'c' |
+-----------------------------+
| 0 |
+-----------------------------+SELECT 1 NOT BETWEEN 1 AND NULL;
+--------------------------+
| 1 NOT BETWEEN 1 AND NULL |
+--------------------------+
| NULL |
+--------------------------+<SELECT (t1.a, t1.b) < (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a < t2.x) OR ((t1.a = t2.x) AND (t1.b < t2.y))
FROM t1 INNER JOIN t2;SELECT 2 < 2;
+-------+
| 2 < 2 |
+-------+
| 0 |
+-------+SELECT 3<'4';
+-------+
| 3<'4' |
+-------+
| 1 |
+-------+SELECT 'a'<'A';
+---------+
| 'a'<'A' |
+---------+
| 0 |
+---------+SELECT GREATEST('B','A','C');
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C |
+-----------------------+SELECT 2 IN (0,3,5,7);
+----------------+
| 2 IN (0,3,5,7) |
+----------------+
| 0 |
+----------------+SELECT 'wefwf' IN ('wee','wefwf','weg');
+----------------------------------+
| 'wefwf' IN ('wee','wefwf','weg') |
+----------------------------------+
| 1 |
+----------------------------------+SELECT 1 IN ('1', '2', '3');
+----------------------+
| 1 IN ('1', '2', '3') |
+----------------------+
| 1 |
+----------------------+SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
| NULL |
+-------------------+
SELECT 1 IN (1, 2, NULL);
+-------------------+
| 1 IN (1, 2, NULL) |
+-------------------+
| 1 |
+-------------------+
SELECT 5 IN (1, 2, NULL);
+-------------------+
| 5 IN (1, 2, NULL) |
+-------------------+
| NULL |
+-------------------+SELECT '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
| 1 |
+-----------------+
SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
| 0 |
+---------------+
SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
| 1 |
+-------------------+SELECT LEAST(34.0,3.0,5.0,767.0);
+---------------------------+
| LEAST(34.0,3.0,5.0,767.0) |
+---------------------------+
| 3.0 |
+---------------------------+SELECT LEAST('B','A','C');
+--------------------+
| LEAST('B','A','C') |
+--------------------+
| A |
+--------------------+SELECT (t1.a, t1.b) <=> (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a <=> t2.x) AND (t1.b <=> t2.y)
FROM t1 INNER JOIN t2;SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
| 1 | 1 | 0 |
+---------+---------------+------------+
SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
| 1 | NULL | NULL |
+-------+-------------+----------+SELECT 0.1 <= 2;
+----------+
| 0.1 <= 2 |
+----------+
| 1 |
+----------+SELECT 'a'<='A';
+----------+
| 'a'<='A' |
+----------+
| 1 |
+----------+Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
There is an important difference between using IS TRUE or comparing a value with TRUE using =. When using =, only 1 equals to TRUE. But when using IS TRUE, all values which are logically true (like a number > 1) return TRUE.
Difference between = and IS TRUE:
This page is licensed: GPLv2, originally from
IS boolean_valueSELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
+-----------+------------+-----------------+
| 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN |
+-----------+------------+-----------------+
| 1 | 1 | 1 |
+-----------+------------+-----------------+SELECT 2 = TRUE, 2 IS TRUE;
+----------+-----------+
| 2 = TRUE | 2 IS TRUE |
+----------+-----------+
| 0 | 1 |
+----------+-----------+>=Greater than or equal operator. Evaluates both SQL expressions and returns 1 if the left value is greater than or equal to the right value and 0 if it is not, or NULL if either expression is NULL. If the expressions return different data types, (for instance, a number and a string), performs type conversion.
When used in row comparisons, these two queries return the same results:
This page is licensed: GPLv2, originally from
SELECT (t1.a, t1.b) >= (t2.x, t2.y)
FROM t1 INNER JOIN t2;
SELECT (t1.a > t2.x) OR ((t1.a = t2.x) AND (t1.b >= t2.y))
FROM t1 INNER JOIN t2;SELECT 2 >= 2;
+--------+
| 2 >= 2 |
+--------+
| 1 |
+--------+
SELECT 'A' >= 'a';
+------------+
| 'A' >= 'a' |
+------------+
| 1 |
+------------+IS NOT NULLTests whether a value is not NULL. See also NULL Values in MariaDB.
This page is licensed: GPLv2, originally from
SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
+---------------+---------------+------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
| 1 | 1 | 0 |
+---------------+---------------+------------------+