CODEWITHSUNDEEP
databaseoracle-databaseoracle11gleft-joinoracle-sqldeveloper
Shruti sharma
Shruti sharma

Reputation: 211

Old style join in Oracle SQL query

I know this syntax .i know its quite old but I am tuning an old query. I know below syntax

emp.deptno(+)=dept.deptno; this is right outer join.

But I am not able to understand meaning of below.

AND TRUNC(SYSDATE) BETWEEN ptm.effective_start_date (+) AND ptm.effective_end_date (+)

how (+) is getting used in between. what is the meaning of it?

Upvotes: 0

Views: 331

Answers (2)

Popeye
Popeye

Reputation: 35910

Apart from good explanation in other answers, there is a golden rule when you use the old syntax.

Rule:

  • always use the (+) sign with outer joined table's column in WHERE clause if any condition is checking value against any constant/sys values.

So it will go like this:

Select * from t1 , t2
 Where t1.col = t2.col(+) -- this represent outer join
   And t1.other_col = sysdate -- or some constant -- this is ok as this is main table in join
   And t2.other_col(+) = sysdate -- or some constant -- (+) is needed as it is outer joined table
                                 -- if you will omit (+) here, join will be converted to inner join

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21085

Let examine the followign query

select tab1.id, tab2.start_date, tab2.end_date
from  tab1,
tab2
where tab1.id = tab2.id(+) and
sysdate between tab2.start_date(+) and tab2.end_date(+)

The ANSI equivalent is

select tab1.id, tab2.start_date, tab2.end_date
from  tab1 
left outer join tab2
on tab1.id = tab2.id and
sysdate between tab2.start_date  and tab2.end_date

The meaning is join on ID and constraint the start_date and end_date but is there is no match in the second table, do not suppress the row from the first table.

Sample data and the result

select * from tab1;

        ID
----------
         1
         2

 select * from tab2;

        ID START_DATE          END_DATE           
---------- ------------------- -------------------
         1 01.01.2020 00:00:00 31.12.2020 00:00:00

the join returns

        ID START_DATE          END_DATE           
---------- ------------------- -------------------
         1 01.01.2020 00:00:00 31.12.2020 00:00:00
         2

Upvotes: 2

Related Questions