3.8 SQL Operators

So far, we have used the = operator for the obvious task of verifying that two values in a WHERE clause equal one another. Other fairly basic operations include <>, >, <, <=, and >=. Note that MySQL allows you to use either <> or != for "not equal." Table 3-6 contains a full set of simple SQL operators.

Table 3-6. The simple SQL operators supported by MySQL

Operator	Context	Description
+	Arithmetic	Addition
-	Arithmetic	Subtraction
*	Arithmetic	Multiplication
/	Arithmetic	Division
=	Comparison	Equal
<> or !=	Comparison	Not equal
<	Comparison	Less than
>	Comparison	Greater than
<=	Comparison	Less than or equal to
>=	Comparison	Greater than or equal to
AND	Logical	And
OR	Logical	Or
NOT	Logical	Negation

MySQL operators have the following order of precedence:

1. BINARY

2. NOT !

3. - (unary minus)

4. * / %

5. + -

6. << >>

7. &

8. |

9. < <= > >= = <=> <> IN IS LIKE REGEXP RLIKE

10. BETWEEN

11. AND &&

12. OR ||

3.8.1 Logical Operators

SQL's logical operators—AND, OR, and NOT—let you build more dynamic WHERE clauses. The AND and OR operators specifically let you add multiple criteria to a query:

SELECT USER_NAME
FROM USER
WHERE AGE > 18 AND STATUS = 'RESIDENT';

This sample query provides a list of all users who are residents and are old enough to vote. In other words, it finds every resident 18 years or older.

You can build increasingly complex queries and override MySQL's order of precedence with parentheses. The parentheses tell MySQL which comparisons to evaluate first:

SELECT USER_NAME 
FROM USER
WHERE (AGE > 18 AND STATUS = 'RESIDENT')
OR (AGE > 18 AND STATUS = 'APPLICANT');

In this more complex query, we are looking for anyone currently eligible to vote as well as people who might be eligible in the near future. Finally, you can use the NOT operator to negate an entire expression:

SELECT USER_NAME
FROM USER
WHERE NOT (AGE > 18 AND STATUS = 'RESIDENT');

In this case, negation provides all the users who are not eligible to vote.

3.8.2 Null's Idiosyncrasies

Null is a tricky concept for most people new to databases to understand. As in other programming languages, null is not a value, but the absence of a value. This concept is useful, for example, if you have a customer profiling database that gradually gathers information about your customers as they offer it. When you first create a record, for example, you may not know how many pets the customer has. You want that column to hold NULL instead of 0 so you can tell the difference between customers with no pets and customers whose pet ownership is unknown.

The concept of null gets a little funny when you use it in SQL calculations. Many programming languages use null as simply another kind of value. In Java, the following syntax evaluates to true when the variable is null and false when it is not:

str == null 

The similar expression in SQL, COL = NULL, is neither true nor false—it is always NULL, no matter what the value of the COL column. The following query will therefore not act as you might expect:

SELECT title FROM book WHERE author = NULL;

Because the WHERE clause will never evaluate to true no matter what value is in the database for the author column, this query always provides an empty result set—even when you have author columns with NULL values. To test for "nullness," use the IS NULL and IS NOT NULL operators:

SELECT TITLE FROM BOOK WHERE AUTHOR IS NULL;

MySQL also provides a special operator called the null-safe operator <=>, which you can use when you are not sure if you are dealing with null values. It returns true if both sides are null or false if both sides are not null:

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

This simple query shows how the null-safe operator works with a variety of inputs.

3.8.3 Membership Tests

Sometimes applications need to check if a value is a member of a set of values or within a particular range. The IN operator helps with the former:

SELECT TITLE FROM BOOK WHERE AUTHOR IN ('Stephen King', 'Richard Bachman');

This query will return the titles of all books written by Stephen King.^[5] Similarly, you can check for all books by authors other than Stephen King with the NOT IN operator.

^[5] Richard Bachman is a pseudonym used by Stephen King for some of his books.

To determine if a value is in a particular range, use the BETWEEN operator:

SELECT TITLE FROM BOOK WHERE BOOK_ID BETWEEN 1  AND 100;

Both of these simple examples could, of course, be replicated with the basic operators. The Stephen King check, for example, could have been done by using the = operator and an OR:

SELECT title 
FROM book
WHERE author = 'Stephen King' OR author = 'Richard Bachman';

The check on book IDs could also have been done with an OR clause using the >= and <= or > and < operators. As your queries get more complex, however, membership tests can help you build both readable and better-performing queries than those you might create with the basic operators.

3.8.4 Pattern Matching

We provided a peek at ANSI SQL pattern matching earlier with the query:

SELECT name FROM people WHERE name LIKE 'Stac%'

