Subqueries
📖 Описание
Подзапрос в Oracle — запрос внутри другого SQL-оператора. Подзапросы могут находиться в WHERE, FROM или списке выражений SELECT.
Подзапрос в WHERE
Найдём доступные таблицы со столбцом STUDIO_ID через системные представления словаря:
SELECT tabs.owner, tabs.table_name
FROM all_tables tabs
WHERE (tabs.owner, tabs.table_name) IN (
SELECT cols.owner, cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = 'STUDIO_ID'
)
ORDER BY tabs.owner, tabs.table_name;
Сопоставляются и владелец, и имя таблицы: одинаковые имена в разных схемах не смешиваются. Результат зависит от объектов и прав в вашей базе; ALL_TABLES и ALL_TAB_COLUMNS — реальные системные представления.
В Oracle 19c допускается до 255 уровней вложенности подзапросов в WHERE.
Подзапрос в FROM
Такой подзапрос называется встроенным представлением (inline view). Самостоятельный пример сначала суммирует сборы по студиям, затем соединяет итоги с названиями:
WITH studios (studio_id, studio_name) AS (
SELECT 11, 'Clay Lab' FROM dual
UNION ALL SELECT 12, 'Paper House' FROM dual
UNION ALL SELECT 13, 'Canvas Room' FROM dual
), booking_fees (booking_id, studio_id, amount) AS (
SELECT 101, 11, 80 FROM dual
UNION ALL SELECT 102, 11, 120 FROM dual
UNION ALL SELECT 103, 13, 90 FROM dual
)
SELECT s.studio_name, totals.total_fee
FROM studios s
JOIN (
SELECT b.studio_id, SUM(b.amount) AS total_fee
FROM booking_fees b
GROUP BY b.studio_id
) totals ON totals.studio_id = s.studio_id
ORDER BY s.studio_id;
| STUDIO_NAME | TOTAL_FEE |
|---|---|
| Clay Lab | 200 |
| Canvas Room | 90 |
Подзапрос с псевдонимом totals возвращает studio_id и total_fee. Внешний запрос обращается к его столбцам как к столбцам таблицы. Paper House отсутствует, поскольку для неё нет бронирований и используется внутреннее соединение.
Документация Oracle 19c не задаёт отдельного предела числа уровней вложенности в FROM верхнего запроса; это не означает неограниченных ресурсов или целесообразности глубокой вложенности.
Подзапрос в SELECT
Скалярный подзапрос возвращает один столбец не более чем одной строки. При отсутствии строк его значение NULL; при нескольких строках возникает ошибка.
SELECT tabs.owner, tabs.table_name,
(SELECT COUNT(*)
FROM all_tab_columns cols
WHERE cols.owner = tabs.owner
AND cols.table_name = tabs.table_name) AS column_count
FROM all_tables tabs
WHERE tabs.owner = USER
ORDER BY tabs.table_name;
Здесь подзапрос связан с каждой внешней таблицей по owner и table_name; COUNT без GROUP BY возвращает одну строку. Выбраны таблицы схемы пользователя сеанса USER; количество зависит от вашей базы.
Скалярность не требует обязательной агрегации: подходит и поиск по уникальному ключу. SUM, MIN и MAX также могут свести набор к одному значению, если не добавлена группировка, создающая несколько строк.