#конспект #SQL
Пора бы продолжить конспектирование и добить тему SQL)
ПодзапросыSELECT count( * ) FROM bookings
WHERE total_amount >
( SELECT avg( total_amount ) FROM bookings );
count
-------
87224
(1 строка)
В приведенном запросе присутствует два предложения
SELECT, но при этом только одно из них является главным в этом запросе, а другое представляет собой подзапрос. Он заключается в круглые скобки и является частью более общего запроса.
Подзапросы могут присутствовать в предложениях
SELECT,
FROM,
WHERE и
HAVING, а также в предложении
WITH, о котором мы расскажем позднее.
В качестве примера давайте выясним, какие маршруты существуют между городами часового пояса Asia/Krasnoyarsk. Подзапрос будет выдавать список городов из этого часового пояса, а в предложении
WHERE главного запроса с помощью предиката
IN будет выполняться проверка на принадлежность города этому списку. При этом подзапрос выполняется только один раз для всего внешнего запроса, а не при обработке каждой строки из таблицы routes во внешнем запросе. Повторного выполнения подзапроса не требуется, т. к. его результат не зависит от значений, хранящихся в таблице routes. Такие подзапросы называются
некоррелированными.
SELECT flight_no, departure_city, arrival_city
FROM routes
WHERE departure_city IN (
SELECT city
FROM airports
WHERE timezone ~ 'Krasnoyarsk'
)
AND arrival_city IN (
SELECT city
FROM airports
WHERE timezone ~ 'Krasnoyarsk'
);
flight_no | departure_city | arrival_city
--------------+------------+--------------
PG0070 | Абакан | Томск
PG0071 | Томск | Абакан
PG0313 | Абакан | Кызыл
PG0314 | Кызыл | Абакан
PG0653 | Красноярск | Барнаул
PG0654 | Барнаул | Красноярск
(6 строк)
Можно сформировать множество значений для предиката
IN с помощью скалярных подзапросов. Если мы захотим найти самый западный и самый восточный аэропорты и представить полученные сведения в наглядной форме, то запрос может быть таким:
SELECT airport_name, city, longitude
FROM airports
WHERE longitude IN (
( SELECT max( longitude ) FROM airports ),
( SELECT min( longitude ) FROM airports )
)
ORDER BY longitude;
airport_name | city | longitude
--------------+-------------+------------
Храброво | Калининград | 20.592633
Анадырь | Анадырь | 177.741483
(2 строки)
Рассмотрим использование подзапросов в предложениях
SELECT,
FROM и
HAVINGSELECT a.model,
( SELECT count( * )
FROM seats s
WHERE s.aircraft_code = a.aircraft_code
AND s.fare_conditions = 'Business'
) AS business,
( SELECT count( * )
FROM seats s
WHERE s.aircraft_code = a.aircraft_code
AND s.fare_conditions = 'Comfort'
) AS comfort,
( SELECT count( * )
FROM seats s
WHERE s.aircraft_code = a.aircraft_code
AND s.fare_conditions = 'Economy'
) AS economy
FROM aircrafts a
ORDER BY 1;
В этом запросе использовали коррелированные подзапросы. Все они ссылаются на столбец таблицы «Самолеты» (
aircrafts), которая обрабатывается во внешнем запросе. Для каждой обрабатываемой строки таблицы
aircrafts подсчитывается число строк в таблице
seats, в которых атрибут
aircraft_code имеет такое же значение, что и в строке таблицы aircrafts. Подзапросы отличаются друг от друга только условием
fare_conditions.
Поскольку все эти подзапросы не зависят друг от друга, то, хотя все они обращаются к таблице «Места» (
seats), не требуется использовать для нее различные псевдонимы в этих подзапросах.
model | business | comfort | economy
---------------------+----------+---------+---------
Airbus A319-100 | 20 | 0 | 96
Airbus A320-200 | 20 | 0 | 120
...
Разделим тему запросов и подтему со сложными подзапросами. Вынесу в отдельный пост, так как там запросы уж очень длинные 😍
