Соединения таблиц: JOIN

В прошлом уроке каждый запрос смотрел в одну таблицу. Но в books у книги записан только номер автора, а имя лежит в authors. Так данные и раскладывают: каждый факт в одном месте, мы делали это в уроке про нормализацию. Чтобы увидеть книгу и её автора в одной строке, таблицы нужно соединить. Примеры работают на учебной базе магазина.

Номерок в гардеробе

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

author_id в таблице книг и есть такой номерок:

SELECT title, author_id FROM books WHERE id <= 3;

       title        | author_id
--------------------+-----------
 Мастер и Маргарита |         1
 Собачье сердце     |         1
 Пикник на обочине  |         2
(3 rows)

Номер 1 это Булгаков, номер 2 Стругацкие, но чтобы прочитать имена, надо пойти в authors и найти строки с этими id. JOIN делает это за тебя: для каждой книги находит автора с совпадающим номером и склеивает их в одну строку.

Первый JOIN

SELECT books.title, authors.name
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY books.id;

       title        |    name
--------------------+------------
 Мастер и Маргарита | Булгаков
 Собачье сердце     | Булгаков
 Пикник на обочине  | Стругацкие
 Трудно быть богом  | Стругацкие
 Шинель             | Гоголь
 Мёртвые души       | Гоголь
 Грокаем алгоритмы  | Бхаргава
 Совершенный код    | Макконнелл
 Нос                | Гоголь
(9 rows)

Разберём по частям:

• FROM books JOIN authors называет две таблицы, которые соединяем.
• ON authors.id = books.author_id это условие склейки: какой автор подходит к какой книге. Обычно здесь сравнивают внешний ключ с первичным.
• books.title и authors.name уточняют, из какой таблицы колонка. Когда таблиц две, у них могут быть колонки с одним именем.

Последнее не формальность. В обеих таблицах есть колонка id, и без уточнения PostgreSQL не знает, о какой речь:

ERROR:  column reference "id" is ambiguous

Псевдонимы таблиц

Писать books. и authors. перед каждой колонкой долго. Таблицам дают короткие псевдонимы, как колонкам:

SELECT b.title, a.name AS author, a.country
FROM books AS b
JOIN authors AS a ON a.id = b.author_id
WHERE a.country = 'США';

       title       |   author   | country
-------------------+------------+---------
 Грокаем алгоритмы | Бхаргава   | США
 Совершенный код   | Макконнелл | США
(2 rows)

Слово AS перед псевдонимом таблицы можно опустить: FROM books b. После JOIN работает всё, что мы знаем: WHERE, ORDER BY, LIMIT. Фильтр смотрит на уже склеенные строки, поэтому в нём доступны колонки обеих таблиц.