Subqueries in Phoenix
Overview
Phoenix supports subqueries contained in WHERE and FROM clauses. You can use logical operators and comparison operators, both modified and unmodified, when composing subqueries.
To be able to follow examples given in this article, you need to create the example tables in your Phoenix environment as described in the JOIN clause in Phoenix article.
IN
The IN operator allows you to search the result of a subquery. The subquery in the parenthesis returns a table (possibly empty), and the IN operator treats this table as the set of values among which to search for matches according to the specified condition.
For example, to list the people who live in american states where there are also enterprises located, you need the following query:
SELECT p.name, p.state, p.town
FROM people AS p
WHERE p.state IN
(SELECT e.state
FROM enterprises AS e
WHERE e.country='USA')
LIMIT 20;Result:
+-------------------+---------+---------------+ | P.NAME | P.STATE | P.TOWN | +-------------------+---------+---------------+ | Abbott Delia | TX | Dallas | | Abbott Howard | LA | Baton Rouge | | Adams Clyde | LA | Lafayette | | Aguilar Terry | CA | Fresno | | Alexander Derrick | TX | Dallas | | Alexander Gregory | LA | Lafayette | | Alexander Leon | AZ | Kingman | | Alvarado Melvin | TX | Austin | | Alvarado Timothy | CA | San Francisco | | Alvarez Bruce | CA | Modesto | | Andrews Lucy | AZ | Kingman | | Armstrong Marie | NM | Santa Fe | | Atkins Gene | TX | Dallas | | Austin Eugene | LA | Lafayette | | Austin Travis | TX | El Paso | | Bailey Cameron | TX | Lubbock | | Baker Daniel | LA | Baton Rouge | | Ball Nelle | NM | Albuquerque | | Barber Minerva | TX | Beaumont | | Barnes Mollie | LA | Baton Rouge | +-------------------+---------+---------------+
You can also search for multiple column values. The number of columns in the WHERE clause must be the same as the number of columns in the SELECT clause of the subquery. If it is more than one, then the columns in the WHERE clause must be enclosed in parenthesis.
For example, to list the enterprises which are located in state capitols also being the largest city, you need the following query:
SELECT e.name, e.state, e.town
FROM enterprises AS e
WHERE (e.state, e.town) IN
(SELECT s.abbr, s.capitol
FROM states AS s
WHERE s.capitol=s.largest);Result:
+--------+---------+---------+ | E.NAME | E.STATE | E.TOWN | +--------+---------+---------+ | Avnet | AZ | Phoenix | +--------+---------+---------+
The NOT keyword inverts the IN clause. Using the NOT IN operator will search among the values that are not the results of the subquery.
For example, to list the people who live in the states where there are no 44 years old people, you need the following query:
SELECT p.name, p.state, p.age
FROM people AS p
WHERE p.state NOT IN
(SELECT pp.state
FROM people AS pp
WHERE pp.age=44)
LIMIT 20;Result:
+-------------------+---------+-------+ | P.NAME | P.STATE | P.AGE | +-------------------+---------+-------+ | Alexander Leon | AZ | 42 | | Andrews Lucy | AZ | 33 | | Becker Marie | AZ | 19 | | Bowman Randall | AZ | 46 | | Bryant Marcus | OR | 30 | | Buchanan Todd | OR | 25 | | Burton Luella | AZ | 62 | | Byrd Nannie | OR | 59 | | Carpenter Carrie | AZ | 24 | | Chambers Hettie | AZ | 52 | | Clarke Joe | AZ | 26 | | Cox Shawn | AZ | 25 | | Cunningham Sallie | AZ | 56 | | Duncan Ollie | AZ | 45 | | Elliott Myrtie | OR | 59 | | Evans Sophie | AZ | 58 | | Ferguson Josie | AZ | 28 | | Fletcher Julia | AZ | 38 | | Fowler Hettie | OR | 36 | | Fowler May | OR | 64 | +-------------------+---------+-------+
EXISTS
The EXISTS operator is logical. It returns true if the subquery result is not empty, and false otherwise. As the query is processed, the subquery in the parenthesis is executed for every iteration of the outer query. Also, for an EXISTS subquery to have any sense, it needs to be correlated to the outer query, i.e. to have some reference to its conditions. If there is no correlation between the queries, then the EXISTS clause will always resolve to the same value regardless of the outer query.
For example, to list american states where people of age 65 live, you need the following query:
SELECT s.name
FROM states AS s
WHERE EXISTS
(SELECT p.state
FROM people AS p
WHERE p.age=65 AND p.state=s.abbr);As you can see, in this example, the subquery contains the reference to the states table, as does the outer query.
Result:
+------------+ | S.NAME | +------------+ | Arizona | | California | | Louisiana | | Nevada | | Oregon | | Texas | +------------+
The NOT keyword inverts the logical operator. The NOT EXISTS operator will return true if the subquery result is empty, and false otherwise.
For example, to list all the states outside USA from the people table, you need the following query:
SELECT p.state
FROM people AS p
WHERE NOT EXISTS
(SELECT s.abbr
FROM states AS s
WHERE s.abbr=p.state)
GROUP BY p.state;Result:
+-----------------+ | P.STATE | +-----------------+ | California Baja | | Chihuahua | | Quintana Roo | | Sonora | | Yucatan | +-----------------+
Comparison operators
You can use comparison operators in the WHERE clause with the subquery being the right operand. Phoenix uses the following comparison operators:
-
=— equal to; -
!=or<>— not equal to; -
>— greater than; -
<— less than; -
>=or!<— greater than or equal to; -
<=or!>— less than or equal to.
Unmodified
Unmodified comparison operators are used as they are in ordinary comparison operations, without any modificators.
For example, to list the distribution of ages of people older than average, you need the following query:
SELECT p.age, count(p.age)
FROM people AS p
WHERE p.age >
(SELECT avg(p.age)
FROM people AS p)
GROUP BY p.age;Result:
+-----+------------+ | AGE | COUNT(AGE) | +-----+------------+ | 41 | 19 | | 42 | 19 | | 43 | 22 | | 44 | 16 | | 45 | 19 | | 46 | 17 | | 47 | 23 | | 48 | 23 | | 49 | 14 | | 50 | 12 | | 51 | 17 | | 52 | 26 | | 53 | 21 | | 54 | 21 | | 55 | 10 | | 56 | 26 | | 57 | 20 | | 58 | 26 | | 59 | 22 | | 60 | 22 | | 61 | 23 | | 62 | 21 | | 63 | 18 | | 64 | 21 | | 65 | 17 | +-----+------------+
Modified
Modified comparison operators are logical and are used with one of the following modifiers:
-
ANYorSOME— the operator will returntrueif at least one of the subquery results matches the comparison condition, andfalseotherwise; -
ALL— the operator will returntrueif all of the subquery results match the comparison condition, andfalseotherwise.
The subquery must return values from a single column.
For example, to list the american states where there are enterprises located along with their capitols, you need the following query:
SELECT s.name, s.capitol
FROM states AS s
WHERE s.abbr = ANY
(SELECT e.state
FROM enterprises AS e
JOIN
states AS s
ON s.abbr=e.state);Result:
+------------+-------------+ | NAME | CAPITOL | +------------+-------------+ | Arizona | Phoenix | | California | Sacramento | | Louisiana | Baton Rouge | | Maryland | Annapolis | | New Mexico | Santa Fe | | New York | Albany | | Texas | Austin | | Washington | Olympia | +------------+-------------+
To list the people whose age is not found among the residents of Nevada, along with their state and age, you need the following query:
SELECT p.name, p.state, p.age
FROM people AS p
WHERE p.age <> ALL
(SELECT p.age
FROM people AS p
WHERE p.state='NV')
LIMIT 20;Result:
+--------------------+-----------------+-----+ | NAME | STATE | AGE | +--------------------+-----------------+-----+ | Abbott Jack | Chihuahua | 29 | | Adams Clyde | LA | 29 | | Alvarado Dominic | California Baja | 63 | | Alvarez Jacob | Yucatan | 49 | | Anderson Lucile | Sonora | 33 | | Andrews Lucy | AZ | 33 | | Andrews Noah | California Baja | 63 | | Armstrong Isabella | Sonora | 49 | | Atkins Gene | TX | 37 | | Bailey Richard | Yucatan | 39 | | Benson Hallie | California Baja | 37 | | Bowers Norman | Chihuahua | 29 | | Boyd Travis | Yucatan | 49 | | Brock Carlos | Quintana Roo | 49 | | Burgess Nell | TX | 33 | | Byrd Nell | Quintana Roo | 39 | | Castillo Edna | Quintana Roo | 63 | | Chapman Lilly | Sonora | 63 | | Christensen Gavin | Quintana Roo | 63 | | Clark Joshua | Sonora | 63 | +--------------------+-----------------+-----+
Correlated subqueries
A subquery is called correlated or synchronized when it references the same tables the outer query does. A correlated subquery is executed each time the outer query processes a next row according to its own clauses. This distinguishes the correlated subquery from an independent one, which is unrelated to the outer query and is only executed once.
For example, to list the american states where there are enterprises located in their largest cities, along with their capitols, you need the following query:
SELECT s.name, s.capitol
FROM states AS s
WHERE s.abbr IN
(SELECT e.state
FROM enterprises AS e
WHERE e.town=s.largest);Result:
+------------+------------+ | S.NAME | S.CAPITOL | +------------+------------+ | Arizona | Phoenix | | California | Sacramento | | New Mexico | Santa Fe | | New York | Albany | +------------+------------+
Branches and nesting
You can unite subqueries using the AND and OR operators. Also, you can enclose the subqueries in one another however deep you want.
AND and OR branches
The AND operator is logical and means both operands of the expression need to be true for the whole expression to be true. The OR operator is logical and means any of the operands of the expression needs to be true for the whole expression to be true.
For example, to list the american people whose age is 65, and people anywhere whose age is 18, you need the following query:
SELECT p.name, p.age, p.state
FROM people AS p
WHERE p.state IN
(SELECT s.abbr
FROM states AS s)
AND p.age>64
OR p.age IN
(SELECT p.age
FROM people AS p
WHERE p.age=18);Result:
+--------------------+-------+-----------------+ | P.NAME | P.AGE | P.STATE | +--------------------+-------+-----------------+ | Aguilar Terry | 65 | CA | | Arnold Bettie | 18 | NV | | Bailey Cameron | 18 | TX | | Banks Catherine | 18 | Sonora | | Campbell Todd | 18 | NV | | Casey Louise | 18 | California Baja | | Davis Alan | 18 | CA | | Farmer Dean | 65 | LA | | Fitzgerald Timothy | 18 | Chihuahua | | Griffin Alexander | 65 | NV | | Harper Alvin | 65 | AZ | | Hill Katharine | 18 | Quintana Roo | | Hodges Abbie | 18 | Yucatan | | James Loretta | 18 | TX | | Lopez Katharine | 65 | AZ | | Love Dylan | 65 | TX | | McCarthy Eunice | 65 | LA | | Miller Francis | 18 | Sonora | | Munoz Ricardo | 18 | CA | | Myers Ricky | 18 | LA | | Norton Bertha | 18 | TX | | Phelps Rena | 18 | Quintana Roo | | Riley Mollie | 65 | OR | | Stokes Mittie | 18 | OR | | Strickland Helena | 65 | TX | | Thompson Warren | 18 | AZ | | Torres Elsie | 18 | LA | | Vaughn Belle | 18 | CA | +--------------------+-------+-----------------+
Nesting
You can use subqueries inside subqueries. This is called nesting.
For example, to list the enterprises in the states that are listed from A to C for the people from the people table, you need the following query:
SELECT e.name
FROM enterprises AS e
WHERE e.state IN
(SELECT p.state
FROM people AS p
WHERE p.state IN
(SELECT s.abbr
FROM states AS s
WHERE s.name < 'D'));Result:
+----------+ | E.NAME | +----------+ | Apple | | Avnet | | Intel | | Obsidian | | Sierra | +----------+
Derived tables
You can specify a subquery in the FROM clause. The result of such subquery will make up a table which can be searched for values.
For example, to list the people outside the USA along with their hometowns and ages, who are also older than 63, you need the following query:
SELECT pp.name, pp.town, pp.age
FROM
(SELECT p.name, p.town, p.age
FROM people AS p
WHERE p.country != 'USA')
AS pp
WHERE pp.age>63;Result:
+------------------+-----------+-----+ | NAME | TOWN | AGE | +------------------+-----------+-----+ | Bryant Addie | Izamal | 64 | | Chavez Don | Chetumal | 65 | | Cohen Victor | Chetumal | 64 | | Cox Lou | Tijuana | 64 | | Craig Bruce | Chihuahua | 65 | | Elliott Callie | Juarez | 65 | | Flores Lottie | Uman | 64 | | Flores Nannie | Chihuahua | 64 | | Frank Chester | Nogales | 65 | | Gill Bessie | Nogales | 64 | | Hunter Curtis | Mexicali | 65 | | Keller Loretta | Tijuana | 65 | | Little Antonio | Juarez | 64 | | Massey Hettie | Tijuana | 65 | | McGee Lulu | Cancun | 64 | | Mitchell Gary | Uman | 64 | | Mitchell Jason | Ensenada | 65 | | Riley Terry | Juarez | 64 | | Torres Katharine | Juarez | 64 | | Wheeler Oscar | Merida | 64 | | Wise Andre | Cancun | 64 | +------------------+-----------+-----+