3.8.1. Logical Operators

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.

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

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;

SELECT TITLE FROM BOOK WHERE AUTHOR IS 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

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

SELECT TITLE FROM BOOK WHERE BOOK_ID BETWEEN 1  AND 100;

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%'

Table 3-7. MySQL extended regular expressions

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)