Соединения и подзапросы

В теме 3 были введены операции реляционной алгебры — соединения, полусоединение, антисоединение, деление — и запрос SELECT к одной таблице. Но данные «Книжного узла», как и любой нормализованной базы, разнесены по многим таблицам: название книги хранится в book, имя автора — в author, связь между ними — в book_author, а покупатель, заказ, позиции заказа и оплата — в четырёх разных таблицах. Почти любой практический вопрос — «какие книги купил покупатель», «какие издательства не выпустили ни одной книги по Python», «сколько заплатили за заказ» — требует собрать данные из нескольких таблиц. Остаётся открытым вопрос, как алгебраические соединения и подзапросы записываются в SQL и чем SQL с его NULL и мультимножествами отличается здесь от алгебры.

Тема отвечает на этот вопрос в два шага. Первый — соединения: внутренние, внешние, самосоединения и соединения многих таблиц по пути внешних ключей. Здесь возникают две ловушки, на которых ошибаются и опытные разработчики: условие в WHERE, незаметно превращающее внешнее соединение во внутреннее, и размножение строк при соединении с двумя независимыми таблицами «многих». Второй шаг — подзапросы: скалярные, в FROM, в условиях с IN, EXISTS, ANY/ALL, коррелированные и LATERAL. Подзапросы дают в SQL прямые аналоги полусоединения, антисоединения и деления; рядом с ними — третья ловушка, NOT IN при наличии NULL.

Все примеры выполнены на учебной базе bookstore масштаба scale=1, числа строк приведены из реального вывода. Группировка и агрегатные функции рассматриваются в теме 5; здесь функции count, sum и avg используются только в простейшем виде — для подсчёта строк и суммы по всем строкам результата или подзапроса. Как СУБД выполняет соединения (вложенные циклы, хеширование, слияние), разбирается в теме 11.

Внутреннее соединение

От декартова произведения к JOIN

Если в предложении FROM перечислить две таблицы через запятую или соединить их ключевыми словами CROSS JOIN, результатом будет декартово произведение: каждая строка первой таблицы в паре с каждой строкой второй ⁵. В учебной базе 2010 книг и 4 склада, поэтому SELECT count(*) FROM book CROSS JOIN warehouse возвращает 8040. Произведение бывает нужно само по себе — например, чтобы получить все сочетания «книга — склад» и затем найти отсутствующие остатки, — но чаще за ним следует отбор осмысленных пар, то есть тета-соединение темы 3.

Исторически соединение записывали именно так: произведение в FROM и условие соединения в WHERE. Стандарт SQL-92 ввёл явный синтаксис соединения (англ. explicit join syntax), в котором условие соединения записывается рядом с таблицей в предложении ON ⁴¹:

-- старый стиль: произведение и условие в WHERE
SELECT b.title, p.name
FROM book b, publisher p
WHERE p.publisher_id = b.publisher_id AND b.book_id <= 3;

-- явное соединение
SELECT b.title, p.name
FROM book b
JOIN publisher p ON p.publisher_id = b.publisher_id
WHERE b.book_id <= 3;

          title           |  name
--------------------------+--------
 Война и мир              | АСТ
 Преступление и наказание | Эксмо
 Евгений Онегин           | Азбука
(3 rows)

Для внутреннего соединения обе формы эквивалентны, и PostgreSQL строит для них одинаковый план. Явная форма предпочтительнее по двум причинам. Условия соединения отделены от условий отбора, и запрос легче читать. И главное — пропущенное условие соединения в явной форме вызывает синтаксическую ошибку (JOIN требует ON или USING), а в старой форме молча превращает запрос в декартово произведение. Слово JOIN означает INNER JOIN — внутреннее соединение (англ. inner join): в результат попадают только пары строк, для которых условие истинно. Строка без пары исчезает: книг 2010, а book JOIN publisher возвращает 1915 строк, потому что у 95 книг publisher_id равен NULL, а сравнение NULL = значение не истинно.

Буквы b и p — псевдонимы таблиц (англ. table alias), реализация переименования ρ. Когда в запросе несколько таблиц, имена столбцов принято уточнять псевдонимом (b.title), даже если имя уникально: запрос становится понятнее и не ломается, если в другую таблицу позже добавят столбец с тем же именем. Если же имя столбца встречается в двух таблицах, уточнение обязательно, иначе PostgreSQL сообщает об ошибке column reference "…" is ambiguous.

USING и NATURAL JOIN

Когда соединение идёт по равенству одноимённых столбцов, вместо ON можно написать USING: book JOIN book_author USING (book_id) означает ON book.book_id = book_author.book_id, но в результате остаётся один столбец book_id, а не два ⁵. Это удобно и безопасно: столбцы соединения перечислены явно.

NATURAL JOIN идёт дальше: он соединяет по всем одноимённым столбцам, не перечисляя их, — это естественное соединение алгебры, введённое ещё Коддом ¹². Именно поэтому им почти не пользуются в рабочем коде. Условие соединения зависит от схемы, а не от запроса, и меняется вместе с ней. В учебной базе у order_item и book два общих имени: book_id и price. Пока цена книги в каталоге совпадает с ценой в заказах, естественное соединение возвращает все 59 910 позиций заказов. Изменим в транзакции цену «Войны и мира» и посмотрим ещё раз:

BEGIN;
UPDATE book SET price = 1390 WHERE book_id = 1;
SELECT count(*) FROM order_item NATURAL JOIN book;           -- 58702
SELECT count(*) FROM order_item JOIN book USING (book_id);   -- 59910
ROLLBACK;

1208 позиций с «Войной и миром» исчезли из естественного соединения без всякого предупреждения: столбец order_item.price хранит цену на момент заказа, book.price — текущую, и условие order_item.price = book.price для них стало ложным. Такие ошибки трудно заметить, потому что запрос продолжает работать и возвращает правдоподобные данные. Обратный случай тоже возможен: warehouse NATURAL JOIN publisher соединяет склады с издательствами по городу — случайному общему имени, о котором автор запроса мог и не думать. Если же общих имён нет совсем, NATURAL JOIN становится декартовым произведением.

Соединение нескольких таблиц: путь по внешним ключам

Вопрос, затрагивающий несколько таблиц, решается соединением по цепочке внешних ключей (англ. join path): от таблицы к таблице, на каждом шаге — по паре «внешний ключ — первичный ключ». Чтобы найти этот путь, удобно держать перед глазами схему базы. Например, для вопроса «из каких рубрик книги в заказе 1» путь такой: orders → order_item (по order_id) → book (по book_id) → category (по category_id).

SELECT o.order_id, o.created_at::date AS d, b.title,
       cat.name AS category, oi.quantity, oi.price
FROM orders o
JOIN order_item oi ON oi.order_id = o.order_id
JOIN book b        ON b.book_id = oi.book_id
JOIN category cat  ON cat.category_id = b.category_id
WHERE o.order_id = 1
ORDER BY b.book_id;

 order_id |     d      |          title           |  category  | quantity |  price
----------+------------+--------------------------+------------+----------+---------
        1 | 2023-09-27 | Хроники баз данных 286   | Фантастика |        1 | 1489.99
        1 | 2023-09-27 | Практика времени 345     | Математика |        1 | 1479.99
        1 | 2023-09-27 | История старого дома 756 | Фантастика |        1 | 2559.99
(3 rows)

Синтаксически соединения в FROM выполняются слева направо: a JOIN b ON … JOIN c ON … означает (a JOIN b ON …) JOIN c ON …, и в условии ON можно ссылаться только на таблицы, уже появившиеся левее ⁷. Для внутренних соединений порядок записи не влияет ни на результат (соединение коммутативно и ассоциативно), ни на скорость: планировщик сам выбирает порядок выполнения (тема 11). Для внешних соединений порядок важен, к этому вернёмся ниже.

Полезно уметь оценивать мощность результата до выполнения. Соединение «многие к одному» по внешнему ключу, обязательному (NOT NULL) и ссылочно целостному, сохраняет число строк левой таблицы: каждая позиция заказа находит ровно одну книгу, каждая книга — ровно одну рубрику, поэтому три позиции заказа дают три строки. Соединение «один ко многим» может увеличить число строк — об этом отдельный раздел.

Самосоединение: рубрики и их родители

Таблица category хранит дерево рубрик: столбец parent_id ссылается на ту же таблицу. Чтобы рядом с рубрикой вывести название родителя, таблицу соединяют саму с собой — это самосоединение (англ. self-join). Псевдонимы здесь обязательны: две копии одной таблицы играют разные роли, и их надо различать, как при переименовании ρ в алгебре. Добавив третью копию, получим «дедушку» — рубрику верхнего уровня:

SELECT c.name AS category, p.name AS parent, g.name AS grandparent
FROM category c
JOIN category p ON p.category_id = c.parent_id
JOIN category g ON g.category_id = p.parent_id
ORDER BY c.category_id;

        category        |      parent      |        grandparent
------------------------+------------------+---------------------------
 Python                 | Программирование | Компьютерная литература
 C и C++                | Программирование | Компьютерная литература
 Java                   | Программирование | Компьютерная литература
 PostgreSQL             | Базы данных      | Компьютерная литература
 NoSQL                  | Базы данных      | Компьютерная литература
 Космическая фантастика | Фантастика       | Художественная литература
(6 rows)

