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((hmag-jmag)*2) as bin
FROM (
  SELECT TOP 4000 * FROM twomass) 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.


Markus Demleitner

Copyright Notice