Select Statement  «Prev 

Use the subquery Statement

When you are building your subquery statement, a tip would be to first get the subquery running by working with it against your table. When you have it returning the results you expect and need, then you can build the outer SELECT statement around it.
This will make it easier to troubleshoot your query, and will make sure you know exactly what the results are that you are expecting.
Subselects allow you to
  1. query within a result set,
  2. creatively limit your result set,
  3. or correlate your results with an otherwise unrelated query in a single call to the database.

subquery in SQL

A subquery in SQL is a table expression, tx say, enclosed in parentheses; if the table denoted by tx is t, the table denoted by the subquery is t also. The expression tx cannot be an explicit JOIN expression.
Thus, for example,
( A NATURAL JOIN B )

is not a legal subquery.
By contrast, the following expression is a legal subquery:
SELECT * FROM A NATURAL JOIN B

Subqueries fall into three categories (though the syntax is the same in every case). The details, partly repeated from earlier chapters, are as follows:
  1. A table subquery is a subquery that is neither a row subquery nor a scalar subquery.
  2. A row subquery is a subquery appearing in a position where a row expression is expected. Let rsq be such a subquery; then rsq must denote a table with just one row. Let the table in question be t, and let the single row in t be r; then rsq behaves as if it denoted that row r (in other words, t is coerced to r).
    Note: If rsq does not denote a table with just one row, then
    1. if it denotes a table with n rows (n > 1), an error is raised;
    2. if it denotes a table with no rows at all, then that table is treated as if it contained just one row, where the row in question contains a null in every column position.
  3. A scalar subquery is a subquery appearing in a position where a scalar expression is expected. Let ssq be such a subquery; then ssq must denote a table with just one row and just one column. Let the table in question be t, let the single row in t be r, and let the single value in r be v; then ssq behaves as if it denoted that value v (in other words, t is coerced to r, and then r is coerced to v). Note: If ssq does not denote a table with just one row and just one column, then (a) if it denotes a table with m columns (m > 1), an error is raised probably at compile time); (b) if it denotes a table with one column and n rows (n > 1), an error is raised (probably at run time); (c) if it denotes a table with one column and no rows at all, then that table is treated as if it contained just one row, where the row in question contains a single null.