O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required