Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Constructs
Outer Join Constructs [message #257954] Thu, 09 August 2007 12:17 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Has anyone seen a construct like the following?

and our_permit.cc55typc(+) = 'P'

What would be the point of doing an outer join with a constant?
Re: Outer Join Constructs [message #257958 is a reply to message #257954] Thu, 09 August 2007 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There surely is another outer join condition elsewhere.

Regards
Michel
Re: Outer Join Constructs [message #257968 is a reply to message #257954] Thu, 09 August 2007 12:42 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I think it is an example of incorrect coding. Look here:

http://orafaq.com/node/855
Re: Outer Join Constructs [message #257987 is a reply to message #257954] Thu, 09 August 2007 13:47 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You are right, I found the other join columns, and the outer join to the constant was "correct".

Thanks also for pointing out that link, as it is a nice little article and explains why the outer join to the constant is needed, which is an error that almost caught me in this case.

The result of using an outer join in this case I'm looking at, is that it causes the query, which selects a single column, to always return a single row, of either a null value or not. Rather than returning 0 or 1 rows had an inner join been used.

But then it gets strange, as this query is the first argument to a nvl(). This appears to be the whole reason for the outer join instead of the inner join: so that they'd have a single value to see if it was null. Instead of using an exists.

Or, instead of just using an inner join inside the nvl as in:

MYDBA@orcl > select nvl((select deptno from emp where ename = 'blah blah'),100) from dual;

NVL((SELECTDEPTNOFROMEMPWHEREENAME='BLAHBLAH'),100)
---------------------------------------------------
                                                100

1 row selected.

Previous Topic: ora-01466
Next Topic: Hierarchical Query but going backwards
Goto Forum:
  


Current Time: Sat Dec 10 22:50:54 CST 2016

Total time taken to generate the page: 0.09606 seconds