Home » SQL & PL/SQL » SQL & PL/SQL » Left Outer Join in Oracle
Left Outer Join in Oracle [message #227740] Thu, 29 March 2007 04:29 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi All,
How do we write a left outer join in Oracle in the below case using (+)

I have written this query in SQL Server and it works fine. How do we rewrite this in Oracle way. I know INNER JOIN/LEFT OUTER JOIN works in 9i onwards, but interested to know how the query would look like in its traditional syntax.

Query:


SELECT tab2.EMPLOYEE_ID, tab1.TRANS_DATE, tab1.Time, tab1.JOB_ID, tab3.charge
FROM 
table1 tab1
inner join table2 tab2 
    ON tab2.TRANS_ID=tab1.TRANS_ID
LEFT OUTER JOIN table3 tab3
    ON  tab2.EMPLOYEE_ID=tab3.EMPLOYEE AND tab1.TRANS_DATE
        BETWEEN tab3.START_DATE_ACTIVE AND NVL(tab3.END_DATE_ACTIVE, '31-MAR-4712')
        AND tab1.JOB_ID=tab3.JOB_ID



Thanks in advance
Sandi
[EDITED by DJM: cut overly long line]

[Updated on: Wed, 09 June 2010 19:17] by Moderator

Report message to a moderator

Re: Left Outer Join in Oracle [message #227745 is a reply to message #227740] Thu, 29 March 2007 04:48 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hi sandhya

If You have 2 tables say employees and dept that you want to do a left outer join on then this is how you do it.


select a.ename,b.dname,a.sal from emp a,dept b where
	 b.deptno=a.deptno(+)

ENAME   DNAME           SAL
----------------------------
CLARK	ACCOUNTING	2450
KING	ACCOUNTING	5000
MILLER	ACCOUNTING	1300
SMITH	RESEARCH	800
ADAMS	RESEARCH	1100
FORD	RESEARCH	3000
SCOTT	RESEARCH	3000
JONES	RESEARCH	2975
ALLEN	SALES	        1600
BLAKE	SALES	        2850
MARTIN	SALES	        1250
JAMES	SALES	        950
TURNER	SALES	        1500
WARD	SALES	        1250
	OPERATIONS	



Here i did a left outer join between dept table and emp table
and it returns me records from dept table for which there are no values in emp table.

An easy way to remember this would be
if you want to do

a left outer join b
on a.id=b.id

put it as

where a.id=b.id(+)

ie. if you want to do left outer join the (+) comes at the end of condition on RIGHT hand side.

if you wanted to do

a right outer join b
on a.id=b.id


put it as

where a.id(+)=b.id

Here we see that the (+) comes at the LEFT of equality operator
Re: Left Outer Join in Oracle [message #227747 is a reply to message #227745] Thu, 29 March 2007 04:54 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks for the update.
Can you please helpe in rewriting the same for the query in my example.
When I try to write it gives error: "a table may be outer joined to at most one other table"

Regards,
Sandi
Re: Left Outer Join in Oracle [message #227749 is a reply to message #227740] Thu, 29 March 2007 04:59 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You might be able to restructure it using inline views to avoid the multiple outer joins. It would look something like:

SELECT tab23.EMPLOYEE_ID, tab1.TRANS_DATE, tab1.JOB_ID, tab23.start_date_active
FROM 
(
select tab2.EMPLOYEE_ID, tab3.start_date_active, tab3.job_id, tab2.trans_id, tab3.end_date_active
from 
tab2 tab2,
tab3 tab3
where  tab2.EMPLOYEE_ID=tab3.EMPLOYEE (+)
) tab23,
tab1 tab1
where tab1.TRANS_DATE BETWEEN tab23.START_DATE_ACTIVE (+) AND NVL(tab23.END_DATE_ACTIVE(+), '31-MAR-4712')
and tab23.TRANS_ID(+)=tab1.TRANS_ID
AND tab1.JOB_ID=tab23.JOB_ID (+)
Re: Left Outer Join in Oracle [message #227759 is a reply to message #227749] Thu, 29 March 2007 05:31 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
Thats a great idea. I tried and it worked.

Thanks a lot.

Regards,
Sandi
Re: Left Outer Join in Oracle [message #227760 is a reply to message #227747] Thu, 29 March 2007 05:31 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Hi sandhya

Oracle is reporting this error because you are using the table tab2 multiple time for join .

select tab_newtab2.EMPLOYEE_ID,tab_newtab2.TRANS_DATE  ,tab_newtab2.Time ,tab_newtab2.JOB_ID  
from 
(SELECT tab2.EMPLOYEE_ID as EMPLOYEE_ID, tab1.TRANS_DATE as TRANS_DATE , tab1.Time as Time ,
        tab1.JOB_ID as JOB_ID,tab1.TRANS_DATE  as TRANS_DATE  
FROM 
table1 tab1, tab2 
where tab2.TRANS_ID=tab1.TRANS_ID) as tab_newtab2,tab3
where tab_newtab2.EMPLOYEE_ID=tab3.EMPLOYEE 
and   tab_newtab2.TRANS_DATE BETWEEN tab3.START_DATE_ACTIVE AND NVL(tab3.END_DATE_ACTIVE, '31-MAR-4712')
and tab_newtab2.JOB_ID=tab3.JOB_ID 


As i understand your requirement this is the query.

"Cthulhu" mate I haven't understood the query you wrote.
Please let me know if iam wrong

Thanks and regards
Asher
[EDITED by DJM: cut overly long line]

[Updated on: Wed, 09 June 2010 19:16] by Moderator

Report message to a moderator

Re: Left Outer Join in Oracle [message #227761 is a reply to message #227740] Thu, 29 March 2007 05:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think it's essentially the same as yours except that I joined tab2 and tab3 in the inline view whereas you did tab1 and tab2. Also, mine is actually an outer join whereas you don't seem to have an outer join anywhere.

[Updated on: Thu, 29 March 2007 05:36]

Report message to a moderator

Re: Left Outer Join in Oracle [message #227765 is a reply to message #227749] Thu, 29 March 2007 06:05 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
SELECT tab2.employee_id
     , tab1.trans_date
     , tab1.TIME
     , tab1.job_id
     , tab3.charge
FROM   table1 tab1
     , table2 tab2
     , table3 tab3
WHERE  tab2.trans_id = tab1.trans_id
AND    tab2.employee_id = tab3.employee(+)
AND    tab1.trans_date BETWEEN tab3.start_date_active(+) AND NVL
                                                                (tab3.end_date_active(+)
                                                               , '31-MAR-4712'
                                                                )
AND    tab1.job_id = tab3.job_id(+)

[Updated on: Thu, 29 March 2007 06:48] by Moderator

Report message to a moderator

Re: Left Outer Join in Oracle [message #227774 is a reply to message #227740] Thu, 29 March 2007 06:41 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If you actually try this, you'll find it doesn't work.
Re: Left Outer Join in Oracle [message #227775 is a reply to message #227774] Thu, 29 March 2007 06:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@muzahidul islam: I've modified your post in the following way.
1. I ran your code through a SQL Formatter
2. I added code tags.

Do so yourself next time. And check your code.

MHE
Re: Left Outer Join in Oracle [message #227812 is a reply to message #227775] Thu, 29 March 2007 08:18 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And...both parameters to an NVL must be of the same TYPE. Sloppy, sloppy, sloppy.
Previous Topic: What does Set Pages do?
Next Topic: Hey Guro: Need Urgent Help on writing Query
Goto Forum:
  


Current Time: Sun Dec 11 04:25:55 CST 2016

Total time taken to generate the page: 0.23954 seconds