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