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.
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.