JOIN в SQL на примерах: INNER, LEFT и RIGHT JOIN

Что такое JOIN и зачем он тестировщику

В реляционной базе данные об одной покупке лежат в разных таблицах: покупатель в одной, заказ в другой, платёж в третьей. Таблицы связаны общими полями. У заказа, например, есть номер покупателя customer_id, и он совпадает с id в таблице покупателей. JOIN объединяет таблицы по такому полю, и в одной строке результата видно и заказ, и того, кто его сделал.

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

По моему опыту, на собеседовании junior-тестировщика чаще всего просят написать обычный SELECT, а JOIN дают как задание со звёздочкой. Кто с ним справился, получает большой плюс, поэтому синтаксис стоит выучить заранее.

• СтатьяЗапросы SELECT: WHERE, ORDER BY и LIMIT
• СтатьяБазы данных для тестировщика: таблицы и ключи

INNER, LEFT и RIGHT JOIN: в чём разница

Я разбираю три вида объединения и показываю их на схемах из двух пересекающихся кругов. Пример в уроке построен на двух таблицах учебной базы студентов: courses с факультетами и general с именем, фамилией, возрастом и полом. Общее поле у них id. В general есть строка с id 6, которой нет в courses, а в courses есть id 7, которого нет в general.

INNER JOIN выводит пять строк, где id совпали, и обе непарные строки в результат не попадают. LEFT JOIN с таблицей courses слева выводит шесть строк: к пяти добавляется id 7, и имя с фамилией у него равны NULL. RIGHT JOIN выводит все строки general, и в результате появляется id 6 с пустым факультетом.

• INNER JOIN

  Внутреннее объединение выводит только строки, для которых пара нашлась в обеих таблицах. Слово INNER можно опустить, и просто JOIN означает то же самое.

• LEFT JOIN

  Левое внешнее объединение выводит все строки таблицы, которая стоит после FROM. Если пары справа нет, колонки правой таблицы заполняются NULL. Полное название – LEFT OUTER JOIN.

• RIGHT JOIN

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

• FULL OUTER JOIN

  Полное объединение выводит все строки обеих таблиц и ставит NULL там, где пары нет. В MySQL его нет, и такой результат собирают из LEFT JOIN и RIGHT JOIN через UNION. В PostgreSQL оно есть, в SQLite появилось в версии 3.39.

Как написать запрос с JOIN

Запрос с JOIN – это обычный SELECT. После FROM стоит первая таблица, после JOIN вторая, а после ON условие, по которому сравниваются строки. Чаще всего это равенство ключей: первичного ключа одной таблицы и внешнего ключа другой. Например, SELECT orders.id, customers.name FROM orders INNER JOIN customers ON customers.id = orders.customer_id; выводит номер каждого заказа и имя покупателя.

Перед каждой колонкой лучше писать имя таблицы через точку. Там, где колонка с одним именем есть в обеих таблицах, без этого не обойтись: запрос с голым id упадёт с ошибкой о неоднозначном имени. В остальных местах префикс помогает читать запрос. Длинные имена сокращают псевдонимами, FROM orders o JOIN customers c, и дальше пишут o.id и c.name.

Ниже места, где в запросах с JOIN легко ошибиться.

• Порядок таблиц

  Для INNER JOIN он не важен. Для LEFT и RIGHT JOIN от него зависит, строки какой таблицы попадут в результат целиком.

• Условие на правую таблицу в WHERE

  LEFT JOIN заполняет колонки непарных строк значением NULL, а условие вроде WHERE o.status = 'cancelled' такие строки отбрасывает, и запрос незаметно превращается во внутреннее объединение. Если нужны все строки левой таблицы, условие переносят в ON. Исключение – условие IS NULL: оно, наоборот, оставляет только непарные строки.

• Повторы строк

  Если у строки слева несколько пар справа, она повторится столько раз, сколько пар нашлось. В базе песочницы у заказа 111 два успешных платежа, и в объединении заказов с платежами он выводится дважды. Тестировщику такой повтор подсказывает проверить, не списали ли деньги два раза.

• Больше двух таблиц

  JOIN можно писать подряд: FROM orders o JOIN customers c ON c.id = o.customer_id JOIN payments p ON p.order_id = o.id. Каждое следующее объединение добавляет колонки к уже собранному результату.

Пример: оплаченный заказ без платежа

В SQL-песочнице лежит учебная база интернет-магазина "Полка". Для примера нужны три её таблицы: customers с покупателями, orders с заказами, их статусом и суммой и payments с платежами, у каждого из которых есть номер заказа order_id. Задача из рабочего дня тестировщика – проверить, что у каждого заказа в статусе paid есть платёж. Все запросы ниже выполняются в песочнице как есть.

• 1. Кто что заказал (INNER JOIN)

  SELECT o.id, c.name, o.status, o.amount FROM orders o INNER JOIN customers c ON c.id = o.customer_id; В результате 14 строк, по одной на заказ, и рядом с номером заказа стоит имя покупателя.

