Outer join logic [message #385597] |
Tue, 10 February 2009 03:47  |
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   |
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 #385602 is a reply to message #385597] |
Tue, 10 February 2009 04:00   |
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 #385635 is a reply to message #385600] |
Tue, 10 February 2009 05:49   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In the first query, the line 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 #385689 is a reply to message #385638] |
Tue, 10 February 2009 10:18   |
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   |
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.
|
|
|
|