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.