Using the LIKE operator, we compared a column value (name) to an incomplete literal ('Stac%'). MySQL supports the ability to place special characters into string literals that match like wild cards. The % character, for example, matches any arbitrary number of characters, including no character at all. The above SELECT statement would therefore match Stacey, Stacie, Stacy, and even Stac. The character _ matches any single character. Stac_y would match only Stacey. Stac__ would match Stacie and Stacey, but not Stacy or Stac.

Pattern-matching expressions should never be used with the basic comparison operators. Instead, they require the LIKE and NOT LIKE operators. It is also important to remember that these comparisons are case-insensitive except on binary columns.

MySQL supports a non-ANSI kind of pattern matching that is actually much more powerful using the same kind of expressions to which Perl programmers and grep users are accustomed. MySQL refers to these as extended regular expressions. Instead of LIKE and NOT LIKE, these operators must be used with the REGEXP and NOT REGEXP operators. MySQL provides synonyms for these: RLIKE and NOT RLIKE. Table 3-7 contains a list of the supported extended regular expression patterns.

Table 3-7. MySQL extended regular expressions

Pattern	Description	Examples
.	Matches any single character.	Stac.. matches any value containing the characters "Stac" followed by two characters of any value.
[]	Matches any character in the brackets. You can also match a range of characters.	[Ss]tacey matches values containing both "Stacey" and "stacey." [a-zA-Z] matches values containing one instance of any character in the English (unaccented) portion of the Roman alphabet.
*	Matches zero or more instances of the character that precedes it.	Ap*le matches values containing "Aple," "Apple," "Appple," etc. Los .*es matches values containing the strings "Los " and "es" with anything in between. [0-9]* matches values containing any arbitrary number.
^	What follows must come at the beginning of the value.	^Stacey matches values that start with "Stacey."
$	What precedes it must end the value.	cheese$ matches any value ending in the string "cheese."

You should note a couple of important facts about extended regular expressions. Unlike basic pattern matching, MySQL extended regular expressions are case sensitive. They also do not require a match for the entire string. The pattern simply needs to occur somewhere within the value. Consider the following example:

mysql> SELECT * FROM BOOK;
+---------+-----------------------------------------+---------------+
| BOOK_ID | TITLE                                   | AUTHOR        |
+---------+-----------------------------------------+---------------+
|       1 | Database Programming with JDBC and Java | George Reese  |
|       2 | JavaServer Pages                        | Hans Bergsten |
|       3 | Java Distributed Computing              | Jim Farley    |
+---------+-----------------------------------------+---------------+
3 rows in set (0.01 sec)

In this table, we have three books from O'Reilly's Java series. The interesting thing about the Java series is that all books begin with or end with the word "Java." The first sample query checks for any titles LIKE 'Java':

mysql> SELECT TITLE FROM BOOK WHERE TITLE LIKE 'Java'; 
Empty set (0.01 sec)

Because LIKE looks for an exact match of the pattern specified, no rows match—none of the titles are exactly 'Java'. To find out which books start with the word Java using simple patterns, we need to add a %:

mysql> SELECT TITLE FROM BOOK WHERE TITLE LIKE 'Java%';
+----------------------------+
| TITLE                      |
+----------------------------+
| JavaServer Pages           |
| Java Distributed Computing |
+----------------------------+
2 rows in set (0.00 sec)

This query had two matches because only two of the books had titles that matched Java% exactly. The extended regular expression matches, however, are not exact matches. They simply look for the expression anywhere within the compared value:

mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP 'Java';
+-----------------------------------------+
| TITLE                                   |
+-----------------------------------------+
| Database Programming with JDBC and Java |
| JavaServer Pages                        |
| Java Distributed Computing              |
+-----------------------------------------+
3 rows in set (0.06 sec)

By simply changing the operator from LIKE to REGEXP, we changed how it matches things. Java appears somewhere in each of the titles, so the query returns all the titles. To find only the titles that start with the word Java using extended regular expressions, we need to specify that we are interested in the start:

mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP '^Java';
+----------------------------+
| TITLE                      |
+----------------------------+
| JavaServer Pages           |
| Java Distributed Computing |
+----------------------------+
2 rows in set (0.01 sec)

The same thing applies to finding titles with Java at the end:

mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP 'Java$';
+-----------------------------------------+
| TITLE                                   |
+-----------------------------------------+
| Database Programming with JDBC and Java |
+-----------------------------------------+
1 row in set (0.00 sec)

The extended regular expression syntax is definitely much more complex than the simple pattern matching of ANSI SQL. In addition to the burden of extra complexity, you should also consider the fact that MySQL extended regular expressions do not work in most other databases. When you need complex pattern matching, however, they provide you with power that is simply unsupportable by simple pattern matching.