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.
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 , but applied to all the three arguments.
NULL:
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
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)>Greater than operator. Evaluates both SQL expressions and returns 1 if the left value is greater than 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
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
IS NULLSELECT 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 |
+------------------+------------------+---------------------+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
NULLNULLWhen used in row comparisons these two queries return the same results:
This page is licensed: GPLv2, originally from fill_help_tables.sql
<>, !=This page is licensed: GPLv2, originally from fill_help_tables.sql
GREATEST(value1,value2,...)SELECT GREATEST(2,0);
+---------------+
| GREATEST(2,0) |
+---------------+
| 2 |
+---------------+NULLNULLWhen 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
NULLNULLWhen used in row comparisons these two queries return the same results:
This page is licensed: GPLv2, originally from fill_help_tables.sql
<=FALSEUNKNOWNThere 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 fill_help_tables.sql
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 maxNULLNULL0NULLNULLa <=> 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 NULL Values in MariaDB.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ISNULL()1See also NULL Values in MariaDB.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ISNULL(expr)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 |
+-----------+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 |
+-----------------------+SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
+------------------+-------------------+
| NULL IS NOT TRUE | NULL IS NOT FALSE |
+------------------+-------------------+
| 1 | 1 |
+------------------+-------------------+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 (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 '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
| 1 |
+-----------------+
SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
| 0 |
+---------------+
SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
| 1 |
+-------------------+SELECT GREATEST(34.0,3.0,5.0,767.0);
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
| 767.0 |
+------------------------------+SELECT GREATEST('B','A','C');
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C |
+-----------------------+<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 (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 0.1 <= 2;
+----------+
| 0.1 <= 2 |
+----------+
| 1 |
+----------+SELECT 'a'<='A';
+----------+
| 'a'<='A' |
+----------+
| 1 |
+----------+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 |
+----------+-----------+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) 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 ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
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
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
INTERVAL(N0,N1,N2,N3,...)Returns the index of the last argument that is less than or equal to the first argument, or is NULL.
Returns 0 if N0 < N1, 1 if N1 <= N0 < N2, 2 if N2 <= N0 < N3 and so on or -1 if N0 is NULL. All arguments are treated as integers. It is required that N1 <= N2 <= N3 <= ... <= Nn for this function to work correctly because a fast binary search is used.
This page is licensed: GPLv2, originally from
>=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
IS NOT NULLSELECT 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 |
+---------------+---------------+------------------+expr IN (value,...)SELECT INTERVAL(22, 24, 26, 28);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 LEAST(2,0);
+------------+
| LEAST(2,0) |
+------------+
| 0 |
+------------+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 |
+--------------------++--------------------------+
| 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 |
+-----------------------+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 |
+------------+SELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
| 1 |
+--------------------+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 |
+-----------------------+Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. At least one parameter must be passed.
The function is useful when substituting a default value for null values when displaying data.
See also .
When two arguments are given, COALESCE() is the same as :
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 ), 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
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 |
+------------------------+