Subqueries¶
Subqueries allows you to reuse of results from another query.
Blue supports relational (also called derived table) subqueries, which appear as SELECT queries as part of a table expression.
Blue 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
Blue does not currently support correlated subqueries or scalar subqueries.
There is no limit to the number of subqueries or nesting limits in a statement
Table Subqueries¶
The following is an example of table named nba with the following structure:
CREATE TABLE nba
(
"Name" TEXT,
"Team" TEXT,
"Number" TINYINT,
"Position" TEXT,
"Age" TINYINT,
"Height" TEXT,
"Weight" REAL,
"College" TEXT,
"Salary" FLOAT
);
To see the table contents, click Download 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 |
Scalar Subqueries¶
The following are examples of scalar subqueries.
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