Managing & Using MySQL, 2nd Edition

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.

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.

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