SQL Tip::Using OUTER JOIN with Filter

September 12th, 2007 by Rajender Singh · 4 Comments

Hi Guys!

Today I came across very simple and very interesting fact!

First Assume as follows:

Table Structure
——————————-

SQL> desc emp;
Name Null? Type
—————————————– ——– ————-
EMP_CODE NUMBER
EMP_NAME VARCHAR2(100)

SQL> desc emp_dept;
Name Null? Type
—————————————– ——– ————-
EMP_CODE NUMBER
DEPT_CODE NUMBER
FROM_DATE DATE
TO_DATE DATE

Table Data
——————————-

SQL> select * from emp;

EMP_CODE EMP_NAME
———- —————
10000 Oracle Nerd
20000 Oracle Nerd2

SQL> select * from emp_dept;

EMP_CODE DEPT_CODE FROM_DATE TO_DATE
———- ———- ———- ———-
10000 10 12/12/2005 30/12/2006
10000 10 31/12/2006

Now I want to list out all the employee with their department, to do so I will shoot out query as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, emp_dept
3 WHERE emp.emp_code = emp_dept.emp_code
4 AND TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)
6 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10

But now when we see the result we see “Oracle Nerd2″ is not coming, so I changed my query and put outer join as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, emp_dept
3 WHERE emp.emp_code = emp_dept.emp_code (+)
4 AND TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)
6 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10

But still I cannot see “Oracle Nerd2″, then I changed my query as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, ( SELECT emp_code, dept_code
3 FROM emp_dept
4 WHERE TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)) emp_dept
6 WHERE emp.emp_code = emp_dept.emp_code (+)
7 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10
20000 Oracle Nerd2

Ahhh at last its ok now!

Now what has happened?

Actually Oracle was doing the join first and then filtering the query where as I need to filter data first to get the current department and then do the join!

Any way at last problem solved for my team with use of this inline view with outer join!

Bookmark this post

Hide

Tags: Interesting Coding Showcase · SQL and PL/SQL

4 responses so far ↓

1 Nigel // Sep 12, 2007 at 3:40 pm

Hi Brains

Another maybe simpler approach is to use ANSI syntax for the outer join, which is a whole lot easier to read once you get used to it - no worrying about lost (+) or having to build inline views:

select emp.emp_code, emp.emp_name, emp_dept.dept_code
from emp
LEFT JOIN emp_dept
ON emp.emp_code = emp_dept.emp_code AND TO_CHAR(from_date, â€˜YYYYMMâ€™) = â€˜20070912â€² OR to_date IS NULL)

Oh, and that date comparison looks completely wrong; usually you want to turn your literal into a date (rather than your column into a varchar2) something like:

from_date to_date(’20070912′,’yyyymmdd’))

If you have an index that includes FROM_DATE, this may now be used.

NB using TO_DATE as a column name is dodgy, when it is also a function name…

Regards Nigel
2 Patrick Wolf // Sep 12, 2007 at 5:31 pm

Hi,

it’s just a matter of correctly setting the brackets if you have a OR clause!
Try the following SQL statement instead. BTW, your date compare can also never match, because you have a format mask of YYYYMM but compare agains 20070912. Anyway I’m not sure what you expect from this equal date comparison.

SELECT e.*, d.*
FROM emp e
, emp_dept d
WHERE d.emp_code (+) = e.emp_code
AND ( to_char(d.from_date, ‘YYYYMM’) = ‘200709′
OR d.to_date IS NULL
);

Greetings
Patrick
3 Rajender Singh // Sep 12, 2007 at 8:47 pm

Hi Nigel,

“Another maybe simpler approach is to use ANSI syntax for the outer join, which is a whole lot easier to read once you get used to it - no worrying about lost (+) or having to build inline views”

I agree with you thats a better and much simpler way to solve same problem.

Surely I will let me Team member know about it and will force them to use your way.

“Oh, and that date comparison looks completely wrong; usually you want to turn your literal into a date (rather than your column into a varchar2) something like”

You are right it should be ‘yyyymmdd’ not ‘yyyymm’.

“NB using TO_DATE as a column name is dodgy, when it is also a function name”

Here also you are absolutely right!

Thanks for such a good feedback and advice!

Regards,

Rajs

PS. When writing this comment, we had an earthquake in Malaysia. Believe me I was sitting on the 12th floor of the building. So I have to run for my life to ground floor.

Any way everything is okay now and nothing happen to our building.
4 Rajender Singh // Sep 12, 2007 at 9:23 pm

Hi Patrick & Nigel,

I am sorry for putting the where condition in such a way!

I was so excited with my finding that I really didn’t double check before posting them.

Actually Oracle was doing the join first and then filtering the query where as I need to filter data first to get the current department and then do the join!

I am again posting the condition which will bring only one row if I use Patrick or Nigel or what I though before!

Nigel:

SELECT e.emp_code, e.emp_name, d.dept_code,d.from_date,d.to_date
FROM emp e LEFT JOIN emp_dept d ON e.emp_code = d.emp_code
WHERE ( to_char(to_date, ‘YYYYMM’) = ‘200709′
OR d.to_date IS NULL)
AND d.from_date IS NOT NULL
/

Patrick:

SELECT e.emp_code, e.emp_name, d.dept_code,d.from_date,d.to_date
FROM emp e, emp_dept d
WHERE d.emp_code (+) = e.emp_code
AND ( to_char(to_date, ‘YYYYMM’) = ‘200709′
OR d.to_date IS NULL)
AND d.from_date IS NOT NULL
/

New One which will bring right result!

SELECT e.emp_code, e.emp_name, d.dept_code
FROM emp e, ( SELECT emp_code, dept_code
FROM emp_dept
WHERE (TO_CHAR(to_date, ‘YYYYMM’) = ‘200709′ OR to_date IS NULL)
AND from_date IS NOT NULL) d
WHERE e.emp_code = d.emp_code (+)
/

Again I am sorry for not posting right condition!

Thanks Patrick and Nigel for your contribution to this discussion!

Looking for more from you guys in future!

Regards,

Raj