.. sectionauthor:: Adam Scott .. index:: query, sub-queries, join 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. .. sidebar:: 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 );