В рубрикаторе 23 рубрики, но в ответе шесть: внутреннее соединение отбросило рубрики, у которых нет родителя или «дедушки», — четыре рубрики верхнего уровня и тринадцать рубрик второго уровня. Каждое следующее самосоединение спускается на один уровень дерева, поэтому дерево произвольной глубины самосоединениями не обойти; для этого служат рекурсивные запросы (тема 6).

Другой типичный пример самосоединения — пары строк одной таблицы, связанных общим значением. Соавторы — это две строки book_author с одной книгой и разными авторами; условие ba2.author_id > ba1.author_id оставляет каждую пару один раз и исключает пары автора с самим собой. Для первых десяти книг такой запрос возвращает две строки — Аркадий и Борис Стругацкие для «Пикника на обочине» и «Трудно быть богом».

Соединение по неравенству

Условие соединения не обязано быть равенством. Соединение по произвольному условию — тета-соединение алгебры; когда условие отлично от равенства, его называют неэквисоединением (англ. non-equi join). Типичный случай — соединение с таблицей интервалов: тарифной сеткой, шкалой скидок, ценовыми классами. Справочник интервалов не обязательно хранить в базе: конструктор VALUES с псевдонимом и именами столбцов создаёт маленькую таблицу прямо в запросе ⁵.

SELECT b.book_id, b.title, b.price, band.label
FROM book b
JOIN (VALUES ('дешёвая', 0, 500),
             ('средняя', 500, 1500),
             ('дорогая', 1500, 100000)) AS band (label, lo, hi)
  ON b.price >= band.lo AND b.price < band.hi
WHERE b.book_id <= 6
ORDER BY b.book_id;

 book_id |              title               |  price  |  label
---------+----------------------------------+---------+---------
       1 | Война и мир                      | 1290.00 | средняя
       2 | Преступление и наказание         |  450.00 | дешёвая
       3 | Евгений Онегин                   |  390.00 | дешёвая
       4 | Пикник на обочине                |  520.00 | средняя
       5 | Трудно быть богом                |  540.00 | средняя
       6 | Убийство в «Восточном экспрессе» |  480.00 | дешёвая
(6 rows)

Рассуждение о мощности здесь то же, что и для соединения по ключу, но гарантий меньше: ни первичный, ни внешний ключ не обещают, что каждой книге найдётся ровно один интервал. Если интервалы пересекаются, книга на границе попадёт в два класса и строка размножится; если между интервалами есть разрыв, книга в разрыве исчезнет из внутреннего соединения. По всей таблице запрос возвращает 2010 строк — ровно по одной на книгу. Сдвиг нижней границы первого класса с 0 на 1 оставляет 2009 строк: бесплатная книга выпадает в разрыв. А замена < на <= делает интервалы пересекающимися, но на учебных данных по-прежнему даёт 2010 строк, потому что ни одна книга не стоит ровно 500 или 1500 рублей. Ошибка в запросе есть, а на данных она не видна — к этому наблюдению мы вернёмся в конце темы. Поэтому интервалы принято задавать полуоткрытыми (нижняя граница включается, верхняя нет) и примыкающими друг к другу. Для неэквисоединения PostgreSQL не может применить ни соединение хешированием, ни соединение слиянием — обоим методам нужно условие равенства — и выполняет его вложенными циклами (тема 11).

Внешние соединения

LEFT, RIGHT и FULL: дополнение значениями NULL

Внутреннее соединение теряет строки без пары, а вопрос часто требует их сохранить: «все книги с издательствами, в том числе книги без издательства». Для этого служат внешние соединения (англ. outer join) ¹²⁵:

• левое внешнее соединение A LEFT JOIN B ON … возвращает все строки внутреннего соединения и, кроме того, каждую строку A, не нашедшую пары, дополненную значениями NULL в столбцах B;
• правое A RIGHT JOIN B — то же для строк B; оно равносильно B LEFT JOIN A, и на практике обычно пишут левые соединения;
• полное A FULL JOIN B сохраняет строки без пары с обеих сторон.

SELECT b.book_id, b.title, p.name AS publisher
FROM book b
LEFT JOIN publisher p ON p.publisher_id = b.publisher_id
WHERE b.book_id <= 8
ORDER BY b.book_id;

 book_id |               title               | publisher
---------+-----------------------------------+-----------
       1 | Война и мир                       | АСТ
       2 | Преступление и наказание          | Эксмо
       3 | Евгений Онегин                    | Азбука
       4 | Пикник на обочине                 | АСТ
       5 | Трудно быть богом                 | АСТ
       6 | Убийство в «Восточном экспрессе»  | Эксмо
       7 | Искусство программирования. Том 1 |
       8 | Высоконагруженные приложения      | ДМК Пресс
(8 rows)

Книга Кнута сохранилась, а на месте издательства — NULL. По всей таблице book LEFT JOIN publisher даёт 2010 строк против 1915 у внутреннего соединения. Рисунок ниже сравнивает три вида соединений на двух маленьких таблицах.

Полное соединение удобно для сверки двух наборов: какие города есть только у складов, какие только у издательств, какие у обоих.

SELECT w.city AS warehouse_city, p.name AS publisher
FROM warehouse w
FULL JOIN publisher p ON p.city = w.city
ORDER BY w.warehouse_id, p.publisher_id;

 warehouse_city  |   publisher
-----------------+---------------
 Москва          | ДМК Пресс
 Москва          | Эксмо
 Москва          | АСТ
 Москва          | Наука
 Москва          | МЦНМО
 Москва          | Самокат
 Санкт-Петербург | Питер
 Санкт-Петербург | БХВ
 Санкт-Петербург | Азбука
 Новосибирск     |
 Казань          |
                 | Северный узел
(12 rows)

Склады в Новосибирске и Казани не нашли издательств, а издательство «Северный узел» с неизвестным городом не нашло склада — и все три строки сохранены. Заметим, что NULL в результате внешнего соединения неоднозначен: в последней строке пустой warehouse_city означает «пары нет», но такой же NULL мог бы прийти из самой таблицы. Чтобы отличать эти случаи, проверяют на NULL столбец, который в таблице заведомо не пуст, — первичный ключ.

Условие в ON и условие в WHERE

Для внутреннего соединения безразлично, записано условие в ON или в WHERE: в обоих случаях оно отбирает пары. Для внешнего соединения это различие принципиально, и оно следует из логического порядка выполнения запроса (тема 3). Условие ON — часть соединения: оно определяет, какие строки считаются парой, а строки левой таблицы без пары сохраняются в любом случае. Условие WHERE применяется после соединения к уже построенным строкам, включая дополненные NULL ⁵⁷.

Рассмотрим вопрос «покупатели из Томска и их заказы за декабрь 2024 года — включая покупателей, которые в декабре ничего не заказывали». Покупателей из Томска 576.

-- условие на дату в ON: 584 строки
SELECT c.customer_id, o.order_id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.created_at >= '2024-12-01'
WHERE c.city = 'Томск';

-- условие на дату в WHERE: 72 строки
SELECT c.customer_id, o.order_id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Томск' AND o.created_at >= '2024-12-01';

Первый запрос отвечает на поставленный вопрос: 72 строки — декабрьские заказы томичей, ещё 512 строк — томичи без декабрьских заказов, с NULL в order_id; 72 + 512 = 584. Во втором запросе левое соединение сначала строит все заказы томичей за всё время и строки для томичей без заказов вообще, а затем WHERE отбрасывает каждую строку, где created_at не больше границы, — в том числе все строки, дополненные NULL, потому что сравнение NULL >= '2024-12-01' не истинно. Остаются 72 строки, ровно как у внутреннего соединения: условие в WHERE «превратило» внешнее соединение во внутреннее. Схема на рисунке показывает, на каком шаге теряются строки.

Правило, которое из этого следует: при LEFT JOIN условия на правую таблицу пишутся в ON, условия на левую — в WHERE. Условие WHERE на столбец правой таблицы допустимо только в одном случае — когда оно проверяет IS NULL и намеренно оставляет строки без пары. PostgreSQL сам замечает, что WHERE с таким условием отбрасывает дополненные строки, и выполняет соединение как внутреннее; в плане выполнения это видно по узлу Join без слова Left (тема 11).

Тот же эффект возникает в цепочке соединений: a LEFT JOIN b ON … JOIN c ON c.id = b.c_id — внутреннее соединение с c отбрасывает строки, где b дополнено NULL, и внешнее соединение с b теряет смысл. Если строки a должны сохраниться, вся цепочка после первого внешнего соединения тоже должна быть внешней, либо b JOIN c заключается в скобки как единое целое: a LEFT JOIN (b JOIN c ON …) ON ….

Строки без пары: антисоединение через LEFT JOIN

Внешнее соединение даёт и способ найти строки без пары — антисоединение темы 3. Левое соединение сохраняет строки левой таблицы без пары, дополняя их NULL; остаётся оставить только их, проверив на NULL первичный ключ правой таблицы. Покупатели, не сделавшие ни одного заказа:

SELECT count(*)
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;   -- 621

Проверять на NULL нужно именно столбец, который в правой таблице не может быть NULL, — столбец условия соединения или первичный ключ: проверка необязательного столбца, например o.warehouse_id IS NULL, смешала бы строки без пары с настоящими заказами без склада. Другие формулировки антисоединения — NOT EXISTS, NOT IN, EXCEPT — рассмотрены в разделе о подзапросах.

Размножение строк при соединении

Мощность соединения «один ко многим»

