PostgreSQL - операторы JOINS

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

Типы Join в PostgreSQL:

CROSS JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

Прежде чем продвинуться дальше, рассмотрим две таблицы, COMPANY и DEPARTMENT. Мы уже видели команды INSERT для «заселения» таблицы COMPANY. Давайте представим записи в таблице COMPANY следующим образом:

 id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

Другая таблица – DEPARTMENT имеет следующее описание:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Список INSERT для «заселения» таблицы DEPARTMENT:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );
 
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Наконец, в таблице DEPARTMENT появятся следующие записи:

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

CROSS JOIN

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

Базовый синтаксис CROSS JOIN:

SELECT ... FROM table1 CROSS JOIN table2 ...

На основании приведенных выше таблиц мы можем записать CROSS JOIN следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

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

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

INNER JOIN

INNER JOIN создает новую таблицу результатов, объединяя значения столбцов двух таблиц (таблица1 и таблица2) на основе предиката. Запрос сравнивает каждую строку таблицы table1 с каждой строкой таблицы table2, чтобы найти все пары строк, которые удовлетворяют предикату. Когда предикат соблюден, значения столбца для каждой совпавшей пары строк table1 и table2 объединяются в результирующую строку.

INNER JOIN – самый распространенный оператор, использующийся по умолчанию. Можете использовать ключевое слово INNER по желанию.

Базовый синтаксис INNER JOIN выглядит следующим образом:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

С учетом выше упомянутых таблиц запишем INNER JOIN следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

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

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

LEFT OUTER JOIN

OUTER JOIN - расширение INNER JOIN. Стандарт SQL определяет три типа OUTER JOIN: LEFT, RIGHT, и FULL; PostgreSQL поддерживает все эти три типа.

В случае LEFT OUTER JOIN, сначала выполняется inner join. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется присоединяемая строка со значениями NULL в столбцах T2. Таким образом, в объединенной таблице всегда есть, по крайней мере, одна строка для каждой строки в T1.

Синтаксис LEFT OUTER JOIN:

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

На основе двух выше упомянутых таблиц запишем inner join следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

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

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

RIGHT OUTER JOIN

Для начала выполняется inner join. Затем для каждой строки в таблице T2, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T1, добавляется присоединяемая строка со значениями NULL в столбцах T1. Это обратная сторона левого соединения; в таблице результатов всегда будет строка для каждой строки в T2.

Синтаксис RIGHT OUTER JOIN:

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

Запишем inner join следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

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

 emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

FULL OUTER JOIN

Сначала выполняется inner join. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется присоединяемая строка со значениями NULL в столбцах T2. Кроме того, для каждой строки T2, которая не удовлетворяет условию соединения с какой-либо строкой в T1, добавляется объединенная строка со значениями NULL в столбцах T1.

Синтаксис FULL OUTER JOIN:

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

Запишем inner join следующим образом:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

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

 emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

← Предыдущая статья

Ограничения в PostgreSQL

Следующая статья →

PostgreSQL – операторы UNIONS