Subject to various platform restrictions, subqueries may be used in most SQL statements as follows:
See the earlier subsection "The SELECT Clause."
See the earlier subsection "The FROM Clause."
See the earlier subsection "The ORDER BY Clause."
See the earlier subsection "Subquery Inserts."
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 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 ...