Home » SQL & PL/SQL » SQL & PL/SQL » Outer join logic (Oracle 9.2)
Outer join logic [message #385597] Tue, 10 February 2009 03:47 Go to next message
mikey71
Messages: 2
Registered: February 2009
Junior Member
Hi, Can you please explain the difference in the 2 sql queries below and why they dont return the same result set?

SQL>
SQL> -- Manager's request:
SQL> -- Report all Departments and Employees who draw a commission including Departments where no employee draws a commission.

SQL> -- Query1
SQL> SELECT
  2   d.dname, e.ename, e.job, e.comm
  3  FROM
  4   portal30_demo.dept d,
  5   portal30_demo.emp e
  6  WHERE
  7   d.deptno = e.deptno (+)
  8   AND e.comm IS NOT NULL
  9  ORDER BY
 10   d.dname,
 11   e.ename;

DNAME          ENAME      JOB       COMM                    
-------------- ---------- --------- ----------                    
SALES          ALLEN      SALESMAN  300                    
SALES          MARTIN     SALESMAN  1400                    
SALES          TURNER     SALESMAN  0                    
SALES          WARD       SALESMAN  500                    

SQL> -- Incorrect result.
SQL> -- Returns rows only where EMP rows exist and COMM is not null.

SQL> -- Query2
SQL> SELECT
  2   d.dname, e.ename, e.job, e.comm
  3  FROM
  4   portal30_demo.dept d,
  5   (SELECT *
  6    FROM portal30_demo.emp e
  7    WHERE e.comm IS NOT NULL) e
  8  WHERE
  9   d.deptno = e.deptno (+)
 10  ORDER BY
 11   d.dname,
 12   e.ename;

DNAME          ENAME      JOB       COMM                    
-------------- ---------- --------- ----------                    
ACCOUNTING                                                      
OPERATIONS                                                      
RESEARCH                                                        
SALES          ALLEN      SALESMAN 300                    
SALES          MARTIN     SALESMAN 1400                    
SALES          TURNER     SALESMAN 0                    
SALES          WARD       SALESMAN 500  

SQL> -- Correct result.
SQL> -- Returns rows where no EMP rows exist, and EMP rows where COMM is not null.
Questions:
Is Query2 the best way to code the query and why doesnt Query1 return the same result as Query2?

Interestingly, if "e.comm is not null" is changed to "e.comm is null" then both queries return the same result. Why is this?

[Updated on: Tue, 10 February 2009 03:55] by Moderator

Report message to a moderator

Re: Outer join logic [message #385599 is a reply to message #385597] Tue, 10 February 2009 03:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try to do an explain plan for both the queries and analyze the output of the plan. In one of your query you will find the keyword outer and in the other query you will find either a nested loop or hash join based on the volume of the records. These two queries are different. Oracle is clever enough to identify when to use outer join and when not to even though you insist on using the outer join operator.

Also check this link for the most common mistakes been made when using outer join.

http://www.orafaq.com/node/855

Regards

Raj

P.S : Also from next time please format your post so that it will be easier to read your post.
Re: Outer join logic [message #385600 is a reply to message #385597] Tue, 10 February 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
why doesnt Query1 return the same result as Query2?

You have to also outer join the condition on emp:
AND e.comm (+) IS NOT NULL

Regards
Michel

Re: Outer join logic [message #385602 is a reply to message #385597] Tue, 10 February 2009 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Outer joins return a made up row of nulls for the outer joined table if a matching row can't be found.
Consequently this:
SELECT d.dname, e.ename, e.job, e.comm
FROM
portal30_demo.dept d,
portal30_demo.emp e
WHERE
d.deptno = e.deptno (+)
AND e.comm IS NOT NULL
ORDER BY
d.dname,
e.ename;


Is logically equivalent to this:

SELECT d.dname, e.ename, e.job, e.comm
FROM
portal30_demo.dept d,
portal30_demo.emp e
WHERE
d.deptno = e.deptno
AND e.comm IS NOT NULL
ORDER BY
d.dname,
e.ename;


I.e. the same query without the outer join.
This is because for a dept without matching emp records oracle makes up a row of nulls for the emp record.
The IS NOT NULL constraint then discards this made up row.

The 2nd SQL:
SELECT
d.dname, e.ename, e.job, e.comm
FROM
portal30_demo.dept d,
(SELECT *
 FROM portal30_demo.emp e
 WHERE e.comm IS NOT NULL) e
 WHERE d.deptno = e.deptno (+)
ORDER BY
d.dname,
e.ename;


Works the way you're expecting it to because the IS NOT NULL restriction is inside the inline view and is applied BEFORE the outer join.
So that one links dept to a list of employees who get commision and the outer join means that depts that don't have a matching emp in the list are still reported.

Hope that's clear.

By the way - next time you post code here can you use code tags like I have - details of how to do it are in the ORAFAQ forum guide.
Re: Outer join logic [message #385634 is a reply to message #385597] Tue, 10 February 2009 05:45 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

@mikey71

Query2 will give the wrong result set as per your requirement. Because it returns the dname with comm as null as it is outer join with emp.

[Updated on: Tue, 10 February 2009 05:48]

Report message to a moderator

Re: Outer join logic [message #385635 is a reply to message #385600] Tue, 10 February 2009 05:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the first query, the line
AND e.comm IS NOT NULL
means that the outer join isn't used - the outer join will return a record composed entirely of null values if the join condition is not met. The line show above tells Oracle to ignore all these records.
Re: Outer join logic [message #385638 is a reply to message #385635] Tue, 10 February 2009 05:56 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Tue, 10 February 2009 11:49
In the first query, the line
AND e.comm IS NOT NULL
means that the outer join isn't used - the outer join will return a record composed entirely of null values if the join condition is not met. The line show above tells Oracle to ignore all these records.


Could have sworn that was what I said Confused
Re: Outer join logic [message #385689 is a reply to message #385638] Tue, 10 February 2009 10:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Could have sworn that was what I said


Completely true. I was called away between reading the question and making my post, and didn't refresh the thread before replying, and so didn't see your reply.

Re: Outer join logic [message #385690 is a reply to message #385597] Tue, 10 February 2009 10:21 Go to previous messageGo to next message
mikey71
Messages: 2
Registered: February 2009
Junior Member
Thanks for your comments - ok i understand.

Just one follow-on question. If you needed to report all Departments without Employees, and all Employees without Departments how would you write the code.

Thanks again.
Re: Outer join logic [message #385693 is a reply to message #385597] Tue, 10 February 2009 10:36 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHERE NOT EXISTS
Previous Topic: outer join / normal select / analytical function
Next Topic: PLS-00049: Error
Goto Forum:
  


Current Time: Sat Feb 15 12:16:34 CST 2025