CODEWITHSUNDEEP
sqloracle-database
mano
mano

Reputation: 67

Joins with + operator

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.

Upvotes: 0

Views: 160

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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';

Upvotes: 3

user5683823
user5683823

Reputation:

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."

Upvotes: 0

Related Questions