SQL для тестировщика – Часть 3: JOIN

Третья часть расширенной шпаргалки по SQL: JOIN – все виды, анти-джойн, самосоединение и главные грабли. Тема номер один на собеседованиях и в проверках связности данных.

Канал: QA❤️4Life
Автор: Евгений Гусинец

SQL для тестировщика – Часть 3 из 5: JOIN

Демо-схема для всех примеров серии:

• users (id, email, status, created_at)
• orders (id, user_id, sum, status, created_at)
• payments (id, order_id, amount, created_at)

Что такое JOIN

Что делает. Соединяет две таблицы по ключу: первичный ключ одной таблицы (id) подставляется как внешний ключ в другой (user_id, order_id).

INNER JOIN

Что делает. Оставляет только строки с совпадением в обеих таблицах.

SELECT o.id, u.email, o.sum
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

Зачем. Составить отчёт «заказ + пользователь». Записи без пары исчезают из результата – помни об этом, когда считаешь.

LEFT JOIN

Что делает. Все строки левой таблицы + совпадения из правой. Нет пары – правые поля будут NULL.

SELECT u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Зачем. «Покажи всех пользователей, даже без заказов» – база для проверок полноты.

RIGHT JOIN

Что делает. Зеркально LEFT: все строки правой таблицы + совпадения слева.

Зачем. На практике почти всегда переписывают на LEFT – читается проще: «полная таблица слева, к ней пристаёгиваем».

FULL OUTER JOIN

Что делает. Всё из обеих таблиц; без пары – NULL с противоположной стороны. В MySQL не реализован.

Зачем. Сверка двух систем: расхождения видны в обе стороны – что есть только слева и что только справа.

Anti-join: записи без пары

Что делает. LEFT JOIN + проверка правого ключа на NULL = строки левой таблицы, у которых нет пары. Это не отдельный оператор, а приём.

SELECT o.id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE p.id IS NULL;

Эквивалент через NOT EXISTS – в части 4.

Зачем. Классика собеседований и ежедневных проверок: заказы без оплаты, пользователи без заказов:

SELECT u.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Self-join: таблица к самой себе

Что делает. Два алиаса одной таблицы: сравнение строк между собой.

SELECT a.id, b.id
FROM users a
JOIN users b
  ON a.email = b.email
 AND a.id < b.id;

Зачем. Дубли email с их id, сотрудники и их менеджеры в одной таблице (manager_id ссылается на ту же users).

Many-to-many через связующую таблицу

Что делает. Связь «многие ко многим» разрывают промежуточной таблицей: orders → order_items → products. Два JOIN подряд.

SELECT p.name, COUNT(*)
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name;

Зачем. Проверки корзин и составов заказов: что реально куплено.

CROSS JOIN одной строкой

Что делает. Декартово произведение – все комбинации строк. Для генерации тестовых данных: 10 статусов × 10 сумм = 100 комбинаций.

Грабли JOIN

1. Условие на правую таблицу в WHERE превращает LEFT JOIN в INNER. Строки с NULL отфильтруются. Условие – в ON:

SELECT u.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'paid';

2. Один-ко-многим дублирует строки. Пользователь с пятью заказами появится пять раз. SUM и COUNT растут. Сначала проверь: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 1.

3. JOIN по NULL-ключам ничего не находит. NULL не равен даже NULL – соединение по NULL-ключу не сработает никогда.

4. Без алиасов читаемость падает. u, o, p короче и однозначнее, чем полные имена в каждом условии.

Резюме части 3

• INNER – только совпадения, LEFT – всё слева + пары.
• Anti-join = LEFT JOIN + IS NULL – приём, а не оператор.
• Условие на правую таблицу – в ON, не в WHERE.
• JOIN один-ко-многим размножает строки – проверь перед SUM/COUNT.
• NULL-ключ не джойнится.