0

I have the below query.

Select a.c1,b.c1,a.c2,b.c2 from tab1 a,tab2 b
where a.c3(+)=b.c3 and b.c3(+) = 'abc'

I know the above query is left join tab1 with tab2. But I didn't understand what b.c3(+) mean. Please help me to resolve this. Thanks in advance.

mano
  • 67
  • 1
  • 7
  • 3
    Possible duplicate of [Oracle "(+)" Operator](https://stackoverflow.com/questions/4020786/oracle-operator) – André Stannek Jul 17 '17 at 13:59
  • https://stackoverflow.com/questions/4020786/oracle-operator – mohan111 Jul 17 '17 at 14:03
  • @AndréStannek - This question is **not a duplicate** of the one you suggested. The question here is "what does the (+) mean **when on the other side of the equality we have a constant value**" –  Jul 17 '17 at 14:25
  • @mohan111 - Same note to you (see my note above). –  Jul 17 '17 at 14:26
  • @mathguy I read it as OP wants to know what the operator does in general. I see your point but I'm not quite convinced. You are guessing that this is what OP meant. To be fair, I do so too. I will retract my close vote if OP confirms that your interpretation is correct. – André Stannek Jul 17 '17 at 14:35
  • @mathguy nevermind, I just discovered OPs comment below Gordons answer. – André Stannek Jul 17 '17 at 14:37
  • This is a slightly confusing example, as here `tab2` is the driving table and `tab1` is the one being outer-joined to it (at first glance it looks the other way around), so as mathguy spotted, a `(+)` against `tab2` is meaningless. If it was against `tab1` (the outer-joined table) that would be equivalent of specifying a literal expression within a `left outer join` condition. – William Robertson Jul 17 '17 at 18:16

2 Answers2

3

You don't need to understand what is happening. The syntax is obsolete and should be deprecated (similar syntax is deprecated in SQL Server).

Just learn to write this as an outer join:

Select a.c1, b.c1, a.c2, b.c2
from tab1 a left join
     tab2 b
     on a.c3 = b.c3 and b.c3 = 'abc';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    I'd be curious to know what your thoughts are of profs/lecturers who are actually still teaching 30+ year old syntax which is seldom used today. – Tim Biegeleisen Jul 17 '17 at 14:01
  • I am analyzing an old code. And there I got this line a.b(+)='abc' .Is this a.b(+) has some meaning or does this just mean a.b = 'abc' – mano Jul 17 '17 at 14:04
  • It means the expression that precedes it should be treated as the weak side of an outer join; i.e. in your example, if there is no row in table `A` matching `b.c3` then the `B` row will not be excluded from the results and will be treated as if it had joined to a row from `A` with all null values. – William Robertson Jul 17 '17 at 14:21
  • And this translates to a `right join`, not `left` :) – dnoeth Jul 17 '17 at 14:26
  • @WilliamRobertson - no, I don't think it means weak side of an outer join **in this case**. Specifically, in this case on the RHS there is a CONSTANT EXPRESSION. There is no join here at all. –  Jul 17 '17 at 14:27
  • @mathguy you are quite right, I missed that it was about the constant. – William Robertson Jul 17 '17 at 14:28
  • I never got that right, I just know it's not always simple to rewrite an old +-join to new syntax. *Never touch a running system* – dnoeth Jul 17 '17 at 14:29
  • @TimBiegeleisen - There are still parts of Oracle SQL that simply do not accept ANSI join syntax and require old Oracle proprietary syntax. Materialized views with fast refresh, for example, do not accept ANSI syntax. –  Jul 17 '17 at 14:33
0

Interesting question! You don't need more than one table to ask the question, or to experiment with it.

Take the EMP table in the SCOTT schema.

select * from emp where deptno = 10

will return three rows, for the employees in Department 10.

What do we get if we change this to

select * from emp where deptno(+) = 10

? One possibility is that we get an error message. Another is that - similar to Oracle notation for outer joins - we get rows for the EMP rows even when deptno is not equal to 10. (Although, really, that would be the wrong expectation; to get rows when the deptno is not 10, the (+) should be on the right-hand side - and that will cause an error.)

In fact, we get neither of those. Instead, the (+) is ignored. We get exactly the same three rows as before.

So your guess was correct: the (+) in that context does nothing.

Just to be very clear: the "context" is that we have a condition with = and with the (+) syntax on one side, but the other side is a constant expression - the condition is not an actual "join condition."