Tom:

You've been doing SQL for a few years... I've never really found the "ansi" syntax to be all that appealing. Maybe I'm not being flexible - I'm also having a hard time moving from C to Java (even though I don't code that much anymore, I suppose I should stay current :) especially with the pace that Oracle Corp. is integrating/implementing Java features!) What's your preference for the "Oracle" syntax and the "ansi" syntax?

- Mark

Is the syntax shown in your explanation for 9i only. I am on 8.1.7 and the select statement does not parse correctly.

Your Query:
select * from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
left outer join d on a.x = d.x ) left outer join e on a.x = e.x )


My results:
SQLWKS> select * from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
2> left outer join d on a.x = d.x ) left outer join e on a.x = e.x )
3>
select * from (((( a inner join b on a.x = b.x ) left outer join c on a.x = c.x )
*
ORA-00928: missing SELECT keyword

Thanks
Ted

ANSI join might be superior when you join 2 tables with more than one equality predicate. For example, what does

select * from emp, dept
where emp.deptno = dept.deptno
and emp.deptno = dept.deptno(+)

mean?

The example

select a.c1, a.c2, b.c1, b.c2 from (
select 1 c1, NULL c2 from dual
union
select 1 c1, 1 c2 from dual
union
select 1 c1, 2 c2 from dual
) a,(
select 1 c1, NULL c2 from dual
union
select 1 c1, 1 c2 from dual
union
select 2 c1, 1 c2 from dual
) b
where a.c1(+)= b.c1 and a.c2= b.c2(+)

demonstrates that the outer join is really between the tables, not between the join predicates. So does ANSI syntax. Plus you can express full outer join.

Hello TOM,
I traversed all the outer join here. But i coudn't know how to write the query for following table.

TABLE T1:
column A
1
2
3
4
5
6
TABLE T2
COLUMN A COLUMN B COLUMN C
1 10 10
2 10 20
3 10 30
TABLE T3
COLUMN A COLUMN C
3 10
4 20
5 30
I need the result like as follows:

A B C
1 10 10
2 10 20
3 10 30
3 null 10
4 null 20
5 null 30
6 null null

I can achive with the following statement.
select t2.a, t2.b , t2.c from t2
union
select t3.a,null,t3.c from t3
union
select t1.a,null,null from t1
Where not exists (
select 1 from t2 where t1.a = t2.a union select 1 from t3 where t1.a = t3.a)

Is there any other way to do it?. B'cos our table has more than 1 lakh rows in t1, t2 and t3. It will take more time to fetch the record thru this way.

We are using oracle 8i and oracle 9i.

sorry -- the question you are asking requires literally a TON of work -- a ton.  

Looks like a problem in the data model more then anything else.  

You can try:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select t1.a, x.b, x.c
  2  from t1, (select a, b, c from t2 UNION ALL select a, to_number(null), c from t3 ) x
  3  where t1.a = x.a(+)
  4  /

         A          B          C
---------- ---------- ----------
         1         10         10
         2         10         10
         3         10         10
         3                    10
         4                    20
         5                    30
         6

7 rows selected.

which should be more efficient then the above.

 

Hello TOM,
Thanks very much. I need this way. B'cos my production tables are like this.

regards,
sivababu

Hi Tom

I have a further query regarding full outer joins in 9i. I tried these two syntax

select empno, b.deptno
from emp a full outer join dept b on (a.deptno = b.deptno)
and empno=7934

select empno, b.deptno
from emp a full outer join dept b on (a.deptno = b.deptno)
where empno=7934

I get two different results! What´s the difference between using an AND and a WHERE after outer join condition?

Hi Tom

I am probably dumb but how can you join a.deptno=deptno and empno=7934

may be if you could rewrite

select empno, b.deptno
from emp a full outer join dept b on (a.deptno = b.deptno and empno=7934)

to the old style SQL (with right outer join) so I might be able to understand it :-(

SELECT RT.XID,NVL(RL.TEXT,RLFB.TEXT),NVL(RL.REVERSE_NAME,RLFB.REVERSE_NAME),RT.SYMMETRIC, RT.TRANSITIVE,RT.INHERIT,RT.AUTOMATIC,RT.XTENSION_XID
FROM MAM_RELATION_TYPES RT,MAM_RELATION_LANGUAGES RL,
MAM_RELATION_LANGUAGES RLFB
WHERE RT.XID > 4 AND RL.RELATION_TYPE_XID(+) = RT.XID
AND RL.LANGUAGE_ID(+) = 4 AND RLFB.RELATION_TYPE_XID = RT.XID AND RLFB.LANGUAGE_ID = 1

What is the meaning of RL.LANGUAGE_ID(+) = 4 in this Query as the outer join is always wrt the table.columnname. Please explain this concept?

Can uou please rewrite this query for us using Ansi (to be converted and run well in Oracle 9i)

Outer joins have always managed to confuse me.  Could you help with this?
SQL> DROP TABLE ACTIVITY_TEST;

Table dropped.

SQL> CREATE TABLE ACTIVITY_TEST (
  2    ACTIVITY_ID  NUMBER,
  3    NAME         VARCHAR2 (30),
  4    MORDER       NUMBER (3),
  5    BGCOLOR      VARCHAR2 (7),
  6    TEXT_COLOUR  VARCHAR2 (7) ) ;

Table created.

SQL> INSERT INTO ACTIVITY_TEST ( ACTIVITY_ID, NAME, MORDER, BGCOLOR,
  2  TEXT_COLOUR ) VALUES (
  3  1, 'Key Kids events', 1, '#9999CC', '#000000');

1 row created.

SQL> DROP TABLE EVENT_TEST;

Table dropped.

SQL> CREATE TABLE EVENT_TEST (
  2    ID             NUMBER        NOT NULL,
  3    START_DATE     DATE,
  4    END_DATE       DATE,
  5    GROUP_ID       NUMBER,
  6    FRANCHISE_ID   NUMBER,
  7    ACTIVITY_ID    NUMBER,
  8    USER_ID        NUMBER,
  9    PLACE          VARCHAR2 (50),
 10    NOTES          VARCHAR2 (2000),
 11    LAST_UPDATED   DATE,
 12    COUNTRY_ID     VARCHAR2 (4),
 13    ACTIVITY_NAME  VARCHAR2 (100),
 14    TIME           VARCHAR2 (30),
 15    TASKFORCE_ID   NUMBER ) ;

Table created.

SQL> INSERT INTO EVENT_TEST ( ID, START_DATE, END_DATE, GROUP_ID, FRANCHISE_ID, ACTIVITY_ID, USER_ID,
  2  PLACE, NOTES, LAST_UPDATED, COUNTRY_ID, ACTIVITY_NAME, TIME, TASKFORCE_ID ) VALUES (
  3  1011,  TO_Date( '10/01/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '10/01/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
  4  , 3, 0, 1, 360, NULL, NULL,  TO_Date( '01/23/2002 06:16:34 PM', 'MM/DD/YYYY HH:MI:SS AM')
  5  , '1', 'Halloween', NULL, NULL);

1 row created.

SQL> INSERT INTO EVENT_TEST ( ID, START_DATE, END_DATE, GROUP_ID, FRANCHISE_ID, ACTIVITY_ID, USER_ID,
  2  PLACE, NOTES, LAST_UPDATED, COUNTRY_ID, ACTIVITY_NAME, TIME, TASKFORCE_ID ) VALUES (
  3  6222,  TO_Date( '10/12/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '10/12/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
  4  , 3, 1, 1, 101, NULL, NULL,  TO_Date( '06/19/2002 02:01:45 PM', 'MM/DD/YYYY HH:MI:SS AM')
  5  , '1', 'Great Activity', NULL, NULL);

1 row created.

SQL> DROP TABLE COUNTRY;

Table dropped.

SQL> CREATE TABLE COUNTRY (
  2    COUNTRY_ID  VARCHAR2 (4),
  3    COUNTRY     VARCHAR2 (30),
  4    IMAGE_NAME  VARCHAR2 (30),
  5    ALT_NAME    VARCHAR2 (20),
  6    SHORT_CODE  VARCHAR2 (3) );

Table created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '1', 'United Kingdom', 'uk_flag_curve.gif', 'United Kingdom', 'UK');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '2', 'France', 'france_flag_curve.gif', 'France', 'FR');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '3', 'Germany', 'gsa_flag_curve.gif', 'GSA', 'DE');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '4', 'Spain', 'iberia_flag_curve.gif', 'Iberia', 'ES');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '5', 'Belgium', 'benelux_flag_curve.gif', 'Benelux', 'BE');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '6', 'Switzerland', 'gsa_flag_curve.gif', 'GSA', 'CH');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '7', 'Sweden', 'nordic_flag_curve.gif', 'Nordic', 'SE');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '8', 'Norway', 'nordic_flag_curve.gif', 'Nordic', 'NO');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '9', 'Netherlands', 'benelux_flag_curve.gif', 'Benelux', 'NL');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '10', 'Luxembourg', 'benelux_flag_curve.gif', 'Benelux', 'LU');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '11', 'Italy', 'italy_flag_curve.gif', 'Italy', 'IT');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '12', 'Finland', 'nordic_flag_curve.gif', 'Nordic', 'FI');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '13', 'Denmark', 'nordic_flag_curve.gif', 'Nordic', 'DK');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '0', 'Europe', 'europe_flag_curve.gif', 'Europe', NULL);

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '100', 'None', NULL, NULL, NULL);

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '14', 'Poland', 'poland_flag_curve.gif', NULL, 'PL');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '15', 'Austria', 'gsa_flag_curve.gif', 'GSA', 'AT');

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '16', 'Emerging Markets', 'emerging_flag_curve.gif', 'Emerging Markets', NULL);

