MySQL Operators and Expressions: Arithmetic and Logic

An expression is anything MySQL can calculate to one value: 2 + 3, price * 0.18, name = 'Asha'. Operators are the symbols that combine values.

Arithmetic operators

query.sql

SQL

SELECT 7 + 2 AS plus, 7 - 2 AS minus, 7 * 2 AS times, 7 / 2 AS divide, 7 DIV 2 AS integer_divide, 7 % 2 AS remainder;

Output

OUTPUT

+------+-------+-------+--------+----------------+-----------+
| plus | minus | times | divide | integer_divide | remainder |
+------+-------+-------+--------+----------------+-----------+
|    9 |     5 |    14 | 3.5000 |              3 |         1 |
+------+-------+-------+--------+----------------+-----------+
1 row in set

/ always gives a decimal result in MySQL (3.5 here).
DIV throws away the fraction (3). It is MySQL-specific.
% (or MOD) gives the remainder.

Comparison operators

They give 1 (true), 0 (false) or NULL (unknown):

query.sql

SQL

SELECT 5 = 5 AS equal, 5 <> 6 AS not_equal, 5 != 6 AS also_not_equal, 5 > 3 AS greater, 5 <= 4 AS less_equal, 5 <=> 5 AS null_safe_equal, NULL <=> NULL AS null_safe_null;

Output

OUTPUT

+-------+-----------+----------------+---------+------------+-----------------+----------------+
| equal | not_equal | also_not_equal | greater | less_equal | null_safe_equal | null_safe_null |
+-------+-----------+----------------+---------+------------+-----------------+----------------+
|     1 |         1 |              1 |       1 |          0 |               1 |              1 |
+-------+-----------+----------------+---------+------------+-----------------+----------------+
1 row in set

<> is the standard “not equal”. != also works. <=> is MySQL’s NULL-safe equal: it treats two NULLs as equal and never returns NULL.

Logical operators

AND, OR and NOT combine conditions.

query.sql

SQL

SELECT (1 = 1 AND 2 = 3) AS and_result, (1 = 1 OR 2 = 3) AS or_result, NOT (1 = 2) AS not_result;

Output

OUTPUT

+------------+-----------+------------+
| and_result | or_result | not_result |
+------------+-----------+------------+
|          0 |         1 |          1 |
+------------+-----------+------------+
1 row in set

AND is true only when both sides are true. OR is true when at least one is. XOR is true when exactly one is.

Three more you will use a lot

query.sql

SQL

SELECT 5 BETWEEN 1 AND 10 AS between_check, 'b' IN ('a', 'b', 'c') AS in_check, 'Pune' LIKE 'Pu%' AS like_check;

Output

OUTPUT

+---------------+----------+------------+
| between_check | in_check | like_check |
+---------------+----------+------------+
|             1 |        1 |          1 |
+---------------+----------+------------+
1 row in set

Each of these gets its own lesson in the WHERE chapter.

Operator precedence

When an expression mixes operators, MySQL applies them in a fixed order, highest first (simplified):

Parentheses ( )
Unary minus, NOT
*, /, DIV, %
+, -
Comparisons (=, <, >, LIKE, IN, BETWEEN, IS)
AND
OR

query.sql

SQL

SELECT 1 = 1 OR 1 = 2 AND 1 = 2 AS without_brackets, (1 = 1 OR 1 = 2) AND 1 = 2 AS with_brackets;

Output

OUTPUT

+------------------+---------------+
| without_brackets | with_brackets |
+------------------+---------------+
|                1 |             0 |
+------------------+---------------+
1 row in set

AND binds tighter than OR. The first expression is read as 1 = 1 OR (1 = 2 AND 1 = 2), which is true. When in doubt, add parentheses. They cost nothing and make your meaning clear.

Operators on text

+ does not join text in MySQL. It converts the text to numbers:

query.sql

SQL

SELECT '5' + '3' AS numeric_addition, CONCAT('5', '3') AS text_join;

Output

OUTPUT

+------------------+-----------+
| numeric_addition | text_join |
+------------------+-----------+
|                8 | 53        |
+------------------+-----------+
1 row in set

The + in other databases (SQL Server, for example) may join text. In MySQL, use CONCAT(). See String Functions.

warning

The OR trap

WHERE city = 'Pune' OR city = 'Delhi' AND price > 100 does not mean what many people think. AND is applied first. Write WHERE (city = 'Pune' OR city = 'Delhi') AND price > 100 to filter both cities by price.