From: Mark W. Farnham
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.

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,

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

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

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

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

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

Infosys e-mail system.

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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
postedJun 23, '08 at 4:17p
activeJun 23, '08 at 4:17p

1 user in discussion

Mark W. Farnham: 1 post



site design / logo © 2022 Grokbase