15. Subqueries

One of the more powerful features of SQL is that you can have subqueries instead of tables within FROM. Just put them in parentheses and give them a name using AS. This is particularly convenient when you first want to try some query on a subset of a big table:

SELECT count(*) as n, round((u-z)*2) as bin
  SELECT TOP 4000 * FROM sdssdr7.sources) AS q

Another use of subqueries is in the connection with EXISTS, which is an operator on queries that’s true when a query result is not empty.

Beware – people coming from other languages have a tendency to use EXISTS when they should be using JOIN (which typically is easier to optimize for the database engine). On the other hand, EXISTS frequently is the simpler and more robust solution.

As an example, to get arihip stars that happen to be in RAVE DR3, you could write both

FROM arihip.main as a
    SELECT 1
    FROM rave.dr3 as r
      POINT(r.raj2000, r.dej2000),
      POINT(a.raj2000, a.dej2000)) < 1/3600)


FROM arihip.main AS a
JOIN rave.dr3 AS r
      POINT(a.raj2000, a.dej2000),
      POINT(r.raj2000, r.dej2000)) < 1/3600)

(but see the exercise to this problem before making a pattern out of this).



Sit back for a minute and think whether the JOIN and the EXIST solution are actually equivalent (Hint: They’re not). Think of how you’d need to change the queries to show that non-equivalence.

Hendrik Heinl

Copyright Notice