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), (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 NULL s 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: trueanything = true NULLfalse = NULL NULLNULL = NULL falsefalse = 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