blocks|key|893738|text|运算符(%2B)表示外部联接。这意味着即使在没有匹配的情况下，Oracle仍将从连接的另一端返回记录。例如，如果a和b是emp和dept，并且您可以将员工取消分配到某个部门，则以下语句将返回所有员工的详细信息，无论他们是否已分配到某个部门。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|893739|select+*+from+emp,+dept+where+emp.dept_id=dept.dept_id(%2B)|code-block|syntax|javascript|893740|因此，简而言之，删除(%2B)可能会产生重大差异，但根据您的数据，您可能在一段时间内不会注意到这一点！|893741|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The (+) operator indicates an outer join. This means that Oracle will still return records from the other side of the join even when there is no match. For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they've been assigned to a department.

<pre><code>select * from emp, dept where emp.dept_id=dept.dept_id(+)
</code></pre>

So in short, removing the (+) may make a significance difference but you might not notice for a while depending on your data!

blocks|key|1108793|text|在实践中，%2B符号直接放在条件语句中，放在可选表的一侧(允许在条件语句中包含空或空值的表)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1108794|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In practice, the + symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional).

blocks|key|894146|text|在Oracle中，(%2B)表示联接中的“可选”表。因此，在您查询中，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|894147|SELECT+a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a,b
WHERE+a.id=b.id(%2B)|code-block|syntax|javascript|894148|这是表'b‘到表'a’的左外部联接。当另一端(可选表'b')没有数据时，它将返回表'a‘的所有数据而不会丢失其数据。|894149|​|894150|📷|atomic|offset|length|894151|894152|相同查询的现代标准语法为|894153|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a
LEFT+JOIN+b+ON+a.id=b.id|894154|或者使用a.id=b.id的简写(并非所有数据库都支持)：|style|CODE|894155|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a
LEFT+JOIN+b+USING(id)|894156|如果删除(%2B)，则它将是普通的内连接查询|894157|较旧的语法，在Oracle和其他数据库中：|894158|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a,b
WHERE+a.id=b.id|894159|更现代的语法：|894160|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a
INNER+JOIN+b+ON+a.id=b.id|894161|或者简单地说：|894162|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a
JOIN+b+ON+a.id=b.id|894163|894164|894165|894166|它将只返回'a‘和'b’表'id‘值相同的所有数据，表示公共部分。|894167|如果您想使查询成为右连接|894168|这与LEFT+JOIN相同，但切换哪个表是可选的。|894169|894170|894171|894172|旧Oracle语法：|894173|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a,b
WHERE+a.id(%2B)=b.id|894174|现代标准语法：|894175|SELECT++a.id,+b.id,+a.col_2,+b.col_2,+...
FROM+a
RIGHT+JOIN+b+ON+a.id=b.id|894176|参考帮助(&H)：|894177|https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187|894178|Left+Outer+Join+using+%2B+sign+in+Oracle+11g|894179|https://www.w3schools.com/sql/sql_join_left.asp|894180|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/deacbc6191e6d86e9ed7406ec1e915ac.jpeg|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/286cbfe399607b7e1adb410c517c1deb.jpeg|2|https://ask.qcloudimg.com/http-save/yehe-900000/fc5d5da825f4ef034b3af011f9f280b3.jpeg|3|LINK|MUTABLE|url|4|https://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g|5^0|0|0|0|0|0|1|0|0|0|0|0|4|9|0|0|0|0|0|0|0|0|0|0|0|1|1|0|0|0|0|0|0|0|1|2|0|0|0|0|0|0|0|0|29|3|0|0|16|4|0|0|1B|5|0^^$0|@$1|2|3|4|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|2R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|2S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|2T|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|2U|8|@]|9|@$N|2V|O|2W|1|2X]]|A|$]]|$1|P|3|J|5|6|7|2Y|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|2Z|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|30|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|31|8|@$N|32|O|33|W|X]]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|34|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|35|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|36|8|@]|9|@]|A|$]]|$1|14|3|15|5|D|7|37|8|@]|9|@]|A|$E|F]]|$1|16|3|17|5|6|7|38|8|@]|9|@]|A|$]]|$1|18|3|19|5|D|7|39|8|@]|9|@]|A|$E|F]]|$1|1A|3|1B|5|6|7|3A|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|D|7|3B|8|@]|9|@]|A|$E|F]]|$1|1E|3|J|5|6|7|3C|8|@]|9|@]|A|$]]|$1|1F|3|L|5|M|7|3D|8|@]|9|@$N|3E|O|3F|1|3G]]|A|$]]|$1|1G|3|J|5|6|7|3H|8|@]|9|@]|A|$]]|$1|1H|3|1I|5|6|7|3I|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|3J|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|6|7|3K|8|@]|9|@]|A|$]]|$1|1N|3|J|5|6|7|3L|8|@]|9|@]|A|$]]|$1|1O|3|L|5|M|7|3M|8|@]|9|@$N|3N|O|3O|1|3P]]|A|$]]|$1|1P|3|J|5|6|7|3Q|8|@]|9|@]|A|$]]|$1|1Q|3|1R|5|6|7|3R|8|@]|9|@]|A|$]]|$1|1S|3|1T|5|D|7|3S|8|@]|9|@]|A|$E|F]]|$1|1U|3|1V|5|6|7|3T|8|@]|9|@]|A|$]]|$1|1W|3|1X|5|D|7|3U|8|@]|9|@]|A|$E|F]]|$1|1Y|3|1Z|5|6|7|3V|8|@]|9|@]|A|$]]|$1|20|3|21|5|6|7|3W|8|@]|9|@$N|3X|O|3Y|1|3Z]]|A|$]]|$1|22|3|23|5|6|7|40|8|@]|9|@$N|41|O|42|1|43]]|A|$]]|$1|24|3|25|5|6|7|44|8|@]|9|@$N|45|O|46|1|47]]|A|$]]|$1|26|3|-4|5|6|7|48|8|@]|9|@]|A|$]]]|27|$28|$5|29|2A|2B|A|$2C|2D|2E|-4]]|2F|$5|29|2A|2B|A|$2C|2G|2E|-4]]|2H|$5|29|2A|2B|A|$2C|2I|2E|-4]]|2J|$5|2K|2A|2L|A|$2M|21]]|2N|$5|2K|2A|2L|A|$2M|2O]]|2P|$5|2K|2A|2L|A|$2M|25]]]]

