Раздел 35 · SQLite с нуля

INNER JOIN и LEFT JOIN

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы разложили «Пенку» по связям: заказ знает номер гостя, строка order_items знает номер заказа и номер напитка. Только номера читать неудобно. Управляющей нужен чек «Маша, капучино и эспрессо», а не «заказ 1, напитки 3 и 1». Сегодня соединяем таблицы и собираем такие ответы одним запросом.

Два списка и общий номер

Представь двух администраторов. У первого журнал заказов: номер заказа, номер гостя, способ оплаты. У второго список гостей: номер и имя. Чтобы узнать, кто сделал заказ 3, первый читает номер гостя, второй ищет этот номер у себя и называет имя. Каждая строка журнала встречается со своей строкой из списка по общему номеру.

Ровно это делает соединение. Ты говоришь, какие таблицы соединить и по какому правилу, а SQLite подбирает пары строк.

Первый JOIN

sqlite> SELECT orders.id, customers.name, orders.paid
   ...> FROM orders
   ...> JOIN customers ON customers.id = orders.customer_id
   ...> WHERE orders.id <= 4;
╭────┬──────┬───────╮
│ id │ name │ paid  │
╞════╪══════╪═══════╡
│  1 │ Маша │ карта │
│  3 │ Пётр │ карта │
│  4 │ Маша │ карта │
╰────┴──────┴───────╯

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

  1. FROM orders берёт журнал заказов.
  2. JOIN customers подключает список гостей.
  3. ON customers.id = orders.customer_id задаёт правило пары: номер гостя в заказе совпадает с номером в списке.
  4. WHERE и список столбцов работают как обычно, только теперь им доступны столбцы обеих таблиц.

У обеих таблиц есть столбец id, поэтому имя столбца уточняют таблицей через точку: orders.id, customers.name. Без уточнения SQLite не поймёт, какой id ты имеешь в виду, и вернёт ошибку.

Где заказ 2? Его сделал гость без карты, у заказа customer_id пустой. Пары для пустого номера нет, и строка выпала. Вернёмся к этому через пару разделов.

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

Писать customers. перед каждым столбцом утомительно. Таблице, как и столбцу в уроке про SELECT, можно дать короткое имя, псевдоним: FROM orders o. Дальше в запросе её столбцы зовут o.id, o.paid.

С псевдонимами удобно соединять сразу три таблицы. Соберём чеки заказов 1 и 5 с названиями напитков:

sqlite> SELECT o.id, d.name, i.qty
   ...> FROM orders o
   ...> JOIN order_items i ON i.order_id = o.id
   ...> JOIN drinks d ON d.id = i.drink_id
   ...> WHERE o.id IN (1, 5);
╭────┬──────────┬─────╮
│ id │   name   │ qty │
╞════╪══════════╪═════╡
│  1 │ Эспрессо │   1 │
│  1 │ Капучино │   1 │
│  5 │ Раф      │   2 │
│  5 │ Какао    │   1 │
╰────┴──────────┴─────╯

Каждый JOIN добавляет к строкам ещё одну таблицу. Сначала заказ встретился со своими строками из order_items, потом каждая такая строка нашла свой напиток. Заказ 1 размножился на две строки: в нём два напитка. Так и должно быть: одна строка результата это одна пара, а у заказа с двумя напитками пар две.

INNER JOIN теряет строки без пары

Просто JOIN в SQL это сокращение от INNER JOIN, внутреннего соединения. Оно оставляет только строки, у которых нашлась пара. Посчитаем, сколько заказов было и сколько осталось после соединения с гостями:

sqlite> SELECT count(*) FROM orders;
╭──────────╮
│ count(*) │
╞══════════╡
│       12 │
╰──────────╯
sqlite> SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id;
╭──────────╮
│ count(*) │
╞══════════╡
│        9 │
╰──────────╯

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

LEFT JOIN оставляет всех слева

Иногда нужны все строки одной таблицы, даже без пары. Для этого есть LEFT JOIN. Левая таблица та, что стоит в FROM, правая та, что в JOIN. Все строки левой попадают в результат. Если пары справа нет, её столбцы заполняются NULL.

Посмотрим на гостей с третьего по шестого и их заказы:

sqlite> SELECT c.name, o.id AS order_id
   ...> FROM customers c
   ...> LEFT JOIN orders o ON o.customer_id = c.id
   ...> WHERE c.id >= 3;
