MySQL operators

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

Operators combine values into a new value. You have already used one: the * in rental_rate * 1.1. This lesson covers the everyday set, and the one behavior that catches everybody, which is what happens when NULL turns up. Open the client with mysql -u root -p sakila before you start.

How an operator works

SELECT LEFT_VALUE OPERATOR RIGHT_VALUE;

Replace LEFT_VALUE and RIGHT_VALUE with values and OPERATOR with one of the symbols below. A value can be a column, or it can be a literal, meaning a number or a piece of text written directly into the statement. A SELECT with no FROM evaluates its expressions and returns a single row, which makes it the quickest way to try an operator out.

Examples

1) Comparison returns 1 or 0

MySQL has no distinct boolean type. A comparison that holds returns the integer 1, and one that does not returns 0. The words TRUE and FALSE are spellings of those 2 integers.

SELECT 1 = 1, 1 <> 2, 1 != 2, 2 > 3, 3 >= 3, 2 <= 2;

+-------+--------+--------+-------+--------+--------+
| 1 = 1 | 1 <> 2 | 1 != 2 | 2 > 3 | 3 >= 3 | 2 <= 2 |
+-------+--------+--------+-------+--------+--------+
|     1 |      1 |      1 |     0 |      1 |      1 |
+-------+--------+--------+-------+--------+--------+
1 row in set (0.00 sec)

Equality is a single =, not the double == that most programming languages use. <> and != both mean not equal and behave identically. This tutorial writes <>.

2) Arithmetic, and which operator binds tighter

SELECT 2 + 3 * 4, (2 + 3) * 4, 7 / 2, 7 DIV 2, 7 % 2;

+-----------+-------------+--------+---------+-------+
| 2 + 3 * 4 | (2 + 3) * 4 | 7 / 2  | 7 DIV 2 | 7 % 2 |
+-----------+-------------+--------+---------+-------+
|        14 |          20 | 3.5000 |       3 |     1 |
+-----------+-------------+--------+---------+-------+
1 row in set (0.00 sec)

2 + 3 * 4 is 14, so multiplication happened first. Brackets override that and make it 20. / gives a decimal result even between 2 whole numbers, DIV throws the fraction away, and % gives the remainder.

3) DIV and % bind as tightly as division

SELECT 10 - 4 / 2, 10 - 4 DIV 2, 1 + 7 % 3;

+------------+--------------+-----------+
| 10 - 4 / 2 | 10 - 4 DIV 2 | 1 + 7 % 3 |
+------------+--------------+-----------+
|     8.0000 |            8 |         2 |
+------------+--------------+-----------+
1 row in set (0.00 sec)

All 3 give the answer you get by doing the division or the remainder first. If - bound tighter than /, the first column would be 3 rather than 8.

4) Operators work on columns

Anything you can do to a literal you can do to a column.

SELECT title, length, length > 100 AS is_long FROM film ORDER BY title;

+-----------------------------+--------+---------+
| title                       | length | is_long |
+-----------------------------+--------+---------+
| ACADEMY DINOSAUR            |     86 |       0 |
| ACE GOLDFINGER              |     48 |       0 |
| ADAPTATION HOLES            |     50 |       0 |
| AFFAIR PREJUDICE            |    117 |       1 |
| AFRICAN EGG                 |    130 |       1 |
...
+-----------------------------+--------+---------+
1000 rows in set (0.00 sec)

The comparison becomes a column of 1s and 0s, and the alias gives it a name.

NULL is not equal to anything, including NULL

You met NULL in the sorting lesson as the marker for a value that was never recorded. It behaves unlike any real value here.

SELECT NULL = NULL, NULL <> NULL, NULL <=> NULL, 1 <=> NULL;

+-------------+--------------+---------------+------------+
| NULL = NULL | NULL <> NULL | NULL <=> NULL | 1 <=> NULL |
+-------------+--------------+---------------+------------+
|        NULL |         NULL |             1 |          0 |
+-------------+--------------+---------------+------------+
1 row in set (0.00 sec)

Asking whether one unrecorded value equals another cannot be answered, so NULL = NULL returns NULL rather than 1 or 0. So does NULL <> NULL. Every operator in example 1 behaves that way against NULL. <=> is the exception. It compares 2 values and always returns 1 or 0, never NULL, treating NULL as equal to itself. The consequence is that = NULL matches nothing, ever. When you need to find the rows where a value is absent, IS NULL is the test, and it returns 1 or 0 like an ordinary comparison.

Summary

Expect 1 or 0 back from a comparison, not true or false.
Use = for equality and <> for inequality.
Remember that *, /, DIV and % bind tighter than + and -.
Use DIV for whole number division and % for the remainder.
Expect NULL from any ordinary comparison involving NULL, and use <=> or IS NULL instead.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How an operator works

Examples

1) Comparison returns 1 or 0

2) Arithmetic, and which operator binds tighter

3) DIV and % bind as tightly as division

4) Operators work on columns

NULL is not equal to anything, including NULL

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How an operator works

​Examples

​1) Comparison returns 1 or 0

​2) Arithmetic, and which operator binds tighter

​3) DIV and % bind as tightly as division

​4) Operators work on columns

​NULL is not equal to anything, including NULL

​Summary

​See also

How an operator works

Examples

1) Comparison returns 1 or 0

2) Arithmetic, and which operator binds tighter

3) DIV and % bind as tightly as division

4) Operators work on columns

NULL is not equal to anything, including NULL

Summary

See also