Соединение таблиц
Функция
Определение правил соединения таблиц.
Спецификация
::=
::=
::=
::=
::=
::=
::=
::=
::=
::=
::=
::=
::=
::=
Синтаксические правила
-
В
разрешены ссылки только к таблицам соединения (внешние ссылки запрещены). -
В случае использования
<логическое выражение>
не может быть<предикатом внешнего соединения в стиле ORACLE>
или включать его в себя как составную часть. -
Для соединения таблиц без использования оператора JOIN используется
, в которой<логическое выражение>
может быть<предикатом внешнего соединения в стиле ORACLE>
или включать его в себя как составную часть. -
используется для указания списка имен столбцов, по значениям которых должно выполняться соединение (столбцы с указанными именами должны содержаться в обеих соединяемых таблицах), при этом результирующий набор содержит только один столбец для каждой пары одноименных столбцов исходных таблиц.Примечание
В отличие от
NATURAL JOIN
, при котором соединение таблиц выполняется по всем одноименным столбцам, использование
позволяет ограничиться отдельными одноименными столбцами. -
Если
<тип соединения>
не указан, но<условие соединения>
присутствует, по умолчанию используется<внутреннее соединение>
(INNER JOIN
).Эти конструкции эквивалентны: SELECT * FROM A INNER JOIN B ON A.X=B.X; SELECT * FROM A JOIN B ON A.X=B.X; SELECT * FROM A, B WHERE A.X=B.X;
-
Если
<тип соединения>
и<условие соединения>
не указаны, то по умолчанию используется<перекрестное соединение>
(CROSS JOIN
).Эти конструкции эквивалентны: select count(*) from auto cross join person; select count(*) from auto join person; select count(*) from auto, person;
-
Если указаны [INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN без ключевого слова NATURAL и без <условия соединения>, то результат соединения будет таким же, как для CROSS JOIN.
-
Для
<естественного соединения>
(NATURAL JOIN) и<перекрестного соединения>
(CROSS JOIN)
и
недопустимы. -
Каждая таблица, входящая в
<соединение таблиц>
, имеет своей областью видимости весь подзапрос, содержащий это соединение, за исключением той части
, которая не содержит соединения, включающего данную таблицу. -
При соединении таблиц следует различать использование
<условия соединения>
(
,
), которое непосредственно определяет условие, по которому будут соединены таблицы, и
, которая ограничивает выборку, полученную в результате соединения.Тестовые таблицы: create or replace table tab1 (i int, ch char(1)); insert into tab1 (i,ch) values (1, 'a'); insert into tab1 (i,ch) values (2, 'b'); insert into tab1 (i,ch) values (3, 'c'); insert into tab1 (i,ch) values (4, 'd'); create or replace table tab2 (j int, cm char(1)); insert into tab2 (j,cm) values (2, 'b'); insert into tab2 (j,cm) values (4, 'g'); insert into tab2 (j,cm) values (5, 'e'); insert into tab2 (j,cm) values (7, 'f'); а) Наличие
и SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.i=tab2.j WHERE (i > 2); Сначала выполняется условие соединения ON (ищутся строки, имеющие равные значения столбцов i и j), в результате которого получается выборка: I CH J CM - -- - -- | 1|a | | | | 2|b | 2|b | | 3|c | | | | 4|d | 4|g | Затем к полученной выборке применяется условие WHERE (строки, для которых i > 2), получаем результирующий набор данных: I CH J CM - -- - -- | 3|c | | | | 4|d | 4|g | б) Отсутствие (выполняется условие соединения, включающее в себя два логических выражения): SELECT * FROM tab1 LEFT JOIN tab2 ON (tab1.i=tab2.j) AND (i > 2); I CH J CM - -- - -- | 1|a | | | | 2|b | | | | 3|c | | | | 4|d | 4|g | в) При отсутствии соединение LEFT JOIN эквивалентно CROSS JOIN (сначала формируется полное декартово произведение строк, затем применяется условие WHERE) SELECT * FROM tab1 LEFT JOIN tab2 WHERE (tab1.i=tab2.j) AND (i > 2); I CH J CM - -- - -- | 4|d | 4|g | -
Запрещено повторение имен таблиц и заменяющих их имен в одной
. Например, конструкции вида:SELECT ... FROM A, A ... SELECT ... FROM A, B AS A ... SELECT ... FROM B AS A, A ... SELECT ... FROM B AS A, C AS A ...
являются недопустимыми.
Общие правила
-
Соединение двух и более таблиц используется для сопоставления строк одной таблицы строкам другой таблицы, т.е. позволяет выводить информацию из нескольких таблиц, связывая их по значениям определенных полей.
-
Определение того, какие именно исходные строки войдут в результирующий набор и в каких сочетаниях, зависит от типа соединения и от заданного условия соединения. Условие соединения (
), то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение. -
Конструкция
<внутреннее соединение>
(INNER JOIN) создает результирующий набор, содержащий пары строк двух таблиц, для которых выполняется<условие соединения>
. Если<условие соединения>
не указано, результирующий набор будет содержать полное декартово произведение строк двух таблиц (тип соединения CROSS JOIN).Тестовые данные: Пусть имеются таблицы сотрудников (persons) и отделов (departments), соединенные между собой по номеру отдела (d_id). create or replace table departments(d_id int, d_name char(20)); insert into departments values (1, 'Sales'); insert into departments values (2, 'IT-technologies'); insert into departments values (3, 'Finance'); insert into departments values (4, 'Management'); insert into departments values (5, 'Design'); create or replace table persons(p_id int, p_name char (20), d_id int); insert into persons values (1, 'John', 3); insert into persons values (2, 'Mary', 2); insert into persons values (3, 'Kate', 4); insert into persons values (4, 'Jack', 2); insert into persons values (5, 'Peter', 7); insert into persons values (6, 'Ann', 5); Получить список сотрудников, работающих в имеющихся отделах. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p, departments d WHERE p.d_id = d.d_id; SELECT p.p_name, d.d_name FROM persons p INNER JOIN departments d USING (d_id); SELECT p.p_name, d.d_name FROM persons p INNER JOIN departments d ON p.d_id = d.d_id; P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Ann |Design | SELECT * FROM A INNER JOIN B ON <логическое выражение1> WHERE <логическое выражение2> есть то же самое, что SELECT * FROM A, B WHERE <логическое выражение1> AND <логическое выражение2>
Примечание
Здесь и далее в разделе обозначения A, B представляют собой табличные выражения.
-
При внешнем соединении, в отличие от внутреннего, в результат выборки попадают не только все связанные строки обеих таблиц, но и строки одной из таблиц (или обеих), для которых нет связанных строк в другой таблице. Недостающим значениям столбцов другой таблицы при этом присваивается значение NULL.
-
Конструкция
<левостороннее внешнее соединение>
(LEFT [OUTER] JOIN) возвращает результирующий набор, содержащий:-
пары строк двух таблиц, для которых выполняется
<условие соединения>
; -
все строки левой таблицы, для которых не нашлось по
<условию соединения>
соответствующих строк в правой таблице, дополненные NULL-значениями в столбцах результата, соответствующих столбцам правой таблицы.
Примеры с использованием тестовых данных, описанных ранее: а) Получить полный список сотрудников и соответствующих отделов, в которых они работают, включая сотрудников, для которых отделы не указаны. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p LEFT JOIN departments d ON p.d_id = d.d_id; SELECT p.p_name, d.d_name FROM persons p LEFT JOIN departments d USING (d_id); SELECT p.p_name, d.d_name FROM persons p, departments d WHERE p.d_id = d.d_id(+); P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Peter | | |Ann |Design | б) Получить полный список отделов и работающих в них сотрудников, включая отделы, для которых не указано ни одного сотрудника. Эти конструкции эквивалентны: SELECT d.d_name, p.p_name FROM departments d LEFT JOIN persons p ON p.d_id = d.d_id; SELECT d.d_name, p.p_name FROM persons p, departments d WHERE p.d_id(+) = d.d_id; SELECT d.d_name, p.p_name FROM departments d LEFT JOIN persons p USING (d_id); D_NAME P_NAME ------ ------ |Sales | | |IT-technologies |Mary | |IT-technologies |Jack | |Finance |John | |Management |Kate | |Design |Ann |
Примечание
Оператор (+) ставится у таблицы, в которой могут отсутствовать строки соответствия и которая, соответственно, дополняется записями с NULL-значениями.
-
-
Для получения результата
<левостороннего внешнего соединения>
оператор (+) ставится у правой таблицы (см. пункт «Предикат внешнего соединения в стиле ORACLE»).SELECT * FROM A LEFT OUTER JOIN B ON <логическое выражение> есть то же самое, что SELECT * FROM A, B WHERE <логическое выражение> UNION SELECT A.*, <пустые значения> FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE <логическое выражение>)
Примечание
Под
<пустыми значениями>
в этом и следующих примерах понимаются NULL-значения в таком количестве и таких типов, чтобы содержащий их запрос совпадал по количеству и типам значений с тем запросом, с которым он объединяется поUNION
. -
Конструкция
<правостороннее внешнее соединение>
(RIGHT [OUTER] JOIN) возвращает результирующий набор, содержащий:-
пары строк двух таблиц, для которых выполняется
<условие соединения>
; -
все строки правой таблицы, для которых не нашлось по
<условию соединения>
соответствующих строк в левой таблице, дополненные NULL-значениями в столбцах результата, соответствующих столбцам левой таблицы.
-
-
Конструкция RIGHT [OUTER] JOIN работает так же, как и LEFT [OUTER] JOIN, с той лишь разницей, что результирующий набор формируется по правой таблице.
-
Конструкции типа «A LEFT OUTER JOIN B» и «B RIGHT OUTER JOIN A» являются эквивалентными.
Эти 2 конструкции эквивалентны: SELECT tab1.* FROM tab1 LEFT JOIN tab2 WHERE tab1.i=tab2.j; SELECT tab1.* FROM tab2 RIGHT JOIN tab1 WHERE tab1.i=tab2.j;
-
Для получения результата
<правостороннего внешнего соединения>
в
оператор (+) ставится у левой таблицы (см. пункт «Предикат внешнего соединения в стиле ORACLE»).Примеры с использованием тестовых данных, описанных ранее: а) Получить полный список отделов и работающих в них сотрудников, включая отделы, для которых не указано ни одного сотрудника. Эти конструкции эквивалентны: SELECT d.d_name, p.p_name FROM persons p RIGHT JOIN departments d ON p.d_id = d.d_id; SELECT d.d_name, p.p_name FROM persons p RIGHT JOIN departments d USING (d_id); SELECT d.d_name, p.p_name FROM departments d LEFT JOIN persons p ON p.d_id = d.d_id; SELECT d.d_name, p.p_name FROM persons p, departments d WHERE p.d_id(+) = d.d_id; D_NAME P_NAME ------ ------ |Sales | | |IT-technologies |Mary | |IT-technologies |Jack | |Finance |John | |Management |Kate | |Design |Ann | б) Получить полный список сотрудников и соответствующих отделов, в которых они работают, включая сотрудников, для которых отдел не указан. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM departments d RIGHT JOIN persons p ON p.d_id = d.d_id; SELECT p.p_name, d.d_name FROM departments d RIGHT JOIN persons p USING (d_id); SELECT p.p_name, d.d_name FROM persons p LEFT JOIN departments d ON p.d_id = d.d_id; SELECT p.p_name, d.d_name FROM persons p, departments d WHERE d.d_id(+) = p.d_id; P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Peter | | |Ann |Design | в) SELECT * FROM A RIGHT OUTER JOIN B ON <логическое выражение> есть то же самое, что: SELECT * FROM A, B WHERE <логическое выражение> UNION SELECT <пустые значения>, B.* FROM B WHERE NOT EXISTS (SELECT * FROM A WHERE <логическое выражение>)
-
Конструкция
<полное внешнее соединение>
FULL [OUTER] JOIN возвращает все строки обеих соединяемых таблиц. Если строке из одной таблицы нет соответствующей строки в другой таблице, возвращается NULL-значение. -
Для получения результата
<полного внешнего соединения>
в
оператор (+) ставится у обеих таблиц: и у левой и у правой (см. пункт «Предикат внешнего соединения в стиле ORACLE»).Примеры с использованием тестовых данных, описанных ранее: а) Получить полный список имеющихся сотрудников и полный список имеющихся отделов с соответствием между сотрудниками и отделами. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p FULL JOIN departments d ON p.d_id = d.d_id; SELECT p.p_name, d.d_name FROM persons p FULL JOIN departments d USING (d_id); SELECT p.p_name, d.d_name FROM persons p, departments d WHERE p.d_id(+) = d.d_id(+); P_NAME D_NAME ------ ------ | |Sales | |Mary |IT-technologies | |Jack |IT-technologies | |John |Finance | |Kate |Management | |Ann |Design | |Peter | | б) SELECT * FROM A FULL OUTER JOIN B ON
<логическое выражение>
есть то же самое, что SELECT * FROM A, B WHERE<логическое выражение>
UNION SELECT A.*,<пустые значения>
FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE<логическое выражение>
) UNION SELECT<пустые значения>
, B.* FROM B WHERE NOT EXISTS (SELECT * FROM A WHERE<логическое выражение>
) -
Конструкция
<естественное соединение>
NATURAL [INNER | {LEFT | RIGHT | FULL} [OUTER] | UNION] JOIN – это эквисоединение, при котором автоматически (столбцы специально не указываются) происходит сравнение всех столбцов в обеих таблицах, которые имеют одинаковые имена. -
Если соединяемые таблицы не имеют совпадающих по именам столбцов, то результат
<естественного соединения>
будет эквивалентен результату<перекрестного соединения>
CROSS JOIN. -
Условием для выполнения
<естественного соединения>
служит наличие идентичных значений в совпадающих столбцах. Результирующий набор содержит только один столбец для каждой пары одноименных столбцов. -
Выражения NATURAL [INNER | {LEFT | RIGHT | FULL} [OUTER] | UNION] JOIN для двух таблиц определяется как семантический эквивалент INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN или UNION JOIN с выражением USING, в котором указаны все совпадающие столбцы обеих таблиц.
Примеры с использованием тестовых данных, описанных ранее: а) Получить список сотрудников, работающих в имеющихся отделах. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p NATURAL JOIN departments d; SELECT p.p_name, d.d_name FROM persons p INNER JOIN departments d USING (d_id); SELECT p.p_name, d.d_name FROM persons p JOIN departments d USING (d_id); P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Ann |Design | б) Получить полный список сотрудников и соответствующих отделов, в которых они работают, включая сотрудников, для которых отдел не указан. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p NATURAL LEFT JOIN departments d; SELECT p.p_name, d.d_name FROM persons p LEFT JOIN departments d USING (d_id); P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Peter | | |Ann |Design | в) SELECT * FROM A NATURAL JOIN B; есть то же самое, что SELECT * FROM A INNER JOIN B USING (
<столбцы>
), г) SELECT * FROM A NATURAL {LEFT | RIGHT| FULL} [OUTER] JOIN B; есть то же самое, что SELECT * FROM A {LEFT | RIGHT| FULL}[OUTER] JOIN B USING (<столбцы>
), д) SELECT * FROM A NATURAL UNION JOIN B; есть то же самое, что SELECT * FROM A UNION JOIN B USING (<столбцы>
), В примерах в)-д) под<столбцами>
подразумевается перечисление имен совпадающих столбцов соединяемых таблиц. -
Конструкция
<перекрестное соединение>
CROSS JOIN возвращает полное декартово произведение строк двух таблиц, т.е. каждая строка одной таблицы будет соединяться с каждой строкой другой таблицы. Количество записей в результирующем наборе будет равно произведению числа записей в соединяемых таблицах.select count(*) from auto; | 1001| select count(*) from person; | 987| 1001*987=987987 select count(*) from auto cross join person; | 987987|
Пример с использованием тестовых данных, описанных ранее: Получить полный список сотрудников со всеми возможными вероятными отделами, или, что то же самое, получить полный список отделов со всеми возможными вероятными сотрудниками. Эти конструкции эквивалентны: SELECT p.p_name, d.d_name FROM persons p CROSS JOIN departments d; SELECT p.p_name, d.d_name FROM persons p JOIN departments d; SELECT p.p_name, d.d_name FROM persons p INNER JOIN departments d; SELECT p.p_name, d.d_name FROM persons p, departments d; P_NAME D_NAME ------ ------ |John |Sales | |John |IT-technologies | |John |Finance | |John |Management | |John |Design | |Mary |Sales | |Mary |IT-technologies | |Mary |Finance | |Mary |Management | |Mary |Design | |Kate |Sales | |Kate |IT-technologies | |Kate |Finance | |Kate |Management | |Kate |Design | |Jack |Sales | |Jack |IT-technologies | |Jack |Finance | |Jack |Management | |Jack |Design | |Peter |Sales | |Peter |IT-technologies | |Peter |Finance | |Peter |Management | |Peter |Design | |Ann |Sales | |Ann |IT-technologies | |Ann |Finance | |Ann |Management | |Ann |Design |
-
При добавлении к конструкции CROSS JOIN
(условия выборки записей) результат будет эквивалентен соединению INNER JOIN (использующим для соединения то же условие).SELECT * FROM A CROSS JOIN B WHERE <логическое выражение> есть то же самое, что SELECT * FROM A INNER JOIN B ON <логическое выражение> есть то же самое, что SELECT * FROM A, B WHERE <логическое выражение>
-
Конструкция
<консолидированное соединение>
(UNION JOIN) возвращает все несовпадающие строки обеих таблиц, дополненные NULL-значениями в столбцах результата, соответствующих столбцам другой таблицы. -
<Консолидированное соединение>
(UNION JOIN) является обратным по отношению к<внутреннему соединению>
(INNER JOIN). -
Если из результирующего набора
<полного внешнего соединения>
удалить строки, полученные в результате<внутреннего соединения>
, то получится<консолидированное соединение>
.Примеры с использованием тестовых данных, описанных ранее: 1) Получить список сотрудников, не работающих ни в одном отделе, и список отделов, в которых не работает ни один сотрудник. SELECT p.p_name, d.d_name FROM persons p UNION JOIN departments d ON p.d_id = d.d_id; P_NAME D_NAME ------ ------ | |Sales | |Peter | | 2) SELECT * FROM A UNION JOIN B ON <логическое выражение> есть то же самое, что SELECT A.*, <пустые значения> FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE <логическое выражение>) UNION SELECT <пустые значения>, B.* FROM B WHERE NOT EXISTS (SELECT * FROM A WHERE <логическое выражение>)
-
На практике наиболее часто используются частные случаи описанных выше типов соединений:
-
эквисоединение;
-
рефлексивное соединение.
-
-
Эквисоединение создает результирующий набор, содержащий пары строк двух таблиц, для которых выполняется условие соединения, представляющее собой равенство значений пар соответствующих столбцов.
Пример соединения, которое является одновременно внутренним соединением и эквисоединением с использованием тестовых данных, описанных ранее: Получить список сотрудников, работающих в имеющихся отделах. SELECT p.p_name, d.d_name FROM persons p INNER JOIN departments d ON p.d_id = d.d_id; P_NAME D_NAME ------ ------ |John |Finance | |Mary |IT-technologies | |Kate |Management | |Jack |IT-technologies | |Ann |Design |
-
Рефлексивное соединение используется для соединения таблицы самой с собой. В этом случае для различения разных экземпляров одной и той же таблицы применяются псевдонимы.
Пример соединения, которое является одновременно внутренним соединением, эквисоединением и рефлексивным соединением с использованием тестовых данных, описанных ранее: Найти пары сотрудников, работающих в одном и том же отделе. SELECT a.*, b.* FROM persons a INNER JOIN persons b ON a.d_id = b.d_id WHERE a.p_id > b.p_id; P_ID P_NAME D_ID P_ID P_NAME D_ID ---- ------ ---- ---- ------ ---- | 2|Mary | 2| 4|Jack | 2|
-
Для соединения нескольких таблиц необходимо применить операцию соединения последовательно несколько раз.
Дополнение определенных выше тестовых данных. Добавлена таблица floors с информацией об этажах (номер и название). create or replace table floors (num_f int, f_name char(20)); insert into floors values (1, 'First'); insert into floors values (2, 'Second'); insert into floors values (3, 'Third'); insert into floors values (4, 'Fourth'); insert into floors values (5, 'Fifth'); insert into floors values (6, 'Sixth'); Добавлена таблицу departments со столбцом num_f, содержащим номер этажа, на котором находится каждый из отделов. create or replace table departments(d_id int, d_name char(20), num_f int); insert into departments values (1, 'Sales', 1); insert into departments values (2, 'IT-technologies', 3); insert into departments values (3, 'Finance', 4); insert into departments values (4, 'Management', 4); insert into departments values (5, 'Design', 3); Пример с использованием тестовых данных, описанных ранее: Получить полный список сотрудников, отделов, в которых они работают, а также номера этажей этих отделов, упорядочить полученный список по номеру сотрудника (p_id) по возрастанию. SELECT p.p_name, d.d_name, floors.num_f FROM persons p LEFT JOIN departments d ON p.d_id = d.d_id LEFT JOIN floors ON d.num_f = floors.num_f ORDER BY p.p_id; P_NAME D_NAME NUM_F ------ ------ ----- |John |Finance | 4| |Mary |IT-technologies | 3| |Kate |Management | 4| |Jack |IT-technologies | 3| |Peter | | | |Ann |Design | 3|
-
В конструкции JOIN можно использовать вложенные подзапросы, т.е. в качестве табличной ссылки может использоваться SELECT-запрос.
Получить полный список отделов и полный список работающих в них сотрудников, номер (p_id) которых больше 2, с соответствием сотрудников и указанных отделов. SELECT tab_p.p_id, tab_p.p_name, d.d_id, d.d_name FROM departments d FULL OUTER JOIN (SELECT * FROM persons WHERE p_id > 2) AS tab_p ON d.d_id = tab_p.d_id; P_ID P_NAME D_ID D_NAME ---- ------ ---- ------ | 3|Kate | 4|Management | | 4|Jack | 2|IT-technologies | | 5|Peter | | | | 6|Ann | 5|Design | | | | 1|Sales | | | | 3|Finance |
-
Разрешено выполнять соединение таблиц по их псевдонимам и псевдонимам их столбцов.
Пример с использованием тестовых данных, описанных ранее: Получить список сотрудников, работающих в имеющихся отделах. SELECT d.b, p.d FROM departments d (a, b) NATURAL JOIN persons p (c, d, a); B D - - |Finance |John | |IT-technologies |Mary | |Management |Kate | |IT-technologies |Jack | |Design |Ann |
-
При использовании
в соединяемых таблицах при отсутствии столбцов с одинаковыми именами, возможен случай использования псевдонимов для таких столбцов.CREATE OR REPLACE TABLE J1TBL (i integer, j integer, t char(32)); CREATE OR REPLACE TABLE J2TBL (k integer, m integer); insert into J1TBL values (1, 1, 'aa'); insert into J1TBL values (2, 2, 'bb'); insert into J2TBL values (1, 1); insert into J2TBL values (3, 2); SELECT * FROM J1TBL t1 (a, b, c) JOIN J2TBL t2 (a, b) USING (a, b); A B C - - - | 1| 1|aa |