Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Lists (IN and NOT IN)

The IN keyword allows you to select values that match any one of a list of values. For example, without IN, if you want a list of the names and states of all the authors who live in California, Indiana, or Maryland, you can type this query:

SQL

select au_lname, state
from authors
where state = 'CA' or state = 'IN' or state = 'MD'
					

However, you get the same results with less typing if you use IN. The items following the IN keyword must be

• inside parentheses

• separated by commas

• enclosed in quotes, if they are character or date values

SQL

select au_lname, state
from authors
where state in ('CA', 'IN', 'MD')

Following is what results from either query:

Results

 au_lname state ======================================== ===== Bennet ...