1.11.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.

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 );