ШПАРГАЛКА

SQL JOIN и подзапросы

Шпаргалка по SQL JOIN: INNER, LEFT, RIGHT, FULL, CROSS, SELF. ON и USING, подзапросы, CTE (WITH), UNION и типичные ошибки с примерами.

Соединения (JOIN) и подзапросы — это то, без чего реляционная база остаётся набором разрозненных таблиц. Эта шпаргалка показывает, какие строки возвращает каждый вид JOIN, в чём разница между ON и USING, как вкладывать запросы друг в друга, использовать CTE и операции над множествами, а также какие грабли чаще всего ломают результат.

Что такое соединение таблиц

JOIN объединяет строки из двух (и более) таблиц по условию связи — обычно по совпадению ключей. Вместо того чтобы хранить все данные в одной широкой таблице, реляционная модель разносит их по таблицам (например, сотрудники и отделы), а JOIN на лету «склеивает» их обратно.

Будем работать с двумя таблицами на протяжении всей шпаргалки:

-- Таблица employees
-- id | name    | dept_id
-- ---+---------+--------
--  1 | Анна    | 10
--  2 | Борис   | 20
--  3 | Вера    | 10
--  4 | Глеб    | NULL     -- сотрудник без отдела

-- Таблица departments
-- id | title
-- ---+--------------
-- 10 | Разработка
-- 20 | Маркетинг
-- 30 | Логистика     -- отдел без сотрудников

Связь идёт по employees.dept_id = departments.id. Обратите внимание на «крайние» строки: у Глеба нет отдела (NULL), а у Логистики нет сотрудников — именно они показывают разницу между видами JOIN.

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

INNER JOIN возвращает только те пары строк, где условие связи выполнено в обеих таблицах. Строки без пары отбрасываются. Это самый частый вид соединения; слово INNER можно опустить — просто JOIN означает то же самое.

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- результат:
-- name  | title
-- ------+------------
-- Анна  | Разработка
-- Борис | Маркетинг
-- Вера  | Разработка
-- (Глеб выпал — у него dept_id = NULL;
--  Логистика выпала — в ней нет сотрудников)

Какие строки попадают в результат:

СтрокаЕсть пара?В результате?
Анна (dept 10)да (Разработка)да
Глеб (dept NULL)нетнет
Логистика (нет людей)нетнет

LEFT JOIN — все из левой таблицы

LEFT JOIN (он же LEFT OUTER JOIN) возвращает все строки левой таблицы, а к ним — совпадения из правой. Если пары нет, поля правой таблицы заполняются NULL. Левая таблица — та, что идёт до слова JOIN.

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- результат:
-- name  | title
-- ------+------------
-- Анна  | Разработка
-- Борис | Маркетинг
-- Вера  | Разработка
-- Глеб  | NULL        -- остался, но title = NULL
-- (Логистика по-прежнему не показана — она в правой таблице)

Частый приём — найти «сирот»: строки левой таблицы без пары. Ищем по IS NULL на столбце правой таблицы:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

-- результат:
-- name
-- ------
-- Глеб

RIGHT JOIN — все из правой таблицы

RIGHT JOIN — зеркальное отражение LEFT: возвращает все строки правой таблицы и подходящие из левой. На практике используется реже, потому что любой RIGHT JOIN можно переписать как LEFT, поменяв таблицы местами (это читается естественнее).

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- результат:
-- name  | title
-- ------+------------
-- Анна  | Разработка
-- Вера  | Разработка
-- Борис | Маркетинг
-- NULL  | Логистика   -- отдел остался, сотрудника нет
-- (Глеб выпал — он в левой таблице без пары)

-- Эквивалент через LEFT JOIN (предпочтительнее):
SELECT e.name, d.title
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id;

FULL OUTER JOIN — все строки обеих таблиц

FULL OUTER JOIN возвращает все строки и слева, и справа. Там, где пары нет, недостающая сторона заполняется NULL. Это объединение результатов LEFT и RIGHT.

SELECT e.name, d.title
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- результат:
-- name  | title
-- ------+------------
-- Анна  | Разработка
-- Борис | Маркетинг
-- Вера  | Разработка
-- Глеб  | NULL        -- сотрудник без отдела
-- NULL  | Логистика   -- отдел без сотрудников

MySQL не поддерживает FULL OUTER JOIN напрямую — там его эмулируют через UNION результатов LEFT и RIGHT JOIN. В PostgreSQL, SQL Server, Oracle он работает «из коробки».

CROSS JOIN — декартово произведение

