O'Reilly logo

SQL in a Nutshell 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

Synopsis

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

Platform

Command

DB2

Supported

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 values. This type of subquery retrieves more than a 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, WHERE clause, and HAVING clause. Nested table subqueries tend to appear in the FROM clause 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 than its own level.

Different rules govern the behavior of a subquery depending on which clause it appears in. 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 ...

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