Section author: Adam Scott <adam.ascott@noirlab.edu>

1.13.3. Sub Queries

If you see two or more SELECT commands in a query, it has a subquery in it. Some call this a nested query.

SELECT ra, dec from smash_dr1.exposure e
WHERE e.expnum = ( SELECT expnum from smash_dr1.object o
                   INNER JOIN smash_dr1.source s
                   ON o.id = s.id
                   WHERE s.chi > 17.0 );

We take the results of one query, the inner query, and feed them into the outer query.

Query description

The inner SELECT returns all exposure numbers (expnum) from the smash_dr1.object table whose matching row in the smash_dr1.source table has a DAOPHOT chi value greater than 17.0.

The outer SELECT retrieves the ra dec in the smash_dr1.exposure table for all of the expnum values in the inner SELECT.

Beside the = operator, >, <, IN, ANY, and EXISTS also are supported.

It is possible to change this query to join smash_dr1.exposure to smash_dr1.object instead of using a subquery. Either way should perform the same if the database optimizer is working correctly.

Many joins can be rewritten as subqueries, and many, not all, subqueries can be rewritten as joins.

Here is the subquery written as a join.

SELECT ra, dec FROM smash_dr1.exposure e
INNER JOIN smash_dr1.object o
        ON e.expnum = o.expnum
INNER JOIN smash_dr1.source s
        ON o.id = s.id
     WHERE s.chi > 17.0;

Here is a subquery that cannot be re-written as a join. (Note the MAX() aggregate function).

SELECT expnum from smash_dr1.source s
 WHERE s.chi = ( SELECT MAX(chi)
                   FROM smash_dr1.source s2 );