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,

(NATURAL JOIN`A`

)`B`

isn’t a legal subquery.^{[169]} By contrast, the following expression *is* a legal subquery:

( SELECT * FROMNATURAL JOIN`A`

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

Start Free Trial

No credit card required