Database Reference
In-Depth Information
Subqueries
A subquery is aquery within another query, a
SELECT
statement within another SQL
statement. A subquery returns a single value, a row of data, a single column from several
rows, or several columns from several rows. These are known respectively as scalar,
column, row, and table subqueries. I'll refer to these distinctions later in this chapter.
Although the same results can be accomplished by using the
JOIN
clause and sometimes
the
UNION
, depending on the situation, subqueries are a cleaner approach. They make a
complex query more modular, which makes it easier to create and to troubleshoot prob-
lems. Here are two generic examples of subqueries (we also used a few subqueries in
UPDATE
table_1
SET
col_5
= 1
WHERE
col_id
=
SELECT
col_id
FROM
table_2
WHERE
col_1 = value
;
SELECT
column_a
,
column_1
FROM
table_1
JOIN
(
SELECT
column_1
,
column_2
FROM
table_2
WHERE
column_2 = value
) AS
derived_table
USING(
col_id
);
In the first example, the
SELECT
statementis an inner query. The
UPDATE
statement is re-
ferred to as the main or outer query. In the second example, the
SELECT
within paren-
theses is the inner query and the
SELECT
outside of the parentheses is the outer query. An
outer query containing a subquery can be a
SELECT
,
INSERT
,
UPDATE
,
DELETE
,
DO
, or
even a
SET
statement. There are some limitations, though. An outer query cannot generally
select data or modify datafrom the same table of an inner query. This doesn't apply though
if the subquery is part of a
FROM
clause.
These generic examples may be confusing. Generic examples aren't usually easy to follow.
I'd rather present first the syntax for subqueries, but there is no syntax per se for the use of
subqueries — other than the syntax inherent in the SQL statements used for the inner and
outer queries. Subqueries are rather a method of constructing combinations ofSQL state-
ments. As such, you need only to make sure of two basic factors with subqueries.
