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

Subqueries Introduced with IN

Subqueries introduced with the keyword IN take the following general form:

						Start of SELECT, INSERT, UPDATE, DELETE statement; or
						subquery
WHERE expression [NOT] IN
     (subquery)
[End of SELECT, INSERT, UPDATE, DELETE statement; or
						subquery]

The result of the inner subquery is a list of zero or more values. Once the subquery returns results, the outer query makes use of them.

Here's an example of a statement that you could formulate either with a subquery or with a join. The English version of the query is “Find the names of all second authors who live in California and receive less than 30 percent of the royalties on the books they coauthor.” Using a subquery, the statement is

SQL

 select au_lname, au_fname from authors ...