PostgreSQL оператор Joins

В этом учебном пособии вы узнаете, как использовать PostgreSQL JOINS (внутренний и внешний) с синтаксисом, наглядными иллюстрациями и примерами.

Описание

PostgreSQL JOINS используется для извлечения данных из нескольких таблиц. PostgreSQL JOIN выполняется всякий раз, когда две или более таблицы объединяются в операторе SQL.
Существуют разные типы соединений PostgreSQL:

  • PostgreSQL INNER JOIN (или иногда называется простым соединением)
  • PostgreSQL LEFT OUTER JOIN (или иногда называется LEFT JOIN
  • PostgreSQL RIGHT OUTER JOIN (или иногда называется RIGHT JOIN
  • PostgreSQL FULL OUTER JOIN (или иногда называется FULL JOIN

Итак, давайте обсудим синтаксис JOIN в PostgreSQL, посмотрим на визуальные иллюстрации JOIN в PostgreSQL и рассмотрим примеры JOIN.

INNER JOIN (простое соединение)

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

Синтаксис

Синтаксис для INNER JOIN в PostgreSQL:

Визуальная Иллюстрация

На этой визуальной диаграмме PostgreSQL INNER JOIN возвращает затененную область:
inner-join

PostgreSQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2.

Пример

Вот пример INNER JOIN PostgreSQL:

Этот пример PostgreSQL INNER JOIN вернет все строки из таблиц suppliers и orders, где в таблицах suppliers и orders есть соответствующее значение supplier_id.
Давайте посмотрим на некоторые данные, чтобы объяснить, как работают внутренние соединения:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть еще одна таблица под названием orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 10.04.2019
500126 10001 20.04.2019
500127 10004 30.04.2019

Если мы запустим оператор PostgreSQL SELECT (который содержит INNER JOIN) ниже:

Наш набор результатов будет выглядеть так:

supplier_id name order_date
10000 IBM 10.04.2019
10001 Hewlett Packard 20.04.2019

Строки для 'Microsoft' и 'NVIDIA' из таблицы suppliers будут опущены, поскольку 10002 и 10003 supplier_id не существуют в обеих таблицах. Строка для 500127 (order_id) из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.

Старый синтаксис

В завершение стоит упомянуть, что приведенный выше пример INGER JOIN PostgreSQL можно переписать с использованием более старого неявного синтаксис следующим образом (но мы все же рекомендуем использовать синтаксис c ключевыми словами INNER JOIN):

LEFT OUTER JOIN

Другой тип соединения называется PostgreSQL LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для PostgreSQL LEFT OUTER JOIN:

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

Визуальная Иллюстрация

На этой визуальной диаграмме PostgreSQL LEFT OUTER JOIN возвращает затененную область:
left-outer-join

PostgreSQL LEFT OUTER JOIN будет возвращать все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

Вот пример PostgreSQL LEFT OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

В этом примере LEFT OUTER JOIN будут возвращены все строки из таблицы employees и только те строки из таблицы orders, где объединенные поля равны.

Если значение supplier_id в таблице employees не существует в таблице orders, все поля в таблице orders будут отображаться как в результирующем наборе.
Рассмотрим некоторые данные, чтобы объяснить, как работают LEFT OUTER JOINS:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть вторая таблица с именем orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 10.04.2019
500126 10001 20.04.2019

Если мы запустим оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Наш набор результатов будет выглядеть так:

supplier_id supplier_name order_date
10000 IBM 10.04.2019
10001 Hewlett Packard 20.04.2019
10002 Microsoft
10003 NVIDIA

Строки для 'Microsoft' и 'NVIDIA' будут включены, потому что использовался LEFT OUTER JOIN. Однако вы заметите, что поле order_date для этих записей содержит значение .

RIGHT OUTER JOIN

Другой тип соединения называется PostgreSQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблицы с правосторонним соединением, указанной в условии ON, и только те строки из другой таблицы, где соединенные поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для PostgreSQL RIGHT OUTER JOIN:

SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;

Визуальная Иллюстрация

На этой визуальной диаграмме PostgreSQL RIGHT OUTER JOIN возвращает заштрихованную область:
right outer join

PostgreSQL RIGHT OUTER JOIN будет возвращать все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Вот пример RIGHT OUTER JOIN для PostgreSQL:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

В этом примере RIGHT OUTER JOIN будет возвращать все строки из таблицы orders и только те строки из таблицы suppliers, где соединенные поля равны.
Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля таблицы suppliers будут отображаться как в результирующем наборе.
Давайте посмотрим на некоторые данные, чтобы объяснить, как работают RIGHT OUTER JOINS:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

supplier_id supplier_name
10000 Yandex
10001 Google

У нас есть вторая таблица с именем orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 10.04.2019
500126 10001 20.04.2019
500127 10002 30.04.2019

Если мы запустим оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Наш набор результатов будет выглядеть так:

order_id order_date supplier_name
500125 10.04.2019 Yandex
500126 20.04.2019 Google
500127 30.04.2019

Строка для 500127 (order_id) будет включена, поскольку используется RIGHT OUTER JOIN. Однако вы заметите, что поле supplier_name для этой записи содержит значение.

FULL OUTER JOIN

Другой тип соединения называется PostgreSQL FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL - значениями в месте, где условие соединения не выполняется.

Синтаксис

Синтаксис для PostgreSQL FULL OUTER JOIN:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Визуальная Иллюстрация

На этой визуальной диаграмме PostgreSQL FULL OUTER JOIN возвращает затененную область:
full outer join

PostgreSQL FULL OUTER JOIN будет возвращать все записи из table1 и table2.

Пример

Рассмотрим пример FULL OUTER JOIN в PostgreSQL :

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Этот пример FULL OUTER JOIN будет возвращать все строки из таблицы suppliers и все строки из таблицы orders и всякий раз, когда условие соединения не выполняется, то поля в результирующем наборе будут принимать значения .
Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля в таблице orders будут отображаться как в результирующем наборе. Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться как в результирующем наборе.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают FULL OUTER JOINS:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть вторая таблица с именем orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 10.04.2019
500126 10001 20.04.2019
500127 10004 30.04.2019

Если мы запустим SQL запрос (который содержит FULL OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Наш набор результатов будет выглядеть так:

supplier_id supplier_name order_date
10000 IBM 10.04.2019
10001 Hewlett Packard 20.04.2019
10002 Microsoft
10003 NVIDIA
30.04.2019

Строки для 'Microsoft' и 'NVIDIA' будут включены, поскольку использовался FULL OUTER JOIN. Однако вы заметите, что поле order_date для этих записей содержит значение.
Строка для supplier_id 10004 также будет включена, поскольку используется FULL OUTER JOIN. Тем не менее, вы заметите, что поля supplier_id и supplier_name для этих записей содержат значение.