Соединение по внешнему ключу «один ко многим» с точки зрения стороны «один» размножает строки: заказ 1 соединяется с тремя своими позициями и превращается в три строки, и все атрибуты заказа — дата, покупатель, статус — повторяются трижды. Это не ошибка: каждая строка результата описывает одну позицию, и соединение отвечает на вопрос о позициях. Ошибка возникает, когда результат такого соединения используют для вопроса о заказах — например, считают заказы или складывают атрибуты заказа. Размножение строк (англ. row multiplication, также fan-out) — самая частая причина неверных отчётов, потому что запрос выполняется без ошибок и даёт число правдоподобного масштаба.

Мощность соединения можно оценить заранее. Если каждая строка A соединяется в среднем с k строками B, то |A ⋈ B| ≈ k · |A|. В учебной базе 20 000 заказов и 59 910 позиций, в среднем три позиции на заказ, поэтому orders JOIN order_item даёт 59 910 строк. Любая сумма по атрибуту orders, посчитанная после такого соединения, окажется примерно втрое больше правильной.

Две независимые связи «один ко многим»

Опаснее всего случай, когда к одной строке присоединяются две независимые таблицы «многих». У заказа есть позиции (order_item) и платежи (payment); позиции и платежи между собой не связаны, связь у каждого — только с заказом. Соединение orders JOIN order_item JOIN payment сочетает каждую позицию заказа с каждым его платежом, то есть строит декартово произведение внутри каждого заказа: при трёх позициях и одном платеже платёж повторяется трижды, при трёх позициях и двух платежах получилось бы шесть строк. Родственную ситуацию в ER-моделировании называют ловушкой «веер» (англ. fan trap, тема 7); рисунок ниже показывает её на заказе 1.

Посчитаем сумму платежей за ноябрь 2024 года правильно и «с соединением», которое понадобилось бы, например, чтобы заодно вывести названия купленных книг:

-- платежи без соединения
SELECT count(*), sum(amount)
FROM payment
WHERE paid_at >= '2024-11-01' AND paid_at < '2024-12-01';

-- те же платежи, соединённые с позициями заказов
SELECT count(*), sum(p.amount)
FROM payment p
JOIN order_item oi ON oi.order_id = p.order_id
WHERE p.paid_at >= '2024-11-01' AND p.paid_at < '2024-12-01';

 count |    sum
-------+------------
   553 | 3456109.22
(1 row)

 count |     sum
-------+-------------
  1658 | 10361057.69
(1 row)

553 платежа превратились в 1658 строк, а выручка выросла втрое: каждый платёж сложен столько раз, сколько позиций в его заказе. Ошибку выдаёт именно число строк: если запрос отвечает на вопрос о платежах, строк должно быть столько же, сколько платежей. Отсюда простое правило самопроверки — перед суммированием посчитать строки и сравнить с числом объектов, о которых задан вопрос.

Как избежать размножения

Способов три, и все они сводятся к тому, чтобы не соединять независимые таблицы «многих» напрямую.

Первый — не соединять, если атрибуты второй таблицы не нужны в результате. Если таблица в запросе нужна только для условия («платежи по заказам, в которых есть книги Кнута»), её место не в JOIN, а в полусоединении EXISTS (раздел о подзапросах): полусоединение по определению не размножает строки.

Второй — свести каждую таблицу «многих» к одной строке на ключ до соединения: вычислить сумму позиций заказа коррелированным подзапросом или подзапросом в FROM и только потом соединить с платежами. Предварительная агрегация (англ. pre-aggregation) подзапросом с GROUP BY — основной приём построения отчётов, он подробно разбирается в теме 5.

Третий способ, который часто применяют и который не следует применять, — DISTINCT или count(DISTINCT …) поверх размноженного результата. Он маскирует симптом: два разных платежа на одинаковую сумму по одному заказу DISTINCT склеит в один, и сумма станет меньше правильной. Правильный запрос не порождает лишних строк, а не убирает их после.

Подзапросы

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

Подзапрос (англ. subquery) — запрос SELECT, вложенный в другой запрос и заключённый в скобки. Место подзапроса определяет, что от него требуется. Скалярный подзапрос (англ. scalar subquery) стоит там, где ожидается одно значение, и должен вернуть не более одной строки с одним столбцом; если строк нет, значение подзапроса — NULL, если строк больше одной — возникает ошибка выполнения ⁸. Средняя цена книги — 1567.74 рубля; книги дороже средней:

SELECT count(*) FROM book WHERE price > (SELECT avg(price) FROM book);   -- 991

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

SELECT book_id, title, price,
       round(price - (SELECT avg(price) FROM book), 2) AS diff
FROM book
WHERE book_id <= 5
ORDER BY book_id;

 book_id |          title           |  price  |   diff
---------+--------------------------+---------+----------
       1 | Война и мир              | 1290.00 |  -277.74
       2 | Преступление и наказание |  450.00 | -1117.74
       3 | Евгений Онегин           |  390.00 | -1177.74
       4 | Пикник на обочине        |  520.00 | -1047.74
       5 | Трудно быть богом        |  540.00 | -1027.74
(5 rows)

Этот подзапрос не зависит от внешнего запроса — такой подзапрос называют некоррелированным (англ. uncorrelated), и СУБД вычисляет его один раз. В плане выполнения PostgreSQL он виден как узел InitPlan.

Подзапросы в FROM: производные таблицы

Подзапрос в предложении FROM называется производной таблицей (англ. derived table): его результат участвует в запросе как обычная таблица ⁵. Стандарт и PostgreSQL до версии 15 включительно требуют дать ему псевдоним; в PostgreSQL 16 псевдоним можно опустить, но давать его по-прежнему принято — по нему ссылаются на столбцы подзапроса. Производные таблицы решают две задачи. Первая — дать имя вычисленному столбцу, чтобы использовать его в WHERE внешнего запроса, обойдя ограничение логического порядка выполнения из темы 3:

SELECT title, new_price
FROM (SELECT title, price * 1.2 AS new_price FROM book) AS t
WHERE new_price > 4000;

               title               | new_price
-----------------------------------+-----------
 Искусство программирования. Том 1 |  4788.000
(1 row)

Вторая — подготовить данные до соединения: свести таблицу «многих» к одной строке на ключ и избежать размножения строк (подробно — в теме 5). Производная таблица — это то же, что подвыражение в дереве алгебры; планировщик PostgreSQL, как правило, «раскрывает» простые подзапросы в FROM, встраивая их в основной запрос, так что вынос выражения в подзапрос не делает запрос медленнее. Для длинных запросов ту же роль удобнее играет общее табличное выражение WITH (тема 6).

IN, ANY и ALL

Подзапрос, возвращающий один столбец и сколько угодно строк, используется в условиях со специальными предикатами ⁶:

• x IN (подзапрос) — истинно, если x равно хотя бы одному значению подзапроса;
• x оп ANY (подзапрос) (синоним SOME) — истинно, если сравнение x оп v истинно хотя бы для одного значения v; x = ANY (…) равносильно IN;
• x оп ALL (подзапрос) — истинно, если сравнение истинно для всех значений; x <> ALL (…) равносильно NOT IN.

Книги дороже всех книг рубрики «Базы данных» (рубрика 13, самая дорогая — 2979.99):

SELECT count(*) FROM book
WHERE price > ALL (SELECT price FROM book WHERE category_id = 13);   -- 13

Предикат ALL скрывает неочевидный случай: для пустого подзапроса он истинен. В рубрике 1 «Художественная литература» книг нет (книги лежат в её подрубриках), поэтому price > ALL (SELECT price FROM book WHERE category_id = 1) истинно для всех 2010 книг — точно так же, как деление на пустой делитель в теме 3 возвращает всех кандидатов. Формулировка через max (price > (SELECT max(price) …)) в этом случае вернула бы пустой результат, потому что максимум пустого множества — NULL. Две формулировки, кажущиеся равносильными, расходятся именно на граничном случае. Ещё одна тонкость — NULL в подзапросе: если среди значений есть NULL, а для остальных сравнение истинно, ALL даёт неизвестность, и строка не проходит. Это та же ловушка, что и у NOT IN, рассмотренная ниже.

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

Подзапрос, ссылающийся на столбцы внешнего запроса, называется коррелированным (англ. correlated subquery). Логически он вычисляется заново для каждой строки внешнего запроса — как функция от этой строки. Сумма каждого заказа покупателя 4003:

SELECT o.order_id, o.status,
       (SELECT sum(oi.quantity * oi.price)
        FROM order_item oi
        WHERE oi.order_id = o.order_id) AS total
FROM orders o
WHERE o.customer_id = 4003
ORDER BY o.order_id;

 order_id |  status   |  total
----------+-----------+---------
     5939 | delivered | 3949.97
     8889 | delivered | 5449.97
    10737 | delivered | 4079.97
    19913 | delivered | 3059.96
(4 rows)

Ссылка o.order_id внутри подзапроса связывает его с текущей строкой внешнего запроса. Здесь коррелированный подзапрос сразу решает проблему размножения: для каждого заказа он возвращает ровно одно число, и соединять заказ с позициями не нужно. В условиях WHERE коррелированный подзапрос выражает зависимость «для этой строки существует / не существует / сравнивается с …», на чём построены полусоединение и антисоединение.

«Вычисляется для каждой строки» описывает смысл, а не способ выполнения. Ещё в 1982 году Ким показал, как преобразовывать вложенные запросы в соединения ⁹, и современные оптимизаторы, включая PostgreSQL, превращают многие коррелированные подзапросы в EXISTS/IN в полусоединения и антисоединения. Скалярный коррелированный подзапрос в списке SELECT PostgreSQL 16 выполняет как SubPlan — действительно по разу на строку, что на больших таблицах бывает медленно (тема 11).

