O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

SUBQUERY Substatement

A subquery is a nested query. Subqueries may appear in various places within a SQL statement.

Platform

Command

MySQL

Supported, with limitations

Oracle

Supported

PostgreSQL

Supported

SQL Server

Supported

SQL supports the following types of subquery:

Scalar subqueries

Subqueries that retrieve a single value. These are the most widely supported type of subquery among the various database platforms.

Table subqueries

Subqueries that retrieve more than one value or row of values.

Nested table subqueries

Subqueries that retrieve more than one column and more than one row.

Scalar and vector subqueries can, on some platforms, appear as part of the expression in a SELECT list of items, a WHERE clause, or a HAVING clause. Nested table subqueries tend to appear in the FROM clauses of SELECT statements.

A correlated subquery is a subquery that is dependent upon a value in an outer query. Consequently, the inner query is executed once for every record retrieved in the outer query. Since subqueries can be nested many layers deep, a correlated subquery may reference any level in the main query higher that is than its own level.

Different rules govern the behavior of a subquery, depending on the clause in which it appears. The level of support amongst the database platforms also varies: some platforms support subqueries in all clauses mentioned earlier (SELECT, FROM, WHERE, and HAVING), while others support subqueries in only one or two of the clauses.

Subqueries are usually associated with ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required