Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Full Outer Join - Different results !!!!!

Re: Full Outer Join - Different results !!!!!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Apr 2004 11:09:58 +0000 (UTC)
Message-ID: <c630e6$7c5$1@hercules.btinternet.com>

Notes in-line

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"aphrikanos" <aphriki_at_yahoo.com> wrote in message
news:370b9881.0404200105.cf08728_at_posting.google.com...

> Hi all,
> Don't know what I am doing wrong but a full outer join does not return all
rows.
> if I change the tables to inline views it works.
>
> from
> s_d_comp_prod_qty_pvt y1
> full outer
> join s_d_comp_prod_qty_pvt y2
> on y1.company_id = y2.company_id and y1.part_no = y2.part_no
The result should be exactly what you see. The line above is your join clause, which will introduce rows with nulls on both sides.
> where y1.company_id = 'C0031'
> and y1.year = 2003
> and y2.year = 2004
>
Then your where clause takes the join result, and eliminates lots of rows. Change the WHERE to an AND - which makes all three tests part of the join condition, and I think you will see the result you want.
Received on Tue Apr 20 2004 - 06:09:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US