CROSS JOIN соединяет каждую строку левой таблицы с каждой строкой правой — без условия связи. Результат содержит N × M строк. Полезен для генерации всех комбинаций (например, размер × цвет), но опасен: забытое условие связи легко превращает обычный JOIN в случайный CROSS.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- 3 размера × 2 цвета = 6 строк всех сочетаний

-- Скрытый CROSS JOIN (старый синтаксис без условия) — частая ошибка:
SELECT e.name, d.title
FROM employees e, departments d;   -- 4 × 3 = 12 строк, мусор!

SELF JOIN — таблица сама с собой

SELF JOIN — это соединение таблицы с самой собой, обязательно с разными псевдонимами (алиасами). Применяется для иерархий и сравнения строк внутри одной таблицы. Допустим, в employees есть колонка manager_id, ссылающаяся на id руководителя.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- результат:
-- employee | manager
-- ---------+--------
-- Анна     | Борис
-- Вера     | Борис
-- Борис    | NULL     -- у Бориса нет руководителя

Здесь e и m — две «копии» одной таблицы; LEFT JOIN сохраняет даже сотрудников без менеджера.

Сравнение видов JOIN

Сводная таблица: какие строки возвращает каждое соединение (L — левая таблица, R — правая).

JOINСовпавшие L+RL без парыR без пары
INNER JOINданетнет
LEFT JOINдада (R = NULL)нет
RIGHT JOINданетда (L = NULL)
FULL OUTER JOINдада (R = NULL)да (L = NULL)
CROSS JOINвсе комбинации L × R, условия нет

Словесные «диаграммы» (как в кругах Венна):

  • INNER — только пересечение: записи, у которых есть пара по обе стороны.
  • LEFT — весь левый круг: пересечение плюс «одинокие» строки слева.
  • RIGHT — весь правый круг: пересечение плюс «одинокие» строки справа.
  • FULL — оба круга целиком: пересечение и все одинокие строки с обеих сторон.
  • CROSS — не круги Венна вовсе, а прямоугольник всех пар «каждый с каждым».

Условие соединения: ON и USING

ON задаёт произвольное условие связи — самый гибкий вариант. USING — сокращение, когда столбцы в обеих таблицах называются одинаково: пишем имя столбца один раз, и в результате он не дублируется.

-- ON: явное условие, столбцы могут называться по-разному
SELECT *
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- USING: столбец называется одинаково в обеих таблицах (dept_id)
SELECT *
FROM employees
JOIN departments USING (dept_id);
-- столбец dept_id в выводе будет один, а не два

Есть ещё NATURAL JOIN — он автоматически соединяет по всем одноимённым столбцам. На практике его избегают: добавление столбца с совпадающим именем может молча изменить условие соединения и сломать запрос. Лучше явный ON или USING.

СпособКогдаДублирует столбец?
ONлюбое условие, разные именада (оба столбца)
USINGодноимённые столбцынет (один столбец)
NATURALпочти никогда (рискованно)нет

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

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

SELECT e.name, d.title AS dept, p.title AS project
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN projects p ON p.dept_id = d.id;

-- Можно смешивать виды: важно помнить, что INNER JOIN
-- после LEFT JOIN способен «отфильтровать» NULL-строки.
-- Если хотите сохранить сотрудников без проекта — используйте LEFT и дальше:
SELECT e.name, p.title AS project
FROM employees e
LEFT JOIN projects p ON p.dept_id = e.dept_id;

Подзапросы: в SELECT, WHERE, FROM

Подзапрос — это запрос внутри запроса. Он может стоять в разных местах.

В WHERE — фильтрация по результату вложенного запроса:

-- Сотрудники из отделов, где более одного человека
SELECT name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM employees
    GROUP BY dept_id
    HAVING COUNT(*) > 1
);
-- результат: Анна, Вера (отдел 10)

В SELECT — скалярный подзапрос как вычисляемая колонка (должен вернуть одно значение):

SELECT d.title,
       (SELECT COUNT(*)
        FROM employees e
        WHERE e.dept_id = d.id) AS people
FROM departments d;

-- результат:
-- title      | people
-- -----------+-------
-- Разработка | 2
-- Маркетинг  | 1
-- Логистика  | 0

В FROM — подзапрос как временная таблица (производная таблица), которой обязательно дают алиас:

SELECT t.dept_id, t.cnt
FROM (
    SELECT dept_id, COUNT(*) AS cnt
    FROM employees
    GROUP BY dept_id
) AS t
WHERE t.cnt > 1;

Коррелированные подзапросы

