Подзапросы

Подзапросы — это скобочные выражения запросов, которые появляются в рамках более крупного внешнего запроса. Подзапросы обычно основаны на SELECT ... FROM, но в DuckDB в качестве подзапроса могут также использоваться другие конструкции запросов, такие как PIVOT.

Скалярный подзапрос

Скалярные подзапросы — это подзапросы, возвращающие единственное значение. Они могут быть использованы в любом месте, где может быть использовано выражение. Если скалярный подзапрос возвращает более одного значения, возникает ошибка (если scalar_subquery_error_on_multiple_rows не установлено в false, в этом случае случайным образом выбирается строка).

Рассмотрим следующую таблицу:

Оценки

CREATE TABLE grades (grade INTEGER, course VARCHAR);
INSERT INTO grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

Мы можем выполнить следующий запрос, чтобы получить минимальную оценку:

SELECT min(grade) FROM grades;

Используя скалярный подзапрос в WHERE-запросе, мы можем определить, по какому предмету была получена эта оценка:

SELECT course FROM grades WHERE grade = (SELECT min(grade) FROM grades);

Сравнения подзапросов: ALL, ANY и SOME

В разделе о скалярных подзапросах скалярное выражение напрямую сравнивалось с подзапросом с использованием оператора сравнения равенства оператора сравнения (=). Такие прямые сравнения имеют смысл только со скалярными подзапросами.

Скалярные выражения по-прежнему могут быть сравнены с подзапросами, возвращающими несколько строк с одним столбцом, указав квантификатор. Доступные квантификаторы — ALL, ANY и SOME. Квантификаторы ANY и SOME эквивалентны.

ALL

Квантификатор ALL указывает, что сравнение в целом оценивается как true, когда отдельные результаты сравнения выражения слева от оператора сравнения с каждым из значений из подзапроса справа от оператора сравнения все оцениваются как true:

SELECT 6 <= ALL (SELECT grade FROM grades) AS adequate;

возвращает:

потому что 6 меньше или равно каждому из результатов подзапроса 7, 8 и 9.

Однако следующий запрос

SELECT 8 >= ALL (SELECT grade FROM grades) AS excellent;

возвращает

потому что 8 не больше или равно результату подзапроса 7. И, следовательно, поскольку не все сравнения оцениваются true, >= ALL в целом оценивается как false.

ANY

Квантификатор ANY указывает, что сравнение в целом оценивается как true, когда по крайней мере один из индивидуальных результатов сравнения оценивается как true. Например:

SELECT 5 >= ANY (SELECT grade FROM grades) AS fail;

возвращает

потому что ни один результат подзапроса не меньше или равен 5.

Квантификатор SOME может быть использован вместо ANY: ANY и SOME взаимозаменяемы.

EXISTS

Оператор EXISTS проверяет наличие любой строки внутри подзапроса. Он возвращает либо true, когда подзапрос возвращает одну или несколько записей, либо false в противном случае. Оператор EXISTS обычно является наиболее полезным в качестве коррелированного подзапроса для выражения операций полусоединения. Однако он также может использоваться как некоррелированный подзапрос.

Например, мы можем использовать его, чтобы выяснить, есть ли оценки по определенному предмету:

SELECT EXISTS (FROM grades WHERE course = 'Math') AS math_grades_present;

SELECT EXISTS (FROM grades WHERE course = 'History') AS history_grades_present;

Подзапросы в примерах выше используют тот факт, что вы можете опустить SELECT * в DuckDB благодаря FROM-синтаксису. Оператор SELECT требуется в подзапросах другими системами SQL, но не может выполнять никакой функции в подзапросах EXISTS и NOT EXISTS.

NOT EXISTS

Оператор NOT EXISTS проверяет отсутствие строк в подзапросе. Он возвращает true, если подзапрос возвращает пустой результат, и false в противном случае. Оператор NOT EXISTS обычно наиболее полезен в качестве коррелированного подзапроса для выражения операций антисоединения. Например, чтобы найти узлы Person без интереса:

CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE interest (PersonId BIGINT, topic VARCHAR);

INSERT INTO Person VALUES (1, 'Jane'), (2, 'Joe');
INSERT INTO interest VALUES (2, 'Music');

SELECT *
FROM Person
WHERE NOT EXISTS (FROM interest WHERE interest.PersonId = Person.id);

DuckDB автоматически обнаруживает, когда подзапрос NOT EXISTS выражает операцию антисоединения. Нет необходимости вручную переписывать такие запросы, чтобы использовать LEFT OUTER JOIN ... WHERE ... IS NULL.

IN Оператор

Оператор IN проверяет включение левого выражения в результат, определённый подзапросом или набором выражений справа (RHS). Оператор IN возвращает true, если выражение присутствует в RHS, false, если выражение отсутствует в RHS, и RHS не содержит NULL значений, или NULL, если выражение отсутствует в RHS, и RHS содержит NULL значения.

Мы можем использовать оператор IN аналогичным образом, как мы использовали оператор EXISTS:

SELECT 'Math' IN (SELECT course FROM grades) AS math_grades_present;

Коррелированные подзапросы

Все представленные до этого подзапросы были некоррелированными подзапросами, где сами подзапросы полностью самодостаточны и могут быть выполнены без родительского запроса. Существует второй тип подзапросов — коррелированные подзапросы. Для коррелированных подзапросов подзапрос использует значения из родительского подзапроса.

По сути, подзапросы выполняются один раз для каждой строки в родительском запросе. Возможно, проще представить это так, что коррелированный подзапрос — это функция, которая применяется к каждой строке в наборе исходных данных.

Например, предположим, что мы хотим найти минимальную оценку для каждого курса. Мы можем сделать это следующим образом:

SELECT *
FROM grades grades_parent
WHERE grade =
    (SELECT min(grade)
     FROM grades
     WHERE grades.course = grades_parent.course);

Подзапрос использует столбец из родительского запроса (grades_parent.course). По сути, мы можем рассматривать подзапрос как функцию, где коррелированный столбец является параметром этой функции:

SELECT min(grade)
FROM grades
WHERE course = ?;

Теперь, когда мы выполняем эту функцию для каждой строки, мы видим, что для Math она вернёт 7, а для CS она вернёт 8. Затем мы сравниваем это со значением оценки для данной строки. В результате строка (Math, 9) будет отфильтрована, так как 9 <> 7.

Возвращение каждой строки подзапроса как структуры

Использование имени подзапроса в SELECT-запросе (без указания конкретного столбца) превращает каждую строку подзапроса в структуру, поля которой соответствуют столбцам подзапроса. Например:

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;