SQL Pocket Guide, 3rd Edition

Subqueries

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

In the SELECT list of a SELECT statement

See The SELECT Clause.

In the FROM clause of a SELECT statement

See The FROM Clause.

In the WHERE clause of a SELECT statement

See Predicates, and also The WHERE Clause.

In the ORDER BY clause of a SELECT statement

See The ORDER BY Clause.

In an INSERT…SELECT…FROM statement

See Subquery Inserts.

In the SET clause of an UPDATE statement

See New Values from a Subquery.

Subqueries in the FROM Clause

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 and SQL Server, 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.

Subqueries in the WITH Clause

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. DB2, Oracle, PostgreSQL, and SQL Server support WITH.

Note

See Hierarchical Queries to learn how WITH is used to write recursive queries.

The following SELECT repeats two subqueries twice to generate a list of counties containing more than the average number of waterfalls per county:

SELECT c.name, (SELECT COUNT(*) ...