Hi All,
I recently happened to see a article in the given link below and its quiet interesting to know that scalar sub query is suitable for replacing outer joins.
http://www.oratechinfo.co.uk/scalar_subqueries.html#scalar2
I have 2 clarifications on this.
1) As said in the link i had tried with my sample data in emp&dept tables. I was not able to see any performance gain by changing a outer join query into a scalar sub query.
2) It seems there is a restriction to that too as it allows only left outer join. If we happen to right a right outer join we tend to get the error "Sub query returns more than 1 row". So is there any way to avoid this error and by pass the same and get the benefits of scalar subquery(as claimed in the external link provide) and avoid outer joins.
Examples i have used.
--Left Outer Join
select e.empno, d.dname
from dept d, emp e
where d.deptno(+) = e.deptno
--Replaced with Scalar Subquery
select e.empno,(select d.dname from dept d
where e.deptno=d.deptno
)dname
from emp e
--Right outer join
select e.empno, d.dname
from dept d, emp e
where d.deptno = e.deptno(+)
-- How can the equivalent can be written for this in scalar a subquery.
Request the sql experts to discuss and clarify me on this.
Thanks in advance.