1 row created.

SQL> INSERT INTO COUNTRY ( COUNTRY_ID, COUNTRY, IMAGE_NAME, ALT_NAME,
  2  SHORT_CODE ) VALUES (
  3  '17', 'Portugal', 'iberia_flag_curve.gif', 'Iberia', 'PO');

1 row created.

SQL> DROP TABLE FRANCHISE_TEST;

Table dropped.

SQL> CREATE TABLE FRANCHISE_TEST (
  2    ID        NUMBER,
  3    NAME      VARCHAR2 (50),
  4    BGCOLOR   VARCHAR2 (10),
  5    XLOB      VARCHAR2 (1)  NOT NULL,
  6    CALENDAR  VARCHAR2 (1)  NOT NULL ) ;

Table created.

SQL> INSERT INTO FRANCHISE_TEST ( ID, NAME, BGCOLOR, XLOB, CALENDAR ) VALUES (
  2  1, 'Franchise 1', '#FFFF33', 'N', 'Y');

1 row created.

SQL> INSERT INTO FRANCHISE_TEST ( ID, NAME, BGCOLOR, XLOB, CALENDAR ) VALUES (
  2  0, 'None', '#FFFFFF', 'N', 'Y');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into activity_test Values(2,'School Events',2,'#FF9999','#000000');

1 row created.

SQL> commit;

Commit complete.

SQL> col name format a20
SQL> col activity_name format a20

SQL> select a.name, e.activity_name
  2  from activity_test a, event_test e
  3* WHERE a.activity_id = e.activity_id

NAME                 ACTIVITY_NAME                                              
-------------------- --------------------                                       
Key Kids events      Halloween                                                  
Key Kids events      Great Activity                                             

SQL> ed
Wrote file afiedt.buf

  1  select a.name, e.activity_name
  2  from activity_test a, event_test e
  3* WHERE a.activity_id = e.activity_id(+)
SQL> /

NAME                 ACTIVITY_NAME                                              
-------------------- --------------------                                       
Key Kids events      Halloween                                                  
Key Kids events      Great Activity                                             
School Events                                                                   

SQL> ed
Wrote file afiedt.buf

  1  select a.name, e.activity_name, f.bgcolor
  2  from activity_test a, event_test e, country c, franchise_test f
  3  WHERE a.activity_id = e.activity_id(+)
  4  AND   c.country_id = e.country_id
  5* AND   e.franchise_id = f.id
SQL> /

NAME                 ACTIVITY_NAME        BGCOLOR                               
-------------------- -------------------- ----------                            
Key Kids events      Halloween            #FFFFFF                               
Key Kids events      Great Activity       #FFFF33 

What I really need to see is the entry for School events with nulls for the activity_name and BGCOLOR, but adding the extra tables seem to "lose" them!! 

I tried -
select a.name, e.activity_name, f.bgcolor
from activity_test a, event_test e, country c, franchise_test f
WHERE a.activity_id = e.activity_id(+)
AND c.country_id(+) = e.country_id
AND e.franchise_id = f.id(+)

And that brings back what I need. Amazing what sleeping on a problem can do. Still don't fully understand what's happening though!

Thanks Tom. Didn't see your reply until after my last post - but the explanation helps a great deal.

Cheers

I remember using multiple left joins / multiple tables with outer joins long time back. I was not able to recollect the syntax I tried looking into SQL Reference and Code Tip archives for a long time. Finally, tried here and got my solution in the first search itself.

Good information with nice search facility.

Hi Tom,

I used your example and tried to rewrite it, but the following two returns very different result. Could you help explain? Thanks.

SQL> ed
Wrote file afiedt.buf

  1   select *
  2*    from (((( a inner join b on a.x = b.x ) left outer join (c inner join d on c.x = d.x) on a.x = c.x )))
SQL> /

         X          X          X          X
---------- ---------- ---------- ----------
         1          1

SQL> ed
Wrote file afiedt.buf

  1   select *
  2*    from (((( a inner join b on a.x = b.x ) left outer join c  on a.x = c.x inner join d on c.x = d.x)))
SQL> /

no rows selected

