Извлечение данных из нескольких таблиц  —  важнейший навык в области управления базами данных. Одним из мощных инструментов, находящимся в нашем распоряжении, является join (джоин, т. е. соединение). В этой статье рассмотрим концепции, лежащие в основе join-операций, и изучим стратегии эффективного извлечения данных. Сосредоточимся на MySQL  —  одной из самых популярных систем управления реляционными базами данных.

Основа join-операций: идентификация связанных столбцов

Использование джоинов основывается на идентификации связанных столбцов в двух таблицах. Установленная связь служит мостом, который при определенных условиях позволяет соединять и извлекать общую информацию из двух таблиц. Для примера рассмотрим сценарий, в котором столбец “Номер сотрудника” является общим для двух таблиц. Этот общий столбец становится ключом к раскрытию сокровищницы данных.

Установление этой общности позволяет добавлять столбцы из обеих таблиц во выходные данные. В приведенном выше примере можно без труда получить такую информацию, как номер сотрудника, имя, номер отдела и дата начала контракта. Это открывает спектр возможностей для анализа данных и составления отчетов.

Для лучшего понимания того факта, что join-операции основаны на идентификации связанных столбцов, возьмем пример из реальной жизни. Рассмотрим сценарий, в котором есть две таблицы: “employees” (“Сотрудники”) и “salaries” (“Зарплаты”). Общим для этих таблиц является столбец “employee number” (“Номер сотрудника”), который выступает в качестве ключа для установления связи.

Вот структуры таблиц.

Таблица “employees”:

CREATE TABLE employees 
(employee_number INT PRIMARY KEY,     
employee_name VARCHAR(255),     
department_number INT,     
hire_date DATE );

Таблица “salaries”:

CREATE TABLE salaries 
(employee_number INT,     
salary_amount DECIMAL(10, 2),     
effective_date DATE );

Предположим, что нужно получить информацию о сотрудниках и их зарплатах. “Employee number” является общим столбцом для этих двух таблиц, что позволяет применить джоин.

Вот пример запроса с использованием ключевого слова INNER JOIN (внутренний джоин):

SELECT
    employees.employee_number,
    employees.employee_name,
    employees.department_number,
    employees.hire_date,
    salaries.salary_amount,
    salaries.effective_date
FROM
    employees
INNER JOIN
    salaries ON employees.employee_number = salaries.employee_number;

В этом запросе:

  • Из таблиц “employees” и “salaries” выбираются релевантные столбцы, данные которых необходимо включить в набор результатов.
  • Ключевое слово INNER JOIN используется для объединения строк из обеих таблиц, если выполняется указанное условие.
  • Условие задается в операторе ON, где указывается, что столбец “employee_number” в таблице “employees” должен совпадать со столбцом “employee_number” в таблице “salaries”.

Этот запрос позволяет получить полный набор данных, включающий информацию о сотрудниках и их зарплатах. Ключевым моментом здесь является идентификация общего столбца “employee_number” (“Номер сотрудника”), который служит связующим звеном для двух таблиц.

Практические сценарии, подобные этому, демонстрируют возможности джоинов в объединении данных из разных таблиц с обеспечением целостного представления о базе данных.

Предостережение: будьте внимательны при выборе столбцов

Прежде чем погрузиться в тонкости синтаксиса джоинов, поговорим о внимательном отношении к выбору столбцов, устанавливающих связь между таблицами. Эти столбцы должны представлять один и тот же объект, например ID (идентификатор). Однако сами таблицы не обязательно должны быть смежными (логически связанными) в реляционной модели. 

Например, смежными могут быть такие таблицы, как “Сотрудники” и “Руководитель отдела” или “Сотрудники” и “Должности”. Напротив, таблицы “Руководитель отдела” и “Сотрудники” могут не быть смежными (логически не связанными), но их все равно можно объединить, выявив связанный столбец. Главное  —  определить общую основу (например, номер сотрудника), которая связывает две таблицы.

Возможности синтаксиса джоинов

Синтаксис джоинов в MySQL предоставляет мощный способ объединения данных из разных таблиц на основе связанных столбцов. Он обеспечивает гибкость в выборе полей, необходимых для анализа, и упрощает процесс запроса сложных наборов данных. Далее рассмотрим наиболее значительный тип джоинов в MySQL  —  внутренние джоины.

Возьмем практический пример из реальной жизни. В этом сценарии имеется база данных с двумя таблицами: employees (“Сотрудники”) и departments (“Отделы”). Таблица employees содержит информацию о сотрудниках, включая номер, имя и номер отдела каждого сотрудника. Таблица departments содержит сведения о различных отделах, включая номер и название каждого отдела.

Пример сценария: получение информации о сотрудниках с подробными сведениями об отделах.

-- Создание таблицы employees
CREATE TABLE employees (
    employee_number INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_number INT
);



