About subqueries

A subquery is aquery that appears inside anotherquery statement. Subqueries are also referred to as sub-selects ornested selects. The fullSELECT syntax is valid in subqueries.

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

SELECTusernameFROM(WITHresultAS(SELECT*FROMPlayers)SELECT*FROMresult);

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 ina query wherever expressions are valid. They return a single value, as opposedto a column or table. Expression subqueries can becorrelated.

Scalar subqueries

SELECT ( subquery ) FROMtable

Description

A subquery inside an expression is interpreted as a scalar subquery.Scalar subqueries are often used in theSELECT list orWHERE clause.

A scalar subquery is an ordinary SELECT query in parentheses that returnsexactly one row with one column. The SELECT query is executed and the singlereturned value is used in the surrounding value expression. It is an error touse a query that returns more than one row or more than one column as a scalarsubquery. (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 canrefer to variables from the surrounding query, which will act as constantsduring any one evaluation of the subquery.

Examples

In this example, a correlated scalar subquery returns the mascots for a list ofplayers, using thePlayers andMascotstables:

SELECTusername,(SELECTmascotFROMMascotsWHEREPlayers.team=Mascots.team)ASplayer_mascotFROMPlayers;+---------------------------+|username|player_mascot|+---------------------------+|gorbie|cardinal||junelyn|finch||corba|parrot|+---------------------------+

In this example, an aggregate scalarsubquery calculatesavg_level, the average level of a user in thePlayers table.

SELECTusername,level,(SELECTAVG(level)FROMPlayers)ASavg_levelFROMPlayers;+---------------------------------------+|username|level|avg_level|+---------------------------------------+|gorbie|29|24.66||junelyn|2|24.66||corba|43|24.66|+---------------------------------------+

IN subqueries

SELECTvalue IN ( subquery )

Description

Returns TRUE ifvalue matches the select-list column value in any of thereturned rows.

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

Returns NULL ifvalue is NULL or if no equal row is found and the subqueryreturns at least one NULL row.

The subquery's SELECT list must have a single column of any type andits type must be comparable to the type forvalue. If not, an error isreturned.

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, theIN operator that checks to see if a username calledcorba exists within thePlayers table:

SELECT'corba'IN(SELECTusernameFROMPlayers)ASresult;+--------+|result|+--------+|TRUE|+--------+

NOT IN subqueries

SELECTvalue NOT IN ( subquery )

Description

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

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

Returns NULL ifvalue is NULL or if no equal row is found and the subqueryreturns at least one NULL row.

The subquery's SELECT list must have a single column of any type andits type must be comparable to the type forvalue. If not, an error isreturned.

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 thesubquery produces zero rows. Never returnsNULL. Unlike all otherexpression 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, theEXISTS operator checks to see if any rows areproduced, using thePlayers table:

SELECTEXISTS(SELECTusernameFROMPlayersWHEREteam='yellow')ASresult;+--------+|result|+--------+|FALSE|+--------+

Table subqueries

SELECTselect-list FROM ( subquery ) [ [ AS ] alias ]

Description

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

Examples

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

SELECTresults.usernameFROM(SELECT*FROMPlayers)ASresults;+-----------+|username|+-----------+|gorbie||junelyn||corba|+-----------+

Correlated subqueries

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

Examples

In this example, a list of mascots that don't have any players assigned to themis returned. TheMascots andPlayerstables are referenced.

SELECTmascotFROMMascotsWHERENOTEXISTS(SELECTusernameFROMPlayersWHEREMascots.team=Players.team);+----------+|mascot|+----------+|sparrow|+----------+

In this example, a correlated scalar subquery returns the mascots for a list ofplayers, using thePlayers andMascotstables:

SELECTusername,(SELECTmascotFROMMascotsWHEREPlayers.team=Mascots.team)ASplayer_mascotFROMPlayers;+---------------------------+|username|player_mascot|+---------------------------+|gorbie|cardinal||junelyn|finch||corba|parrot|+---------------------------+