SQL>  

What is wrong with the following query?:

1)SQL> SELECT S.DNAME, S.LOC, S1.EMPNO, S1.ENAME, S1.JOB, S1.SAL, S1.DEPTNO, S1.GRADE, S2.GRADE, S2.LO
SAL, S2.HISAL
  2  FROM (SCOTT.DEPT S FULL OUTER JOIN
  3         (SCOTT.EMP S1 FULL OUTER JOIN SCOTT.SALGRADE S2 ON S2.GRADE=S1.GRADE)
  4       ON S1.DEPTNO=S.DEPTNO) ;
FROM (SCOTT.DEPT S FULL OUTER JOIN
            *
ERROR at line 2:
ORA-00600: internal error code, arguments: [kokbtns1], [], [], [], [], [], [], []

Why do I get internal error?

2) SELECT d.deptno,d.dname,d.loc,e.empno,e.ename,e.deptno,e.sal,e.grade,s.grade,s.losal,s.hisal
FROM dept d FULL OUTER JOIN emp e ON(d.deptno=e.deptno) FULL OUTER JOIN salgrade s 

ON(e.grade=s.grade);


Are query 1 and 2 the same?
 

Hi, Tom,

FYI: the query by above
<Quote --================
select a.c1, a.c2, b.c1, b.c2 from (
select 1 c1, NULL c2 from dual
union
select 1 c1, 1 c2 from dual
union
select 1 c1, 2 c2 from dual
) a,(
select 1 c1, NULL c2 from dual
union
select 1 c1, 1 c2 from dual
union
select 2 c1, 1 c2 from dual
) b
where a.c1(+)= b.c1 and a.c2= b.c2(+)
=======---- Quote>

is not run under my 9i:

<-------=========
where a.c1(+)= b.c1 and a.c2= b.c2(+)
*
ERROR at line 14:
ORA-01416: two tables cannot be outer-joined to each other
-------------->

Is this only support by ANSI not 9i?

Thanks,

Hi Tom,

Are the following two queries semanticly the same?

SELECT A.COL, B.COL FROM A LEFT OUTER JOIN B ON (A.B_ID = B.B_ID);

SELECT A.COL, (SELECT B.COL FROM B WHERE B.B_ID = A.B_ID) COL FROM A;

Assume there is a optional FK on A.B_ID to B.B_ID

Which one is better from performance perspective?

Thanks.

select .. from emp,dept
where emp.aa = dept.aa
and emp.bb = dept.bb

Which is the inner / outer table ?

Thanks

I think I did not frame my question correctly .
If it is a NL join then which is the outer and inner among the two tables (emp or dept) if CBO is used and no ordered hint.

thanks

We are in the process of migrating one of our application to oracle and came across a query something like following which doesn't work on oracle(version 9.2.0.5).
I want to use outer join (as row might not exist in other table) and use subquery to get first record(min(b2.id)) depending upon the criteria as follows.This query works fine in other database.
"Can you show me how to use subquery with some function inside along with outerjoin".I will have to use function inside the subquery to select minimum one which I want.


create table a (id number, name varchar(5));
insert into a values (1, 'one');
insert into a values (2, 'two');
insert into a values (3, 'three');
create table b (id number, aid number);
insert into b values (11, 1);
insert into b values (21, 2);
insert into b values (22, 2);



select a.name, b.id
from a
left outer join b on b.id =
(select min(b2.id)
from b b2
where b2.aid = a.id)

ORA-01799: a column may not be outer-joined to a subquery

wow, whacky.  

ops$tkyte@ORA9IR2> select a.name, (select min(b2.id) from b b2 where b2.aid = a.id) id
  from a;
  2
NAME                                   ID
------------------------------ ----------
one                                    11
two                                    21
three
 

i think you'll find that to be massively more "performant" too -- I'm not even sure what an optimizer would do with that first one.   

How should ANSI queries be written when NOT EXISTS and other correlated subqueries are used?

I presume the requirement is to be consistent and self documenting, but the documentation does not mention them specifically. I am trying to be more ansi, but am ending up more antsy (bad pun I know, sorry).

Below is a trivial example, but seems to be not ansi enough..

SELECT t.table_name
FROM user_tables t
WHERE NOT EXISTS (SELECT 'X'
FROM user_tab_columns c
WHERE t.table_name = c.table_name
AND c.data_type = 'DATE')

Maybe it's just me. Ah well.

Just wondering if there was an ansi way of representing that style of join.

A less trivial example may help..

SELECT i.index_name
, t.table_name
FROM user_indexes i
LEFT JOIN (user_tables t)
ON ( t.table_name = i.table_name
AND NOT EXISTS (SELECT 'X'
FROM user_tab_columns c
WHERE t.table_name = c.table_name
AND c.data_type = 'DATE') )

As queries get more complex the ansi way seems be only a small part of the query.

Nothing that I can express that well states the query was not ansi, it just feels like the query becomes less ansi style as a number of EXISTS, NOT IN or other subqueries increases.

I am looking for a more specific response :
I am using the following query in 9.2.0.4 with compatibiltiy set to 9.0.0.0

SELECT PN_NUM, NAME, MAILBOX_NUM, GENDER, NATIONALITY, TEAM_NUM, RANK_CODE
FROM CCSRMAIN.CTS_CREW_PERSONAL_DETAILS C
LEFT OUTER JOIN
CCSRMAIN.CTS_TEAM_HISTORY T ON
T.CREW_ID_SEQ = C.CREW_ID_SEQ
AND T.TEAM_EFFECTIVE_DATE = (
SELECT MAX(B.TEAM_EFFECTIVE_DATE) FROM CCSRMAIN.CTS_TEAM_HISTORY B
WHERE B.CREW_ID_SEQ = C.CREW_ID_SEQ AND B.TEAM_EFFECTIVE_DATE <= SYSDATE
)
LEFT OUTER JOIN
CCSRMAIN.CTS_RANK_HISTORY R ON R.CREW_ID_SEQ = C.CREW_ID_SEQ
AND R.RANK_EFFECTIVE_DATE = (
SELECT MAX(D.RANK_EFFECTIVE_DATE) FROM CCSRMAIN.CTS_RANK_HISTORY D
WHERE D.CREW_ID_SEQ = C.CREW_ID_SEQ AND D.RANK_EFFECTIVE_DATE <= SYSDATE
)
WHERE 1 = 1 ;

How to get rid of this error.

HI TOM,

I have a question about the following error in the follow up:
"
ORA-01799: a column may not be outer-joined to a subquery "


I have the following stories :

at the bgeining we have the following query:

SELECT ...,decode(a.f5,1,b.f3,2),... FROM test1 a,test2 b,..
where
.
.
.
a.f1 = b.f1
AND b.f2 =
(
SELECT max(c.f2) FROM test2 C
WHERE c.f1=b.f1);


later we need to change to the query like this, because we need a outjoin on f1.


