SQL Pocket Guide, 2nd Edition

Subqueries

Subject to various platform restrictions, subqueries may be used in most SQL statements as follows:

In the SELECT list of a SELECT statement

See the earlier subsection "The SELECT Clause.”

In the FROM clause of a SELECT statement

See the earlier subsection "The FROM Clause.”

In the WHERE clause of a SELECT statement

See the earlier section "Predicates" and the earlier subsection "The WHERE Clause.”

In the ORDER BY clause of a SELECT statement

See the earlier subsection "The ORDER BY Clause.”

In an INSERT . . . SELECT . . . FROM statement

See the earlier subsection "Subquery Inserts.”

In the SET clause of an UPDATE statement

See the later subsection "New Values from a Subquery.”

A subquery in the FROM clause of a SELECT statement functions like a view and replaces a table as a data source. You can use subqueries—just as you can use views—as targets of INSERT, DELETE, and UPDATE statements. For example, for all platforms except MySQL, you can specify:

DELETE
FROM (SELECT * FROM upfall u
      WHERE u.open_to_public = 'n') u2
WHERE u2.owner_id IS NOT NULL;

This statement deletes waterfalls that are not open to the public and for which an owner is known.

The WITH Clause (Oracle, SQL Server, DB2)

The SQL standard defines a WITH clause that you can use to factor out a subquery so that you don’t need to repeat it in your SELECT statement. Oracle, DB2, and SQL Server (beginning in SQL Server 2005) support WITH. Currently, MySQL and PostgreSQL do not, but support for WITH is currently planned ...