Quite frequently you want to submit a further select on the result of a select. There are several ways to do that, one way being to nest a select statement inside another. A nested select statement is called a subquery.
Here is an example of a subquery:
SELECT Person.name FROM Person
WHERE
Person.lives_in IN ('England', 'USA')
AND
Person.name NOT IN
( SELECT ListensTo.person_name FROM ListensTo
WHERE
ListensTo.music_group_name = 'Beatles' );
The simplest way to understand subqueries is to look at them piece by piece, starting from the innermost nested one. In this case, the nested select statement is:
( SELECT ListensTo.name FROM ListensTo
WHERE
ListensTo.music_group_name = 'Beatles' );
A moment's reading should convince you that this provides a result set of names of people who listen to the Beatles. So substitute that into the entire statement, and we get:
SELECT Person.name FROM Person
WHERE
Person.lives_in IN ('England', 'USA')
AND
Person.name NOT IN ( names-of-people-who-listen-to-Beatles ) ;
That can quickly be seen as all the people who live in England or the USA, and who do not listen to the Beatles. Be careful. Excessive use of subqueries results in SQL code that is hard to understand and hard to debug. As an alternative to subqueries you can often create, insert into, select from, and then drop temporary tables. Another alternative is to generate the queries dynamically. That is, to use one query to get the list of names, hold that in a variable, and use that variable in the second query. This will become clearer after reading the next chapter.
SELECT and all the SQL statements have even more features than are shown here, but this is enough to start writing real applications.