Объединение таблиц

0. Связи в базах данных: почему таблицы разделены

В реальной базе данных данные не хранятся в одной огромной таблице. Вместо этого они разделены на несколько таблиц, связанных между собой. Это называется нормализацией.

Зачем?

• Меньше дублирования: название отдела хранится один раз, а не повторяется для каждого сотрудника.
• Целостность данных: если отдел переименовали, меняем в одном месте.
• Гибкость: можно добавить отдел, в котором ещё нет сотрудников.

Первичный ключ (PRIMARY KEY, PK)

Это поле (или комбинация полей), которое уникально идентифицирует каждую строку в таблице.

В нашей таблице employees это id — у каждого сотрудника свой уникальный номер.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,  -- автоматически увеличивается
    name VARCHAR(100),
    department_id INTEGER,  -- ссылка на отдел
    ...
);

Внешний ключ (FOREIGN KEY, FK)

Это поле, которое ссылается на первичный ключ другой таблицы. Так мы связываем таблицы.

CREATE TABLE employees (
    ...
    department_id INTEGER REFERENCES departments(id)
);

department_id в employees — это внешний ключ, который указывает на id в таблице departments.

1. Наши учебные таблицы

Давай добавим к employees ещё две таблицы, чтобы было что объединять.

Таблица departments

Таблица projects

Обрати внимание: у проекта "CRM система" department_id = NULL — он пока не привязан к отделу.

Обновлённая таблица employees (теперь с department_id)

Теперь вместо названия отдела у нас department_id — ссылка на departments.id.

2. INNER JOIN: пересечение

Самый частый тип объединения. Берёт только те строки, для которых есть совпадение в обеих таблицах.

Пример: сотрудники с названиями их отделов

SELECT e.name, d.name AS department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

→ Анна (IT), Иван (Продажи), Мария (IT), Андрей (Маркетинг), Елена (Продажи), Павел (IT), Дарья (Маркетинг), Сергей (Продажи).

Как это работает:

1. Берём каждую строку из employees.
2. Ищем в departments строку, где d.id = e.department_id.
3. Если нашли — объединяем в одну строку.
4. Если не нашли — пропускаем.

💡 Псевдонимы таблиц:e и d — это короткие имена для таблиц. Удобно, чтобы не писать каждый раз employees.department_id.

⚠️ Что если бы у сотрудника department_id = NULL? Он бы не попал в результат, потому что нет совпадения.

⚠️ Что если бы в departments был отдел без сотрудников (например, HR)? Он тоже не попал бы в результат.

INNER JOIN — это пересечение двух таблиц.

3. LEFT JOIN: всё из левой таблицы + совпадения

Берёт все строки из левой таблицы (первой в запросе), а из правой — только те, где есть совпадение. Если совпадения нет, подставляет NULL.

Пример: все сотрудники, даже если у них нет отдела

SELECT e.name, d.name AS department_name, e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Результат такой же, как INNER JOIN, потому что у всех сотрудников есть department_id.

Но если бы у Сергея department_id = NULL:

→ Сергей, NULL, 85000.

Пример: все отделы, даже если в них нет сотрудников

SELECT d.name AS department_name, e.name AS employee_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id;

→ IT (Анна), IT (Мария), IT (Павел), Продажи (Иван), Продажи (Елена), Продажи (Сергей), Маркетинг (Андрей), Маркетинг (Дарья), HR, NULL.

Отдел HR попал в результат, но employee_name = NULL, потому что в этом отделе нет сотрудников.

💡 Когда использовать LEFT JOIN: когда тебе нужны все записи из основной таблицы, даже если нет связанных данных. Например: «покажи всех клиентов, даже если у них не было заказов».

4. RIGHT JOIN: всё из правой таблицы + совпадения

Работает как LEFT JOIN, но наоборот: берёт всё из правой таблицы.

SELECT e.name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Это то же самое, что:

SELECT e.name, d.name AS department_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id;

💡 Совет:RIGHT JOIN используется редко. Проще поменять таблицы местами и использовать LEFT JOIN — так запрос читается естественнее (слева направо).

5. FULL OUTER JOIN: всё из обеих таблиц

Берёт все строки из обеих таблиц. Если нет совпадения — подставляет NULL с той стороны, где совпадения нет.

SELECT e.name, d.name AS department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

→ Все сотрудники (даже если у них нет отдела) + все отделы (даже если в них нет сотрудников).

Если бы у Сергея department_id = NULL, а отдел HR был бы без сотрудников:

→ Сергей, NULL + NULL, HR.

💡 Когда использовать: когда тебе нужно увидеть все данные из обеих таблиц, даже несвязанные.

6. CROSS JOIN: декартово произведение

Объединяет каждую строку из первой таблицы с каждой строкой из второй. Без условия ON.