SELECT ...,decode(a.f5,1,b.f3,2),... FROM test1 a,test2 b,..
where
.
.
.
a.f1 = b.f1(+)
AND b.f2(+) =
(
SELECT max(c.f2) FROM test2 C
WHERE c.f1=b.f1);

my question about this is : if we put a outjoin on f1 then we need to the followin outjoin ,right ?

AND b.f2(+) =
(
SELECT max(c.f2) FROM test2 C
WHERE c.f1=b.f1);


but if so, we got the error:

" ORA-01799: a column may not be outer-joined to a subquery "


Can you give me a idea how to do this ?


Thank you very much !!!


yg

I have the following situation: Table A has child tables B and C. Table B contains a date and a value, table c contains two dates (identifying a date range) and a value.

I need to query against tables B and C, with B as the driver and matching on their common parent *and* where the date in B is within the range specified in C. The range specified by C may be open-ended i.e. the 'end-date' may be null.

create table TAB_A
(
A_KEY NUMBER not null,
A_VALUE NUMBER
)
;
alter table TAB_A
add constraint TAB_A_PK primary key (A_KEY);

create table TAB_B
(
B_KEY NUMBER not null,
A_KEY NUMBER not null,
B_DATE DATE not null,
B_VALUE NUMBER
)
;
alter table TAB_B
add constraint TAB_B_PK primary key (B_KEY);
alter table TAB_B
add constraint TAB_B_TAB_A_FK foreign key (A_KEY)
references TAB_A (A_KEY);

create table TAB_C
(
C_KEY NUMBER not null,
A_KEY NUMBER not null,
C_DATE_START DATE not null,
C_DATE_END DATE,
C_VALUE NUMBER not null
)
;
alter table TAB_C
add constraint TAB_C_PK primary key (C_KEY);
alter table TAB_C
add constraint TAB_C_TAB_A foreign key (A_KEY)
references TAB_A (A_KEY);

insert into TAB_A (A_KEY, A_VALUE)
values (1, 999);
insert into TAB_A (A_KEY, A_VALUE)
values (2, 999);
insert into TAB_A (A_KEY, A_VALUE)
values (3, 999);
commit;
insert into TAB_B (B_KEY, A_KEY, B_DATE, B_VALUE)
values (1, 1, to_date('01-01-2003', 'dd-mm-yyyy'), 88);
insert into TAB_B (B_KEY, A_KEY, B_DATE, B_VALUE)
values (2, 1, to_date('01-01-2004', 'dd-mm-yyyy'), 88);
insert into TAB_B (B_KEY, A_KEY, B_DATE, B_VALUE)
values (3, 1, to_date('01-02-2004', 'dd-mm-yyyy'), 88);
insert into TAB_B (B_KEY, A_KEY, B_DATE, B_VALUE)
values (4, 1, to_date('01-03-2004', 'dd-mm-yyyy'), 88);
insert into TAB_B (B_KEY, A_KEY, B_DATE, B_VALUE)
values (5, 2, to_date('01-03-2004', 'dd-mm-yyyy'), 88);
commit;
insert into TAB_C (C_KEY, A_KEY, C_DATE_START, C_DATE_END, C_VALUE)
values (1, 1, to_date('06-06-2003', 'dd-mm-yyyy'), to_date('15-01-2004', 'dd-mm-yyyy'), 77);
insert into TAB_C (C_KEY, A_KEY, C_DATE_START, C_DATE_END, C_VALUE)
values (2, 1, to_date('15-01-2004', 'dd-mm-yyyy'), null, 66);
insert into TAB_C (C_KEY, A_KEY, C_DATE_START, C_DATE_END, C_VALUE)
values (3, 2, to_date('06-06-2003', 'dd-mm-yyyy'), null, 55);
commit;

The query I have written is:

SELECT b.b_key
,b.a_key
,b.b_date
,b.b_value
,c.c_key
,c.a_key
,c.c_date_start
,c.c_date_end
,c.c_value
FROM tab_b b
,tab_c c
WHERE b.a_key = c.a_key(+)
AND b.b_date >= c.c_date_start(+)
AND (c.c_date_end IS NULL OR (b.b_date < c.c_date_end));

This seems to being back the desired results, but it doesn't 'feel' right as the 'outer join' is not mentioned in the final predicate. However, attempting to do so produces ORA-01719 errors.

Am I following the correct approach?

in 9.2.0.5:

SQL> create table t1 (c1 number, c2 number);

Table created.

SQL> 
SQL> create table t2 (c1 number, c3 number);

Table created.

SQL> 
SQL> create table t3 (c2 number, c3 number);

Table created.

SQL> 
SQL> select t1.c1, t2.c3, t3.c2
  2    from t1, t2, t3
  3   where t1.c1 = t2.c1 (+)
  4     and t1.c2 = t3.c2 (+)
  5     and t2.c3 = t3.c3 (+)
  6  /
   and t1.c2 = t3.c2 (+)
             *
ERROR at line 4:
ORA-01417: a table may be outer joined to at most one other table


SQL> 
SQL> select t1.c1, t2.c3, t3.c2
  2    from t1 
  3    left outer join t2
  4          on t2.c1 = t2.c1
  5    left outer join t3
  6          on t1.c2 = t3.c2
  7         and t2.c3 = t3.c3
  8  /

no rows selected

This may seem like a pathological example, but with classic Oracle outer join syntax, you cannot outer join A->B and outer join *both* tables to C. 

ops$tkyte@ORA9IR1> select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1 )  x,
  5         t3
  6   where x.t1_c2 = t3.c2
  7     and x.t2_c3 = t3.c3
  8  /
 
no rows selected
 
 

Thanks, it does indeed make sense.

Hi Tom,

My situation is like this. I have a table that stores the values of vacation taken by employees by month. It looks like

emp_num end_date vacation_taken(hours)
1 09/30/2004 80
1 10/31/2004 100
1 11/30/2004 108
1 12/31/2004 160

2 10/31/2004 16
2 11/30/2004 24
2 12/31/2004 56

Now I am trying to do this :

select emp_num, v1.vacation_taken, v2.vacation_taken
from emp_vacation v1, emp_vacation v2
where v1.emp_num = v2.emp_num
and v1.end_date = '12/31/2004'
and v2.end_date = '09/30/2004'

to get this result
emp_num as of 12/31 as of 9/30
1 160 80
2 56 0

Since, employee 2 has no record for 9/30, I am getting only

emp_num as of 12/31 as of 9/30
1 160 80

how should I re-write the qurey?

Thanks,
Ravi.

SQL> drop table a;

Table dropped.

SQL> create table a (a number);

Table created.

SQL> drop table b;

Table dropped.

SQL> create table b (b number, c number);

Table created.

SQL> insert into a values (1);

1 row created.

SQL> insert into a values (2);

1 row created.

SQL> 
SQL> insert into b values (1, 10);

1 row created.

SQL> insert into b values (1, 11);

1 row created.

SQL> insert into b values (1, 12);

