Operators

There are three main sets of operators in MySQL: arithmetic, comparison, and logical. We briefly talked about these operators back in Chapter 6, "Querying MySQL." Let's look at them in more detail now.

Arithmetic Operators
MySQL has the arithmetic operators you would expect: addition (+), subtraction (-), multiplication (*), and division (/). Division by zero produces a safe NULL result.

Comparison Operators
The main trick to remember with comparison operators is that, with the exception of a few special cases, comparing anything to NULL gives a NULL result. This includes comparing NULL to NULL:

select NULL=NULL;

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

Compare this to the following query:

select NULL IS NULL;

+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

This reinforces the point that we made in Chapter 6, that you must be careful when using comparison operators when a NULL may be involved.

A second point to remember is that string comparisons in MySQL are case insensitive for the most part. If you want strings to be compared in a case-sensitive fashion, prefix one of them with the keyword BINARY. For example,

select * from department where name='marketing';

This query will match the word 'marketing' regardless of case, and we will get the following results:

+---------------+-----------+
| departmentID | name |
+---------------+-----------+
| 130 | Marketing |
+---------------+-----------+
1 row in set (0.41 sec)

If case is important, we can add the keyword binary as follows:

select * from department where name = binary 'marketing';

This will give us no matching rows in the employee database:

Empty set (0.18 sec)

Having said all that, let's look at the comparison operators. The most commonly used ones are shown in Table 8.1.

Table 8.1. Comparison Operators Operator
Meaning

=
Equality

!= or <>
Inequality

<
Less than

<=
Less than or equal to

>
Greater than

>=
Greater than or equal to

n BETWEEN min AND max
Range testing

n IN (set)
Set membership. Can be used with a list of literal values or expressions or with a subquery as the set. An example of a set is (apple, orange, pear)

<=>
NULL safe equal. This will return 1 (true) if we compare two NULL values

n IS NULL
Use to test for a NULL value in n

ISNULL(n)
Use to test for a NULL value in n

Logical Operators
MySQL supports all the usual logical operators that can be used to join expressions. Logical expressions in MySQL can evaluate to 1 (true), 0 (false), or NULL. In addition, MySQL interprets any nonzero, non-null value as true.

Some of the truth tables are a little different from what you might expect when NULLs are involved. The logical operators are shown in Table 8.2.

Table 8.2. Logical Operators Operator
Example
Meaning

AND or &&
n && m
Logical AND. Here is the truth table:

true&&true = true
false&&anything = false

All other expressions evaluate to NULL.

OR or ||
n || m
Logical OR. Here is the truth table:

true||anything = true
NULL||false = NULL
NULL||NULL = NULL
false||false = false

NOT or !
NOT n
Logical NOT. Here is the truth table:

!true = false
!false = true
!NULL = NULL

XOR
n XOR m
Logical exclusive OR. Here is the truth table:

true XOR true = false
true XOR false = true
false XOR true = true
NULL XOR n = NULL
n XOR NULL = NULL