• 2. Покупатели без заказов (LEFT JOIN и IS NULL)

  SELECT c.name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL; Остаётся один покупатель, Лев Панин, который зарегистрировался и ничего не заказал. Пустое значение ищут через IS NULL, потому что сравнение = NULL не находит ни одной строки.

• 3. Оплаченные заказы без платежа

  SELECT o.id, o.status, o.amount, p.id AS payment_id FROM orders o LEFT JOIN payments p ON p.order_id = o.id WHERE o.status = 'paid' AND p.id IS NULL; Находится заказ 109 на 450: статус paid, а платежа в базе нет. Это дефект в данных, и в баг-репорт идут номер заказа и сам запрос, чтобы разработчик повторил проверку.

• 4. То же через RIGHT JOIN

  SELECT o.id, o.status, p.id AS payment_id FROM payments p RIGHT JOIN orders o ON p.order_id = o.id ORDER BY o.id; Таблицы поменялись местами, и все заказы теперь справа. В результате 16 строк: у заказов 103, 109 и 113 платежа нет, а 107 и 111 выводятся дважды, потому что платежей у них по два.

• 5. Списали больше суммы заказа (JOIN и GROUP BY)

  SELECT o.id, o.amount, SUM(p.amount) AS paid FROM orders o JOIN payments p ON p.order_id = o.id WHERE p.status = 'success' GROUP BY o.id, o.amount HAVING SUM(p.amount) > o.amount; Остаётся заказ 111: при сумме 3100 успешные платежи дали 6200, то есть деньги списали дважды. Такую проверку запускают после правок в оплате, например когда покупатель мог нажать "Оплатить" два раза подряд, и в баг-репорт идут номер заказа и номера обоих платежей, 10 и 11.

• ПесочницаПопробовать запросы прямо в браузере, без регистрации
• ПесочницаВыполнить в песочнице запрос про заказ без платежа
• ПесочницаВыполнить в песочнице запрос про двойное списание

Вложенные подзапросы

Подзапрос – это SELECT внутри другого запроса. Его берут, когда нужный набор не получается выбрать одним SELECT с условиями: внутренний запрос находит значения, а внешний использует их в своём условии. Чаще всего подзапрос стоит в WHERE после IN. Мой совет для собеседования: если задачу не удаётся решить одним SELECT, в него можно вложить второй.

В уроке я строю подзапрос на таблице general. Внешний SELECT выводит имя, фамилию, пол и id, а внутренний отбирает строки, где id больше 1 и возраст не меньше 21. В результат попадают трое студентов. Признаю, пример не самый показательный, ведь такое условие уместилось бы и в обычный WHERE.

В базе песочницы подзапрос пригодится, например, чтобы найти покупателей с отменённым заказом. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled'); Внутренний запрос вернёт номера покупателей, внешний их имена, Пётр Ильин и Игорь Лапин. Похожий результат даёт JOIN с условием на статус, но покупателя с двумя отменёнными заказами JOIN выведет дважды, а IN один раз.

Workbench, песочница и задания

В уроке я пишу запросы в MySQL Workbench, бесплатной программе с окном для запросов вместо командной строки. Её ставят тем же установщиком MySQL Installer, что и сервер: кнопка Add, раздел Applications, MySQL Workbench. Запрос в ней запускают значком молнии, а клавиша Enter только переносит строку. Workbench подчёркивает ошибки, подсказывает ключевые слова и имена таблиц и выполняет несколько запросов сразу, показывая результат каждого на своей вкладке.

Тренироваться удобно на W3Schools, где готовая база уже есть и ставить ничего не нужно. Запросы этой страницы выполняются в SQL-песочнице платформы. На YouTube двадцать второй урок завершает блок о базах данных в курсе "Тестировщик с нуля", а в бесплатном базовом курсе по тестированию есть урок на эту тему, и после теории в нём идут задания.

Шпаргалка по SQL собирает SELECT, WHERE, JOIN и GROUP BY с примерами, а в тренажёре запросы пишут на живых данных. На задачах из рабочего дня тестировщика SQL разбирает мой отдельный курс.

• Урок курсаЭтот урок с заданиями – бесплатно
• Мой видеоурокзапросы JOIN в SQL и MySQL на примерах
• ПесочницаПопробовать запросы прямо в браузере, без регистрации
• ШпаргалкаSQL, язык запросов
• Шпаргалкабазы данных
• ТренажёрSQL-запросы
• СтатьяGROUP BY, HAVING и UNION в SQL
• ПесочницаSQL для тестировщика: зачем он нужен

Курсы по теме

• бесплатный

  Тестирование ПО с нуля. Теория + Практика. Базовый уровень

  Первое рабочее место тестировщика

  89 уроков113 441
• платный

  Рабочий день тестировщика: тренажер по SQL

  Один рабочий день с SQL

  43 урока205

Вопросы и ответы