SOLVED?: Oracle interprets ANSI Inner Join as an Outer Join?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 23 Sep 2009 14:41:01 -0500
Message-ID: <7b8774110909231241s7bb3bcder9d9705a58e977a29_at_mail.gmail.com>



I found another reference that suggested LEFT join actually implies OUTER join. The ANSI standard itself is confusing to me, so I am all ears for accurate interpretations. =)
Another ramification is "did the vendor really mean to do an outer join?".

Or better yet, do modern DBAs need to pick up ANSI SQL? Or is Oracle SQL the way to go? =)

On Wed, Sep 23, 2009 at 14:24, Charles Schultz <sacrophyte_at_gmail.com> wrote:

> Good day, list,
> Not sure if this is an Oracle bug or a feature; under OSEE 10.2.0.2 on
> RHEL4 I have an ANSI "LEFT JOIN" that Oracle is turning into an OUTER JOIN.
> Any idea why? I am having a hard time getting the optimizer to force a INNER
> join without changing the query (stinking db-agnostic vendors *grin*).
>
> NOTE: I do not know ANSI SQL all that well, but from what I have read,
> INNER joins are implied unless OUTER joins are explicitly stated.
>
> SELECT ca_contact.last_name,
> ca_contact.first_name,
> ca_contact.middle_name,
> ca_contact.pri_phone_number,
> ca_contact.alternate_identifier,
> ca_contact.organization_uuid,
> ca_contact.location_uuid,
> ca_contact.contact_uuid
> FROM (MDBADMIN.ca_contact LEFT JOIN MDBADMIN.usp_contact ON
> ca_contact.contact_uuid = usp_contact.contact_uuid)
> WHERE usp_contact.z_uin = 'some_number' OR ca_contact.userid = 'some_value'
> ORDER BY ca_contact.last_name , ca_contact.first_name ,
> ca_contact.middle_name
> /
>
>
> --------------------------------------------------------------------
> | Id | Operation | Name | E-Rows |
> --------------------------------------------------------------------
> |* 1 | FILTER | | |
> | 2 | NESTED LOOPS OUTER | | 10 |
> | 3 | TABLE ACCESS BY INDEX ROWID| CA_CONTACT | 154K|
> | 4 | INDEX FULL SCAN | CA_CONTACT_IDX_02 | 10 |
> | 5 | TABLE ACCESS BY INDEX ROWID| USP_CONTACT | 1 |
> |* 6 | INDEX UNIQUE SCAN | XPKUSP_CONTACT | 1 |
> --------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter(("USP_CONTACT"."Z_UIN"=SYS_OP_C2C(:SYS_B_0) OR
> "CA_CONTACT"."USERID"=SYS_OP_C2C(:SYS_B_1)))
> 6 - access("CA_CONTACT"."CONTACT_UUID"="USP_CONTACT"."CONTACT_UUID")
>
>
> --
> Charles Schultz
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 23 2009 - 14:41:01 CDT

Original text of this message