LATERAL, полусоединение, антисоединение и деление

LATERAL: топ-3 в каждой рубрике

Подзапрос в FROM по стандарту не может ссылаться на другие таблицы того же FROM: производная таблица вычисляется независимо. Ключевое слово LATERAL (стандарт SQL:1999, PostgreSQL 9.3) снимает это ограничение: подзапрос, помеченный LATERAL, может ссылаться на столбцы таблиц, стоящих в FROM левее, и вычисляется логически для каждой их строки ⁵⁷. По сути это коррелированный подзапрос, который возвращает не одно значение, а целую таблицу — несколько строк и столбцов.

Классическая задача для LATERAL — «первые N в каждой группе». Три самые дорогие книги в каждой подрубрике раздела «Базы данных» (рубрика 13):

SELECT c.category_id, c.name, t.book_id, t.title, t.price
FROM category c
CROSS JOIN LATERAL (
        SELECT b.book_id, b.title, b.price
        FROM book b
        WHERE b.category_id = c.category_id
        ORDER BY b.price DESC, b.book_id
        LIMIT 3) AS t
WHERE c.parent_id = 13
ORDER BY c.category_id, t.price DESC, t.book_id;

 category_id |    name    | book_id |            title             |  price
-------------+------------+---------+------------------------------+---------
          21 | PostgreSQL |    1477 | Тайна чисел 1477             | 2969.99
          21 | PostgreSQL |    1849 | Основы северного ветра 1849  | 2959.99
          21 | PostgreSQL |     616 | Основы вычислений 616        | 2919.99
          22 | NoSQL      |      21 | Искусство забытого города 21 | 2949.99
          22 | NoSQL      |    1651 | Хроники северного ветра 1651 | 2949.99
          22 | NoSQL      |    1533 | Введение в алгоритмов 1533   | 2919.99
(6 rows)

Для каждой строки category подзапрос получает её category_id, сортирует книги этой рубрики и оставляет три. Рисунок поясняет, как строится результат. Сделать то же обычным подзапросом в FROM нельзя — у него нет доступа к текущей рубрике, а ORDER BY … LIMIT 3 без LATERAL дал бы три книги на всю таблицу.

Как и обычное соединение, CROSS JOIN LATERAL отбрасывает строки, для которых подзапрос пуст. По всему рубрикатору он возвращает 57 строк — по три для каждой из 19 рубрик с книгами; четыре рубрики верхнего уровня, в которых нет книг, исчезают. Чтобы их сохранить, используется внешний вариант LEFT JOIN LATERAL (…) AS t ON true — он даёт 61 строку, из них четыре с NULL вместо книги. Та же задача решается оконными функциями (row_number() OVER (PARTITION BY …), тема 5); LATERAL выгоден, когда групп мало, строк в группах много и есть индекс по ключу группы и сортировки — тогда для каждой группы читается лишь несколько строк.

EXISTS и NOT EXISTS: полусоединение и антисоединение

Предикат EXISTS (подзапрос) истинен, если подзапрос возвращает хотя бы одну строку; что именно он возвращает, не важно, поэтому принято писать SELECT 1 ⁶. Коррелированный EXISTS — прямая запись полусоединения R ⋉ S, NOT EXISTS — антисоединения R ▷ S. В отличие от соединения, EXISTS не размножает строки: покупатель, оставивший десять отзывов, войдёт в ответ один раз.

Покупатели без заказов — антисоединение — записываются по меньшей мере четырьмя способами, и на учебной базе все дают 621 строку:

-- 1. NOT EXISTS
SELECT c.customer_id FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 2. LEFT JOIN ... IS NULL
SELECT c.customer_id FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

-- 3. NOT IN
SELECT c.customer_id FROM customer c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);

-- 4. EXCEPT
SELECT customer_id FROM customer
EXCEPT
SELECT customer_id FROM orders;

Формы 1 и 2 PostgreSQL выполняет одинаково — узлом Anti Join (форму 2 — только если на NULL проверяется столбец из условия соединения; при проверке o.order_id IS NULL строится внешнее соединение с последующим фильтром, результат тот же). Самосоединение и внешние соединения в задачах «найти строки без пары» подробно разобраны в учебнике ¹¹. Форма 4 возвращает только столбцы, участвующие в разности; чтобы получить имя и город покупателя, её результат пришлось бы снова соединить с customer. Форма 3 работает здесь правильно лишь потому, что orders.customer_id объявлен NOT NULL; в общем случае она опасна.

NOT IN и NULL

Ловушка NOT IN упоминалась в теме 2; здесь она проявляется в полную силу. Условие x NOT IN (v1, v2, v3) означает x <> v1 AND x <> v2 AND x <> v3. Если хотя бы одно vi — NULL, соответствующее сравнение даёт неизвестность, и вся конъюнкция может быть только ложной или неизвестной, но не истинной. Поэтому NOT IN с подзапросом, вернувшим хотя бы один NULL, не возвращает ни одной строки ⁶³.

Найдём склады в городах, где нет издательств:

SELECT city FROM warehouse
WHERE city NOT IN (SELECT city FROM publisher);

 city
------
(0 rows)

SELECT city FROM warehouse w
WHERE NOT EXISTS (SELECT 1 FROM publisher p WHERE p.city = w.city);

    city
-------------
 Казань
 Новосибирск
(2 rows)

Правильный ответ — Казань и Новосибирск, но NOT IN вернул пустой результат: у издательства «Северный узел» город не указан, и для Казани условие превратилось в «Казань ≠ Москва И … И Казань ≠ NULL», то есть в неизвестность. NOT EXISTS такой ловушки не имеет: внутреннее условие p.city = w.city для строки с NULL просто не истинно, и строка-кандидат не находится. Практическое правило: для антисоединения использовать NOT EXISTS (или LEFT JOIN … IS NULL); NOT IN допустим только с подзапросом по столбцу NOT NULL или со списком констант. Кроме корректности, у NOT EXISTS есть и преимущество в скорости: PostgreSQL превращает его в антисоединение, а NOT IN с подзапросом — нет, потому что их семантика при NULL различается (тема 11).

Симметричная, но менее опасная особенность есть и у NOT EXISTS: строка внешнего запроса с NULL в столбце сравнения попадает в ответ антисоединения, так как пары для неё не находится. Покупатели с неизвестным городом войдут в ответ на вопрос «покупатели из городов без издательств» через NOT EXISTS и не войдут через NOT IN даже со списком без NULL. Какой ответ правилен — решает постановка задачи, и это стоит оговаривать явно.

Деление в SQL: покупатели, купившие все книги серии

В SQL нет оператора деления, и вопрос «для всех» переформулируется через двойное отрицание: «покупатель купил все книги серии» равносильно «нет такой книги серии, которую покупатель не купил» ¹⁰¹. Каждое «нет такого» записывается NOT EXISTS. Книги серии «Миры Стругацких» отмечены в attrs (оператор ->> извлекает значение из JSONB, тема 9):

SELECT c.customer_id, c.full_name
FROM customer c
WHERE NOT EXISTS (
        SELECT 1
        FROM book b
        WHERE b.attrs ->> 'series' = 'Миры Стругацких'
          AND NOT EXISTS (
                SELECT 1
                FROM orders o
                JOIN order_item oi ON oi.order_id = o.order_id
                WHERE o.customer_id = c.customer_id
                  AND oi.book_id = b.book_id))
ORDER BY c.customer_id;

Запрос возвращает 17 покупателей (первые — 1 Анна Фролов, 11 Пётр Жуков, 13 Анна Титов). Читается он изнутри наружу: самый внутренний подзапрос проверяет, купил ли покупатель c книгу b; средний ищет книгу серии, для которой такой покупки нет; внешний оставляет покупателей, для которых такой книги не нашлось.

Ту же мысль можно записать через разность: «множество книг серии минус множество книг покупателя пусто».

SELECT count(*)
FROM customer c
WHERE NOT EXISTS (
        SELECT book_id FROM book WHERE attrs ->> 'series' = 'Миры Стругацких'
        EXCEPT
        SELECT oi.book_id
        FROM orders o JOIN order_item oi ON oi.order_id = o.order_id
        WHERE o.customer_id = c.customer_id);      -- 17

Есть и третья формулировка — через подсчёт: покупатель подходит, если число различных купленных им книг серии равно числу книг серии; она требует группировки и рассматривается в теме 5. Все формулировки наследуют особенность алгебраического деления при пустом делителе: если в серии нет книг (например, серия названа с опечаткой), условие «нет такой книги, которую покупатель не купил» истинно для всех, и запрос возвращает всех 5000 покупателей, включая тех, кто не сделал ни одного заказа. Если такой ответ нежелателен, к условию добавляют проверку, что делитель не пуст, или ограничивают кандидатов покупателями, у которых есть заказы.

Эквивалентные формулировки и чтение запросов

Один вопрос — несколько запросов

SQL позволяет записать один и тот же вопрос многими способами, и умение переходить между формулировками полезно и для понимания, и для оптимизации. Возьмём вопрос «покупатели из Казани, оставившие хотя бы один отзыв с оценкой 2» и сравним пять формулировок:

