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

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.