Левое внешнее объединение с помощью знака + в Oracle 11g
Кто-нибудь может сказать мне, являются ли ниже 2 запроса примером левого внешнего соединения или правого внешнего соединения?
Table Part:
Name Null? Type
PART_ID NOT NULL VARCHAR2(4)
SUPPLIER_ID VARCHAR2(4)
PART_ID SUPPLIER_ID
P1 S1
P2 S2
P3
P4
Table Supplier:
Name Null? Type
SUPPLIER_ID NOT NULL VARCHAR2(4)
SUPPLIER_NAME NOT NULL VARCHAR2(20)
SUPPLIER_ID SUPPLIER_NAME
S1 Supplier#1
S2 Supplier#2
S3 Supplier#3
Показать все детали независимо от того, поставляет их поставщик или нет:
SELECT P.Part_Id, S.Supplier_Name ОТ Часть P, Поставщик S ГДЕ P.Supplier_Id = S.Supplier_Id (+) SELECT P.Part_Id, S.Supplier_Name ОТ Часть P, Поставщик S ГДЕ S.Supplier_Id (+) = P.Supplier_Id
Спасибо!
7 ответов
TableA LEFT OUTER JOIN TableB эквивалентно TableB RIGHT OUTER JOIN Table A,
В Oracle (+) обозначает "необязательную" таблицу в JOIN. Так что в вашем первом запросе это P LEFT OUTER JOIN S, В вашем втором запросе это S RIGHT OUTER JOIN P, Они функционально эквивалентны.
В терминологии RIGHT или LEFT указывают, какая сторона объединения всегда имеет запись, а другая сторона может быть нулевой. Так что в P LEFT OUTER JOIN S, P всегда будет иметь запись, потому что это на LEFT, но S может быть нулевым.
Посмотрите этот пример от java2s.com для дополнительного объяснения.
Чтобы уточнить, я полагаю, я говорю, что терминология не имеет значения, так как она используется только для визуализации. Важно то, что вы понимаете, как это работает.
ПРАВО против ЛЕВОГО
Я видел некоторую путаницу в том, что имеет значение при определении ПРАВО против ЛЕВЫХ в неявном синтаксисе соединения.
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT *
FROM A, B
WHERE A.column = B.column(+)
ПРАВО НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT *
FROM A, B
WHERE B.column(+) = A.column
Все, что я сделал, это поменялся сторонами терминов в предложении WHERE, но они все еще функционально эквивалентны. (См. Выше в моем ответе для получения дополнительной информации об этом.) Размещение (+) определяет ПРАВО или ВЛЕВО. (В частности, если (+) справа, это левое соединение. Если (+) слева, это ПРАВИЛЬНОЕ СОЕДИНЕНИЕ.)
Типы JOIN
Два стиля JOIN - это неявные соединения и явные соединения. Это разные стили написания JOIN, но они функционально эквивалентны.
Смотри этот ТАК вопрос.
Неявные соединения просто перечисляют все таблицы вместе. Условия соединения указаны в предложении WHERE.
Неявное СОЕДИНЕНИЕ
SELECT *
FROM A, B
WHERE A.column = B.column(+)
Явные СОЕДИНЕНИЯ связывают условия соединения с включением определенной таблицы, а не в предложении WHERE.
Явное ПРИСОЕДИНЕНИЕ
SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column
Эти неявные соединения могут быть более трудными для чтения и понимания, и они также имеют несколько ограничений, так как условия соединения смешаны в других условиях WHERE. Таким образом, неявные соединения обычно рекомендуются в пользу явного синтаксиса.
Эти два запроса выполняются OUTER JOIN, Увидеть ниже
Oracle рекомендует использовать синтаксис OUTER JOIN предложения FROM, а не оператор соединения Oracle. На запросы внешнего соединения, использующие оператор соединения Oracle (+), распространяются следующие правила и ограничения, которые не применяются к синтаксису OUTTER JOIN предложения FROM:
Вы не можете указать оператор (+) в блоке запроса, который также содержит синтаксис соединения предложения FROM.
Оператор (+) может появляться только в предложении WHERE или в контексте левой корреляции (при указании предложения TABLE) в предложении FROM и может применяться только к столбцу таблицы или представления.
Если A и B объединяются несколькими условиями соединения, то вы должны использовать оператор (+) во всех этих условиях. Если вы этого не сделаете, то Oracle Database вернет только строки, полученные в результате простого объединения, но без предупреждения или ошибки, сообщив вам, что у вас нет результатов внешнего соединения.
Оператор (+) не создает внешнее соединение, если вы указываете одну таблицу во внешнем запросе, а другую таблицу - во внутреннем запросе.
Вы не можете использовать оператор (+) для внешнего соединения таблицы с самим собой, хотя самостоятельные объединения допустимы. Например, следующее утверждение недопустимо:
-- The following statement is not valid: SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id;Однако допустимо следующее самостоятельное объединение:
SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;Оператор (+) может применяться только к столбцу, а не к произвольному выражению. Однако произвольное выражение может содержать один или несколько столбцов, помеченных оператором (+).
Условие WHERE, содержащее оператор (+), нельзя объединить с другим условием с помощью логического оператора OR.
Условие WHERE не может использовать условие сравнения IN для сравнения столбца, отмеченного оператором (+), с выражением.
Если предложение WHERE содержит условие, которое сравнивает столбец из таблицы B с константой, то к столбцу должен применяться оператор (+), чтобы Oracle возвращал строки из таблицы A, для которых он сгенерировал пустые значения для этого столбца. В противном случае Oracle возвращает только результаты простого объединения.
В запросе, который выполняет внешние объединения более двух пар таблиц, одна таблица может быть сгенерированной нулем только для одной другой таблицы. По этой причине вы не можете применить оператор (+) к столбцам B в условии соединения для A и B и в условии соединения для B и C. Обратитесь к SELECT для синтаксиса внешнего соединения.
Взято с http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm
Я видел некоторые противоречия в ответах выше, я только что попробовал следующее на Oracle 12c, и следующее правильно:
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT *
FROM A, B
WHERE A.column = B.column(+)
ПРАВО НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT *
FROM A, B
WHERE B.column(+) = A.column
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
ВЫБРАТЬ * ИЗ A, B, ГДЕ A.column = B.column(+)
ПРАВО НАРУЖНОЕ СОЕДИНЕНИЕ
ВЫБРАТЬ * ИЗ A, B, ГДЕ A.column (+)= B.column
You can see answers from previous posts
However I added little more information
create table r2020 (id int, name varchar2(50),rank number);
insert into r2020 values (101,'Rob Rama',1);
insert into r2020 values (102,'Ken Krishna',3);
insert into r2020 values (108,'Ray Rama',2);
insert into r2020 values (109,'Kat Krishna',4);
create table r2021 (id int, name varchar2(50),rank number);
insert into r2021 values (102,'Ken Krishna',1);
insert into r2021 values (103,'Tom Talla',2);
insert into r2021 values (108,'Ray Rama',2);
--LEFT OUTER JOIN
--oracle notation
select * from r2020 r1, r2021 r2
where r1.id = r2.id (+)
order by r1.id;
--ANSI notation
select * from r2020 r1
left outer join r2021 r2 on r1.id = r2.id
order by r1.id;
--OUT PUT
NAME ID RANK NAME_1 ID_1 RANK_1
---- -- ---- ---- ---- ------
Rob Rama 101 1 (null) (null) (null)
Ken Krishna 102 3 Ken Krishna 102 1
Ray Rama 108 2 Ray Rama 108 2
Kat Krishna 109 4 (null) (null) (null)
--RIGHT OUTER JOIN
--oracle notation
select * from r2020 r1, r2021 r2
where r1.id (+) = r2.id
order by r1.id;
--ANSI notation
select * from r2020 r1
right outer join r2021 r2 on r1.id = r2.id
order by r1.id;
--OUT PUT
NAME ID RANK NAME_1 ID_1 RANK_1
---- -- ---- ---- ---- ------
Ken Krishna 102 3 Ken Krishna 102 1
Ray Rama 108 2 Ray Rama 108 2
(null) (null) (null) Tom Talla 103 2
--<b>MULTIPLE COLUMNS IN JOIN CONDITION</b>
--LEFT OUTER JOIN
--oracle notation
select * from r2020 r1, r2021 r2
where r1.id = r2.id (+) and
r1.rank = r2.rank (+)
order by r1.id;
--ANSI notation
select * from r2020 r1
left outer join r2021 r2 on r1.id = r2.id and
r1.rank = r2.rank
order by r1.id;
--OUT PUT
NAME ID RANK NAME_1 ID_1 RANK_1
---- -- ---- ---- ---- ------
Rob Rama 101 1 (null) (null) (null)
Ken Krishna 102 3 (null) (null) (null)
Ray Rama 108 2 Ray Rama 108 2
Kat Krishna 109 4 (null) (null) (null)
--RIGHT OUTER JOIN
--oracle notation
select * from r2020 r1, r2021 r2
where r1.id (+) = r2.id and
r1.rank(+) = r2.rank
order by r1.id;
--ANSI notation
select * from r2020 r1
right outer join r2021 r2 on r1.id = r2.id and
r1.rank = r2.rank
order by r1.id;
--OUT PUT
NAME ID RANK NAME_1 ID_1 RANK_1
---- -- ---- ---- ---- ------
(null) (null) (null) Ken Krishna 102 1
Ray Rama 108 2 Ray Rama 108 2
(null) (null) (null) Tom Talla 103 2
Чтобы написать запрос, который выполняет внешнее соединение таблиц A и B и возвращает все строки из A (левое внешнее соединение), используйте синтаксис LEFT [OUTER] JOIN в предложении FROM или примените оператор внешнего соединения (+) к все столбцы B в условии соединения в предложении WHERE
В этой теме есть неверная информация. Я скопировал и вставил неверную информацию:
ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT * FROM A, B WHERE A.column = B.column(+)ПРАВО НАРУЖНОЕ СОЕДИНЕНИЕ
SELECT * FROM A, B WHERE B.column(+) = A.column
Выше НЕПРАВИЛЬНО!!!!! Оно задом наперед. Как я определил, что это неверно, из следующей книги:
Oracle OCP Введение в Oracle 9i: Руководство по экзамену SQL. Таблица 3-1 содержит хорошее резюме по этому вопросу. Я не мог понять, почему мой конвертированный SQL не работал должным образом, пока я не пошел в старую школу и посмотрел печатную книгу!
Вот резюме из этой книги, скопированное построчно:
Синтаксис внешнего соединения Oracle:
from tab_a a, tab_b b,
where a.col_1 + = b.col_1
Эквивалент ANSI/ISO:
from tab_a a left outer join
tab_b b on a.col_1 = b.col_1
Обратите внимание, что это обратное тому, что написано выше. Я полагаю, что в этой книге могут быть ошибки, однако я доверяю этой книге больше, чем тому, что есть в этой теме. Это руководство к экзамену для громкого крика...