1 row created.

SQL> insert into b values (2, 22);

1 row created.

SQL> select *
  2  from a,b
  3  where b.b (+) = a.a and
  4   b.c (+) in (10, 11)
  5  
SQL> select *
  2  from a,b
  3  where b.b (+) = a.a and
  4   b.c (+) in (10, 11)
  5  /
 b.c (+) in (10, 11)
         *
ERROR at line 4:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN



result should be


a    b    c
---    ----    ----
1    1    10
1    1    11
2    null    null


I tried this which works ...

SQL> select *
  2  from a,b
  3  where b.b (+) = a.a and
  4  b.c (+) in (10)
  5  union
  6  select *
  7  from a,b
  8  where b.b (+) = a.a and
  9  b.c (+) in (11);

         A          B          C
---------- ---------- ----------
         1          1         10
         1          1         11
         2

but I cann't do like this because i have lot of values in "in" clause.

Any help will be appreciated.

cheers
 

ops$tkyte@ORA9IR2> select *
  2    from a, (select * from b where c in ( 10, 11 ) ) b
  3   where a.a = b.b(+)
  4  /
 
         A          B          C
---------- ---------- ----------
         1          1         10
         1          1         11
         2
 

SELECT * FROM A LEFT JOIN B ON (b.b = a.a AND b.c IN (10,11) )
A B C
---------- ---------- ----------
1 1 10
1 1 11
2
3 rows selected

In one of your follow up post ( see follow *** ):

Should we no include the outer join symbol lik

ops$tkyte@ORA9IR1> select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1(+) )  x,
  5         t3
  6   where x.t1_c2 = t3.c2(+)
  7     and x.t2_c3 = t3.c3(+)
  8  /

______________________________________________________
SQL> select t1.c1, t2.c3, t3.c2
  2    from t1, t2, t3
  3   where t1.c1 = t2.c1 (+)
  4     and t1.c2 = t3.c2 (+)
  5     and t2.c3 = t3.c3 (+)
  6  /
   and t1.c2 = t3.c2 (+)
             *
ERROR at line 4:
ORA-01417: a table may be outer joined to at most one other table


SQL> 
SQL> select t1.c1, t2.c3, t3.c2
  2    from t1 
  3    left outer join t2
  4          on t2.c1 = t2.c1
  5    left outer join t3
  6          on t1.c2 = t3.c2
  7         and t2.c3 = t3.c3
  8  /

no rows selected

This may seem like a pathological example, but with classic Oracle outer join 
syntax, you cannot outer join A->B and outer join *both* tables to C. 


*** Followup:  
ops$tkyte@ORA9IR1> select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1 )  x,
  5         t3
  6   where x.t1_c2 = t3.c2
  7     and x.t2_c3 = t3.c3
  8  /
 
no rows selected

 
 

TO achieve this :
SQL> select t1.c1, t2.c3, t3.c2
  2    from t1, t2, t3
  3   where t1.c1 = t2.c1 (+)
  4     and t1.c2 = t3.c2 (+)
  5     and t2.c3 = t3.c3 (+)
  6  /
   and t1.c2 = t3.c2 (+)
             *
ERROR at line 4:
ORA-01417: a table may be outer joined to at most one other table

you said use this :
select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1 )  x,
  5         t3
  6   where x.t1_c2 = t3.c2
  7     and x.t2_c3 = t3.c3
  8  /

Which I think should be :
this ( Added Outer joins )

select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1(+) )  x,
  5         t3
  6   where x.t1_c2 = t3.c2(+)
  7     and x.t2_c3 = t3.c3(+)
  8  /

Please correct me if I am wrong. 

ah, ok -- yes :) they are missing.  the inline view works around the 1417


ops$tkyte@ORA9IR2> select *
  2    from ( select t1.c1 t1_c1, t1.c2 t1_c2, t2.c3 t2_c3
  3             from t1, t2
  4            where t1.c1 = t2.c1(+) )  x,
  5         t3
  6   where x.t1_c2 = t3.c2(+)
  7     and x.t2_c3 = t3.c3(+)
  8  /
 
no rows selected
 

hello tom,
this is vijay i want to clear my doupt
how to insert picture in oracle 9i and 8i?

hello tom,
how to insert video file in oracle 9i pls
give to syntax and steps also?

SQL> desc INCIDENT_REPORT_V3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
--------------------
 REGION                                                         NUMBER
 DIVISION                                                       NUMBER
 FACILITY                                                       NUMBER(12)
 INCIDENT_NUMBER                                                NUMBER
 INVESTIGATION_NUMBER                                           NUMBER
 INCIDENT_OCCUR_FROM_DATE                                       DATE
 INCIDENT_CATEGORY                                              NUMBER
 ASSOCIATED_INVESTIGATION                                       NUMBER
 RECORD_STATUS                                                  NUMBER
 INCIDENT_OCCUR_TO_DATE                                         DATE
 CLOSED_DATE                                                    DATE
 REPORTED_DATE                                                  DATE
 ASSIGNED_INVESTIGATOR                                          NUMBER
 INCIDENT_REPORT_TYPE                                           NUMBER
 LOSS_VALUE                                                     NUMBER
 RECOVERY_VALUE                                                 NUMBER
 FATAL                                                          VARCHAR2(50)
 INJURY                                                         VARCHAR2(50)
 INVESTIGATION_CATEGORY                                         NUMBER
 INVESTIGATION_SUBCATEGORY                                      NUMBER
 AWARELINE                                                      NUMBER
 INCIDENT_SUB_CAT                                               NUMBER
 INVESTIGATION_TYPE                                             NUMBER

SQL> desc REGION_DIVISION_FACILITY
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
--------------------
 REG_CD                                                NOT NULL NUMBER
 DIV_CD                                                NOT NULL NUMBER
 FAC_CD                                                NOT NULL NUMBER
 LAST_MOD_BY                                                    VARCHAR2(10)
 LAST_MOD_DATE                                                  DATE


When i issue the query 
select  Region,Division,Facility  from
                REGION_DIVISION_FACILITY RDF 
                FULL outer join
INCIDENT_REPORT_V3 A on RDF.reg_cd =A.region 
 i am getting the error 
ORA-03113: end-of-file on communication channel

Can you help me in solving it? 

Tom,
I am trying to select all the records from child tables for a parent record. I have 25 child tables for a particular parent table.
Is there any best way getting this information. To get this information using select statement means, joining all 26 tables (with outer-joins) and wait for the result..
i can use pl/sql and loop on all the tables..but is there any better approach to this?

Thanks

Tom, We have a parent table let's say a customer table. For each customer we have several tables with his information. Main (parent) table has all the demographic details, but some tables were created to hold different activities and services for this customer. So for each customer he can be given 10 different services etc..
Union will not work as tables do not have same type/no. of columns.
eg:-
Customer (Parent table)
Id,ssn,age,sex,etc..

Activity (child 1 table)
activity_id ,ssn,activity_desc,date_perfromed etc..

