Теория: Объединение обработкой пропусков

В предыдущем уроке мы немного затронули четыре разных типа соединений в SQL:

В этом уроке мы углубимся в эту тему. Мы продолжим знакомиться с темой разных соединений и уделим больше внимания FULL OUTER JOIN, а также LEFT JOIN и RIGHT JOIN.

INNER JOIN

В прошлом уроке мы подробно рассмотрели INNER JOIN. Он возвращает только те строки, которые имеют совпадения в обеих таблицах по заданным условиям связи. Тип INNER JOIN чаще всего встречается при соединении таблиц. Например, он позволяет получить список всех клиентов, которые совершали заказы:

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

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

Чтобы не потерять эти записи, нам нужны другие типы соединений.

LEFT JOIN и RIGHT JOIN

Чтобы не потерять записи, нам нужно объединить таблицы customers и orders так, чтобы нам вернулись все записи из customers и сопоставленные записи из orders.

Для этого мы используем оператор LEFT JOIN. Если соответствия между customers и orders не будет найдено, мы вернем NULL на стороне таблицы orders.

В контексте предыдущего запроса, мы можем использовать оператор LEFT JOIN следующим образом:

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

Как мы видим, синтаксис запроса ничем не отличается от предыдущего, кроме самого оператора JOIN. Сам запрос вернет такую таблицу:

Появилась строка с клиентом по имени Sarah. При этом показатель order_date по этому клиенту содержит NULL — то есть Сара не совершала никаких заказов.

Теперь мы можем отделить этого клиента от других, задав специальный фильтр после оператора WHERE:

SELECT
    customers.customer_name,
    orders.order_date
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id
WHERE order_date IS NULL;

Как видите, WHERE order_date IS NULL помогает отфильтровать результаты и вывести только строки, в которых значение order_date равно NULL.

В контексте этого запроса NULL в поле order_date означает, что у клиента не было ни одного заказа. Другими словами, запрос возвращает имена клиентов, которые пока ничего не заказывали:

В SQL LEFT JOIN выбирает все записи из левой таблицы и соответствующие записи из правой. Так же работает и RIGHT JOIN, только в обратную сторону — выбирает все записи из правой таблицы и соответствующие записи из левой. Если соответствий там нет, результат будет NULL на стороне левой таблицы.

В нашем случае можно получить список всех заказов и соответствующих клиентов. Чтобы это сделать, присоединим таблицу orders (как правую) к таблице customers (как к левой) по полю customer_id:

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

Этот запрос аналогичен тому, что мы делали ранее для LEFT JOIN, но таблицы поменялись местами.

Обратите внимание на заказ с датой 2023-01-04 в правой таблице orders. Мы не нашли соответствующего имени клиента customer_name в таблице customers и поэтому вместо имени вернули NULL.

FULL JOIN

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

Если нет соответствующих записей, то значения в этих полях будут NULL:

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

На примере все того же соединения данных по клиентам из таблицы customers и заказов из таблицы orders, мы можем с помощью FULL JOIN найти, какие клиенты еще не делали заказов, или какие заказы были сделаны клиентами не из нашей базы. Для этого используем фильтрацию WHERE … is NULL по показателю order_date:

SELECT
    customers.customer_name,
    orders.order_date
FROM customers
FULL JOIN orders
    ON customers.customer_id = orders.customer_id
WHERE order_date IS NULL;

Также можно отфильтровать по параметру customer_name:

SELECT
    customers.customer_name,
    orders.order_date
FROM customers
FULL JOIN orders
    ON customers.customer_id = orders.customer_id
WHERE customer_name IS NULL;

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

Выводы

Кратко рассмотрим основные выводы из этого урока:

1. INNER JOIN используется для соединения двух таблиц по общему полю так, что в результат попадают только те записи, для которых найдены совпадения в обеих таблицах. Так можно найти взаимосвязи между двумя наборами данных
2. LEFT JOIN и RIGHT JOIN выбирают все записи из одной таблицы (левой или правой соответственно) и соответствующие записи из другой. Если соответствующих записей нет, вместо них вставляется NULL. Так мы можем увидеть все данные из одной таблицы независимо от наличия сопоставлений в другой
3. FULL JOIN возвращает все записи из обеих таблиц, даже если нет совпадений. Это полезно для получения полного набора данных из обеих таблиц и идентификации записей, которые есть только в одной из них