| Can we avoid using the (+) keyword in a outer join [message #374239] |
Fri, 01 June 2001 00:31  |
Sreenivas
Messages: 15
Registered: January 2000
|
Junior Member |
|
|
Hi ,
Can we write an outer join in oracle without using the
(+) keyword?. The idea is the same query should be used in informix (which has a different syntax for a outer query ,,.i.e we have to use OUTER instead of (+)).
The outer join query should be database independent(between oracle and infomix).
|
|
|
|
|
|
|
|
|
|
| Re: Can we avoid using the (+) keyword in a outer join [message #374247 is a reply to message #374243] |
Fri, 01 June 2001 16:17  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
This is untested, so you might have to play with it a bit. Assume you have DEPTs which may have no EMPs.
The Oracle way:
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept# = e.dept# (+);
The workaround:
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept# = e.dept#
UNION
select d.dept_name, null
from dept d
where d.dept# not in (select e.dept# from emp e);
To make it more efficient - eliminate the "not in":
select d.dept_name, e.emp_name
from dept d, emp e
where d.dept# = e.dept#
UNION
select d.dept_name, null
from dept d
where d.dept# in
(select e.dept# from emp e
MINUS
select d1.dept# from dept d1);
Footnote - Note that UNION and UNION ALL are similar. UNION eliminates duplicates from the resultset (something like DISTINCT). UNION ALL does not. UNION ALL is faster because there is one less step.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
