Сибирский Dev | IT survival on Telegram: “#конспект #SQL Пора бы продолжить конспектирован…”

#конспект #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 и HAVING

SELECT 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
...

Разделим тему запросов и подтему со сложными подзапросами. Вынесу в отдельный пост, так как там запросы уж очень длинные 😍