Предложение PostgreSQL Joins используется для объединения записей из двух или более таблиц в базе данных. JOIN — это средство для объединения полей из двух таблиц с использованием значений, общих для каждой.
Типы соединения в PostgreSQL:
- CROSS JOIN
- ВНУТРЕННЕЕ СОЕДИНЕНИЕ
- ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
- ПРАВИЛЬНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
- ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
Прежде чем мы продолжим, давайте рассмотрим две таблицы, КОМПАНИЯ и ОТДЕЛ. Мы уже видели операторы 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
Другая таблица — ОТДЕЛ, имеет следующее определение —
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 );
Наконец, у нас есть следующий список записей, доступных в таблице ОТДЕЛ —
id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7
CROSS JOIN
CROSS JOIN сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если во входных таблицах есть столбцы x и y соответственно, то в результирующей таблице будут столбцы x + y. Поскольку CROSS JOINs могут генерировать очень большие таблицы, необходимо соблюдать осторожность, чтобы использовать их только при необходимости.
Ниже приводится синтаксис 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 создает новую таблицу результатов, комбинируя значения столбцов двух таблиц (table1 и table2) на основе предиката соединения. Запрос сравнивает каждую строку таблицы table1 с каждой строкой таблицы table2, чтобы найти все пары строк, которые удовлетворяют предикату соединения. Когда предикат соединения удовлетворяется, значения столбцов для каждой соответствующей пары строк таблиц table1 и table2 объединяются в строку результатов.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ является наиболее распространенным типом объединения и является типом соединения по умолчанию. При желании вы можете использовать ключевое слово 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
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
НАРУЖНОЕ СОЕДИНЕНИЕ является продолжением ВНУТРЕННЕГО СОЕДИНЕНИЯ. Стандарт SQL определяет три типа OUTER JOIN: LEFT, RIGHT и FULL, а PostgreSQL поддерживает все это.
В случае LEFT OUTER JOIN сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется объединенная строка с нулевыми значениями в столбцах T2. Таким образом, объединенная таблица всегда имеет хотя бы одну строку для каждой строки в T1.
Ниже приведен синтаксис LEFT OUTER JOIN —
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
На основании приведенных выше таблиц мы можем написать внутреннее соединение следующим образом:
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 |
ПРАВИЛЬНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
Сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T2, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T1, присоединенная строка добавляется с нулевыми значениями в столбцах T1. Это противоположность левого соединения; В таблице результатов всегда будет строка для каждой строки в T2.
Ниже приведен синтаксис RIGHT OUTER JOIN —
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
На основании приведенных выше таблиц мы можем написать внутреннее соединение следующим образом:
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
ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
Сначала выполняется внутреннее соединение. Затем для каждой строки в таблице T1, которая не удовлетворяет условию соединения с какой-либо строкой в таблице T2, добавляется объединенная строка с нулевыми значениями в столбцах T2. Кроме того, для каждой строки T2, которая не удовлетворяет условию соединения с какой-либо строкой в T1, добавляется объединенная строка с нулевыми значениями в столбцах T1.
Ниже приведен синтаксис FULL OUTER JOIN —
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
На основании приведенных выше таблиц мы можем написать внутреннее соединение следующим образом:
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
Приведенный выше запрос даст следующий результат —