Оператор JOIN

Андрей Аксенов

Contents

Обзор синтаксиса
Предварительные требования
Типы соединения
Условия соединения
Самосоединение
Соединение нескольких таблиц

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

Оператор CROSS JOIN возвращает все возможные комбинации строк из <table1> и <table2> (декартово произведение двух таблиц). Синтаксис оператора следующий:

-- CROSS JOIN
<table1> CROSS JOIN <table2>

-- или неявный CROSS JOIN
<table1>, <table2>

Синтаксис с использованием запятой эквивалентен явному CROSS JOIN. Это единственный тип соединения, который не требует условия соединения.

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

-- ON
<table1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2> ON <join_condition>

-- USING
<table1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2> USING (join_column [, ...])

-- NATURAL
<table1> NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <table2>

Поддерживаются следующие типы соединения:

[INNER] JOIN — возвращает только строки с совпадающими значениями в обеих таблицах.
LEFT [OUTER] JOIN — возвращает все строки из левой таблицы и совпадающие строки из правой; несовпадающие столбцы правой таблицы заполняются значениями NULL.
RIGHT [OUTER] JOIN — возвращает все строки из правой таблицы и совпадающие строки из левой; несовпадающие столбцы левой таблицы заполняются значениями NULL.
FULL [OUTER] JOIN — возвращает все совпадающие и несовпадающие строки из обеих таблиц; несовпадающие столбцы с обеих сторон заполняются значениями NULL.

ПРИМЕЧАНИЕ

Ключевые слова INNER и OUTER являются необязательными. JOIN по умолчанию означает INNER JOIN, тогда как LEFT JOIN, RIGHT JOIN и FULL JOIN работают так же, как и соответствующие варианты с OUTER.

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

ON — использует boolean-выражение для определения совпадающих строк, аналогично условию в WHERE.
USING — сопоставляет строки по одному или нескольким столбцам с одинаковыми именами в обеих таблицах.
NATURAL — автоматически соединяет таблицы по всем столбцам с одинаковыми именами без явного перечисления этих столбцов.

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте три связанных таблицы и заполните их данными:

categories

Создайте таблицу для хранения различных категорий товаров:

CREATE TABLE categories
(
    category_code TEXT PRIMARY KEY,
    category_name TEXT
)
    DISTRIBUTED REPLICATED;

Добавьте данные:

INSERT INTO categories
VALUES ('elec', 'Electronics'),
       ('furn', 'Furniture');

products

Создайте таблицу для хранения информации о каждом товаре, включая категорию, к которой он относится:

CREATE TABLE products
(
    id            INT,
    product_name  TEXT,
    category_code TEXT REFERENCES categories (category_code)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO products
VALUES (1, 'Laptop', 'elec'),
       (2, 'Headphones', 'elec'),
       (3, 'Smartphone', 'elec'),
       (4, 'Desk Chair', 'furn'),
       (5, 'Dining Table', 'furn');
-- товар с id = 5 отсутствует в таблице 'sales'

sales

Создайте таблицу, содержащую записи о продажах:

CREATE TABLE sales
(
    id         INT,
    product_id INT,
    date       DATE
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (product_id);

Добавьте данные:

INSERT INTO sales
VALUES (101, 1, '2025-01-05'),
       (102, 2, '2025-01-10'),
       (103, 4, '2025-01-15'),
       (104, 3, '2025-01-20'),
       (105, 6, '2025-02-01');
-- товар с id = 6 отсутствует в таблице 'products'

Этот SQL-запрос демонстрирует использование CROSS JOIN:

Явный

SELECT categories.category_name,
       products.product_name
FROM categories
         CROSS JOIN products;

Неявный

SELECT categories.category_name,
       products.product_name
FROM categories,
     products;

В выводе каждая строка из таблицы categories объединяется с каждой строкой из таблицы products:

 category_name | product_name
---------------+--------------
 Electronics   | Laptop
 Furniture     | Laptop
 Electronics   | Smartphone
 Electronics   | Desk Chair
 Furniture     | Smartphone
 Furniture     | Desk Chair
 Electronics   | Dining Table
 Furniture     | Dining Table
 Electronics   | Headphones
 Furniture     | Headphones
(10 rows)

Этот запрос использует INNER JOIN и возвращает только те товары, для которых существуют соответствующие записи о продажах:

JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         JOIN sales ON products.id = sales.product_id;

INNER JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         INNER JOIN sales ON products.id = sales.product_id;

Запрос объединяет строки из таблиц products и sales по общему столбцу product_id, исключая товары, для которых нет продаж. Результат выглядит так:

 id | product_name | sale_id | sale_date
----+--------------+---------+------------
  3 | Smartphone   |     104 | 2025-01-20
  4 | Desk Chair   |     103 | 2025-01-15
  1 | Laptop       |     101 | 2025-01-05
  2 | Headphones   |     102 | 2025-01-10
(4 rows)

Данный запрос использует LEFT JOIN, чтобы вернуть все товары, включая те, для которых нет соответствующих записей о продажах:

LEFT JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         LEFT JOIN sales ON products.id = sales.product_id;

LEFT OUTER JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         LEFT OUTER JOIN sales ON products.id = sales.product_id;

Результат включает все товары; для товаров, у которых нет продаж, отображаются значения NULL:

 id | product_name | sale_id | sale_date
----+--------------+---------+------------
  3 | Smartphone   |     104 | 2025-01-20
  4 | Desk Chair   |     103 | 2025-01-15
  1 | Laptop       |     101 | 2025-01-05
  5 | Dining Table |         |
  2 | Headphones   |     102 | 2025-01-10
(5 rows)

Чтобы найти товары без продаж, отфильтруйте строки, для которых sales.id — NULL:

SELECT products.id,
       products.product_name
FROM products
         LEFT JOIN sales ON products.id = sales.product_id
WHERE sales.id IS NULL;

Результат выглядит так:

 id | product_name
----+--------------
  5 | Dining Table
(1 row)

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

RIGHT JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         RIGHT JOIN sales ON products.id = sales.product_id;

RIGHT OUTER JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         RIGHT OUTER JOIN sales ON products.id = sales.product_id;

Результат показывает, что нет товара с sale_id, равным 105:

 id | product_name | sale_id | sale_date
----+--------------+---------+------------
  1 | Laptop       |     101 | 2025-01-05
  4 | Desk Chair   |     103 | 2025-01-15
  3 | Smartphone   |     104 | 2025-01-20
    |              |     105 | 2025-02-01
  2 | Headphones   |     102 | 2025-01-10
(5 rows)

В приведенном примере FULL JOIN возвращает все товары и все продажи, включая несовпадающие строки из обеих таблиц:

FULL JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         FULL JOIN sales ON products.id = sales.product_id;

FULL OUTER JOIN

SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         FULL OUTER JOIN sales ON products.id = sales.product_id;

Результат должен выглядеть так:

 id | product_name | sale_id | sale_date
----+--------------+---------+------------
  3 | Smartphone   |     104 | 2025-01-20
  4 | Desk Chair   |     103 | 2025-01-15
  1 | Laptop       |     101 | 2025-01-05
  5 | Dining Table |         |
    |              |     105 | 2025-02-01
  2 | Headphones   |     102 | 2025-01-10
(6 rows)

Условие соединения определяет, какие строки из двух исходных таблиц соответствуют друг другу. Вы можете задать это условие с помощью выражения ON, USING или NATURAL.

Выражение ON позволяет указать условие, по которому сопоставляются строки из двух таблиц. В приведенном ниже примере сопоставляются значения из столбцов products.id и sales.product_id:

SELECT products.product_name,
       sales.date AS sale_date
FROM products
         JOIN sales ON products.id = sales.product_id;

Результат должен выглядеть так:

 product_name | sale_date
--------------+------------
 Headphones   | 2025-01-10
 Smartphone   | 2025-01-20
 Desk Chair   | 2025-01-15
 Laptop       | 2025-01-05
(4 rows)

В выражении ON можно указать несколько условий. В этом примере добавляется условие фильтрации по столбцу sales.date с помощью оператора AND:

SELECT products.product_name,
       sales.date AS sale_date
FROM products
         JOIN sales ON products.id = sales.product_id AND sales.date >= '2025-01-15';

Результат следующий:

 product_name | sale_date
--------------+------------
 Smartphone   | 2025-01-20
 Desk Chair   | 2025-01-15
(2 rows)

Выражение USING позволяет выполнить соединение таблиц по одному или нескольким столбцам с одинаковыми именами:

SELECT categories.category_name AS category_name,
       products.product_name    AS product_name
FROM categories
         JOIN products USING (category_code);

Результат выглядит так:

 category_name | product_name
---------------+--------------
 Electronics   | Smartphone
 Furniture     | Desk Chair
 Electronics   | Laptop
 Electronics   | Headphones
 Furniture     | Dining Table
(5 rows)

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

SELECT categories.category_name AS category_name,
       products.product_name    AS product_name
FROM categories
         NATURAL JOIN products;

Результат должен выглядеть следующим образом:

 category_name | product_name
---------------+--------------
 Electronics   | Smartphone
 Furniture     | Desk Chair
 Electronics   | Laptop
 Electronics   | Headphones
 Furniture     | Dining Table
(5 rows)

ВАЖНО

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

Столбцы, возвращаемые SELECT * при соединении, зависят от типа используемого условия соединения:

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

Самосоединение — это соединение, при котором таблица соединяется сама с собой. В приведенном ниже примере самосоединение позволяет найти пары товаров из одной категории:

SELECT p1.product_name AS product_1,
       p2.product_name AS product_2,
       p1.category_code
FROM products p1
         JOIN products p2 ON p1.category_code = p2.category_code AND p1.id < p2.id;

Обратите внимание, что используются псевдонимы (p1 и p2), чтобы различать два экземпляра одной и той же таблицы products.

Результат выглядит следующим образом:

 product_1  |  product_2   | category_code
------------+--------------+---------------
 Headphones | Smartphone   | elec
 Desk Chair | Dining Table | furn
 Laptop     | Headphones   | elec
 Laptop     | Smartphone   | elec
(4 rows)

Вы можете выполнять соединение более двух таблиц в одном запросе. Следующий SQL-запрос соединяет три таблицы и возвращает информацию о товаре, его категории и продажах:

SELECT products.id,
       products.product_name,
       categories.category_name AS category_name,
       sales.id                 AS sale_id,
       sales.date               AS sale_date
FROM products
         JOIN categories USING (category_code)
         LEFT JOIN sales ON products.id = sales.product_id;

Результат выглядит так:

 id | product_name | category_name | sale_id | sale_date
----+--------------+---------------+---------+------------
  3 | Smartphone   | Electronics   |     104 | 2025-01-20
  4 | Desk Chair   | Furniture     |     103 | 2025-01-15
  5 | Dining Table | Furniture     |         |
  2 | Headphones   | Electronics   |     102 | 2025-01-10
  1 | Laptop       | Electronics   |     101 | 2025-01-05
(5 rows)

Обзор команды SELECT

Подзапросы

Оператор JOIN

Обзор синтаксиса

Предварительные требования

Типы соединения

CROSS JOIN

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Условия соединения

ON

USING

NATURAL

Поведение SELECT * при соединениях

Самосоединение

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