Операторы Inner Join и Outer (left, right, full) Join в SQL (Oracle)
Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:
- inner join;
- outer join;
- cross join;
В свою очередь, outer join может быть left, right и full (слово outer обычно опускается).
В качестве примера (DBMS Oracle) создадим две простые таблицы и сконструируем для них SQL-выражения с использованием join.
В первой таблице будет хранится ID пользователя и его nick-name, а во второй - ID ресурса, имя ресурса и ID пользователя, который может этот ресурс администрировать.
create table t_users ( t_id number(11, 0), t_nick varchar(16), primary key (t_id) ) create table t_resources ( t_id number(11, 0), t_name varchar(16), t_userid number (11, 0), primary key (t_id) )
Содержимое таблиц пусть будет таким:
T_ID T_NICK 1 user1 3 user3 4 user4 T_ID T_NAME T_USERID 1 res1 3 2 res2 1 3 res3 2 5 res5 3
Конструкция join выглядит так:
... join_type join table_name on condition ...
Где join_type - тип join-выражения, table_name - имя таблицы, которая присоединяется к результату, condition - условие объединения таблиц.
Кострукция join располагается сразу после select-выражения. Можно использовать несколько таких конструкций подряд для объединения соответствующего кол-ва таблиц. Логичнее всего использовать join в том случае, когда таблица имеет внешний ключ (foreign key).
Inner join необходим для получения только тех строк, для которых существует соответствие записей главной таблицы и присоединяемой. Иными словами условие condition должно выполняться всегда. Пример:
select t_resources.t_name, t_users.t_nick from t_resources inner join t_users on t_users.t_id = t_resources.t_userid
Результат будет таким:
T_NAME T_NICK res2 user1 res1 user3 res5 user3
В случае с left join из главной таблицы будут выбраны все записи, даже если в присоединяемой таблице нет совпадений, то есть условие condition не учитывает присоединяемую (правую) таблицу. Пример:
select t_resources.t_name, t_users.t_nick from t_resources left join t_users on t_users.t_id = t_resources.t_userid
Результат выполнения запроса:
T_NAME T_NICK res1 user3 res2 user1 res3 (null) res5 user3
Результат показывает все ресурсы и их администраторов, вне зависимотсти от того есть они или нет.
Right join отображает все строки удовлетворяющие правой части условия condition, даже если они не имеют соответствия в главной (левой) таблице:
select t_resources.t_name, t_users.t_nick from t_resources right join t_users on t_users.t_id = t_resources.t_userid
А результат будет следующим:
T_NAME T_NICK res2 user1 res1 user3 res5 user3 (null) user4
Результирующая таблица показывает ресурсы и их администраторов. Если адмнистратор не задействован, эта запись тоже будет отображена. Такое может случиться, например, если ресурс был удален.
Full outer join (ключевое слово outer можно опустить) необходим для отображения всех возможных комбинаций строк из нескольких таблиц. Иными словами, это объединение результатов left и right join.
select t_resources.t_name, t_users.t_nick from t_resources full join t_users on t_users.t_id = t_resources.t_userid
А результат будет таким:
T_NAME T_NICK res1 user3 res2 user1 res3 (null) res5 user3 (null) user4
Некоторые СУБД не поддерживают такую функциональность (например, MySQL), в таких случаях обычно используют объединение двух запросов:
select t_resources.t_name, t_users.t_nick from t_resources left join t_users on t_users.t_id = t_resources.t_userid union select t_resources.t_name, t_users.t_nick from t_resources right join t_users on t_users.t_id = t_resources.t_userid
Наконец, cross join. Этот тип join еще называют декартовым произведением (на английском - cartesian product). Настоятельно рекомендую использовать его с умом, так как время выполнения запроса с увеличением числа таблиц и строк в них растет нелинейно. Вот пример запроса, который аналогичен cross join:
select t_resources.t_name, t_users.t_nick from t_resources, t_users
Конструкция Join (в сочетании с другими SQL конструкциями, например, group by) часто встречается при программировании под базы данных. Думаю, эта статья будет вам полезна.
Кстати, для проверки своих знаний в области баз данных (и в частности Oracle) рекомендую воспользоваться этим сайтом онлайн тестирования - Тесты по базам данных.
Комментариев: 6
Выйти
wert2all:
думаю будет полезна.. но мало рассказано.. так всё быстро… а вот как написать запрос, что бы выводились те записи с первой таблицы. которых нет во второй? ;) это тоже следует написать
devochko:
-- а вот как написать запрос, что бы выводились те записи с первой таблицы. которых нет во второй?
а я раньше работала с mssql, на новой работе оракл, и почему-то запросы с джоинами не выполняются, даже простейшие, типа select t1.p1,t2.p2 from t1 left join t2 on t1.p1=t2.p2 не могу понять, в оракле другой синтаксис или вообще нет оператора join?:(
sergey_mp:
не вижу смысла вообще использовать inner join.
использую Oracle SQL 10 лет и мне удобнее и нагляднее написать (особенно если много полей в условии where)
чем
Спрашиваю у программиста на новой работе- почему ты используешь inner join вместо простого сравнения полей , отвечает меня так научили
Programator:
Работаю с PL/SQL-Developer уже больше пол-года, несколько раз пробовал понять этот join, и на этом примере токо уразумел (опробовал, все норм). Спасибо автору.
DM:
Жаль в оракле старый синтаксис, ansi join там только начинает приживаться в новых версиях, например в materialized views при автообновлении он неподдерживается, а сам ansi join довольно неплохой оператор, позволяет избежать мусора (ненужных столбцов) в select.
Кстати, вопрос про старый оракловский синтаксис - если связаный подзапрос (во where) имеет join-связь между несколькими таблицами, как тогда построить запрос? Если используем старый синтаксис, то в select должны перечислить все столбцы, а подзапрос во where должен вернуть одно единственное значение, соответственно поверх подзапроса ставим select (count, max, min) одного столбца, но тогда теряется связь с главным запросом, так как в оракле область видимости подзапроса только на 1 уровень вверх. Тупик?
С новым ansi join сделать подобный запрос не составит труда, так как не нужно всю кучу столбцов извлекать в select.
Andrey Zakharov:
К предыдущим комментариям: select * from table1 where col not in (select col from table2)