Subqueries in PostgreSQL

About subqueries

A subquery is a query that appears inside another query statement. Subqueries are also referred to as sub-selects or nested selects. The full SELECT syntax is valid in subqueries.

The WITH clause is not supported on a subquery. The following query returns an error:

SELECT username
FROM (
  WITH result AS (SELECT * FROM Players)
  SELECT *
  FROM result);

Common tables used in examples

The following are tables that are used in the sample queries on this page:

Players
+-----------------------------+
| username  | level   | team  |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

Mascots
+-------------------+
| mascot   | team   |
+-------------------+
| cardinal | red    |
| parrot   | green  |
| finch    | blue   |
| sparrow  | yellow |
+-------------------+

Expression subqueries

Expression subqueries are used in a query wherever expressions are valid. They return a single value, as opposed to a column or table. Expression subqueries can be correlated.

Scalar subqueries

SELECT ( subquery ) FROM table

Description

A subquery inside an expression is interpreted as a scalar subquery. Scalar subqueries are often used in the SELECT list or WHERE clause.

A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column. The SELECT query is executed and the single returned value is used in the surrounding value expression. It is an error to use a query that returns more than one row or more than one column as a scalar subquery. (But if, during a particular execution, the subquery returns no rows, there is no error; the scalar result is taken to be null.) The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.

Examples

In this example, a correlated scalar subquery returns the mascots for a list of players, using the Players and Mascots tables:

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM
  Players;

+---------------------------+
| username  | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

In this example, an aggregate scalar subquery calculates avg_level, the average level of a user in the Players table.

SELECT
  username,
  level,
  (SELECT AVG(level) FROM Players) AS avg_level
FROM
  Players;

+---------------------------------------+
| username  | level      | avg_level    |
+---------------------------------------+
| gorbie    | 29         | 24.66        |
| junelyn   | 2          | 24.66        |
| corba     | 43         | 24.66        |
+---------------------------------------+

IN subqueries

SELECT value IN ( subquery )

Description

Returns TRUE if value matches the select-list column value in any of the returned rows.

Returns FALSE if no equal row is found or the subquery returns zero rows.

Returns NULL if value is NULL or if no equal row is found and the subquery returns at least one NULL row.

The subquery's SELECT list must have a single column of any type and its type must be comparable to the type for value. If not, an error is returned.

If you prefer to use ANY/SOME syntax, these are equivalent:

value IN ( subquery )
value = ANY ( subquery )
value = SOME ( subquery )

Operators other than = are not supported for ANY/SOME expressions.

Examples

In this example, the IN operator that checks to see if a username called corba exists within the Players table:

SELECT
  'corba' IN (SELECT username FROM Players) AS result;

+--------+
| result |
+--------+
| TRUE   |
+--------+

NOT IN subqueries

SELECT value NOT IN ( subquery )

Description

Returns FALSE if value does not match the select-list column value in any of the returned rows.

Returns TRUE if no equal row is found or the subquery returns zero rows.

Returns NULL if value is NULL or if no equal row is found and the subquery returns at least one NULL row.

The subquery's SELECT list must have a single column of any type and its type must be comparable to the type for value. If not, an error is returned.

If you prefer to use ALL syntax, these are equivalent:

value NOT IN ( subquery )
value != ALL ( subquery )

Operators other than != are not supported for ALL expressions.

EXISTS subqueries

SELECT EXISTS( subquery )

Description

Returns TRUE if the subquery produces one or more rows. Returns FALSE if the subquery produces zero rows. Never returns NULL. Unlike all other expression subqueries, there are no rules about the column list. Any number of columns may be selected and it will not affect the query result.

Examples

In this example, the EXISTS operator checks to see if any rows are produced, using the Players table:

SELECT
  EXISTS(SELECT username FROM Players WHERE team = 'yellow') AS result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

Table subqueries

SELECT select-list FROM ( subquery ) [ [ AS ] alias ]

Description

With table subqueries, the outer query treats the result of the subquery as a table. You can only use these in the FROM clause.

Examples

In this example, a subquery returns a table of usernames from the Players table:

SELECT results.username
FROM (SELECT * FROM Players) AS results;

+-----------+
| username  |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

Correlated subqueries

A correlated subquery is a subquery that references a column from outside that subquery. Correlation prevents reusing of the subquery result.

Examples

In this example, a list of mascots that don't have any players assigned to them is returned. The Mascots and Players tables are referenced.

SELECT mascot
FROM Mascots
WHERE
  NOT EXISTS(SELECT username FROM Players WHERE Mascots.team = Players.team);

+----------+
| mascot   |
+----------+
| sparrow  |
+----------+

In this example, a correlated scalar subquery returns the mascots for a list of players, using the Players and Mascots tables:

SELECT
  username,
  (SELECT mascot FROM Mascots WHERE Players.team = Mascots.team) AS player_mascot
FROM Players;

+---------------------------+
| username  | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+