Subqueries
I like to think of subqueries such that there are two methods of categorizing
subqueries. The first method of categorization is that a subquery can be scalar,
correlated, nested, or an inline view. We will get to some of these types of subqueries later on. I am going to define subquery types based on the second method of categorization, including some of the first. I find the second method clearer. Following is my second method of categorization. Note that the different types of subqueries as defined here can be combinations of several types. At the end of this chapter you should understand why. A subquery is an SQL statement called from another query or another subquery. Subqueries can return various result sets and can be defined based on what they return.
Types of Subqueries
Single Row / Single Column
. This type of subquery can be used to find a single value (e.g., the ARTIST_ID of a particular song).
Multiple Rows / One Column Each
. This type of subquery returns a list of values (e.g., the SONG_IDs of all songs by a specific artist).
Multiple Columns / Single or Multiple Rows
. This is the most complex variation. For example, a subquery with multiple columns as a single row could return the STATE and COUNTRY of one artist from the ARTIST table. An example of a subquery with multiple columns and multiple rows could return a list of the STATE and COUNTRY for all artists.