January
Операторы Inner Join и Outer (left, right, full) Join в SQL (Oracle)
Posted in: Базы данных, 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
Результирующая таблица показывает ресурсы и их администраторов. Если адмнистратор не задействован, эта запись тоже будет отображена. Такое может случиться, например, если ресурс был удален.
Updated (8.03.2007):
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) часто встречается при программировании под базы данных. Думаю, эта статья будет вам полезна.
35 Comments »
RSS feed for comments on this post. TrackBack URI
думаю будет полезна.. но мало рассказано.. так всё быстро… а вот как написать запрос, что бы выводились те записи с первой таблицы. которых нет во второй? 
это тоже следует написать
В статье описано, как и для чего можно использовать конструкцию join. Естественно, для определенных задач и для сложных баз данных запросы будут сложнее: со вложенными запросами, с конструкциями where, group by, c ключевыми сдовами типа distinct и так далее…
Как раз то, что нужно - сейчас 3 часа ночи и вот появилось легкое непонимание разниц INNER и LEFT/
(Тут еще OUTER JOINT проскакивает - буду разбираться еще).
Вы помогли _быстро_ разобраться 
Спасибо!
Отлично! Как в хорошем справочнике: коротко и ясно. Спасибо!
Очень хорошая статья. Все четко и понятно. Молодцы.
Хорошая статья, помогла. Кратко и ясно. То, что нужно было для начала ;).
Понятно но слишком уж кратко. Хотя бы ссылку на более подробную статью чтоли. Но все равно спасибо. Главное дано сравнение всех JOIN. Но не понятно что делать если объединяется одна и та же таблица под разными именами
Статья действительно хороша. Спасибо.
Огромное спасибо, отличная статья. Моментально стало всё ясно.
И я благодарность наверное выпишу за статейку
Спасибо. Как раз по лабе, которую щас буду делать
Хорошая статья. Мне на собеседовании вопрос про outer join задавали, а я толком ничего сказать не смог. Теперь вроде бы все ясно, спасибо!
Да статья действительно получилось хорошая. Она не вдаваясь в детали объясняет суть! А за подробностями можно не полениться и залезть в мануал СУБД
Ещё стоило бы упомянуть, что в Oracle, по крайней мере в 9i, есть определённые проблемы с ANSI-шным синтаксисом join-ов. Хотя в документации и советуется его использовать взамен оракловых (+).
Поэтому пользоваться ANSI-синтаксисом следует с осторожностью (особенно full).
Подробности можно посмотреть на Металинке.
Хорошая статейка, мне понравилось. Коротко, ясно, а главное доступно… Без засирания мозга полным синтакиссом опреаторов и прочего. Очень наглядно продемонстрировано. Сам работаю с MySQL, однако было очень интересно прочитать. Спасибо.
Всё хорошо, корме одного:
В Oracle нет операции JOIN. Внешние соединения обеспечиваются в Oracle оператором (+) в эквисоединении.
Хорошая статья. Спасибо за простое изложение информации.
Неплохо было бы добавить чуть-чуть инфы о том когда следует пользоваться join.
Большое спасибо, инфа полезная! Как раз сдавать лабы по бд!
Большое спасибо за такую понятную статью. очень доступно всё описано.
А что делает такой select:
Select …
from table1 left join table2 right join table3
on (join condition table2 - table3)
on (join condition table1 - table2)
Какие колонки должны попасть в результат?
И мне статья понравилась: просто и понятно. Спасибо
Great Thanks to you!
From Ubonratchathani University
will try to explain it to the students!
Спасибо, коротко и доступно о нужном )
Большое спасибо, очень полезная стать!
Класс….. +1
>>>а вот как написать запрос, что бы выводились те записи с первой таблицы. которых нет во второй?
select t_resources.t_name, t_users.t_nick
from t_resources
left join t_users
where t_users.t_nick is null
а я раньше работала с mssql, на новой работе оракл, и почему-то запросы с джоинами не выполняются, даже простейшие, типа select t1.p1,t2.p2 from t1 left join t2 on t1.p1=t2.p2 не могу понять, в оракле другой синтаксис или вообще нет оператора join?:(
всем спасибо, я уже нашла)))) это будет так:
select t1.p1,t2.p2 from t1, t2 where t1.p1=t2.p2 (+) = left join
select t1.p1,t2.p2 from t1, t2 where t1.p1(+)=t2.p2 = right join
Очень неплохая статья.
Спасибки, статейка полезная ))
мда, я ничего не имею против девушек it, но я хотел узнать, как правильно искользовать джойны, и заглянул сюда. Первый же пример вызвал у меня сомнения… Создал все в точности в оракловой базе, и в певом запросе выдет 4 записи, а не 3, как показано тут. Следовательно объяснения про inner join уже не верны, дальше читать расхотелось…
Может мы о разных Ораклах говорим?
Спасибо! самая суть…четко , коротко и понятно!!
Спасибо, я наконец поняла как полозоваться джоином :)))
Целый день “гуглил” инет, искал “а вот как написать запрос, что бы выводились те записи с первой таблицы. которых нет во второй?”, где-то к обеду узнал, что мне нужен JOIN и только к полуночи инет все таки раскрыл ТАЙНУ кто такой JOIN. Огромное спасибо вашей статье!!!
может, я не прав, но, на мой взгляд, лучше использовать подзапросы. Они и нагляднее и проще пишутся. А этими Джойнами можно себе мозг взорвать
