The Basics Edit
A sub query is exactly what it sounds like, a query within a query. Of course, this is not always the case because it could be a query within a query within a query, or even a query within a query within a query within a query. Thats the beauty of it, a sub query can reside within a sub query, that itself can reside inside another sub query. To infinity and beyond.
A sub query is as the name implies. It is just a query that lives inside a query. By query of course we mean a SELECT statement.
Sub queries can generally be broken into three broad categories
1. Single value or row
2. Multiple rows
3. Multiple columns
The names are fairly accurate descriptors. Single value or single row is used to send a single value or row, whereas a multiple row or multiple column sends a list of rows or columns. Each of these three categories has a distinct use in SQL, but it is beyond the scope of this article.
Simplifying Work Edit
One of the biggest benefits to sub queries is that they allow a complex problem to be broken up into multiple less complex parts. Instead of trying to do everything in one massive select statement the author can instead break each part of the select statement into a sub query. This not only eases the conceptual design of a statement, but lessens the work any one computer has to do.
Instead of sending whole tables, the database only sends a series of results. When tables reach row lists of thousands this becomes a very big deal.
Select fligth_no, orig, dest, fare
Where Orig = ‘PHX’ and Dest = ‘LAX’ and fare < (Select avg(fare) from flight
Where Orig = ‘PHX’ and Dest = ‘LAX’)
Saves Network Bandwidth Edit
With a single SQL statement all the work is done by one machine, and every machine sends whole tables across the network at a time. The tables are received and the work is done on them. With sub queries the work is done before the table information is sent, so instead of receiving a table with thousands of rows, then having to narrow down those rows into something usable, one can narrow down the table to something usable and then send only those rows and columns selected.
Of course these sub queries can just as easily go to another sub query. The deeper the nesting (the more sub queries the sub query resides in) the more efficient this becomes.
Creating Lists Edit
Another huge advantage to a sub query is that it can send a list that another query or object can use. One great example is in a constraint, where a constraint must be in a list of objects. If this list of objects is dynamic it becomes impossible to do this with just a query, however with a sub query it becomes a walk in the park.
Add Constraint a_constraint Check (english in('big', 'red', 'light', 'heavy', 'thin'))
Add Constraint a_constraint Check (english in(select adverbs from list))