services (child 2 table)
service_id,ssn,service_desc,service_data etc..

Our requirment is
for each customer we need to get :

id,ssn,age,sex,activity_id,activity_desc,date_performed,
service_id,service_desc,service_data.

I know we will be repeating parent info for each child record..(is there any way to get break up for tparent record?)

Thanks,

Tom, Thanks for your immediate response.
Yes, they are 1:M and not 1:1 related.
From my previous example, here is the data i am looking at:

id ssn age sex activity_id activity_desc service_id service
desc
1 123-45-6789 23 M A1 XXXXXXXX S1 XXXXXXXX
A2 YYYYYYYY S2 YYYYYYYY
A3 ZZZZZZZZ

2 000-23-4567 33 F A4 AAAAAAAA S6 DDDDDDDD
S7 EEEEEEEE
S8 MMMMMMMM

As you see, customer 1 has 3 activites but only 2 services and customer 2 has only one activity
,but 3 services (that's why i was thinking about outer join)

Regarding "break up", Sorry my bad, i was not clear. What i meant was like in SQL Plus, we use Break on Column command. that way we will not repeat same record and get a break on parent record.

--> Is pl/sql any answer to this (using loops) or SQL plus is better in performance?

Hope i'm explaining myself clearly here.

As always, thanks and hoping to get a solution.

while I can technically do this -- IF you know the maximum cardinality of any of the child tables for a given parent (max count(*) by parent_id in each child) -- it won't be "the fastest query you've ever run"

You need to take the parent and mulitply it by this "max cardinality" so there are N parent rows.

You need to outer join that cartesian product to each child table AFTER assigning a row_number to the child table by the parent foreign key.

The, just keep the rows where at least one child record was joined.



ops$tkyte@ORA9IR2> set feedback off
ops$tkyte@ORA9IR2> create table t ( x int primary key, t int );
ops$tkyte@ORA9IR2> create table t1 ( x references t, t1 int );
ops$tkyte@ORA9IR2> create table t2 ( x references t, t2 int );
ops$tkyte@ORA9IR2> create table t3 ( x references t, t3 int );
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1 );
ops$tkyte@ORA9IR2> insert into t values ( 2, 2 );
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 101 );
ops$tkyte@ORA9IR2> insert into t1 values ( 1, 102 );
ops$tkyte@ORA9IR2> insert into t1 values ( 2, 203 );
ops$tkyte@ORA9IR2> insert into t1 values ( 2, 204 );
ops$tkyte@ORA9IR2> insert into t1 values ( 2, 205 );
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 1001 );
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 1002 );
ops$tkyte@ORA9IR2> insert into t2 values ( 1, 2003 );
ops$tkyte@ORA9IR2> insert into t2 values ( 2, 2004 );
ops$tkyte@ORA9IR2> insert into t2 values ( 2, 2005 );
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t3 values ( 2, 20001 );
ops$tkyte@ORA9IR2> insert into t3 values ( 2, 20002 );
ops$tkyte@ORA9IR2> insert into t3 values ( 2, 20003 );
ops$tkyte@ORA9IR2> insert into t3 values ( 2, 20004 );
ops$tkyte@ORA9IR2> set feedback on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with aset as (select rownum r from dual connect by 1=1 and rownum <= 10 ),
  2  newt as (select x, t, r from t, aset),
  3  newt1 as (select t1.*, row_number() over (partition by x order by rowid) rn from t1),
  4  newt2 as (select t2.*, row_number() over (partition by x order by rowid) rn from t2),
  5  newt3 as (select t3.*, row_number() over (partition by x order by rowid) rn from t3)
  6  select t.x, t.t, t1.t1, t2.t2, t3.t3
  7    from newt t, newt1 t1, newt2 t2, newt3 t3
  8   where t.x = t1.x(+) and t.r = t1.rn(+)
  9     and t.x = t2.x(+) and t.r = t2.rn(+)
 10     and t.x = t3.x(+) and t.r = t3.rn(+)
 11     and coalesce( t1.x, t2.x, t3.x ) is not null
 12  /
 
         X          T         T1         T2         T3
---------- ---------- ---------- ---------- ----------
         1          1        101       1001
         1          1        102       1002
         1          1                  2003
         2          2        203       2004      20001
         2          2        204       2005      20002
         2          2        205                 20003
         2          2                            20004
 
7 rows selected.



"fast" will not be a term you associate with this I would guess.  (neat trick with dual as shown by a reader on another page on this site.... can be replace with "select rownum r from all_objects where rownum < 10" if you like -- any table with at least "N" rows will do) 

This is some what offtopic now, but isn't this kind of what Tom did here (in the answer to the first review): </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1058232381458 <code>

create type t1Type as object (t1 number);
/
create type t1TabType as table of t1Type;
/
create type t2Type as object (t2 number);
/
create type t2TabType as table of t2Type;
/
create type t3Type as object (t3 number);
/
create type t3TabType as table of t3Type;
/
column t1 format a15
column t2 format a15
column t3 format a15
select t.x, t.t,
cast(multiset(select t1.t1 from t1 where t1.x=t.x) as t1TabType) t1,
cast(multiset(select t2.t2 from t2 where t2.x=t.x) as t2TabType) t2,
cast(multiset(select t3.t3 from t3 where t3.x=t.x) as t3TabType) t3
from t;
X T T1(T1) T2(T2) T3(T3)
---------- ---------- --------------- --------------- ---------------
1 1 T1TABTYPE(T1TYP T2TABTYPE(T2TYP T3TABTYPE()
E(101), T1TYPE( E(1001), T2TYPE
102)) (1002), T2TYPE(
2003))

2 2 T1TABTYPE(T1TYP T2TABTYPE(T2TYP T3TABTYPE(T3TYP
E(203), T1TYPE( E(2004), T2TYPE E(20001), T3TYP
204), T1TYPE(20 (2005)) E(20002), T3TYP
5)) E(20003), T3TYP
E(20004))

Xml might also be a way to go if the client library/language can't handle the complex types.

