﻿12.2. Operators

Main Page

## 12.2. Operators

NameDescription
`AND`, `&&`Logical AND
`BINARY`Cast a string to a binary string
`&`Bitwise AND
`|`Bitwise OR
`^`Bitwise XOR
`/`Division operator
`DIV`(v4.1.0)Integer division
`<=>`NULL-safe equal to operator
`=`Equal operator
`>=`Greater than or equal operator
`>`Greater than operator
`IS NULL`NULL value test
`IS`Test a value against a boolean
`<<`Left shift
`<=`Less than or equal operator
`<`Less than operator
`LIKE`Simple pattern matching
`-`Minus operator
`%`Modulo operator
`!=`, `<>`Not equal operator
`NOT LIKE`Negation of simple pattern matching
`NOT REGEXP`Negation of REGEXP
`NOT`, `!`Negates value
`||`, `OR`Logical OR
`+`Addition operator
`REGEXP`Pattern matching using regular expressions
`>>`Right shift
`RLIKE`Synonym for REGEXP
`SOUNDS LIKE`(v4.1.0)Compare sounds
`~`Invert bits
`*`Times operator
`-`Change the sign of the argument
`XOR`Logical XOR

### 12.2.1. Operator Precedence

Operator precedences are shown in the following list, from lowest precedence to the highest. Operators that are shown together on a line have the same precedence.

```:=
||, OR, XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!
BINARY, COLLATE```

The precedence shown for `NOT` is as of MySQL 5.0.2. For earlier versions, or from 5.0.2 on if the `HIGH_NOT_PRECEDENCE` SQL mode is enabled, the precedence of `NOT` is the same as that of the `!` operator. See Section 5.2.6, “SQL Modes”.

The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. For example:

```mysql> `SELECT 1+2*3;`
-> 7
mysql> `SELECT (1+2)*3;`
-> 9```

### 12.2.2. Type Conversion in Expression Evaluation

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

```mysql> `SELECT 1+'1';`
-> 2
mysql> `SELECT CONCAT(2,' test');`
-> '2 test'```

It is also possible to perform explicit conversions. If you want to convert a number to a string explicitly, use the `CAST()` or `CONCAT()` function (`CAST()` is preferable):

```mysql> `SELECT 38.8, CAST(38.8 AS CHAR);`
-> 38.8, '38.8'
mysql> `SELECT 38.8, CONCAT(38.8);`
-> 38.8, '38.8'```

The following rules describe how conversion occurs for comparison operations:

• If one or both arguments are `NULL`, the result of the comparison is `NULL`, except for the `NULL`-safe `<=>` equality comparison operator. For `NULL <=> NULL`, the result is true.

• If both arguments in a comparison operation are strings, they are compared as strings.

• If both arguments are integers, they are compared as integers.

• Hexadecimal values are treated as binary strings if not compared to a number.

• If one of the arguments is a `TIMESTAMP` or `DATETIME` column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to `IN()`! To be safe, always use complete datetime, date, or time strings when doing comparisons.

• In all other cases, the arguments are compared as floating-point (real) numbers.

The following examples illustrate conversion of strings to numbers for comparison operations:

```mysql> `SELECT 1 > '6x';`
-> 0
mysql> `SELECT 7 > '6x';`
-> 1
mysql> `SELECT 0 > 'x6';`
-> 0
mysql> `SELECT 0 = 'x6';`
-> 1```

Note that when you are comparing a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If `str_col` is an indexed string column, the index cannot be used when performing the lookup in the following statement:

`SELECT * FROM `tbl_name` WHERE `str_col`=1;`

The reason for this is that there are many different strings that may convert to the value `1`, such as `'1'`, `' 1'`, or `'1a'`.

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

```mysql> `SELECT '18015376320243458' = 18015376320243458;`
-> 1
mysql> `SELECT '18015376320243459' = 18015376320243459;`
-> 0```

Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:

```mysql> `SELECT '18015376320243459'+0.0;`
-> 1.8015376320243e+16```

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use `CAST()` so that a value will not be converted implicitly to a float-point number:

```mysql> `SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;`
-> 1```

### 12.2.3. Comparison Functions and Operators

NameDescription
`BETWEEN ... AND ...`Check whether a value is within a range of values
`COALESCE()`Return the first non-NULL argument
`<=>`NULL-safe equal to operator
`=`Equal operator
`>=`Greater than or equal operator
`>`Greater than operator
`GREATEST()`Return the largest argument
`IN`Check whether a value is within a set of values
`INTERVAL()`Return the index of the argument that is less than the first argument
`IS NULL`NULL value test
`IS`Test a value against a boolean
`ISNULL()`Test whether the argument is NULL
`LEAST()`Return the smallest argument
`<=`Less than or equal operator
`<`Less than operator
`LIKE`Simple pattern matching
`NOT BETWEEN ... AND ...`Check whether a value is not within a range of values
`!=`, `<>`Not equal operator
`NOT IN`Check whether a value is not within a set of values
`NOT LIKE`Negation of simple pattern matching
`SOUNDS LIKE`(v4.1.0)Compare sounds

