PostgreSQL JOIN
Предложение JOIN в PostgreSQL используется для объединения строк из двух или более таблиц на основе общих полей между этими таблицами.
В PostgreSQL существует пять типов JOIN:
- CROSS JOIN: Перекрёстное соединение
- INNER JOIN: Внутреннее соединение
- LEFT OUTER JOIN: Левое внешнее соединение
- RIGHT OUTER JOIN: Правое внешнее соединение
- FULL OUTER JOIN: Полное внешнее соединение
Далее давайте создадим две таблицыCOMPANYиDEPARTMENT。
Пример
Создайте таблицу COMPANY (Скачать SQL-файл COMPANY), содержимое данных следующее:
exampledb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
Давайте добавим несколько записей в таблицу:
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
На данный момент записи в таблице COMPANY выглядят следующим образом:
id | name | age | address | salary ----+-------+-----+--------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
Создайте таблицу DEPARTMENT с тремя полями:
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
Вставьте три записи в таблицу 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 сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если две входные таблицы содержат соответственно x и y строк, результирующая таблица содержит x*y строк.
Поскольку CROSS JOIN может создавать очень большие таблицы, при его использовании нужно быть осторожным и применять его только тогда, когда это уместно.
Ниже приведён базовый синтаксис CROSS JOIN:
SELECT ... FROM table1 CROSS JOIN table2 ...
На основе приведённых выше таблиц мы можем записать CROSS JOIN следующим образом:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
Полученный результат выглядит следующим образом:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+--------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
Внутреннее соединение
INNER JOIN создаёт новую результирующую таблицу, объединяя значения столбцов двух таблиц (table1 и table2) на основе предиката соединения. Запрос сравнивает каждую строку в table1 с каждой строкой в table2, чтобы найти все пары строк, удовлетворяющие предикату соединения.
Когда предикат соединения выполняется, значения столбцов каждой совпадающей пары строк A и B объединяются в одну результирующую строку.
INNER JOIN — наиболее распространённый тип соединения и тип соединения по умолчанию.
Ключевое слово INNER необязательно.
Ниже приведён синтаксис INNER JOIN:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
На основе таблиц выше мы можем записать внутреннее соединение следующим образом:
exampledb=# 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
7 | James | Finance
(3 rows)
Левое внешнее соединение
Внешние соединения являются расширением внутренних соединений. Стандарт SQL определяет три типа внешних соединений: LEFT, RIGHT и FULL. PostgreSQL поддерживает все из них.
Для левого внешнего соединения сначала выполняется внутреннее соединение. Затем для каждой строки таблицы T1, которая не удовлетворяет условию соединения с таблицей T2, также добавляется соединённая строка со значениями NULL в столбцах T2. Поэтому соединённая таблица содержит как минимум одну строку для каждой строки T1.
Ниже приведён базовый синтаксис LEFT OUTER JOIN:
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
На основе двух таблиц выше мы можем записать левое внешнее соединение следующим образом:
exampledb=# 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
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Правое внешнее соединение
Сначала выполняется внутреннее соединение. Затем для каждой строки таблицы T2, которая не удовлетворяет условию соединения с таблицей T1, также добавляется соединённая строка со значениями NULL в столбцах T1. Это противоположность левому соединению; для каждой строки T2 результирующая таблица всегда содержит одну строку.
Ниже приведён базовый синтаксис правого внешнего соединения (RIGHT OUT JOIN):
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
На основе двух таблиц выше мы создаём правое внешнее соединение:
exampledb=# 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 | James | Finance
(3 rows)
Полное внешнее соединение
Сначала выполняется внутреннее соединение. Затем для каждой строки таблицы T1, которая не удовлетворяет условию соединения ни с одной строкой таблицы T2, в результат также добавляется строка со значениями NULL в столбцах T2. Кроме того, для каждой строки T2, которая не удовлетворяет условию соединения ни с одной строкой T1, в результат добавляется строка со значениями NULL в столбцах T1.
Ниже приведён базовый синтаксис полного внешнего соединения:
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
На основе двух таблиц выше мы можем создать полное внешнее соединение:
exampledb=# 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 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Другие расширения