Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about operators in MariaDB Server SQL. This section details arithmetic, comparison, logical, and bitwise operators used in expressions and conditions for data manipulation and querying.
>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
Understand operator precedence in MariaDB Server SQL. This section details the order in which operators are evaluated within expressions, crucial for writing accurate and predictable queries.
The precedence is the order in which the SQL operators are evaluated.
The following list shows the SQL operator precedence. Operators that appear first in the list have a higher precedence. Operators which are listed together have the same precedence.
,
Learn about logical operators in MariaDB Server SQL. This section details operators like AND, OR, and NOT used to combine or negate conditions, essential for complex filtering and data selection.
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.
- (unary minus), bitwise not (unary bit inversion)
|| (string concatenation)
^ (bitwise XOR)
& (bitwise AND)
| (bitwise OR)
Functions precedence is always higher than operators precedence.
If the HIGH_NOT_PRECEDENCE SQL_MODE is set, NOT has the same precedence as !.
The || operator's precedence, as well as its meaning, depends on the PIPES_AS_CONCAT SQL_MODE flag: if it is on, || can be used to concatenate strings (like the CONCAT() function) and has a higher precedence.
The = operator's precedence depends on the context - it is higher when = is used as a comparison operator.
Parentheses can be used to modify the operators precedence in an expression.
The AND, OR, && and || operators support short-circuit evaluation. This means that, in some cases, the expression on the right of those operators is not evaluated, because its result cannot affect the result. In the following cases, short-circuit evaluation is used and x() is not evaluated:
FALSE AND x()
FALSE && x()
TRUE OR x()
TRUE || x()
NULL BETWEEN x() AND x()
Note however that the short-circuit evaluation does not apply to NULL AND x(). Also, BETWEEN's right operands are not evaluated if the left operand is NULL, but in all other cases all the operands are evaluated.
This is a speed optimization. Also, since functions can have side-effects, this behavior can be used to choose whether execute them or not using a concise syntax:
This page is licensed: CC BY-SA / Gnu FDL
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;var_name := exprAssignment operator for assigning a value. The value on the right is assigned to the variable on left.
Unlike the = operator, := can always be used to assign a value to a variable.
This operator works with both and .
When assigning the same value to several variables, can be useful.
This page is licensed: CC BY-SA / Gnu FDL
IS NOT NULLTests whether a value is not NULL. See also NULL Values in MariaDB.
This page is licensed: GPLv2, originally from
SELECT some_function() OR log_error();SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
| 0 |
+-------+
SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
| 1 |
+-----------+Learn about assignment operators in MariaDB Server SQL. This section details how to assign values to variables and columns, essential for data manipulation and programmatic logic.
Learn about arithmetic operators in MariaDB Server SQL. This section details how to perform mathematical calculations like addition, subtraction, multiplication, and division within your queries.
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);NULLNULL10In the following example, the right 1 XOR 1 is evaluated first, and returns 0. Then, 1 XOR 0 is evaluated, and 1 is returned.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SELECT 1 NOT BETWEEN 2 AND 3;
+-----------------------+
| 1 NOT BETWEEN 2 AND 3 |
+-----------------------+
| 1 |
+-----------------------+SELECT 1042 % 50;
+-----------+
| 1042 % 50 |
+-----------+
| 42 |
+-----------+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
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 |
+---------------+---------------+------------------++--------------------------+
| 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 |
+-----------------------+XORSELECT 1 XOR 1;
+---------+
| 1 XOR 1 |
+---------+
| 0 |
+---------+
SELECT 1 XOR 0;
+---------+
| 1 XOR 0 |
+---------+
| 1 |
+---------+
SELECT 1 XOR NULL;
+------------+
| 1 XOR NULL |
+------------+
| NULL |
+------------+SELECT 1 XOR 1 XOR 1;
+---------------+
| 1 XOR 1 XOR 1 |
+---------------+
| 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 @x := 10;
+----------+
| @x := 10 |
+----------+
| 10 |
+----------+
SELECT @x, @y := @x;
+------+----------+
| @x | @y := @x |
+------+----------+
| 10 | 10 |
+------+----------+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 ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+IN0exprIf 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 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
<=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
NULL00NULLFor this operator, short-circuit evaluation can be used.
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
<>, !=SELECT GREATEST('B','A','C');
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C |
+-----------------------+expr IN (value,...)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 (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 |
+----------+<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 |
+---------+AND, &&SELECT 1 && 1;
+--------+
| 1 && 1 |
+--------+
| 1 |
+--------+
SELECT 1 && 0;
+--------+
| 1 && 0 |
+--------+
| 0 |
+--------+
SELECT 1 && NULL;
+-----------+
| 1 && NULL |
+-----------+
| NULL |
+-----------+
SELECT 0 && NULL;
+-----------+
| 0 && NULL |
+-----------+
| 0 |
+-----------+
SELECT NULL && 0;
+-----------+
| NULL && 0 |
+-----------+
| 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 '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
| 1 |
+-----------------+
SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
| 0 |
+---------------+
SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
| 1 |
+-------------------+NO_UNSIGNED_SUBTRACTIONFor real or string operands, the operand with the highest precision determines the result precision.
Unary minus:
This page is licensed: CC BY-SA / Gnu FDL
left_expr = right_exprEqual operator. Evaluates both SQL expressions and returns 1 if they are equal, 0 if they are not equal, or if either expression is 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 .
This page is licensed: GPLv2, originally from
Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.
For this operator, can be used.
Note that, if the PIPES_AS_CONCAT is set, || is used as a string concatenation operator. This means that a || b is the same as CONCAT(a,b). See for details.
In , || ignores .
In :
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
identifier = exprThe equal sign is used as both an assignment operator in certain contexts, and as a comparison operator. When used as assignment operator, the value on the right is assigned to the variable (or column, in some contexts) on the left.
Since its use can be ambiguous, unlike the , the = assignment operator cannot be used in all contexts, and is only valid as part of a statement, or the SET clause of an statement
This operator works with both and .
This page is licensed: CC BY-SA / Gnu FDL
<=>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
NOT, !Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL.
By default, the ! operator has a . If the HIGH_NOT_PRECEDENCE flag is set, NOT and ! have the same precedence.
This page is licensed: GPLv2, originally from
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
-SELECT 96-9;
+------+
| 96-9 |
+------+
| 87 |
+------+
SELECT 15-17;
+-------+
| 15-17 |
+-------+
| -2 |
+-------+
SELECT 3.66 + 1.333;
+--------------+
| 3.66 + 1.333 |
+--------------+
| 4.993 |
+--------------+SELECT - (3+5);
+---------+
| - (3+5) |
+---------+
| -8 |
+---------+OR, ||IS boolean_valueSELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
| 1 |
+--------------------+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
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 fill_help_tables.sql
LEAST(value1,value2,...)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 = 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.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 |
+------------+UPDATE table_name SET x = 2 WHERE x > 100;SET @x = 1, @y := 2;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 7*6;
+-----+
| 7*6 |
+-----+
| 42 |
+-----+
SELECT 1234567890*9876543210;
+-----------------------+
| 1234567890*9876543210 |
+-----------------------+
| -6253480962446024716 |
+-----------------------+
SELECT 18014398509481984*18014398509481984.0;
+---------------------------------------+
| 18014398509481984*18014398509481984.0 |
+---------------------------------------+
| 324518553658426726783156020576256.0 |
+---------------------------------------+
SELECT 18014398509481984*18014398509481984;
+-------------------------------------+
| 18014398509481984*18014398509481984 |
+-------------------------------------+
| 0 |
+-------------------------------------+SELECT NOT 10;
+--------+
| NOT 10 |
+--------+
| 0 |
+--------+
SELECT NOT 0;
+-------+
| NOT 0 |
+-------+
| 1 |
+-------+
SELECT NOT NULL;
+----------+
| NOT NULL |
+----------+
| NULL |
+----------+
SELECT ! (1+1);
+---------+
| ! (1+1) |
+---------+
| 0 |
+---------+
SELECT ! 1+1;
+-------+
| ! 1+1 |
+-------+
| 1 |
+-------+SELECT 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 '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 |
+-----------------------+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 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 |
+--------------------+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 sql_auto_is_null variable to 1.
This page is licensed: GPLv2, originally from fill_help_tables.sql
For real or string operands, the operand with the highest precision determines the result precision.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Division operator. Dividing by zero will return NULL. By default, returns four digits after the decimal. This is determined by the server system variable div_precision_increment which by default is four. It can be set from 0 to 30.
Dividing by zero returns NULL. If the default ERROR_ON_DIVISION_BY_ZERO is used, a division by zero also produces a warning.
Changing for the session from the default of four to six:
This page is licensed: GPLv2, originally from
IS NULLSELECT 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 3+5;
+-----+
| 3+5 |
+-----+
| 8 |
+-----+SELECT 1 || 1;
+--------+
| 1 || 1 |
+--------+
| 1 |
+--------+
SELECT 1 || 0;
+--------+
| 1 || 0 |
+--------+
| 1 |
+--------+
SELECT 0 || 0;
+--------+
| 0 || 0 |
+--------+
| 0 |
+--------+
SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
| NULL |
+-----------+
SELECT 1 || NULL;
+-----------+
| 1 || NULL |
+-----------+
| 1 |
+-----------+SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
| 0 |
+-----------+/SELECT 4/5;
+--------+
| 4/5 |
+--------+
| 0.8000 |
+--------+
SELECT 300/(2-2);
+-----------+
| 300/(2-2) |
+-----------+
| NULL |
+-----------+
SELECT 300/7;
+---------+
| 300/7 |
+---------+
| 42.8571 |
+---------+SET div_precision_increment = 6;
SELECT 300/7;
+-----------+
| 300/7 |
+-----------+
| 42.857143 |
+-----------+
SELECT 300/7;
+-----------+
| 300/7 |
+-----------+
| 42.857143 |
+-----------+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
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 |
+------------------------+SELECT 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 |
+------------------+------------------+---------------------+SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
+------------------+-------------------+
| NULL IS NOT TRUE | NULL IS NOT FALSE |
+------------------+-------------------+
| 1 | 1 |
+------------------+-------------------+