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.
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.
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.
(unary minus), (unary bit inversion)
|| (string concatenation)
(bitwise XOR)
, , , , (multiplication, division, modulo)
, (subtraction, addition)
,
(bitwise AND)
(bitwise OR)
, ,
(comparison), , , , , , , ,
,
(logical or),
(assignment),
Functions precedence is always higher than operators precedence.
If the HIGH_NOT_PRECEDENCE is set, NOT has the same precedence as !.
The || operator's precedence, as well as its meaning, depends on the PIPES_AS_CONCAT flag: if it is on, || can be used to concatenate strings (like the function) and has a higher precedence.
The = operator's precedence depends on the context - it is higher when = is used as a comparison operator.
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()
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
+Addition.
If both operands are integers, the result is calculated with BIGINT precision. If either integer is unsigned, the result is also an unsigned integer.
For real or string operands, the operand with the highest precision determines the result precision.
This page is licensed: GPLv2, originally from
NULL BETWEEN x() AND x()
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
SELECT some_function() OR log_error();SELECT 3+5;
+-----+
| 3+5 |
+-----+
| 8 |
+-----+-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 |
+---------+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.
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 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.
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
This operator works with both user-defined variables and local variables.
When assigning the same value to several variables, LAST_VALUE() can be useful.
This page is licensed: CC BY-SA / Gnu FDL
ISNULL()1See also NULL Values in MariaDB.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ISNULL(expr)UPDATE table_name SET x = 2 WHERE x > 100;SET @x = 1, @y := 2;SELECT 1042 % 50;
+-----------+
| 1042 % 50 |
+-----------+
| 42 |
+-----------+var_name := exprSELECT @x := 10;
+----------+
| @x := 10 |
+----------+
| 10 |
+----------+
SELECT @x, @y := @x;
+------+----------+
| @x | @y := @x |
+------+----------+
| 10 | 10 |
+------+----------+SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+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
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
>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 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
XORXOR stands for eXclusive OR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.
In 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
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
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
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
<>, !=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
IS NOT NULLTests whether a value is not NULL. See also NULL Values in MariaDB.
This page is licensed: GPLv2, originally from
<Less than operator. Evaluates both SQL expressions and returns 1 if the left value is less 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:
Type conversion:
Case insensitivity - see :
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 |
+-----------------------+IS boolean_valueSELECT INTERVAL(22, 24, 26, 28);SELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
| 1 |
+--------------------+SELECT 1 NOT BETWEEN 2 AND 3;
+-----------------------+
| 1 NOT BETWEEN 2 AND 3 |
+-----------------------+
| 1 |
+-----------------------+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 |
+------------------------------+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
<=NULLNULL0NULLNULLa <=> 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
+--------------------------+
| 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 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 (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 (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 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 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 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 '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 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 |
+--------------------+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 GREATEST('B','A','C');
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C |
+-----------------------+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 |
+-------------------+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 |
+---------------+---------------+------------------+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 |
+---------+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.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) 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 |
+-------+-------------+----------+Dividing by zero returns NULL. If the default ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, a division by zero also produces a warning.
Changing div_precision_increment for the session from the default of four to six:
This page is licensed: GPLv2, originally from fill_help_tables.sql
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
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
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
/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 |
+-----------+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 maxexpr IN (value,...)SELECT 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 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 |
+-------------------+10NULL1NULLNULLNULLFor this operator, short-circuit evaluation can be used.
In , || ignores null.
In :
This page is licensed: GPLv2, originally from fill_help_tables.sql
OR, ||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 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 |
+------------------+-------------------+