FW: Basic Qs on Outer Join ?

<snipped to fit>

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Monday, June 23, 2008 12:16 PM
To: 'VIVEK_SHARMA_at_infosys.com'; 'oracle-l_at_freelists.org' 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***

--
http://www.freelists.org/webpage/oracle-l