Коррелированный подзапрос ссылается на столбцы внешнего запроса и поэтому выполняется заново для каждой строки. Он мощный, но может быть медленным на больших таблицах. Часто используется с EXISTS.

-- Отделы, в которых есть хотя бы один сотрудник
SELECT d.title
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.id   -- ссылка на d из внешнего запроса
);
-- результат: Разработка, Маркетинг (Логистика выпала)

-- Отделы БЕЗ сотрудников — через NOT EXISTS
SELECT d.title
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);
-- результат: Логистика

CTE: WITH — именованные подзапросы

CTE (Common Table Expression, обобщённое табличное выражение) — это подзапрос, которому через WITH дали имя в начале запроса. Делает сложные запросы читаемее, чем вложенные подзапросы в FROM, и позволяет ссылаться на одно выражение несколько раз.

WITH dept_counts AS (
    SELECT dept_id, COUNT(*) AS cnt
    FROM employees
    GROUP BY dept_id
)
SELECT d.title, dc.cnt
FROM departments d
JOIN dept_counts dc ON dc.dept_id = d.id
WHERE dc.cnt > 1;

-- Несколько CTE перечисляются через запятую:
WITH active AS (
    SELECT * FROM employees WHERE dept_id IS NOT NULL
),
big_dept AS (
    SELECT dept_id FROM active GROUP BY dept_id HAVING COUNT(*) > 1
)
SELECT * FROM active WHERE dept_id IN (SELECT dept_id FROM big_dept);

CTE бывают и рекурсивными (WITH RECURSIVE) — для обхода иерархий и деревьев, например построения цепочки «сотрудник → менеджер → менеджер менеджера».

UNION, INTERSECT, EXCEPT — операции над множествами

Эти операторы объединяют результаты двух SELECT-запросов (а не таблицы по строкам). Требование: одинаковое число столбцов и совместимые типы.

  • UNION — объединение, дубликаты убираются.
  • UNION ALL — объединение со всеми дубликатами (быстрее, не сортирует).
  • INTERSECT — только строки, присутствующие в обоих запросах.
  • EXCEPT (в Oracle — MINUS) — строки первого запроса, которых нет во втором.
-- Все названия из двух источников без дублей
SELECT name FROM employees_msk
UNION
SELECT name FROM employees_spb;

-- UNION ALL сохраняет дубли (и работает быстрее)
SELECT name FROM employees_msk
UNION ALL
SELECT name FROM employees_spb;

-- Кто есть и там, и там
SELECT name FROM employees_msk
INTERSECT
SELECT name FROM employees_spb;

-- Кто в Москве, но не в Питере
SELECT name FROM employees_msk
EXCEPT
SELECT name FROM employees_spb;

JOIN против UNION: JOIN добавляет столбцы (соединяет таблицы «вширь» по условию), а UNION добавляет строки (складывает результаты «ввысь»). Не путайте их.

Типичные ошибки

1. Дубликаты из-за связи «один ко многим». Если правая таблица содержит несколько строк на одну строку левой, JOIN размножит левые строки. Это не баг JOIN, а ожидаемое поведение — но при подсчёте сумм даёт завышенные результаты.

-- Если у отдела 3 проекта, каждый сотрудник отдела
-- появится 3 раза. SUM(salary) утроится!
SELECT d.title, SUM(e.salary)
FROM departments d
JOIN employees e ON e.dept_id = d.id
JOIN projects p ON p.dept_id = d.id   -- размножает строки
GROUP BY d.title;
-- Решение: агрегировать проекты в подзапросе ДО join,
-- либо использовать COUNT(DISTINCT ...) / отдельные запросы.

2. NULL и LEFT JOIN: условие в WHERE «убивает» внешнее соединение. Если перенести условие на правую таблицу из ON в WHERE, LEFT JOIN превращается в INNER — строки с NULL отфильтруются.

-- ОШИБКА: WHERE по правой таблице отбрасывает NULL-строки,
-- и LEFT JOIN фактически становится INNER JOIN
SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.title = 'Разработка';   -- Глеб (NULL) выпал!

-- ПРАВИЛЬНО: дополнительное условие переносим в ON,
-- чтобы сохранить все строки левой таблицы
SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.id AND d.title = 'Разработка';

3. Сравнение с NULL через =. NULL = NULL даёт не TRUE, а NULL. Для проверки на NULL используйте IS NULL / IS NOT NULL, а не = NULL.

4. Забытое условие соединения превращает запрос в неявный CROSS JOIN и раздувает результат до N × M строк. Всегда проверяйте, что у каждого JOIN есть ON.

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

Поддержать проект