FW: Basic Qs on Outer Join ?

<snipped to fit>

  _____  

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Monday, June 23, 2008 12:16 PM
To: 'VIVEK_SHARMA@xxxxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Basic Qs on Outer Join ?

 

There is no restriction on A, so it will return all the rows from table A.

 

For rows where B contains a row where B.f3 matches A.f3, then B.f2 will be
the value in that row. If there are duplicate rows in B for an A.f3, you'll
get multiple rows with whatever the actual B.f2 values are.

For rows where B does not contain a B.f3 that matches A.f3, then B.f2 will
be null.

And nulls for f3 in B do not match nulls for f3 in A, like this using ~ as
the display value for null:

 

SQL> select * from a;

 

F1                 F3

---------- ----------

a row 1             1

a row 2             2

a row 3x            3

a row 3y            3

a row 3z            3

a row 4             4

a row null ~

 

7 rows selected.

 

SQL> select * from b;

 

F2                 F3

---------- ----------

b row 1             1

b row 3             3

b row 4x            4

b row 4y            4

b row 4z            4

b row 5             5

b row null ~

 

7 rows selected.

 

SQL> select a.f1,b.f2 from a,b where a.f3=b.f3(+)

  2  order by a.f3,a.f1;

 

F1         F2

---------- ----------

a row 1    b row 1

a row 2    ~

a row 3x   b row 3

a row 3y   b row 3

a row 3z   b row 3

a row 4    b row 4x

a row 4    b row 4y

a row 4    b row 4z

a row null ~

 

9 rows selected.

<snip>

 

 

 

 

 


**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely


for the use of the addressee(s). If you are not the intended recipient,
please 

notify the sender by e-mail and delete the original message. Further, you
are not 

to copy, disclose, or distribute this e-mail or its contents to any other
person and 

any such actions are unlawful. This e-mail may contain viruses. Infosys has
taken 

every reasonable precaution to minimize this risk, but is not liable for any
damage 

you may sustain as a result of any virus in this e-mail. You should carry
out your 

own virus checks before opening the e-mail or attachment. Infosys reserves
the 

right to monitor and review the content of all messages sent to or from this
e-mail 

address. Messages sent to or from this e-mail address may be stored on the 

Infosys e-mail system.

***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts:

  • » FW: Basic Qs on Outer Join ?