Subqueries

Subqueries allows the reuse of results from another query.

SQream DB supports relational (also called derived table) subqueries, which appear as SELECT queries as part of a table expression.

SQream DB also supports Common table expressions (CTEs), which are a form of subquery. With CTEs, a subquery can be named for reuse in a query.

Note

  • SQream DB does not currently support correlated subqueries or scalar subqueries.

  • There is no limit to the number of subqueries or nesting limits in a statement

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   "Name" varchar(40),
   "Team" varchar(40),
   "Number" tinyint,
   "Position" varchar(2),
   "Age" tinyint,
   "Height" varchar(4),
   "Weight" real,
   "College" varchar(40),
   "Salary" float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv

Name

Team

Number

Position

Age

Height

Weight

College

Salary

Avery Bradley

Boston Celtics

0.0

PG

25.0

6-2

180.0

Texas

7730337.0

Jae Crowder

Boston Celtics

99.0

SF

25.0

6-6

235.0

Marquette

6796117.0

John Holland

Boston Celtics

30.0

SG

27.0

6-5

205.0

Boston University

R.J. Hunter

Boston Celtics

28.0

SG

22.0

6-5

185.0

Georgia State

1148640.0

Jonas Jerebko

Boston Celtics

8.0

PF

29.0

6-10

231.0

5000000.0

Amir Johnson

Boston Celtics

90.0

PF

29.0

6-9

240.0

12000000.0

Jordan Mickey

Boston Celtics

55.0

PF

21.0

6-8

235.0

LSU

1170960.0

Kelly Olynyk

Boston Celtics

41.0

C

25.0

7-0

238.0

Gonzaga

2165160.0

Terry Rozier

Boston Celtics

12.0

PG

22.0

6-2

190.0

Louisville

1824360.0

Simple subquery

t=> SELECT AVG("Age") FROM
.        (SELECT "Name","Team","Age" FROM nba WHERE "Height" > '7-0');
avg
---
26

Combining a subquery with a join

t=> SELECT * FROM
.     (SELECT "Name" FROM nba WHERE "Height" > '7-0') AS t(name)
.     , nba AS n
.       WHERE n."Name"=t.name;
name               | Name               | Team                   | Number | Position | Age | Height | Weight | College    | Salary
-------------------+--------------------+------------------------+--------+----------+-----+--------+--------+------------+---------
Alex Len           | Alex Len           | Phoenix Suns           |     21 | C        |  22 | 7-1    |    260 | Maryland   |  3807120
Alexis Ajinca      | Alexis Ajinca      | New Orleans Pelicans   |     42 | C        |  28 | 7-2    |    248 | \N         |  4389607
Boban Marjanovic   | Boban Marjanovic   | San Antonio Spurs      |     40 | C        |  27 | 7-3    |    290 | \N         |  1200000
Kristaps Porzingis | Kristaps Porzingis | New York Knicks        |      6 | PF       |  20 | 7-3    |    240 | \N         |  4131720
Marc Gasol         | Marc Gasol         | Memphis Grizzlies      |     33 | C        |  31 | 7-1    |    255 | \N         | 19688000
Meyers Leonard     | Meyers Leonard     | Portland Trail Blazers |     11 | PF       |  24 | 7-1    |    245 | Illinois   |  3075880
Roy Hibbert        | Roy Hibbert        | Los Angeles Lakers     |     17 | C        |  29 | 7-2    |    270 | Georgetown | 15592217
Rudy Gobert        | Rudy Gobert        | Utah Jazz              |     27 | C        |  23 | 7-1    |    245 | \N         |  1175880
Salah Mejri        | Salah Mejri        | Dallas Mavericks       |     50 | C        |  29 | 7-2    |    245 | \N         |   525093
Spencer Hawes      | Spencer Hawes      | Charlotte Hornets      |      0 | PF       |  28 | 7-1    |    245 | Washington |  6110034
Tibor Pleiss       | Tibor Pleiss       | Utah Jazz              |     21 | C        |  26 | 7-3    |    256 | \N         |  2900000
Timofey Mozgov     | Timofey Mozgov     | Cleveland Cavaliers    |     20 | C        |  29 | 7-1    |    275 | \N         |  4950000
Tyson Chandler     | Tyson Chandler     | Phoenix Suns           |      4 | C        |  33 | 7-1    |    240 | \N         | 13000000
Walter Tavares     | Walter Tavares     | Atlanta Hawks          |     22 | C        |  24 | 7-3    |    260 | \N         |  1000000

WITH subqueries

See Common table expressions (CTEs) for more information.

nba=> WITH
.        nba_ct AS (SELECT "Name", "Team" FROM nba WHERE "College"='Connecticut'),
.        nba_az AS (SELECT "Name", "Team" FROM nba WHERE "College"='Arizona')
.        SELECT * FROM nba_az JOIN nba_ct ON nba_ct."Team" = nba_az."Team";
Name            | Team            | name0          | team0
----------------+-----------------+----------------+----------------
Stanley Johnson | Detroit Pistons | Andre Drummond | Detroit Pistons
Aaron Gordon    | Orlando Magic   | Shabazz Napier | Orlando Magic