Database Reference
In-Depth Information
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 col-
umns and multiple rows could return a list of the STATE and
COUNTRY for all artists.
Note:
A single-row query or subquery returning a single value can also be
referred to as a scalar query or subquery, scalar meaning returning a single
scalar or literal value, often TRUE, FALSE, or NULL.
A subquery can be of a regular or correlated format, correlated implying
a connection between calling and called queries (subqueries):
Regular Subquery
. A self-contained query implying that there is no
direct relationship between the calling query and the called query.
Correlated Subquery
is used to describe a rela-
tionship between the calling query and the subquery. The rule to
remember with correlated subqueries is that a correlating column
must be passed down into the subquery from the calling query, not
the other way around. Thus a correlated subquery is always depen-
dent on the calling query.
. The word
correlation
Subqueries can also be defined as nested or as inline views:
Nested Subquery
. Subqueries can call other subqueries and so on ad
infinitum. In other words, subqueries can be nested within subque-
ries, within subqueries.
Inline View
. An inline view is a subquery embedded in the FROM
clause of a calling SELECT statement, which by the way is also a sub-
query. Values can be passed from the inline view to the calling query,
or subquery.
Search WWH ::




Custom Search