Соединение отвечает на другой вопрос — «отзывы с оценкой 2, оставленные покупателями из Казани»: покупатель с тремя такими отзывами даёт три строки, отсюда 157 вместо 122. DISTINCT исправляет число строк, но, как было показано выше, только если в списке выборки есть уникальный ключ покупателя: без customer_id два разных покупателя с одинаковым именем слились бы в одну строку. IN и EXISTS — полусоединения, они выражают вопрос точнее всего, и PostgreSQL выполняет их одинаково. INTERSECT даёт верное множество номеров, но других столбцов не вернёт.

Выбор формулировки определяется смыслом вопроса. Если результату нужны столбцы обеих таблиц — соединение. Если вторая таблица служит только условием «есть / нет» — EXISTS или NOT EXISTS. Если вопрос содержит «все» — двойное NOT EXISTS. Если нужны «первые N в каждой группе» — LATERAL или оконные функции. Проверить, что две формулировки эквивалентны на данных, можно приёмом из темы 3: обе разности EXCEPT должны быть пусты. Но совпадение на одних данных не доказывает эквивалентности: формулировки с NOT IN и NOT EXISTS совпадают, пока в данных нет NULL, и расходятся, как только NULL появляется. Доказательство эквивалентности — рассуждение на уровне алгебры и трёхзначной логики, а не тест.

Как читать сложный запрос

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

1. Начать с FROM: какие таблицы соединяются, по каким условиям, какие соединения внешние; построить мысленно путь по внешним ключам и оценить, не размножаются ли строки.
2. Прочитать WHERE: какие условия относятся к левой таблице, какие к правой; нет ли условия на правую таблицу внешнего соединения, превращающего его во внутреннее.
3. Каждый подзапрос прочитать как отдельный вопрос: коррелирован ли он (есть ли ссылки на внешние таблицы), что он возвращает — одно значение, столбец, таблицу; какой операции алгебры соответствует (полусоединение, антисоединение, деление).
4. Проверить граничные случаи: пустой подзапрос (ALL, деление), NULL в подзапросе (NOT IN, ALL), строки без пары (внутреннее соединение их теряет).
5. Только после этого прочитать список SELECT, DISTINCT и ORDER BY.

Полезно также переписывать запрос в выражение алгебры: если в нём есть отрицание, оно должно стоять на месте разности или антисоединения, если есть «все» — на месте деления. Расхождение формулировки вопроса и структуры запроса обычно и указывает на ошибку.

Итоги темы

Соединения в SQL — прямое воплощение операций алгебры. CROSS JOIN — декартово произведение, JOIN … ON — тета-соединение, USING — эквисоединение по перечисленным одноимённым столбцам, NATURAL JOIN — естественное соединение, которое из-за неявного условия ломается при изменении схемы или данных и поэтому почти не используется. Соединение нескольких таблиц идёт по цепочке внешних ключей, самосоединение с псевдонимами позволяет сравнивать строки одной таблицы и спускаться по уровням дерева рубрик.

Внешние соединения сохраняют строки без пары, дополняя их NULL. Условие на правую таблицу внешнего соединения должно стоять в ON: в WHERE оно отбрасывает дополненные строки и превращает соединение во внутреннее — на учебной базе 584 строки вместо 72. Внешнее соединение с проверкой IS NULL первичного ключа — одна из формулировок антисоединения.

Соединение с таблицей «многих» размножает строки, а соединение с двумя независимыми таблицами «многих» строит произведение внутри каждой группы: сумма платежей за месяц выросла втрое. Защита — не соединять таблицу, нужную только для условия (использовать EXISTS), сводить таблицы «многих» к одной строке на ключ до соединения и сверять число строк с числом объектов вопроса, а не прятать размножение за DISTINCT.

Подзапросы бывают скалярными, табличными в FROM, коррелированными и LATERAL. EXISTS и NOT EXISTS выражают полусоединение и антисоединение, двойное NOT EXISTS — деление. Граничные случаи требуют внимания: ALL и деление истинны на пустом множестве, NOT IN с NULL в подзапросе не возвращает строк. В теме 5 к соединениям и подзапросам добавятся группировка и агрегатные функции, которые позволят строить отчёты без размножения строк, а оконные функции дадут ещё один способ решения задачи «первые N в группе».

Литература

1. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.
2. Garcia-Molina H., Ullman J. D., Widom J. Database Systems: The Complete Book. 2nd ed. — Pearson Prentice Hall, 2008.
3. Дейт К. Дж. Введение в системы баз данных. 8-е изд. — М.: Вильямс, 2005.
4. ISO/IEC 9075-2:2023. Information technology — Database languages SQL — Part 2: Foundation (SQL/Foundation). — ISO, 2023.
5. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 7.2. Table Expressions. — 2024. https://www.postgresql.org/docs/16/queries-table-expressions.html
6. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 9.23. Subquery Expressions. — 2024. https://www.postgresql.org/docs/16/functions-subquery.html
7. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. SELECT. — 2024. https://www.postgresql.org/docs/16/sql-select.html
8. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. 4.2. Value Expressions. — 2024. https://www.postgresql.org/docs/16/sql-expressions.html
9. Kim W. On Optimizing an SQL-like Nested Query. — ACM Transactions on Database Systems, 1982, Т. 7, № 3, С. 443–469, DOI: 10.1145/319732.319745.
10. Celko J. Joe Celko's SQL for Smarties: Advanced SQL Programming. 5th ed. — Morgan Kaufmann, 2014.
11. Новиков Б. А., Горшкова Е. А., Графеева Н. Г. Основы технологий баз данных. 2-е изд. — М.: ДМК Пресс, 2020.
12. Codd E. F. A Relational Model of Data for Large Shared Data Banks. — Communications of the ACM, 1970, Т. 13, № 6, С. 377–387, DOI: 10.1145/362384.362685.

Дополнительные материалы

Вопросы для самопроверки

1. В таблице A 10 строк, в таблице B — 4. Какое наименьшее и наибольшее число строк может вернуть A JOIN B ON …, A LEFT JOIN B ON … и A FULL JOIN B ON …?
2. Почему NATURAL JOIN между order_item и book возвращает меньше строк, чем JOIN … USING (book_id), и почему результат первого может измениться без изменения самого запроса?
3. Запрос SELECT … FROM customer c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'paid' должен был вернуть всех покупателей и их оплаченные заказы. Что он возвращает на самом деле и как его исправить?
4. Как с помощью FULL JOIN найти города, в которых есть склад, но нет издательства, и города, где есть издательство, но нет склада, одним запросом? Как в результате отличить «пары нет» от «город не указан»?
5. Заказ содержит 4 позиции и 2 платежа. Сколько строк даст соединение заказа с позициями и платежами? Во сколько раз будет завышена сумма платежей и во сколько — сумма позиций?
6. Чем скалярный подзапрос отличается от подзапроса в IN? Что произойдёт, если скалярный подзапрос вернёт две строки, и что — если ни одной?
7. Почему для пустого подзапроса x > ALL (…) истинно, а x > (SELECT max(…) …) — нет? Какому свойству деления соответствует первое?
8. Перечислите четыре способа записать антисоединение в SQL. Какой из них зависит от наличия NULL в подзапросе, а какой не позволяет вывести столбцы, не участвующие в разности?
9. Запрос с NOT EXISTS и запрос с NOT IN по столбцу без NULL возвращают разные строки, если во внешней таблице есть NULL в столбце сравнения. Какие строки и почему?
10. Почему коррелированный подзапрос в EXISTS планировщик PostgreSQL может выполнить как соединение, а скалярный коррелированный подзапрос в списке SELECT — обычно нет?
11. Почему подзапрос в FROM без LATERAL не может ссылаться на таблицу, стоящую в том же FROM левее? Что меняет LATERAL в порядке вычисления?
12. Запишите через двойное NOT EXISTS вопрос «авторы, у которых есть книги во всех рубриках раздела „Компьютерная литература“». Что вернёт запрос, если в разделе не окажется ни одной рубрики?
13. Как проверить на данных, что две формулировки запроса дают одинаковый результат, и почему такая проверка не доказывает эквивалентности?
14. В каком порядке удобно читать запрос из нескольких соединений и подзапросов и что проверить в первую очередь, чтобы найти ошибку размножения строк?

Глоссарий темы

Шпаргалка по инструментам

Соединения:

SELECT ... FROM a CROSS JOIN b;                               -- произведение
SELECT ... FROM a JOIN b ON b.a_id = a.id;                    -- внутреннее
SELECT ... FROM a JOIN b USING (id);                          -- по одноимённому столбцу
SELECT ... FROM a LEFT JOIN b ON b.a_id = a.id AND b.x = 1;   -- условие на b — в ON
SELECT ... FROM a LEFT JOIN b ON b.a_id = a.id WHERE a.y = 2; -- условие на a — в WHERE
SELECT ... FROM a FULL JOIN b ON b.k = a.k;                   -- строки без пары с обеих сторон
SELECT ... FROM a LEFT JOIN (b JOIN c ON c.id = b.c_id) ON b.a_id = a.id;  -- цепочка внутри внешнего
SELECT ... FROM t t1 JOIN t t2 ON t2.parent_id = t1.id;       -- самосоединение

Подзапросы и операции алгебры:

SELECT ... WHERE x > (SELECT avg(x) FROM t);                  -- скалярный, один раз
SELECT ..., (SELECT sum(...) FROM d WHERE d.m_id = m.id) FROM m;   -- коррелированный в SELECT
SELECT ... FROM (SELECT ... ) AS s WHERE s.col > 0;           -- производная таблица
SELECT ... WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);     -- полусоединение
SELECT ... WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id); -- антисоединение
SELECT ... FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL;  -- антисоединение
SELECT ... WHERE x NOT IN (SELECT y FROM b WHERE y IS NOT NULL);   -- NOT IN — только без NULL
SELECT ... WHERE x > ALL (SELECT ...);                        -- истинно для пустого подзапроса
SELECT ... FROM g CROSS JOIN LATERAL
    (SELECT ... FROM t WHERE t.g_id = g.id ORDER BY ... LIMIT 3) AS top;   -- первые N
