Соединения и деление

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

Данные на весь урок:

students          grades
id | name         student_id | course | mark
---+------        -----------+--------+-----
 1 | Петя                  1 | SQL    |    5
 2 | Аня                   1 | Python |    3
 3 | Олег                  2 | SQL    |    4

У Олега оценок пока нет, это пригодится.

Соединение по условию

Аналогия: гардероб. У каждого пальто номерок, у каждого гостя номерок. Чтобы вернуть пальто, гардеробщик не перебирает все пары «гость и пальто», он ищет пальто с тем же номером. Соединение делает то же: склеивает строки двух таблиц, у которых совпадает признак.

Соединение обозначают знаком ⋈, а условие пишут рядом. По определению это произведение плюс выборка:

students ⋈ id = student_id grades   =   σ id = student_id (students × grades)

id | name | student_id | course | mark
---+------+------------+--------+-----
 1 | Петя |          1 | SQL    |    5
 1 | Петя |          1 | Python |    3
 2 | Аня  |          2 | SQL    |    4

В SQL:

SELECT *
FROM students
JOIN grades ON students.id = grades.student_id;

Важная мысль: определение через произведение описывает, что получится, а не как СУБД это считает. Никакая разумная СУБД не строит миллион пар, чтобы потом выбросить почти все. Она ищет совпадения умнее, например по индексу, как гардеробщик по номерку. Это снова декларативность: ты описал результат, способ выбирает СУБД.

Условие в соединении может быть любым сравнением, тогда его называют тета-соединением. Но в жизни почти всегда это равенство ключей, и такое соединение называют эквисоединением.

Естественное соединение

Если бы ключ в students назывался не id, а student_id, как в grades (переименование ρ из урока 7 делает это одной операцией), условие можно было бы не писать: соединяем по колонкам с одинаковыми именами. Такую операцию называют естественным соединением, и пишут её знаком ⋈ без условия. Одинаковая колонка остаётся в результате один раз.

SELECT * FROM students NATURAL JOIN grades;              -- соединит по всем одноимённым колонкам
SELECT * FROM students JOIN grades USING (student_id);   -- то же, но колонка названа явно

В теории это короче, на практике опасно. Если завтра в обе таблицы добавят колонку created_at, естественное соединение молча начнёт сравнивать и её, и запрос вернёт пустоту. Поэтому в рабочем коде условие пишут явно, через ON или USING (student_id).