SELECT e.name, d.name AS department_name
FROM employees e
CROSS JOIN departments d;

→ 8 сотрудников × 4 отдела = 32 строки.

💡 Когда использовать: крайне редко. Обычно для генерации комбинаций (например, все возможные пары товаров для анализа).

⚠️ Осторожно: если таблицы большие (1000 × 1000), получишь миллион строк.

7. Объединение результатов: UNION и UNION ALL

Это не JOIN, а другой способ комбинировать данные. UNION объединяет результаты двух запросов в одну таблицу (вертикально, а не горизонтально).

UNION: убирает дубликаты

SELECT name AS person, 'employee' AS type
FROM employees
WHERE salary > 100000

UNION

SELECT name, 'department' AS type
FROM departments
WHERE budget > 3000000;

→ Анна (employee), Мария (employee), Павел (employee), IT (department).

⚠️ Важно: количество и типы столбцов должны совпадать.

UNION ALL: не убирает дубликаты (быстрее)

SELECT name FROM employees WHERE city = 'Москва'
UNION ALL
SELECT name FROM employees WHERE salary > 100000;

Если Анна подходит под оба условия, она появится дважды.

💡 Когда что использовать:

• UNION — когда нужны уникальные значения.
• UNION ALL — когда дубликаты допустимы (быстрее, не тратит ресурсы на их удаление).

8. SELF JOIN: таблица соединяется сама с собой

Иногда нужно сравнить строки внутри одной таблицы. Для этого таблицу объединяют саму с собой.

Пример: сотрудники, которые зарабатывают больше, чем их коллеги из того же отдела

SELECT e1.name AS employee, e1.salary, e2.name AS colleague, e2.salary AS colleague_salary
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.salary > e2.salary;

→ Мария (130 000) > Анна (120 000), Мария (130 000) > Павел (110 000), Анна (120 000) > Павел (110 000), Елена (90 000) > Иван (80 000) и т.д.

💡 Как это работает: мы создаём две «копии» таблицы employees (псевдонимы e1 и e2) и объединяем их по department_id.

9. Множественные JOIN: цепочки таблиц

Можно объединять больше двух таблиц.

Пример: сотрудники с их отделами и проектами

SELECT e.name AS employee, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON d.id = p.department_id;

→ Анна (IT, Сайт), Анна (IT, Мобильное приложение), Мария (IT, Сайт), Мария (IT, Мобильное приложение), Павел (IT, Сайт), Павел (IT, Мобильное приложение), Иван (Продажи, NULL), Андрей (Маркетинг, Рекламная кампания) и т.д.

⚠️ Обрати внимание: Анна, Мария и Павел появились трижды, потому что в отделе IT два проекта. Это нормальное поведение JOIN — каждая комбинация становится отдельной строкой.

💡 Совет: если не хочешь дубликатов, используй GROUP BY или подзапросы (этап 4).

10. Практика: 8 задач на этот этап

Попробуй решить самостоятельно, не подглядывая ниже.

1. Выведи имена сотрудников и названия их отделов.
2. Выведи всех сотрудников, даже если у них нет отдела (используй LEFT JOIN).
3. Выведи все отделы, даже если в них нет сотрудников.
4. Найди отделы, в которых больше 2 сотрудников.
5. Выведи проекты и названия отделов, которые их ведут (если отдел есть).
6. Найди сотрудников, которые зарабатывают больше среднего по их отделу (подсказка: SELF JOIN + подзапрос).
7. Объедини имена сотрудников и названия отделов в один список (используй UNION).
8. Найди пары сотрудников из одного отдела, где один зарабатывает больше другого.

-- 1
SELECT e.name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- 2
SELECT e.name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 3
SELECT d.name, e.name AS employee_name
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id;

-- 4
SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name
HAVING COUNT(e.id) > 2;

-- 5
SELECT p.name AS project, d.name AS department
FROM projects p
JOIN departments d ON p.department_id = d.id;

-- 6
SELECT e1.name, e1.salary, AVG(e2.salary) AS dept_avg
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
GROUP BY e1.name, e1.salary
HAVING e1.salary > AVG(e2.salary);

-- 7
SELECT name AS person, 'employee' AS type
FROM employees

UNION

SELECT name, 'department' AS type
FROM departments;

-- 8
SELECT e1.name AS employee1, e1.salary, e2.name AS employee2, e2.salary
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.salary > e2.salary;

Чеклист, что этап освоен

Ты готова к четвёртому этапу, если можешь без подсказки объяснить:

• чем INNER JOIN отличается от LEFT JOIN;
• что будет, если в LEFT JOIN нет совпадения в правой таблице;
• зачем нужны псевдонимы таблиц (e, d);
• чем UNION отличается от UNION ALL;
• когда используется SELF JOIN.