SELECT ... FROM g LEFT JOIN LATERAL (...) AS top ON true;     -- с сохранением пустых групп
SELECT ... FROM c WHERE NOT EXISTS (                          -- деление
    SELECT 1 FROM d WHERE NOT EXISTS (
        SELECT 1 FROM r WHERE r.c_id = c.id AND r.d_id = d.id));

Проверка эквивалентности двух запросов на данных: обе разности (q1) EXCEPT (q2) и (q2) EXCEPT (q1) должны быть пусты; для мультимножеств — EXCEPT ALL.

Метакоманды и средства psql: \d таблица — столбцы, внешние ключи и раздел «Referenced by» (какие таблицы ссылаются на эту), полезный для построения пути соединения; \pset null '∅' — видимые NULL в результатах внешних соединений; EXPLAIN (COSTS OFF) запрос — увидеть узлы Semi Join, Anti Join, SubPlan, InitPlan (подробно — в теме 11).

Что почитать и посмотреть

• Документация PostgreSQL 16, раздел 7.2 «Table Expressions» — https://www.postgresql.org/docs/16/queries-table-expressions.html. Точная семантика всех видов соединений, USING, NATURAL, подзапросов в FROM и LATERAL, с примерами на маленьких таблицах; стоит прочитать подраздел о соединениях целиком.
• Документация PostgreSQL 16, раздел 9.23 «Subquery Expressions» — https://www.postgresql.org/docs/16/functions-subquery.html. Определения EXISTS, IN, NOT IN, ANY/SOME, ALL и их поведение при NULL, сформулированное явно.
• Winand M. Modern SQL — https://modern-sql.com. Статьи о LATERAL и о поддержке соединений и подзапросов разными СУБД; помогает понять, что из темы относится к стандарту, а что — к PostgreSQL.
• Winand M. Use The Index, Luke! — https://use-the-index-luke.com. Глава о соединениях объясняет на уровне индексов, почему одни формулировки выполняются быстро, а другие нет; хорошее введение к теме 11.
• Kim W. On Optimizing an SQL-like Nested Query (1982) — DOI: 10.1145/319732.319745. Классическая статья о преобразовании вложенных запросов в соединения; содержит и знаменитую ошибку с потерей строк при агрегатах, исправленную позднее другими авторами.
• Celko J. SQL for Smarties (5th ed., 2014). Главы о реляционном делении и о запросах «для всех» с разбором нескольких формулировок и их граничных случаев.
• Новиков Б. А., Горшкова Е. А., Графеева Н. Г. Основы технологий баз данных (2-е изд., 2020). Учебник на русском языке; главы о языке SQL содержат примеры соединений и подзапросов и их связь с алгеброй.
• Лекции CMU 15-445/645 Database Systems (Andy Pavlo) — https://15445.courses.cs.cmu.edu. Лекция о современном SQL даёт обзор соединений, подзапросов и LATERAL на нескольких СУБД; лекции о соединениях — материал темы 11.
• Блог depesz (Hubert Lubaczewski) — https://www.depesz.com. Серия заметок «Waiting for PostgreSQL …» показывает, в какой версии появилась каждая возможность; полезно, чтобы узнать, что изменилось в соединениях и подзапросах в новых версиях.
• Википедия, статья «Join (SQL)» (англ.) — справка по видам соединений с иллюстрациями на одних и тех же двух таблицах.

Углубление (по желанию)

Ошибка счёта: COUNT в коррелированном подзапросе

В статье 1982 года Ким предложил правила, по которым коррелированный подзапрос переписывается в соединение с предварительной агрегацией. Вскоре выяснилось, что для подзапросов с count одно из правил неверно. Запрос «рубрики, в которых меньше трёх книг» через коррелированный подзапрос WHERE (SELECT count(*) FROM book b WHERE b.category_id = c.category_id) < 3 возвращает и рубрики без книг: для них подзапрос даёт 0. Переписанный вариант «сгруппировать книги по рубрикам и соединить с рубриками» рубрик без книг не содержит вообще, так как в группировке нет пустых групп. Ошибку назвали «COUNT bug»; исправление — внешнее соединение вместо внутреннего перед группировкой (Ganski и Wong, 1987). Этот сюжет хорошо показывает, почему формально эквивалентные на первый взгляд формулировки надо проверять на граничных случаях, и почему оптимизаторы так осторожны с раскрытием подзапросов.

Почему NOT IN медленнее NOT EXISTS

Можно подумать, что NOT IN и NOT EXISTS — синтаксические варианты одного и того же. Но при NULL их семантика различается, и оптимизатор обязан это учитывать. NOT EXISTS — чистое антисоединение, и PostgreSQL выполняет его любым методом соединения: хешированием, слиянием или вложенными циклами. Для NOT IN с подзапросом PostgreSQL 16 строит узел SubPlan: если результат подзапроса помещается в work_mem, он хешируется («hashed SubPlan»), иначе подзапрос просматривается для каждой строки внешней таблицы, и стоимость становится квадратичной. Узнать, что столбец подзапроса не содержит NULL, можно из ограничений NOT NULL, но PostgreSQL 16 этим знанием для NOT IN не пользуется. Некоторые другие СУБД (например, Oracle начиная с версии 11g) умеют превращать NOT IN в антисоединение, учитывающее NULL (англ. null-aware anti join). Практический вывод не зависит от СУБД: NOT EXISTS и правильнее, и предсказуемее по скорости.

Соединения в алгебре и в SQL: что потерялось

В реляционной алгебре результат соединения — отношение, а в SQL — мультимножество строк, и эта разница проявляется в каждой ловушке темы. Естественное соединение алгебры не может «размножить» строки в смысле SQL: в отношении нет дубликатов, а соединение с двумя независимыми таблицами даёт честный ответ на вопрос о тройках «заказ — позиция — платёж». Проблема возникает, когда к результату применяют агрегат, рассчитанный на заказы. Внешних соединений в исходной алгебре Кодда не было: их ввели позже вместе с NULL, и их алгебраические свойства хуже — левое внешнее соединение не коммутативно и не всегда ассоциативно, поэтому оптимизатор может переставлять его с другими соединениями лишь при определённых условиях. Изучение этих условий — отдельная область теории оптимизации запросов (тема 11).

Лабораторная работа 4. Соединения и подзапросы

• Раздел курса: тема 4 «Соединения и подзапросы»

Введение

В лабораторной работе 3 отрабатывался запрос к одной таблице. Почти любой практический вопрос к «Книжному узлу» затрагивает несколько таблиц: название книги лежит в book, имя автора — в author, связь между ними — в book_author, а заказ, его позиции и платежи — в трёх разных таблицах. Настоящая работа отрабатывает средства SQL, которые собирают данные из нескольких таблиц: внутренние и внешние соединения, самосоединения, подзапросы всех видов, LATERAL, а также запись полусоединения, антисоединения и деления.

Главное требование работы, как и в ЛР 3, — до выполнения запроса предсказать число строк результата. Для соединений это особенно важно: ошибки здесь почти никогда не вызывают сообщений СУБД, а проявляются в неверном числе строк — потерянных строках без пары, размноженных строках при соединении «один ко многим», пустом результате NOT IN при NULL. Привычка сверять число строк с числом объектов, о которых задан вопрос, — основной инструмент самоконтроля при написании запросов.

Группировка с GROUP BY изучается в ЛР 5; здесь агрегатные функции count и sum используются только по всему результату запроса или подзапроса. Планы выполнения подробно разбираются в теме 11; в продвинутой части работы они используются лишь для того, чтобы увидеть, распознаёт ли планировщик полусоединение и антисоединение.

Работа состоит из основной части (части 1–7) и продвинутой (часть 8), которую можно выполнить по желанию.

Цель работы

Научиться формулировать на SQL запросы к нескольким таблицам с помощью соединений и подзапросов, выбирать формулировку по смыслу вопроса и предсказывать мощность результата.

После выполнения работы можно будет:

• построить путь по внешним ключам для вопроса и записать его внутренними и внешними соединениями;
• предсказать число строк соединения и объяснить потерю строк без пары или размножение строк;
• правильно разместить условия в ON и WHERE при внешнем соединении;
• записать полусоединение, антисоединение и деление через EXISTS, NOT EXISTS, IN и LEFT JOIN … IS NULL и объяснить ловушку NOT IN с NULL;
• использовать скалярные и коррелированные подзапросы и LATERAL для задачи «первые N в каждой группе»;
• найти и исправить логическую ошибку в запросе с соединениями, который выполняется без сообщений об ошибке.

Теоретический минимум

Теория изложена в теме 4: разделы «Внутреннее соединение», «Внешние соединения», «Размножение строк при соединении», «Подзапросы», «LATERAL, полусоединение, антисоединение и деление», «Эквивалентные формулировки и чтение запросов». Ниже — сжатая справка.

Правила, которые проверяются в работе: условие на правую таблицу LEFT JOIN пишется в ON, на левую — в WHERE; при антисоединении через LEFT JOIN на NULL проверяется столбец, не допускающий NULL в правой таблице; NOT IN с подзапросом, вернувшим NULL, не возвращает ни одной строки; ALL и деление истинны на пустом множестве.

