FW: Basic Qs on Outer Join ?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Jun 2008 12:17:35 -0400
<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 ?