JOIN в SQL: INNER, LEFT, RIGHT, FULL, CROSS

Все виды соединений таблиц с наглядными примерами, отличие WHERE от ON, самосоединение и типовые ошибки с дублированием строк.

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

-- Студенты            Оценки
-- id  fio             student_id  predmet  ball
-- 1   Иванов          1           Матан    5
-- 2   Петров          1           Физика   4
-- 3   Сидоров         2           Матан    3

INNER JOIN — только совпадения

SELECT s.fio, o.predmet, o.ball
FROM Студенты s
INNER JOIN Оценки o ON o.student_id = s.id;
-- Иванов/Матан/5, Иванов/Физика/4, Петров/Матан/3
-- Сидоров не попал: у него нет оценок

LEFT JOIN — все строки слева

SELECT s.fio, o.predmet, o.ball
FROM Студенты s
LEFT JOIN Оценки o ON o.student_id = s.id;
-- добавится строка: Сидоров / NULL / NULL

Это самый частый рабочий инструмент: «покажи всех студентов, в том числе тех, у кого ничего нет». В связке с IS NULL находит «сирот».

-- студенты вообще без оценок
SELECT s.fio
FROM Студенты s
LEFT JOIN Оценки o ON o.student_id = s.id
WHERE o.student_id IS NULL;

RIGHT и FULL

• RIGHT JOIN — зеркальный LEFT: сохраняются все строки правой таблицы. На практике почти не используют, проще поменять таблицы местами.
• FULL OUTER JOIN — все строки обеих таблиц, несовпадения заполняются NULL. В MySQL не поддерживается напрямую, эмулируется через UNION двух соединений.
• CROSS JOIN — декартово произведение, каждая строка с каждой. Нужен для генерации комбинаций (например, все пары «группа × предмет»).

ON или WHERE — в чём разница

Для INNER JOIN разницы нет. Для LEFT JOIN — принципиальная: условие в ON применяется до соединения и сохраняет строки без пары, а условие в WHERE отсекает их после и превращает LEFT в INNER.

-- Все студенты, а по Матану — оценка, если есть
SELECT s.fio, o.ball
FROM Студенты s
LEFT JOIN Оценки o ON o.student_id = s.id AND o.predmet = 'Матан';

-- А так Сидоров исчезнет — типичная ошибка
SELECT s.fio, o.ball
FROM Студенты s
LEFT JOIN Оценки o ON o.student_id = s.id
WHERE o.predmet = 'Матан';

Самосоединение

-- сотрудник и его руководитель из одной таблицы
SELECT e.fio AS сотрудник, b.fio AS руководитель
FROM Сотрудники e
LEFT JOIN Сотрудники b ON e.boss_id = b.id;

Соединение нескольких таблиц

SELECT g.название AS группа, p.название AS предмет, ROUND(AVG(o.ball), 2) AS средний
FROM Оценки o
JOIN Студенты s ON s.id = o.student_id
JOIN Группы   g ON g.id = s.группа_id
JOIN Предметы p ON p.id = o.предмет_id
GROUP BY g.название, p.название
HAVING AVG(o.ball) < 4
ORDER BY средний;

Запомните порядок выполнения: FROM и JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Поэтому в WHERE нельзя ссылаться на псевдоним из SELECT, а в HAVING — можно фильтровать по агрегатам.