Home » SQL & PL/SQL » SQL & PL/SQL » outer join
outer join [message #310796] Wed, 02 April 2008 07:55 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
hi
i have worked with all kinds of joins ,
but i found a join i am not able to figure out is its prupose , here it is
 select empno from emp e , dept d
       where d.dept# = e.deptno 
        and deptno(+) = 80


deptno(+) = 80 

what is its purpose. what effect we have outer joing a column with a constant.

please explain
Re: outer join [message #310799 is a reply to message #310796] Wed, 02 April 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing in your example.

Regards
Michel
Re: outer join [message #310801 is a reply to message #310796] Wed, 02 April 2008 08:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
When you ran it, since it is based on the standard example tables, what was the result you got? It would be useful for you to run it and see.

Remember that an outer join is still an outer join, and that a constant will "always be there".

Also remember that sometimes people write queries that don't actually solve the problem they think they are solving.
Re: outer join [message #310807 is a reply to message #310801] Wed, 02 April 2008 08:20 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
here table "emp" has data , "dept1" has no data.
i tried , this worked as it returned rows.
select empno from emp e , dept1 d
where  d.dept#(+) = e.deptno

but this query did not return any row.
select empno from emp e , dept1 d
where  d.dept#(+) = 20


my prupose behind this query was to run a query equuavalent to
select empno from emp e , dept1 d
where  d.dept# = nvl(:dno , dept#)

as i thought that outer join there may return all the rows if we dont have any dept as deptno=80.
and that will greatly improve the performance of the query , but hte case not to be
Re: outer join [message #310811 is a reply to message #310807] Wed, 02 April 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want us to accurately answer to your questions you must either provide the test case or use the standard scott tables.
We don't have dept1 table and its data, use dept table.

Regards
Michel
Re: outer join [message #310812 is a reply to message #310807] Wed, 02 April 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL>  select ename, dname
  2   from emp e, dept d
  3   where d.deptno (+) = e.deptno
  4    and d.deptno = 80
  5  /

no rows selected

SQL> select ename, dname
  2  from emp e, dept d
  3  where d.deptno (+) = e.deptno
  4    and d.deptno (+) = 80
  5  /
ENAME      DNAME
---------- --------------
MILLER
KING
CLARK
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
FORD
ADAMS
SCOTT
JONES
SMITH

14 rows selected.

Regards
Michel
Re: outer join [message #310815 is a reply to message #310812] Wed, 02 April 2008 08:39 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
Thanks a lot for your response Micheal.
this will really help me in my queries.
Regards
Imtiaz
Previous Topic: Error ORA-01652 while running select query
Next Topic: ORA-00905, ORA-06512 during Create External Table in PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 18:35:20 CST 2016

Total time taken to generate the page: 0.09830 seconds