In Oracle, (+) denotes the "optional" table in the JOIN. So in your query,

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id(+)
</code></pre>

it's a LEFT OUTER JOIN of table 'b' to table 'a'. It will return all data of table 'a' without losing its data when the other side (optional table 'b') has no data.

<a href="https://i.stack.imgur.com/bceqP.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/bceqP.jpg" alt="Diagram of Left Outer Join"></a>

The modern standard syntax for the same query would be

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b ON a.id=b.id
</code></pre>

or with a shorthand for <code>a.id=b.id</code> (not supported by all databases):

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b USING(id)
</code></pre>

<h1>If you remove (+) then it will be normal inner join query</h1>

Older syntax, in both Oracle and other databases:

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id
</code></pre>

More modern syntax:

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
INNER JOIN b ON a.id=b.id
</code></pre>

Or simply:

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
JOIN b ON a.id=b.id
</code></pre>

<a href="https://i.stack.imgur.com/ggzGl.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/ggzGl.jpg" alt="Diagram of Inner Join"></a>

It will only return all data where both 'a' &amp; 'b' tables 'id' value is same, means common part. 

<h1>If you want to make your query a Right Join</h1>

This is just the same as a LEFT JOIN, but switches which table is optional.

<a href="https://i.stack.imgur.com/vjgSN.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/vjgSN.jpg" alt="Diagram of Right Outer Join"></a>

Old Oracle syntax:

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id(+)=b.id
</code></pre>

Modern standard syntax:

<pre><code>SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a
RIGHT JOIN b ON a.id=b.id
</code></pre>

<h1>Ref &amp; help:</h1>

<a href="https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187" rel="noreferrer">https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187</a>

<a href="https://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g">Left Outer Join using + sign in Oracle 11g</a>

<a href="https://www.w3schools.com/sql/sql_join_left.asp" rel="noreferrer">https://www.w3schools.com/sql/sql_join_left.asp</a>

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.

The DBMS is Oracle

I did not understand a statement which read like this:

<pre><code>select ...
from a,b
where a.id=b.id(+)
</code></pre>

I am confused about the <code>(+)</code> operator, and could not get it at any forums... (searching for + within quotes didn't work either).

Anyway, I used 'Explain Plan' of SQLDeveloper and I got an output saying that <code>HASH JOIN, RIGHT OUTER</code>, etc.

Would there be any difference if I remove the <code>(+)</code> operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before <code>(+)</code> can be used?? It would be greatly helpful if you can provide me a simple understanding, or some good links where I can read about this.

Thanks!

Oracle "(+)" Operator

数据库

我正在检查一些旧的SQL语句，目的是记录它们，可能还会增强它们。DBMS为Oracle我不理解这样的陈述：select ...from a,bwhere a.id=b.id(+)我对(+)操作符感到困惑，在任何论坛上都找不到它……(在引号内搜索+也不起作用)。不管怎样，我使用了SQLDeveloper的“解释计划”，我...

问Oracle "(+)“运算符
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle "(+)“运算符EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle "(+)“运算符
EN