╭────────┬──────────╮
│  name  │ order_id │
╞════════╪══════════╡
│ Лена   │        5 │
│ Лена   │       12 │
│ Дима   │        7 │
│ Дима   │       10 │
│ Света  │ NULL     │
│ Кирилл │ NULL     │
╰────────┴──────────╯

Света и Кирилл ещё ничего не заказывали, но остались в списке с NULL вместо номера заказа. С обычным JOIN их бы не было.

Тот же приём возвращает заказы гостей без карты. Поставим заказы слева, а вместо пустого имени подставим слово через coalesce из урока про NULL:

sqlite> SELECT o.id, coalesce(c.name, 'гость') AS who
   ...> FROM orders o
   ...> LEFT JOIN customers c ON c.id = o.customer_id
   ...> WHERE o.paid = 'наличные';
╭────┬───────╮
│ id │  who  │
╞════╪═══════╡
│  2 │ гость │
│  6 │ гость │
│  9 │ Пётр  │
╰────┴───────╯

Найти строки без пары

NULL в правой части дают ещё один приём: найти тех, у кого пары нет совсем. Соединяем через LEFT JOIN и оставляем строки, где справа пусто:

sqlite> SELECT c.name
   ...> FROM customers c
   ...> LEFT JOIN orders o ON o.customer_id = c.id
   ...> WHERE o.id IS NULL;
╭────────╮
│  name  │
╞════════╡
│ Света  │
│ Кирилл │
╰────────╯

Это гости, которым стоит отправить приглашение на первый кофе. Проверяем именно o.id: у настоящего заказа номер есть всегда, значит NULL там бывает только у строк без пары.

Так же ищется напиток, который ни разу не заказывали:

sqlite> SELECT d.name
   ...> FROM drinks d
   ...> LEFT JOIN order_items i ON i.drink_id = d.id
   ...> WHERE i.order_id IS NULL;
╭─────────────╮
│    name     │
╞═════════════╡
│ Матча-латте │
╰─────────────╯

Матча-латте ещё без цены и без единой продажи. Другие способы решить ту же задачу разобраны в уроке про антисоединения.

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

Последняя ловушка. Хотим всех гостей и их заказы за наличные, если они были. Кажется, хватит дописать условие в WHERE:

sqlite> SELECT c.name, o.id AS cash_order
   ...> FROM customers c
   ...> LEFT JOIN orders o ON o.customer_id = c.id
   ...> WHERE o.paid = 'наличные';
╭──────┬────────────╮
│ name │ cash_order │
╞══════╪════════════╡
│ Пётр │          9 │
╰──────┴────────────╯

Остался один Пётр. WHERE работает после соединения: у гостей без подходящего заказа o.paid равен NULL, сравнение с 'наличные' не выполняется, и строки отбрасываются. LEFT JOIN превратился в обычный.

Условие на правую таблицу нужно перенести в ON. Тогда оно решает, какие заказы считать парой, а гости слева остаются все:

sqlite> SELECT c.name, o.id AS cash_order
   ...> FROM customers c
   ...> LEFT JOIN orders o ON o.customer_id = c.id AND o.paid = 'наличные';
╭────────┬────────────╮
│  name  │ cash_order │
╞════════╪════════════╡
│ Маша   │ NULL       │
│ Пётр   │          9 │
│ Лена   │ NULL       │
│ Дима   │ NULL       │
│ Света  │ NULL       │
│ Кирилл │ NULL       │
╰────────┴────────────╯

Эта разница подробно разобрана в уроке про ON и WHERE в LEFT JOIN, а остальные виды соединений в уроке про типы JOIN. В PostgreSQL соединения работают так же, сравни с уроком про JOIN.

Попробуй сам

В песочнице два запроса: заказы с именем бариста и гостя, и сотрудники вместе с их заказами.

Второй запрос находит сотрудника, который не принял ни одного заказа. Поменяй в первом запросе LEFT JOIN на JOIN и посмотри, какие заказы пропадут.

Проверь себя

Задание

Дальше

Соединение отвечает на вопрос «покажи рядом». А как спросить «какие напитки дороже среднего» или «гости, у которых есть заказ за наличные»? Для этого один запрос вкладывают в другой: следующий урок про подзапросы.

домашка

Домашка