Re: Quick question re outer joins

From: Nuno Souto <dbvision_at_optusnet.com.au>
Date: Sat, 24 Jul 2004 00:42:11 +1000
Message-ID: <410123C3.4020104@optusnet.com.au>

Jonathan Gennick apparently said,on my timestamp of 23/07/2004 10:34 PM:

> We really need to know what direction Nuno is talking about.
>
> Nuno, any chance you can post an example?
>

I'll try with the HR schema as distributed with 9ir2. Drop the constraint EMP_JOB_FK on table EMPLOYEES. I know, it doesn't make sense in THAT context. It's just to show the thing.

Now, UPDATE employee_id = 178 to point to a non-existent job_id. Say: 'ZZZZ'. Commit;

Now do:
SELECT a.employee_id, a.last_name||' '||a.first_name,a.job_id from employees a;

.
.
.

107 rows.
Now do:
SELECT a.employee_id, a.last_name||' '||a.first_name,b.job_title from employees a, jobs b
where a.job_id = b.job_id(+); -- sorry, never know which is left/right...

.
.
.

107 rows.

Ie, same number of rows. This particular outer join does not change the number of rows. Does any other?

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 23 2004 - 09:38:44 CDT