(Btw. I think it would be nice if you could do something like "cast(... as table of object(t1 number))" and didn't have to create/name the types first and thereby "polluting" the namespace)

Tom,
Thanks for your solution. "With Aset" is a new concept for me and i need to play around with that. However, that's the solution i was looking for. Thanks a lot.

1. Are there any added advantages of ansi joins.
2. Is the execution plan going to be different when we have ansi joins.
3. Have they got any additional functionality.

Thanks in advance

Hi Tom
A. why Oracle has provided ansi SQL/Ansi joins. Is it to support sql of other database or

B.

1. Are there any added advantages of Ansi SQl/Ansi joins supported by oracle.
2. Is the execution plan going to be different when we have ansi joins.
3. Have they got any additional functionality.

C. Do you suggetst any cases where we have to use Ansi SQL/Ansi Joins.


Thanks in advance

For the example you provided above

select * from t1, t2
where t1.x = t2.x(+) and t2.y > 5;

Couldn't the parser be smart enough to convert the outer join to an inner join as there is point for the outer join. The parser rewrites sql all the time to make it more effiecent like coverting subsquerys to joins and vice versa.

Incorporating a rule such as
"If a table is outerjoined to another table which has an predicate that equals a cosntant then covert the outerjoin to an inner join"

Maybe this can be added to a future release, if it feasable. Tom what are your thoughts?

I also like to thank you for your contious effort in helping the oracle community by answering our questions and giving us working examples.

actually, looks like the CBO is in fact that smart

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select *
  2    from t1, t2
  3   where t1.x = t2.x(+)
  4     and t2.y > 5;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     MERGE JOIN (OUTER)
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T1'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T2'
 
 
 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ *
  2    from t1, t2
  3   where t1.x = t2.x(+)
  4     and t2.y > 5;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=5 Card=4 Bytes=156)
   1    0   HASH JOIN (Cost=5 Card=4 Bytes=156)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=4 Bytes=104)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=1066)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


;)  at least in the simple cases.   Now, what I'd like to see would cover this:

