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:
SQLselect 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
select au_lname, state
from authors
where state in ('CA', 'IN', 'MD')
Following is what results from either query:
Resultsau_lname state ======================================== ===== Bennet CA Green CA Carson CA DeFrance IN Panteley MD McBadden CA Stringer CA Straight CA Karsen CA MacFeather CA Dull CA Yokomoto CA O'Leary CA Gringlesby CA White CA Hunter CA Locksley CA [17 rows]
The more items in the list, the greater the savings in typing by using IN rather than specifying each condition separately.
An important use for the IN keyword is in nested queries, also referred to as subqueries. For a full discussion of subqueries, see Chapter 8.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.