Подзапросы в Phoenix
Обзор
Phoenix поддерживает подзапросы в выражениях WHERE и FROM. При составлении подзапросов можно использовать логические операторы и операторы сравнения, с модификаторами и без.
Чтобы иметь возможность выполнять примеры, приведенные в этой статье, нужно создать эти таблицы в вашем окружении Phoenix, как указано в статье Оператор JOIN в Phoenix.
IN
Оператор IN позволяет выполнять поиск в результатах выполнения подзапроса. Подзапрос в скобках возвращает таблицу (возможно, пустую), и оператор IN воспринимает эту таблицу как множество значений, среди которых он будет искать совпадения по заданному условию.
Например, чтобы вывести на экран список людей, проживающих в штатах США, в которых расположены предприятия, нужно выполнить следующий запрос:
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;Результат:
+-------------------+---------+---------------+ | 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 | +-------------------+---------+---------------+
Также можно выполнять поиск по нескольким столбцам. Количество столбцов в выражении WHERE должно совпадать с количеством столбцов в выражении SELECT в подзапросе. Если количество столбцов больше одного, то они должны быть заключены в скобки в выражении WHERE.
Например, чтобы вывести на экран предприятия, расположенные в столицах штатов, которые также являются крупнейшими городами, нужно выполнить следующий запрос:
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);Результат:
+--------+---------+---------+ | E.NAME | E.STATE | E.TOWN | +--------+---------+---------+ | Avnet | AZ | Phoenix | +--------+---------+---------+
Ключевое слово NOT инвертирует выражение IN. Оператор NOT IN выполняет поиск среди значений, не принадлежащих результату подзапроса.
Например, чтобы вывести на экран список людей, живущих в штатах, где нет 44-летних людей, нужно выполнить следующий запрос:
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;Результат:
+-------------------+---------+-------+ | 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
Оператор EXISTS является логическим. Он возвращает значение true, если результат подзапроса непуст, и значение false в противном случае. В процессе обработки запроса подзапрос в скобках выполняется для каждой итерации внешнего запроса. Также, чтобы подзапрос в выражении EXISTS имел смысл, нужно, чтобы он коррелировал c внешним запросом, то есть имел связь с его условиями. Если корреляции между запросами нет, то выражение EXISTS всегда будет иметь одно и то же значение независимо от условий внешнего запроса.
Например, чтобы вывести на экран штаты США, в которых живут люди в возрасте 65 лет, нужно выполнить следующий запрос:
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);Как видно, в этом примере подзапрос содержит поиск по таблице states, как и внешний запрос.
Результат:
+------------+ | S.NAME | +------------+ | Arizona | | California | | Louisiana | | Nevada | | Oregon | | Texas | +------------+
Ключевое слово NOT инвертирует логический оператор. Оператор NOT EXISTS возвращает значение true, если результат подзапроса пуст, и значение false в противном случае.
Например, чтобы вывести на экран все штаты за пределами США из таблицы people, нужно выполнить следующий запрос:
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;Результат:
+-----------------+ | P.STATE | +-----------------+ | California Baja | | Chihuahua | | Quintana Roo | | Sonora | | Yucatan | +-----------------+
Операторы сравнения
В выражении WHERE можно использовать операторы сравнения, при этом подзапрос является правым операндом. Phoenix использует следующие операторы сравнения:
-
=— равно; -
!=или<>— не равно; -
>— больше; -
<— меньше; -
>=или!<— больше или равно; -
<=или!>— меньше или равно.
Простые
Простые операторы сравнения используются так же, как и в обычных операциях сравнения, без модификаторов.
Например, чтобы вывести на экран распределение возрастов людей, старших среднего возраста, нужно выполнить следующий запрос:
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;Результат:
+-----+------------+ | 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 | +-----+------------+
Модифицированные
Модифицированные операторы сравнения являются логическими и используют один из следующих модификаторов:
-
ANYилиSOME— оператор возвращает значениеtrue, если хотя бы один результат подзапроса соответствует условию сравнения, и значениеfalseв противном случае; -
ALL— оператор возвращает значениеtrue, если все результаты подзапроса соответствуют условию сравнения, и значениеfalseв противном случае.
Подзапрос должен возвращать значения только из одного столбца.
Например, чтобы вывести на экран штаты США, где расположены предприятия, а также их столицы, нужно выполнить следующий запрос:
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);Результат:
+------------+-------------+ | NAME | CAPITOL | +------------+-------------+ | Arizona | Phoenix | | California | Sacramento | | Louisiana | Baton Rouge | | Maryland | Annapolis | | New Mexico | Santa Fe | | New York | Albany | | Texas | Austin | | Washington | Olympia | +------------+-------------+
Чтобы вывести на экран список людей, чей возраст не встречается среди жителей Невады, а также их штат и возраст, нужно выполнить следующий запрос:
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;Результат:
+--------------------+-----------------+-----+ | 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 | +--------------------+-----------------+-----+
Коррелирующие подзапросы
Подзапрос называется коррелирующим или синхронизированным, если он обращается к тем же таблицам, что и внешний запрос. Коррелирующий подзапрос выполняется каждый раз, когда внешний запрос обрабатывает очередную строку согласно собственным условиям. Это отличает коррелирующий запрос от независимого, который не связан с внешним запросом и выполняется лишь единожды.
Например, чтобы вывести на экран штаты США, в крупнейших городах которых расположены предприятия, а также их столицы, нужно выполнить следующий запрос:
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);Результат:
+------------+------------+ | S.NAME | S.CAPITOL | +------------+------------+ | Arizona | Phoenix | | California | Sacramento | | New Mexico | Santa Fe | | New York | Albany | +------------+------------+
Ветвление и вложенность
Подзапросы можно объединять с помощью операторов AND и OR. Также можно вкладывать подзапросы один в другой сколько угодно раз.
Ветвление AND и OR
Логический оператор AND означает, что оба операнда в выражении должны иметь значение true, чтобы все выражение имело значение true. Логический оператор OR означает, что любой из операндов в выражении может иметь значение true, чтобы все выражение имело значение true.
Например, чтобы вывести на экран жителей США в возрасте 65 лет, а также всех людей в возрасте 18 лет, нужно выполнить следующий запрос:
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);Результат:
+--------------------+-------+-----------------+ | 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 | +--------------------+-------+-----------------+
Вложенность
Можно использовать подзапросы внутри подзапросов. Это называется вложенность.
Например, чтобы вывести на экран предприятия, расположенные в штатах, начинающихся на буквы A, B и C, для людей из таблицы people, нужно выполнить следующий запрос:
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'));Результат:
+----------+ | E.NAME | +----------+ | Apple | | Avnet | | Intel | | Obsidian | | Sierra | +----------+
Производные таблицы
Подзапрос можно задать в выражении FROM. Результатом такого подзапроса является таблица, по которой можно выполнять поиск значений.
Например, чтобы вывести на экран список людей не из США старше 63 лет, а также их возраст и город, нужно выполнить следующий запрос:
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;Результат:
+------------------+-----------+-----+ | 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 | +------------------+-----------+-----+