Select Statement  «Prev 

Subquery options

SELECT id, firstname, lastname, weight,
    (SELECT avg(weight)
     FROM   person sq                -- 'sq' is an arbitrary alias name for the table in the subquery
     WHERE  sq.lastname = p.lastname -- identify the inner and outer table by its alias names
    ) family_average                 -- an arbitrary alias name for the computed family average
FROM   person p                         -- 'p' is an arbitrary alias name for the table in the surrounding query
ORDER BY lastname, weight;

The subselect obtains one row of the surrounding SELECT after the next as an parameter with the name 'p'.
Within the subselect all columns of the row 'p' are known and may be used. Here the family name from the outside row is used in the subquery to find all persons within the family and the average weight of the family members.
Subselect queries are executed once per row of the surrounding query. Therefore they are much more costly than non-correlated subqueries. There might exist an equivalent solution using JOIN or GROUP BY which works with better performance. The query optimizer of the DBMS internally might rearrange the given subquery into one of the equivalent forms. But this does not work in all cases. The distinction between correlated and non-correlated subqueries is universal. It applies also to the other subquery classes.


This is the original SQL query using a subquery statement

The sub-SELECT statement

This is the subquery statement. The Publishers table is queried first to return results from this subquery statement, which returns Pub_ID column values that have a corresponding state of "CA". In this case, the Publishers query returns only a single row, and the Pub_ID is 1389.

If you substitute the value of 1389 in the outer query, the engine is actually using this query, the engine is actually using this query.

An analogous process


An analogous process is the method of completing mathematical calculations by completing calculations in parentheses in parentheses first, working from the inside to the outside.