Таблицы учебной базы, нужные в работе (схема bookstore), и связи между ними:

category    (category_id, parent_id → category, name)          дерево рубрик, 3 уровня
publisher   (publisher_id, name, city)
author      (author_id, full_name, birth_year, country)
book        (book_id, isbn, title, publisher_id → publisher, category_id → category,
             pub_year, pages, price, attrs, description)
book_author (book_id → book, author_id → author, author_order)  многие ко многим
warehouse   (warehouse_id, city)
stock       (warehouse_id → warehouse, book_id → book, quantity) остатки
customer    (customer_id, email, full_name, city, registered_at)
orders      (order_id, customer_id → customer, created_at, status, warehouse_id → warehouse)
order_item  (order_id → orders, book_id → book, quantity, price)
payment     (payment_id, order_id → orders, paid_at, amount, method)
review      (review_id, book_id → book, customer_id → customer, rating, body, created_at)

Столбцы, которые могут содержать NULL и важны в работе: book.publisher_id, publisher.city, customer.city, category.parent_id. Строки таблицы stock с quantity = 0 означают, что книга заведена на склад, но экземпляров нет.

Полезные метакоманды psql: \d book — описание таблицы с внешними ключами и ссылками на неё (раздел «Referenced by»), \pset null '∅' — показывать NULL видимым символом (обязательно для этой работы: иначе дополненные NULL строки внешнего соединения неотличимы от пустых строк), \x auto, \i файл.sql, \timing.

Перечень оснащения

• ОС Ubuntu 24.04 (физическая машина, виртуальная машина или WSL2), PostgreSQL 16 из лабораторной работы 1, psql.
• Учебная база bookstore масштаба scale=1 из файлов schema.sql и data.sql лабораторной работы 1.
• Пакет psycopg 3 для самопроверки: sudo apt install python3-psycopg или pip install "psycopg[binary]" в виртуальном окружении.
• Архив окружения lr-env: bugs.sql (запросы с ошибками), check.py (самопроверка), README.md.
• Ресурсы: менее 100 МБ дискового пространства, доступ в Интернет не требуется.

Порядок выполнения работы

Распакуйте архив и подключитесь к базе:

unzip lr-env.zip -d ~/lab04 && cd ~/lab04/lr-env
psql -d bookstore

В начале сеанса psql выполните:

SET search_path = bookstore;
SET TimeZone = 'Europe/Moscow';
\pset null '∅'

Работа только читает данные; если база всё же повреждена, пересоздайте её командами из README.md. Задания, отмеченные именем файла (например, «сохраните в q04.sql»), проверяются скриптом python3 check.py (или python3 check.py q04 q05 для выбранных заданий). В файле — один оператор SELECT; столбцы результата должны идти ровно в указанном порядке, имена столбцов не проверяются. Если порядок строк в задании не указан, он не проверяется.

В журнал работы по каждой части записываются: предсказания, сделанные до выполнения, тексты запросов, результаты (полностью, если строк не больше 15, иначе первые строки и общее число строк), объяснения расхождений и ответы на вопросы. Предсказание ценно не точностью, а рассуждением: в журнале должно быть видно, из чего выводилось ожидаемое число строк.

Часть 1. Внутреннее и внешнее соединение

Задание

1. Выполните \d book и \d book_author. Нарисуйте (от руки или в журнале работы) фрагмент схемы publisher — book — book_author — author с указанием, какие внешние ключи допускают NULL и какие связи «один ко многим», а какие «многие ко многим».

2. Предсказание. О данных известно: книг 2010, у 95 из них издательство не указано, у каждого издательства есть хотя бы одна книга; строк в book_author 2424, у 43 книг авторы не указаны. Не выполняя запросов, запишите ожидаемое число строк каждого из них:

   SELECT count(*) FROM book b JOIN publisher p ON p.publisher_id = b.publisher_id;
   SELECT count(*) FROM book b LEFT JOIN publisher p ON p.publisher_id = b.publisher_id;
   SELECT count(*) FROM publisher p LEFT JOIN book b ON b.publisher_id = p.publisher_id;
   SELECT count(*) FROM book b CROSS JOIN publisher p;
   SELECT count(*) FROM book b LEFT JOIN book_author ba ON ba.book_id = b.book_id;
   SELECT count(*) FROM book b
       LEFT JOIN book_author ba ON ba.book_id = b.book_id
       JOIN author a ON a.author_id = ba.author_id;
   

   Выполните запросы и сравните. Почему третий запрос возвращает столько же строк, сколько внутреннее соединение? Почему последний запрос, начинающийся с LEFT JOIN, возвращает меньше строк, чем пятый? Перепишите его так, чтобы книги без авторов сохранились.

3. Для каталога рубрики «PostgreSQL» (рубрика 21) выведите все книги рубрики с названием издательства; книги без издательства тоже должны быть в списке. Столбцы: book_id, title, название издательства. Сохраните в q01.sql. Сколько книг потерялось бы при внутреннем соединении?

4. Найдите книги, для которых в базе не указан ни один автор. Столбцы: book_id, title. Сохраните в q02.sql.

5. Выведите авторов первых десяти книг (book_id от 1 до 10). Столбцы: book_id, title, full_name; порядок: по book_id, внутри книги — по порядку авторов на обложке (author_order). Сохраните в q03.sql. Предсказание: сколько строк будет в результате, если известно, что у книг 4 и 5 по два автора, а у остальных — по одному?

Что должно получиться

Схема фрагмента базы; таблица «запрос — предсказание — факт — объяснение» по пункту 2 и исправленный последний запрос; q01.sql–q03.sql и вывод самопроверки; ответ на вопрос пункта 3.

Часть 2. Самосоединение

Задание

1. Выведите все рубрики с названием родительской рубрики; у рубрик верхнего уровня на месте родителя должен быть NULL. Столбцы: category_id, name, название родителя. Сохраните в q04.sql. Предсказание: сколько строк будет при внутреннем соединении и сколько при внешнем?
2. Сколько уровней у дерева рубрик? Ответьте одним запросом с самосоединениями (без рекурсии): выведите рубрики, у которых есть «дедушка», и проверьте, что ни у одной рубрики нет «прадедушки». Объясните, почему дерево произвольной глубины так обойти нельзя (рекурсивные запросы — в теме 6).
3. Найдите все пары соавторов — авторов, написавших хотя бы одну книгу вместе. Каждая пара выводится один раз, номер первого автора меньше номера второго. Столбцы: author_id первого, author_id второго. Сохраните в q05.sql. Предсказание: что изменится, если условие ba2.author_id > ba1.author_id заменить на ba2.author_id <> ba1.author_id, и что — если убрать его совсем? Проверьте подсчётом строк. Нужен ли в вашем запросе DISTINCT и почему: сравните число строк с ним и без него.

Что должно получиться

q04.sql, q05.sql и вывод самопроверки; запрос пункта 2 и ответ о числе уровней; предсказания и факты пункта 3 с объяснением, откуда берутся повторяющиеся пары.

Часть 3. Антисоединение и ловушка NOT IN

Задание

1. Найдите покупателей, не сделавших ни одного заказа. Столбцы: customer_id, email. Запишите запрос тремя способами — через NOT EXISTS, через LEFT JOIN … IS NULL и через NOT IN — и убедитесь, что все три дают одинаковое число строк. Один из них сохраните в q06.sql. Почему форма с NOT IN здесь работает правильно? Какое ограничение схемы это гарантирует?

2. В варианте с LEFT JOIN замените проверку o.customer_id IS NULL на o.warehouse_id IS NULL. Изменился ли результат на этих данных? Объясните, почему такая проверка тем не менее ошибочна и при каких данных она дала бы неверный ответ.

3. Предсказание. Отдел маркетинга хочет пригласить на встречу с издательствами покупателей из городов, где нет ни одного издательства. Сколько строк вернёт каждый запрос? Учтите, что у одного издательства город не указан, а у 471 покупателя не указан город.

   SELECT count(*) FROM customer c
   WHERE c.city NOT IN (SELECT city FROM publisher);
   
   SELECT count(*) FROM customer c
   WHERE NOT EXISTS (SELECT 1 FROM publisher p WHERE p.city = c.city);
   

   Выполните запросы и объясните оба результата по таблицам истинности трёхзначной логики. Какие покупатели попали во второй ответ, хотя их город неизвестен?

4. Напишите правильный запрос: покупатели с известным городом, в котором нет ни одного издательства. Столбцы: customer_id, city. Сохраните в q07.sql.

Что должно получиться

Три формулировки пункта 1 и q06.sql; ответ на вопрос пункта 2 с примером данных, на которых проверка warehouse_id ошибается; предсказания и объяснения пункта 3; q07.sql и вывод самопроверки.

Часть 4. Скалярные и коррелированные подзапросы

Задание

1. Выполните запрос, выводящий для книг 1–5 имя автора скалярным подзапросом:

   SELECT b.book_id,
          (SELECT a.full_name
           FROM book_author ba JOIN author a ON a.author_id = ba.author_id
           WHERE ba.book_id = b.book_id) AS author
   FROM book b
   WHERE b.book_id <= 5;
   

   Прочитайте сообщение об ошибке и объясните его. Для каких книг из пяти подзапрос вернул больше одной строки? Что вернул бы подзапрос для книги без автора?

