SQL and Relational Theory, 2nd Edition

SUBQUERIES

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. Note, however, that (as mentioned in Chapter 1 and Chapter 6) the expression tx can’t be an explicit JOIN expression. Thus, for example,

     ( A NATURAL JOIN B )

isn’t a legal subquery.^[169] 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:

• A table subquery is a subquery that’s neither a row subquery nor a scalar subquery.

• 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 doesn’t denote a table with just one row, then (a) if it denotes a table with n rows (n > 1), an error is raised; (b) 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.

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