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

From: Harel Safra <harel.safra_at_gmail.com>
Date: Wed, 23 Sep 2009 22:43:50 +0300
Message-Id: <AC6613B6-EC6D-41EC-AF11-E2E6178DD1B4_at_gmail.com>

The "outer" keyword is optional. Left join implies left outer join: outer_join_type
The outer_join_type indicates the kind of outer join being performed:

Specify RIGHT to indicate a right outer join.

Specify LEFT to indicate a left outer join.

Specify FULL to indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls.

You can specify the optional OUTER keyword following RIGHT, LEFT, or FULL to explicitly clarify that an outer join is being performed.

Harel

ב-23/09/2009, בשעה 22:24, Charles Schultz <sacrophyte_at_gmail.com> כתב/ה:

> 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

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

Received on Wed Sep 23 2009 - 14:43:50 CDT