2. Для покупателя 4010 выведите все его заказы с суммой заказа (сумма quantity * price позиций), вычисленной коррелированным подзапросом. Столбцы: order_id, дата заказа (created_at::date), status, сумма; порядок: по order_id. Сохраните в q08.sql. Предсказание: сколько строк вернёт запрос и сколько строк вернул бы запрос, который вместо подзапроса соединяет orders с order_item (без группировки)? Проверьте второе число.

3. Найдите книги, цена которых выше средней цены книг той же рубрики. Столбцы: book_id, category_id, price. Сохраните в q09.sql. Сравните число строк с числом книг дороже средней цены по всему каталогу (некоррелированный подзапрос). Какой подзапрос вычисляется один раз, а какой — логически для каждой строки?

4. Найдите книги, которых нет в наличии ни на одном складе: нет ни одной строки stock с положительным остатком. Столбец: book_id. Сохраните в q10.sql. Предсказание: совпадёт ли результат с запросом «книги, для которых в stock нет ни одной строки»? Проверьте и объясните разницу, вспомнив смысл строк с quantity = 0.

Что должно получиться

Сообщение об ошибке пункта 1 и объяснение; q08.sql–q10.sql и вывод самопроверки; предсказания и проверки пунктов 2–4.

Часть 5. LATERAL: первые N в каждой группе

Задание

1. Для витрины «Новинки» в каждой рубрике третьего уровня (у рубрики есть родитель, а у родителя — свой родитель) выведите три самые новые книги: по убыванию pub_year, при равном годе — по возрастанию book_id. Столбцы: category_id, book_id, title, pub_year; порядок строк: по category_id, затем по убыванию pub_year, затем по book_id. Используйте CROSS JOIN LATERAL. Сохраните в q11.sql. Предсказание: сколько строк будет в результате? Для ответа достаточно знать, сколько рубрик третьего уровня (часть 2) и есть ли в каждой из них хотя бы три книги.
2. Уберите из подзапроса второй ключ сортировки book_id. Изменился ли результат? Объясните, почему без него ответ формально не определён, и найдите рубрику, где это могло бы проявиться (несколько книг одного года на границе тройки).
3. Выполните тот же запрос для всех 23 рубрик сначала с CROSS JOIN LATERAL, затем с LEFT JOIN LATERAL (…) AS t ON true. Сравните число строк и объясните разницу. Какие рубрики появились во втором варианте и почему у них нет книг?
4. Попробуйте записать запрос пункта 1 без слова LATERAL (подзапрос в FROM, ссылающийся на c.category_id). Прочитайте сообщение об ошибке и объясните его.

Что должно получиться

q11.sql и вывод самопроверки; ответ на вопрос пункта 2; числа строк и объяснение пункта 3; сообщение об ошибке пункта 4 с объяснением.

Часть 6. Деление: «для всех»

Задание

1. Запишите на естественном языке через двойное отрицание вопрос «книги, которые есть в наличии на всех складах». Каждое «нет такого» переведите в NOT EXISTS. Найдите книги, у которых на каждом из четырёх складов положительный остаток. Столбец: book_id. Сохраните в q12.sql.
2. Найдите покупателей, делавших заказы со всех складов: для каждого склада у покупателя есть хотя бы один заказ, отгруженный с этого склада. Покупатели без заказов в ответ не входят. Столбец: customer_id. Сохраните в q13.sql.
3. Пустой делитель. В запросе пункта 1 ограничьте множество складов условием w.city = 'Омск' (такого склада нет). Предсказание: сколько книг вернёт запрос? Проверьте и объясните результат через определение деления. Как изменить запрос, чтобы при пустом множестве складов ответ был пустым?
4. Почему в пункте 2 оговорено, что покупатели без заказов не входят в ответ? Проверьте, изменится ли результат, если убрать соответствующее условие, и объясните, почему на этих данных он не изменился и при каких данных изменился бы.

Что должно получиться

Формулировка вопроса через двойное отрицание; q12.sql, q13.sql и вывод самопроверки; предсказание и объяснение пункта 3 с исправленным запросом; ответ на вопрос пункта 4.

Часть 7. Найти ошибку

Задание

Файл bugs.sql содержит четыре запроса. Каждый выполняется без сообщений об ошибке, но отвечает не на тот вопрос, который сформулирован в комментарии к нему.

1. Предсказание. Прочитайте запросы, не выполняя их. Для каждого запишите, в чём, по-вашему, ошибка: каких строк не хватает, какие лишние или почему число неверно.
2. Выполните bugs.sql (\i bugs.sql). Для каждого запроса найдите строки, на которых проявляется ошибка, и посчитайте их. Для ошибки 1 найдите книги АСТ из отчёта, у которых больше одного автора (самосоединением book_author, как в части 2), и объясните, во сколько раз завышена выручка по каждой из них. Для ошибки 2 сравните число строк с числом книг рубрики 21. Для ошибки 3 найдите строку, из-за которой результат пуст. Для ошибки 4 посчитайте число строк и число различных покупателей.
3. Исправьте запросы и сохраните исправления в q14.sql … q17.sql. Проверьте самопроверкой.
4. Для ошибки 4 предложите два исправления — через DISTINCT и через EXISTS — и объясните, почему в отчёте о сумме (ошибка 1) приём с DISTINCT был бы неправильным, а здесь допустим.

Что должно получиться

Для каждого запроса: предсказание, найденная ошибка, строки, на которых она проявляется, исправленный запрос; вывод самопроверки q14–q17; ответ на вопрос пункта 4.

Часть 8. Эквивалентные формулировки и их выполнение (продвинутая)

Задание

1. Вопрос «покупатели из Казани, сделавшие хотя бы один заказ» запишите пятью способами: соединением, соединением с DISTINCT, через IN, через EXISTS и через INTERSECT. Для каждого посчитайте строки. Проверьте попарно эквивалентность формулировок с IN, EXISTS и INTERSECT двумя разностями EXCEPT. Объясните, почему соединение даёт другое число строк.
2. Получите планы трёх формулировок антисоединения из части 3 (EXPLAIN (COSTS OFF) …). Найдите узлы Anti Join и SubPlan. Какая формулировка не превращается в антисоединение и почему планировщик не вправе выполнить её так же, как NOT EXISTS? Подробно планы выполнения разбираются в теме 11; здесь достаточно названий узлов.
3. Получите план запроса части 1, пункт 2, с LEFT JOIN … JOIN author (без исправления). Каким соединением выполнено «левое» соединение с book_author и почему планировщик вправе так поступить?
4. Напишите запрос q11.sql без LATERAL — с коррелированным подзапросом в WHERE: книга входит в ответ, если в её рубрике меньше трёх книг, которые «новее» её (с большим годом или с тем же годом и меньшим book_id). Сохраните его в отдельном каталоге как alt/q11.sql и убедитесь, что результат совпадает с эталоном (python3 check.py --dir alt q11), и сравните время выполнения обеих формулировок командой \timing. Оконные функции для той же задачи — в ЛР 5.

Что должно получиться

Пять формулировок пункта 1 с числами строк и проверкой эквивалентности; фрагменты планов пунктов 2–3 с объяснением; запрос пункта 4, вывод самопроверки и время выполнения обеих формулировок (времена приводятся как пример, на разных машинах они будут разными).

Журнал работы

Журнал работы — записи для себя в формате «предсказание — команда или запрос — фактический результат — объяснение». Для каждой части полезно записать предсказание до выполнения, текст запроса или команды, существенный фрагмент результата и объяснение расхождений. Файлы q01.sql … q17.sql и вывод check.py удобно хранить рядом. В конце стоит сформулировать несколько выводов: какие предсказания не оправдались и почему.

Контрольные вопросы

1. Почему пропущенное условие соединения в явном синтаксисе JOIN … ON вызывает синтаксическую ошибку, а в старом синтаксисе с запятой в FROM — нет? Что получится во втором случае?
2. Как оценить число строк соединения «многие к одному» по обязательному внешнему ключу и соединения «один ко многим»? Когда соединение сохраняет число строк левой таблицы?
3. Почему условие на столбец правой таблицы в WHERE превращает LEFT JOIN во внутреннее соединение? В каком единственном случае такое условие в WHERE уместно?
4. Почему внутреннее соединение после внешнего в цепочке a LEFT JOIN b … JOIN c … может отменить действие внешнего соединения? Как это исправить?
5. Какой столбец правой таблицы следует проверять на NULL при антисоединении через LEFT JOIN и почему?
6. Почему NOT IN с подзапросом, вернувшим хотя бы один NULL, не возвращает строк? Почему у NOT EXISTS такой ловушки нет и какую особенность при NULL имеет уже он?
7. Чем коррелированный подзапрос отличается от некоррелированного по смыслу и по способу выполнения? Означает ли «вычисляется для каждой строки», что СУБД действительно выполняет его для каждой строки?
8. Почему соединение с двумя независимыми таблицами «многих» искажает суммы и почему DISTINCT — плохое средство исправления?
9. Какую задачу решает LATERAL и почему её нельзя решить обычным подзапросом в FROM?
10. Как записать деление в SQL? Что вернёт запрос деления, если делитель пуст, и почему так же ведёт себя предикат ALL?
11. Чем EXISTS отличается от соединения с точки зрения мощности результата? Когда вторая таблица должна стоять в JOIN, а когда — в EXISTS?
12. Почему совпадение результатов двух формулировок на учебной базе не доказывает их эквивалентности? Приведите пример пары формулировок, которые совпадают на одних данных и расходятся на других.