-- Вставка примерных данных в таблицу employees
INSERT INTO employees VALUES
    (1, 'John Doe', 101),
    (2, 'Jane Smith', 102),
    (3, 'Bob Johnson', 101),
    (4, 'Alice Williams', 103);
-- Создание таблицы departments
CREATE TABLE departments (
    department_number INT PRIMARY KEY,
    department_name VARCHAR(255)
);
-- Вставка примерных данных в таблицу departments
INSERT INTO departments VALUES
    (101, 'Engineering'),
    (102, 'Marketing'),
    (103, 'Finance');
-- Теперь выполним операцию inner join, чтобы получить информацию о сотруднике и данные об отделе
SELECT employees.employee_number, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_number = departments.department_number;

Этот пример включает:

  • Таблицу employees со столбцами employee_number (номер сотрудника), employee_name (имя сотрудника) и department_number (номер отдела).
  • Таблицу departments со столбцами department_number (номер отдела) и department_name (название отдела).

Задача  —  получить список сотрудников с их именами и названиями соответствующих отделов, используя inner join.

Разбор SQL-запроса:

  • SELECT employees.employee_number, employees.employee_name, departments.department_name: указываем столбцы, данных которых должны быть в наборе результатов.
  • FROM employees: указывает на основную таблицу, из которой хотим получить данные.
  • INNER JOIN departments ON employees.department_number = departments.department_number: в этой решающей части запроса происходит вся магия. Указываем inner join, объединяющий таблицы employees и departments на основе связанного столбца department_number. Это означает, что нас интересуют только те строки, которые совпадают в столбце department_number обеих таблиц.

Результат:

| employee_number | employee_name    | department_name |
|------------------|-------------------|------------------|
| 1                | John Doe          | Engineering      |
| 2                | Jane Smith        | Marketing        |
| 3                | Bob Johnson       | Engineering      |
| 4                | Alice Williams    | Finance          |

Результирующий набор данных объединяет информацию о сотрудниках с названиями их отделов, обеспечивая полное представление данных.

Inner joins: бесшовное объединение таблиц

Inner joins  —  самый распространенный тип джоинов, который является основой для более сложных join-операций. Он извлекает строки из двух таблиц при выполнении определенного условия их объединения. Другими словами, в результирующий набор включаются только строки из указанных таблиц с совпадающими значениями.

Теперь рассмотрим пример создания дубликатов таблиц  —  “departments” (“Отделы”) и “department managers” (“Руководители отделов”). Эти таблицы имеют общий столбец, что позволяет легко соединить их без явного указания связи с другими таблицами.

Создание дубликатов таблиц: практическое упражнение

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

Упражнение предусматривает использование реального сценария, в котором необходимо получить информацию об отделах компании и руководителях отделов. В базе данных компании есть две оригинальные таблицы: original_departments и original_department_managers. Задача  —  создать дубликаты обеих таблиц, а затем выполнить операцию inner join для извлечения необходимой информации.

Шаг 1. Создание дубликатов таблиц

-- Создание таблицы-дубликата для departments
CREATE TABLE duplicate_departments AS
SELECT * FROM original_departments;
-- Подтверждение создания таблицы-дубликата
SELECT * FROM duplicate_departments;
-- Создание таблицы-дубликата для department managers
CREATE TABLE duplicate_department_managers AS
SELECT * FROM original_department_managers;
-- Подтверждение создания таблицы-дубликата
SELECT * FROM duplicate_department_managers;

Шаг 2. Выполнение операции inner join

Теперь, когда созданы дубликаты таблиц, выполним операцию inner join, чтобы получить информацию об отделах и их руководителях на основе общего столбца  —  “department ID” (“идентификатор отдела”).

-- Выполнение операции inner join для получения информации об отделах и их руководителях
SELECT
    d.department_id,
    d.department_name,
    dm.manager_id,
    dm.manager_name
FROM
    duplicate_departments d
INNER JOIN
    duplicate_department_managers dm ON d.department_id = dm.department_id;

В этом фрагменте выбираем релевантные столбцы из обоих дубликатов таблиц (duplicate_departments и duplicate_department_managers). INNER JOIN соединяет две таблицы на основе общего столбца department_id. В результирующий набор включаются только строки из обеих таблиц с совпадающими значениями.

Шаг 3. Анализ результата

Результат операции inner join дает полное представление о каждом отделе и его руководителе. В результате вы увидите такие столбцы, как department_id, department_name, manager_id и manager_name.

Заключение

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

Читайте также:

Читайте нас в Telegram, VK и Дзен

Перевод статьи DataScience Nexus: Mastering MySQL Joins: A Comprehensive Guide to Enhancing Data Retrieval

Предыдущая статьяВнедрение зависимостей для создания элегантных горизонтальных архитектур
Следующая статьяДобавление 3D-элементов на сайты с помощью Babylon.js и Vite