Comparison operations result in a value of `1` (`TRUE`), `0` (`FALSE`), or `NULL`. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

Some of the functions in this section (such as `LEAST()` and `GREATEST()`) return values other than `1` (`TRUE`), `0` (`FALSE`), or `NULL`. However, the value they return is based on comparison operations performed according to the rules described in Section 12.2.2, “Type Conversion in Expression Evaluation”.

To convert a value to a specific type for comparison purposes, you can use the `CAST()` function. String values can be converted to a different character set using `CONVERT()`. See Section 12.9, “Cast Functions and Operators”.

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

• `=`

Equal:

```mysql> `SELECT 1 = 0;`
-> 0
mysql> `SELECT '0' = 0;`
-> 1
mysql> `SELECT '0.0' = 0;`
-> 1
mysql> `SELECT '0.01' = 0;`
-> 0
mysql> `SELECT '.01' = 0.01;`
-> 1```
• `<=>`

`NULL`-safe equal. This operator 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`.

```mysql> `SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;`
-> 1, 1, 0
mysql> `SELECT 1 = 1, NULL = NULL, 1 = NULL;`
-> 1, NULL, NULL```
• `<>`, `!=`

Not equal:

```mysql> `SELECT '.01' <> '0.01';`
-> 1
mysql> `SELECT .01 <> '0.01';`
-> 0
mysql> `SELECT 'zapp' <> 'zappp';`
-> 1```
• `<=`

Less than or equal:

```mysql> `SELECT 0.1 <= 2;`
-> 1```
• `<`

Less than:

```mysql> `SELECT 2 < 2;`
-> 0```
• `>=`

Greater than or equal:

```mysql> `SELECT 2 >= 2;`
-> 1```
• `>`

Greater than:

```mysql> `SELECT 2 > 2;`
-> 0```
• `IS boolean_value`, `IS NOT boolean_value`

Tests a value against a boolean value, where `boolean_value` can be `TRUE`, `FALSE`, or `UNKNOWN`.

```mysql> `SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;`
-> 1, 1, 1
mysql> `SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;`
-> 1, 1, 0```

`IS [NOT] boolean_value` syntax was added in MySQL 5.0.2.

• `IS NULL`, `IS NOT NULL`

Tests whether a value is or is not `NULL`.

```mysql> `SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;`
-> 0, 0, 1
mysql> `SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;`
-> 1, 1, 0```

To work well with ODBC programs, MySQL supports the following extra features when using `IS NULL`:

• You can find the row that contains the most recent `AUTO_INCREMENT` value by issuing a statement of the following form immediately after generating the value:

`SELECT * FROM `tbl_name` WHERE `auto_col` IS NULL`

This behavior can be disabled by setting `SQL_AUTO_IS_NULL=0`. See Section 13.5.3, “`SET` Syntax”.

• For `DATE` and `DATETIME` columns that are declared as `NOT NULL`, you can find the special date `'0000-00-00'` by using a statement like this:

`SELECT * FROM `tbl_name` WHERE `date_column` IS NULL`

This is needed to get some ODBC applications to work because ODBC does not support a `'0000-00-00'` date value.

• `expr BETWEEN min AND max`

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 in Section 12.2.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

```mysql> `SELECT 1 BETWEEN 2 AND 3;`
-> 0
mysql> `SELECT 'b' BETWEEN 'a' AND 'c';`
-> 1
mysql> `SELECT 2 BETWEEN 2 AND '3';`
-> 1
mysql> `SELECT 2 BETWEEN 2 AND 'x-3';`
-> 0```

For best results when using `BETWEEN` with date or time values, you should use `CAST()` to explicitly convert the values to the desired data type. Examples: If you compare a `DATETIME` to two `DATE` values, convert the `DATE` values to `DATETIME` values. If you use a string constant such as `'2001-1-1'` in a comparison to a `DATE`, cast the string to a `DATE`.

• `expr NOT BETWEEN min AND max`

This is the same as `NOT (expr BETWEEN min AND max)`.

• `COALESCE(value,...)`

Returns the first non-`NULL` value in the list, or `NULL` if there are no non-`NULL` values.

```mysql> `SELECT COALESCE(NULL,1);`
-> 1
mysql> `SELECT COALESCE(NULL,NULL,NULL);`
-> NULL```
• `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()`.

```mysql> `SELECT GREATEST(2,0);`
-> 2
mysql> `SELECT GREATEST(34.0,3.0,5.0,767.0);`
-> 767.0
mysql> `SELECT GREATEST('B','A','C');`
-> 'C'```

Before MySQL 5.0.13, `GREATEST()` returns `NULL` only if all arguments are `NULL`. As of 5.0.13, it returns `NULL` if any argument is `NULL`.

• `expr IN (value,...)`

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 in Section 12.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

```mysql> `SELECT 2 IN (0,3,5,7);`
-> 0
mysql> `SELECT 'wefwf' IN ('wee','wefwf','weg');`
-> 1```

You should never mix quoted and unquoted values in an `IN` list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an `IN` expression like this:

`SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');`

`SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');`

The number of values in the `IN` list is only limited by the `max_allowed_packet` value.

To comply with the SQL standard, `IN` returns `NULL` not only if the expression on the left hand side is `NULL`, but also if no match is found in the list and one of the expressions in the list is `NULL`.

`IN()` syntax can also be used to write certain types of subqueries. See Section 13.2.8.3, “Subqueries with `ANY`, `IN`, and `SOME`.

• `expr NOT IN (value,...)`

This is the same as `NOT (expr IN (value,...))`.

• `ISNULL(expr)`

If `expr` is `NULL`, `ISNULL()` returns `1`, otherwise it returns `0`.

```mysql> `SELECT ISNULL(1+1);`
-> 0
mysql> `SELECT ISNULL(1/0);`
-> 1```

`ISNULL()` can be used instead of `=` to test whether a value is `NULL`. (Comparing a value to `NULL` using `=` always yields false.)

The `ISNULL()` function shares some special behaviors with the `IS NULL` comparison operator. See the description of `IS NULL`.

• `INTERVAL(N,N1,N2,N3,...)`

Returns `0` if `N` < `N1`, `1` if `N` < `N2` and so on or `-1` if `N` is `NULL`. All arguments are treated as integers. It is required that `N1` < `N2` < `N3` < `...` < `Nn` for this function to work correctly. This is because a binary search is used (very fast).

```mysql> `SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);`
-> 3
mysql> `SELECT INTERVAL(10, 1, 10, 100, 1000);`
-> 2
mysql> `SELECT INTERVAL(22, 23, 30, 44, 200);`
-> 0```
• `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.

Before MySQL 5.0.13, `LEAST()` returns `NULL` only if all arguments are `NULL`. As of 5.0.13, it returns `NULL` if any argument is `NULL`.

```mysql> `SELECT LEAST(2,0);`
-> 0
mysql> `SELECT LEAST(34.0,3.0,5.0,767.0);`
-> 3.0
mysql> `SELECT LEAST('B','A','C');`
-> 'A'```

Note that the preceding conversion rules can produce strange results in some borderline cases:

```mysql> `SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);`
-> -9223372036854775808```

This happens because MySQL reads `9223372036854775808.0` in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

### 12.2.4. Logical Operators

In SQL, all logical operators evaluate to `TRUE`, `FALSE`, or `NULL` (`UNKNOWN`). In MySQL, these are implemented as 1 (`TRUE`), 0 (`FALSE`), and `NULL`. Most of this is common to different SQL database servers, although some servers may return any non-zero value for `TRUE`.

Note that MySQL evaluates any non-zero or non-`NULL` value to `TRUE`. For example, the following statements all assess to `TRUE`:

```mysql> `SELECT 10 IS TRUE;`
-> 1
mysql> `SELECT -10 IS TRUE;`
-> 1
mysql> `SELECT 'string' IS NOT NULL;`
-> 1```
• `NOT`, `!`

Logical NOT. Evaluates to `1` if the operand is `0`, to `0` if the operand is non-zero, and `NOT NULL` returns `NULL`.

```mysql> `SELECT NOT 10;`
-> 0
mysql> `SELECT NOT 0;`
-> 1
mysql> `SELECT NOT NULL;`
-> NULL
mysql> `SELECT ! (1+1);`
-> 0
mysql> `SELECT ! 1+1;`
-> 1```

The last example produces `1` because the expression evaluates the same way as `(!1)+1`.

Note that the precedence of the `NOT` operator changed in MySQL 5.0.2. See Section 12.2.1, “Operator Precedence”.

• `AND`, `&&`

Logical AND. Evaluates to `1` if all operands are non-zero and not `NULL`, to `0` if one or more operands are `0`, otherwise `NULL` is returned.

```mysql> `SELECT 1 && 1;`
-> 1
mysql> `SELECT 1 && 0;`
-> 0
mysql> `SELECT 1 && NULL;`
-> NULL
mysql> `SELECT 0 && NULL;`
-> 0
mysql> `SELECT NULL && 0;`
-> 0```
• `OR`, `||`

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`.

```mysql> `SELECT 1 || 1;`
-> 1
mysql> `SELECT 1 || 0;`
-> 1
mysql> `SELECT 0 || 0;`
-> 0
mysql> `SELECT 0 || NULL;`
-> NULL
mysql> `SELECT 1 || NULL;`
-> 1```
• `XOR`

Logical XOR. 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.

```mysql> `SELECT 1 XOR 1;`
-> 0
mysql> `SELECT 1 XOR 0;`
-> 1
mysql> `SELECT 1 XOR NULL;`
-> NULL
mysql> `SELECT 1 XOR 1 XOR 1;`
-> 1```

`a XOR b` is mathematically equal to `(a AND (NOT b)) OR ((NOT a) and b)`.