Аналитика на SQL

Теория: Объединение нескольких таблиц

Полный доступ к материалам

Зарегистрируйтесь и получите доступ к этому и десяткам других курсов

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

В таких случаях аналитики используют оператор JOIN — один из ключевых инструментов для объединения таблиц в SQL. Именно его мы изучим в этом уроке.

Оператор `JOIN`

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

В SQL есть несколько видов объединений. Для наглядности возьмем таблицы, которые мы планируем объединить и представим в виде фигур на листе бумаги. В таком случае все виды объединений можно графически изобразить так:

Untitled

На схеме выше мы видим четыре оператора, которые используются для объединения данных разными способами:

INNER JOIN — возвращает строки, которые имеют совпадения в обеих таблицах по заданным условиям. Это самый распространенный тип объединения
LEFT JOIN или LEFT OUTER JOIN — возвращает все строки из левой таблицы, а также соответствующие строки из правой таблицы по заданным условиям. Если в правой таблице нет соответствующих строк, то в результате будут отображаться NULL-значения. Это аналогично области левой таблицы
RIGHT JOIN или RIGHT OUTER JOIN — возвращает все строки из правой таблицы, а также соответствующие строки из левой таблицы по заданным условиям. Если в левой таблице нет соответствующих строк, то в результирующем наборе будут отображаться NULL-значения. Это аналогично области правой таблицы
FULL JOIN или FULL OUTER JOIN — возвращает все строки из обеих таблиц и соответствующие строки по заданным условиям. Если в одной из таблиц нет соответствующих строк, то в результирующем наборе будут отображаться NULL-значения.

Синтаксис объединений и `INNER JOIN`

Итак, для объединения данных из разных таблиц мы будем использовать операторы объединений:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

Синтаксис объединений в SQL выглядит так:

SELECT <название_колонки>
FROM <таблица1>
INNER JOIN <таблица2>
    ON <условия_объединения>;

В этом запросе после оператора FROM мы указываем название левой таблицы — то есть той таблицы, к которой присоединяем данные. После INNER JOIN указываем название правой таблицы — то есть присоединяемой таблицы. В качестве условий объединения указываем показатели, которые связывают обе эти таблицы.

Для примера возьмем нашу базу данных, в которую мы добавили новую таблицу orders.

Таблица customers

customers

Tаблица orders

orders

Выполним следующий запрос:

SELECT
    customers.customer_name,
    orders.order_date
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

Итоговая таблица будет выглядеть так:

join result

Этот запрос соединяет таблицы customers и orders с помощью INNER JOIN. Оператор INNER JOIN возвращает только те строки, где значения в столбце customer_id совпадают в обеих таблицах.

В результате этого запроса получим:

Имена клиентов — customer_name
Даты заказов — order_date

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

Иными словами, мы получим список имен клиентов и дат заказов для существующих связей между клиентами и заказами. Это помогает нам проанализировать поведение клиентов.

Допишем в этот запрос оператор AND после ON. Таким образом мы добавим дополнительное условие для объединения таблиц. Оно должно выполняться одновременно с условием объединения таблиц в операторе ON:

SELECT
    customers.customer_name,
    orders.order_date
FROM customers
INNER JOIN orders
    ON
        customers.customer_id = orders.customer_id
        AND orders.order_date > '2023-01-02';

Мы получим:

Untitled

В этом случае мы:

Проверяем равенство customer_id в таблицах customers и orders
Добавляем условие orders.order_date > '2023-01-02' с помощью оператора AND

Таким образом, запрос вернет имена клиентов customer_name и даты заказов order_date после указанной даты.

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

Отношения в таблицах

В SQL существуют три основных типа связей между таблицами:

«Один-ко-многим» (One-to-Many)
«Один-к-одному» (One-to-One)
«Многие–ко–многим» (Many-to-Many)

Умение отличать эти связи друг от друга помогает правильно использовать оператор JOIN при объединении таблиц в SQL.

Для начала рассмотрим самый распространенный тип связи — «Один-ко-многим» (One-to-Many). При таком типе связи одна запись в одной таблице связана с несколькими записями в другой таблице.

Например, у нас на складе есть десять ноутбуков одной и той же модели. Если мы продадим все ноутбуки разным людям, то эта модель будет входить в десять разных заказов.

Это означает, что каждая запись в таблице products может быть связана с несколькими записями в таблице sales.

Посмотрим на примере такого запроса:

SELECT
    products.product_name,
    sales.quantity
FROM products
INNER JOIN sales
    ON products.product_id = sales.product_id;

Выполнив этот запрос, мы увидим список товаров и количество продаж:

Untitled

Рассмотрим еще один вид связи — «Один-к-одному» (One-to-One). При такой связи каждая запись в одной таблице имеет только одну связанную запись в другой таблице.

Например, каждый клиент в таблице customers может иметь только один заказ в таблице orders. Пример SQL-запроса с такой связью выглядит так:

SELECT
    customers.customer_id,
    customers.customer_name,
    orders.order_id,
    orders.order_date
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

И вот такой результат мы получим:

Untitled

Рассмотрим третий вид связи — «Многие–ко–многим» (Many-to-Many). Этот тип связи возникает, когда множество записей в одной таблице связано с множеством записей в другой таблице. Для реализации такой связи нужна таблица-связь — это вспомогательная таблица, которая содержит связи между записями из обеих таблиц.

Рассмотрим эту связь в наших данных. Именно таким образом связаны товары и магазины — ведь множество товаров может быть связано с множеством магазинов. Для этого используется таблица-связь sales, которая содержит информацию о связи между продуктами и магазинами.

SQL-запрос может выглядеть так:

SELECT
    products.product_id,
    products.product_name,
    sales.sale_id,
    sales.quantity,
    stores.store_name
FROM products
INNER JOIN sales ON products.product_id = sales.product_id
INNER JOIN stores ON sales.store_id = stores.store_id;

Такой результат мы получим при выполнении этого запроса:

Untitled

Выводы

Сделаем краткие выводы:

Чтобы работать с аналитическими запросами в SQL, часто нужно объединять данные из нескольких таблиц
Оператор JOIN позволяет объединять строки из разных таблиц на основе заданных условий связи
В SQL существуют различные типы объединений — INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL JOIN
Связь «Один-ко-многим» (One-to-Many) означает, что одна запись в одной таблице может быть связана с несколькими записями в другой таблице
Связь «Один-к-одному» (One-to-One) означает, что каждая запись в одной таблице имеет только одну связанную запись в другой таблице
Связь «Многие–ко–многим» (Many-to-Many) означает, что каждая запись в одной таблице может быть связана с несколькими записями в другой таблице, и наоборот. Для реализации такой связи используется дополнительная таблица-связка