14. 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
FROM (
  SELECT TOP 4000 * FROM sdssdr7.sources) AS q
GROUP BY bin ORDER BY bin

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

SELECT TOP 10 *
FROM arihip.main as a
WHERE
  EXISTS (
    SELECT 1
    FROM rave.dr3 as r
    WHERE 1=CONTAINS(
      POINT('ICRS', a.raj2000, a.dej2000),
      CIRCLE('ICRS', r.raj2000, r.dej2000, 1/3600.)))

or

SELECT TOP 10 a.*
FROM arihip.main as a
JOIN rave.dr3 as r
ON 1=CONTAINS(
      POINT('ICRS', a.raj2000, a.dej2000),
      CIRCLE('ICRS', r.raj2000, r.dej2000, 1/3600.))

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

Problems

(1)

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.


Markus Demleitner

Copyright Notice