ops$tkyte@ORA9IR2> create table t1( x int primary key);
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( a int primary key references t1, b int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select *
  4    from t1, t2
  5   where t1.x = t2.a(+);
 
View created.

since A is a primary key (unique+not null) and it references T1, the (+) is not needed.  In fact, if you just query column from T1, it should not even need to goto t2...

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
 
ops$tkyte@ORA9IR2> select /*+ first_rows */ * from v;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=108 Card=82 Bytes=3198)
   1    0   NESTED LOOPS (OUTER) (Cost=108 Card=82 Bytes=3198)
   2    1     INDEX (FULL SCAN) OF 'SYS_C007798' (UNIQUE) (Cost=26 Card=82 Bytes=1066)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1 Bytes=26)
   4    3       INDEX (UNIQUE SCAN) OF 'SYS_C007799' (UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> select /*+ first_rows */ x from v;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26 Card=82 Bytes=2132)
   1    0   NESTED LOOPS (OUTER) (Cost=26 Card=82 Bytes=2132)
   2    1     INDEX (FULL SCAN) OF 'SYS_C007798' (UNIQUE) (Cost=26 Card=82 Bytes=1066)
   3    1     INDEX (UNIQUE SCAN) OF 'SYS_C007799' (UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


but it does.... that would be nice. 

As i have mentioned below i have used full outer join and with clause and i got the error listed below.

Could you please give your thoughts on this?.
This happens only when i use bind variables.

Thanks,
Ramasamy T


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for HPUX: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> get test.sql
  1  create or replace procedure p1 as
  2     l1 number := 0;
  3     v_curr_date DATE := TRUNC(SYSDATE);
  4  begin
  5   dbms_output.put_line('l1 ' || l1);
  6   with sub1 as ( select 1 col1 from dual where sysdate > v_curr_date) ,
  7        sub2 as ( select 2 col2 from dual)
  8   select count(*) into l1 from sub1 full outer join sub2 on sub1.col1 = sub2.col2;
  9   dbms_output.put_line('l1 ' || l1);
 10* end;
SQL> @test.sql

Procedure created.

SQL> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> 
 

Hi Tom,

This small paragraph is from metalink note
could you pls tell me what is inner and what is outer table here?

· Are the indexed columns part of join predicates? e.g. emp.deptno= dept.deptno

If Yes then:
What type of join is used?
Only a Nested loops join can allow index lookups on the inner table that are based solely on the join column(s):

Hash / Sort Merge Join:
With Hash joins and Sort Merge joins, information from the outer table is not available at join time to enable row look ups on the inner table; rather both tables are accessed separately and then the resultant data is joined. The inner table of a Hash or Sort Merge cannot be probed solely using an index based on the join columns . This is an inherent limitation of the implementation mechanisms used by these join types. Nested Loops joins are different in as much as they allow index lookups on the join columns.

thanks in advance
sreeni

Hi Tom,

I'd like to ask your opinion whether it's a good idea to replace NULL values for all the FOREIGN KEY columns to a default value and have the referenced tables have a record to indicate it's a NULL.

Reason given for this approach is to avoid having to do too many OUTER JOIN tables for our lookup tables.

Currently we have tables that sometimes have many FOREIGN KEYS to lookup tables. So whenever we do a SELECT, we would have to JOIN all these tables with LEFT/RIGHT OUTER JOIN.

There is this argument that having many LEFT/RIGHT OUTER JOIN will be very slow, so if we were to have no NULL values, then we can use INNER JOIN instead. Is this argument at all valid?

Thanks.

Tom,

I realize this thread is a little stale, but it touches on an issue that I'm curious about.

In the the old RBO days, it was often stated that the order of tables in the FROM clause controlled the table join order.  With the CBO, the optimizer will decide the table join order. However, explain plans generated for ANSI-style join syntax, suggest that the join order seems to be driven by the table order in the FROM clause. 
 
Is this observation normal behavior with this syntax, or possibly just an artifact of my limited test data?

I like the way the ANSI syntax allows you to separate the join conditions from the predicate info, but would prefer to not have to worry about the table join order like in the RBO days.

Thanks.

SQL>select * from v$version;

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>alter table jce_bonus add(bonus number);

Table altered.

SQL>alter table jce_bonus add(bonus_date date);

Table altered.

SQL>set feedback off;

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('WARD', 500, to_date('31-dec-1982', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('MARTIN', 500, to_date('31-dec-1982', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('ALLEN', 1000, to_date('31-dec-1982', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('BLAKE', 1000, to_date('31-dec-1982', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('KING', 1000, to_date('31-dec-1982', 'dd-mon-yyyy'));

SQL>--
SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('KING', 1000, to_date('31-dec-1983', 'dd-mon-yyyy'));

SQL>--
SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('WARD', 1000, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('MARTIN', 500, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('BLAKE', 1000, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('CLARK', 500, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('JONES', 500, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('KING', 1000, to_date('31-dec-1984', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('WARD', 500, to_date('31-dec-1985', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('ALLEN', 500, to_date('31-dec-1985', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('BLAKE', 1000, to_date('31-dec-1985', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('JONES', 500, to_date('31-dec-1985', 'dd-mon-yyyy'));

SQL>insert into jce_bonus  (ename, bonus, bonus_date)
  2  values ('KING', 1000, to_date('31-dec-1985', 'dd-mon-yyyy'));

SQL>set autotrace on explain stat
SQL>-- EXAMPLE 1
SQL>select d.deptno, e.ename, b.bonus_date, b.bonus
  2  from   jce_dept d
  3           left outer join jce_emp e
  4             on d.deptno = e.deptno
  5           left outer join jce_bonus b
  6             on e.ename = b.ename
  7  order by 1, 2, 3, 4;

    DEPTNO ENAME      BONUS_DAT      BONUS
---------- ---------- --------- ----------
        10 CLARK      31-DEC-84        500
        10 KING       31-DEC-82       1000
        10 KING       31-DEC-83       1000
        10 KING       31-DEC-84       1000
        10 KING       31-DEC-85       1000
        10 MILLER                         
        20 ADAMS                          
        20 FORD                           
        20 JONES      31-DEC-84        500
        20 JONES      31-DEC-85        500
        20 SCOTT                          
        20 SMITH                          
        30 ALLEN      31-DEC-82       1000
        30 ALLEN      31-DEC-85        500
        30 BLAKE      31-DEC-82       1000
        30 BLAKE      31-DEC-84       1000
        30 BLAKE      31-DEC-85       1000
        30 JAMES                          
        30 MARTIN     31-DEC-82        500
        30 MARTIN     31-DEC-84        500
        30 TURNER                         
        30 WARD       31-DEC-82        500
        30 WARD       31-DEC-84       1000
        30 WARD       31-DEC-85        500
        40                                

25 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 827909578

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |           |    14 |   574 |    10  (20)| 00:00:01 | 
|   1 |  SORT ORDER BY       |           |    14 |   574 |    10  (20)| 00:00:01 | 
|*  2 |   HASH JOIN OUTER    |           |    14 |   574 |     9  (12)| 00:00:01 | 
|*  3 |    HASH JOIN OUTER   |           |    14 |   168 |     7  (15)| 00:00:01 | 
|   4 |     TABLE ACCESS FULL| JCE_DEPT  |     4 |    12 |     3   (0)| 00:00:01 | 
|   5 |     TABLE ACCESS FULL| JCE_EMP   |    14 |   126 |     3   (0)| 00:00:01 | 
|   6 |    TABLE ACCESS FULL | JCE_BONUS |     1 |    29 |     2   (0)| 00:00:01 | 
---------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."ENAME"="B"."ENAME"(+))
   3 - access("D"."DEPTNO"="E"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL>set feedback on;
SQL>set autotrace off
SQL>set autotrace on explain stat
SQL>select d.deptno, e.ename, b.bonus_date, b.bonus
  2  from   ((jce_dept d
  3           left outer join jce_emp e
  4             on d.deptno = e.deptno
  5          ) left outer join jce_bonus b
  6              on e.ename = b.ename
  7         )
  8  order by 1, 2, 3, 4;


Execution Plan
----------------------------------------------------------
Plan hash value: 827909578

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    14 |   574 |    10  (20)| 00:00:01 |
|   1 |  SORT ORDER BY       |           |    14 |   574 |    10  (20)| 00:00:01 |
|*  2 |   HASH JOIN OUTER    |           |    14 |   574 |     9  (12)| 00:00:01 |
|*  3 |    HASH JOIN OUTER   |           |    14 |   168 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| JCE_DEPT  |     4 |    12 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| JCE_EMP   |    14 |   126 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | JCE_BONUS |     1 |    29 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."ENAME"="B"."ENAME"(+))
   3 - access("D"."DEPTNO"="E"."DEPTNO"(+))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL>-- EXAMPLE 3
SQL>select d.deptno, e.ename, b.bonus_date, b.bonus
  2  from   (jce_dept d
  3           left outer join (jce_emp e
  4                            left outer join jce_bonus b
  5                              on e.ename = b.ename
  6                           )
  7             on d.deptno = e.deptno
  8         )
  9  order by 1, 2, 3, 4;

Execution Plan
----------------------------------------------------------
Plan hash value: 1047699812

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |    14 |   490 |    10  (20)| 00:00:01 |
|   1 |  SORT ORDER BY        |           |    14 |   490 |    10  (20)| 00:00:01 |
|*  2 |   HASH JOIN OUTER     |           |    14 |   490 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | JCE_DEPT  |     4 |    12 |     3   (0)| 00:00:01 |
|   4 |    VIEW               |           |    14 |   448 |     6  (17)| 00:00:01 |
|*  5 |     HASH JOIN OUTER   |           |    14 |   532 |     6  (17)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| JCE_EMP   |    14 |   126 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| JCE_BONUS |     1 |    29 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"="E"."DEPTNO"(+))
   5 - access("E"."ENAME"="B"."ENAME"(+))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed

SQL>-- EXAMPLE 4
SQL>select d.deptno, e.ename, b.bonus_date, b.bonus
  2  from   jce_emp e
  3        left outer join jce_bonus b
  4          on e.ename = b.ename
  5        right outer join jce_dept d
  6          on d.deptno = e.deptno
  7  order by 1, 2, 3, 4;

Execution Plan
----------------------------------------------------------
Plan hash value: 1047699812

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |    14 |   490 |    10  (20)| 00:00:01 |
|   1 |  SORT ORDER BY        |           |    14 |   490 |    10  (20)| 00:00:01 |
|*  2 |   HASH JOIN OUTER     |           |    14 |   490 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | JCE_DEPT  |     4 |    12 |     3   (0)| 00:00:01 |
|   4 |    VIEW               |           |    14 |   448 |     6  (17)| 00:00:01 |
|*  5 |     HASH JOIN OUTER   |           |    14 |   532 |     6  (17)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| JCE_EMP   |    14 |   126 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| JCE_BONUS |     1 |    29 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"="E"."DEPTNO"(+))
   5 - access("E"."ENAME"="B"."ENAME"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets     
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         25  rows processed

 

Tom,

Should have added this to question above:

--There are four examples with different table order in the FROM clause. All four examples return the same result set.
--1) dept LEFT OUTER JOIN emp LEFT OUTER JOIN bonus - explain plan join order is dept, emp, bonus)
--2) ((dept LEFT OUTER JOIN emp )LEFT OUTER JOIN bonus) - explain plan join order same as 1)
--3) (dept LEFT OUTER JOIN (emp LEFT OUTER JOIN bonus)) - explain plan join order is emp, bonus, dept
--4) emp LEFT OUTER JOIN bonus right outer join DEPT - explain plan join order same as 3) RIGHT OUTER
-- necessary to get "null" row for deptno = 40
--
-- Schema is the old scott/tiger schema with emp, dept, and bonus tables.
-- The bonus table is altered with columns bonus and bonus_date added

Tom,

When I use ANSI "full outer join" syntax for big tables [around 650000 rows in both], the query hangs for ever. I waited more than 3 hrs and then killed the session. If I covert ANSI to normal query as below, it runs in 45 seconds. Why?

Left out join on t1,t2
UNION ALL
Right outer join on t1,t2

While developing our application, we didn't face this problem as DEV database had fewer rows [in thousands]. When we moved it into production, we unexpectedly face his problem. Now we are working day and night to convert all ANSI sql into normal sql as above.

Is it a problem with ANSI sql?

Hello Tom,

Can i use alias for the result set of inner join between a and b. if yes, how can i refer the column in outer join with c.

example:
select *
from (( a inner join b on a.x = b.x ) Alias
left outer join c on a.x(how to refer here) = c.x );