Сегодня мы сосредоточимся на выполнении внутренних и внешних соединений в SQL. Во-первых, join — это способ объединения данных из двух таблиц, которые связаны друг с другом. Чтобы настроить наши примеры, я буду использовать таблицу users и таблицу posts. Вот определения SQL.

create table users (
id SERIAL PRIMARY KEY,
name VARCHAR(30));
create table posts (
id SERIAL PRIMARY KEY,
title VARCHAR(30),
body VARCHAR(100),
userid int references users(id));

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

После вставки четырех пользователей имеем:

 id | name
----+-------
  1 | eric
  2 | bob
  3 | david
  4 | chris
(4 rows)

И мы можем написать несколько постов.

 id | title  |  body  | userid
----+--------+--------+--------
  1 | post 1 | body 1 |      1
  2 | post 2 | body 2 |      2
  3 | post 3 | body 3 |      2

В таблице сообщений вы можете увидеть, что Боб является автором двух сообщений, Эрик является автором одного сообщения, а Дэвид и Крис создали 0 сообщений.

Давайте воспользуемся отношением «один ко многим», которое мы создали между пользователями и сообщениями, для выполнения некоторых запросов.

Во-первых, давайте объединим данные из двух таблиц вместе. Мы хотели бы видеть все заголовки и тела сообщений вместе с их авторами.

select p.title, p.body, u.name from posts as p, users as u where p.userid = u.id;
 title  |  body  | name
--------+--------+------
 post 1 | body 1 | eric
 post 2 | body 2 | bob
 post 3 | body 3 | bob

Это внутреннее соединение. Мы ограничиваем данные из обеих таблиц, чтобы показывать нам только отдельные сообщения, в которых совпадает поле идентификатора.

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

select p.title, p.body, u.name from users u left join posts p on p.userid = u.id;
 title  |  body  | name
--------+--------+-------
 post 1 | body 1 | eric
 post 2 | body 2 | bob
 post 3 | body 3 | bob
 [null] | [null] | chris
 [null] | [null] | david

Который показывает те же три поста, авторами которых являются Эрик и Боб, но также показывает нам, что есть некоторые пользователи (Крис и Дэвид), которые не написали ни одного поста. Это отражено [нулевыми] записями под заголовком и телом.

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

Если мы изменим порядок, мы получим другой результат, который должен иметь смысл.

select p.title, p.body, u.name from posts p left join users u on p.userid = u.id;
 title  |  body  | name
--------+--------+------
 post 1 | body 1 | eric
 post 2 | body 2 | bob
 post 3 | body 3 | bob

Здесь мы начинаем с левой таблицы (сообщений), а затем находим соответствующие записи в таблице пользователей. Поскольку есть только три сообщения с авторами bob и eric, мы никогда не видим дополнительных данных из таблицы пользователей, потому что в сообщениях с этими идентификаторами пользователей нет соответствующей записи.

Наконец, разработчики довольно часто (я думаю) просто всегда используют левое внешнее соединение, даже если левое и правое внешние соединения делают одно и то же, но в другом порядке.

Я нашел этот рисунок в Интернете, который помогает визуализировать то, что происходит с внутренними и внешними соединениями.

На словах вы можете думать об этом так:

Внутреннее соединение вернет записи, имеющие совпадающие значения в обеих таблицах.

Левое внешнее соединение вернет все записи из левой таблицы и соответствующие записи из правой таблицы.

Правое внешнее соединение вернет все записи из правой таблицы и соответствующие записи из левой таблицы.

Полное внешнее соединение вернет все записи, если есть совпадение из любой таблицы.