Hi - I hope that this makes sense - I have tried to search for similar but had no luck - my question is why the an ambigious column is picked up in a single join but not a multi-table join, and why it is using the data it does when it works - I have shown the issue with "no data", and then shown what Oracle appears to do "with data". I have put comments above each statement to show what happened, or was expected. I may be missing an obvious step, or have mis-interpretted my own example, but can't see why the logic works as shown - Many thanks ( in advance ) for any help, Warren;

create table test_1
(
 column_1 number
,column_2 number
);

create table test_2
(
 column_1 number
,column_2 number
);

create table test_3
(
 column_1 number
,column_2 number
);

--- This fails as column_1 in select is "ambiguous"
select  column_1
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1;

--- I think this should fail as above, but works
select  column_1
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1
        join test_3 t3 on t3.column_1 = t2.column_1;

--- I think this should fail as above, but again works
select  t2.column_1, column_2
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1
        join test_3 t3 on t3.column_1 = t2.column_1
        
-- I think this mirrors the ansi, yet it fails as an "ambiguous" column selected        
select   column_1
from     test_1 t1
        ,test_2 t2
        ,test_3 t3 
where   t1.column_1 = t2.column_1        
and     t3.column_1 = t1.column_1
and     t3.column_1 = t2.column_1

truncate table test_1;
truncate table test_2;
truncate table test_3;

insert into test_1 values ( 1, 1 );
insert into test_1 values ( 2, 1 );
insert into test_1 values ( 3, 1 );
insert into test_1 values ( 4, 1 );
insert into test_1 values ( 6, 1 );

insert into test_2 values ( 1, 2 );
insert into test_2 values ( 2, 2 );
insert into test_2 values ( 4, 2 );

insert into test_3 values ( 1, 3 );
insert into test_3 values ( 2, 3 );
insert into test_3 values ( 3, 3 );
insert into test_3 values ( 5, 3 );

commit;


--- This fails as column_1 in select is "ambiguous"
select column_1
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1;

--- I think this should fail as above, but seems to work
select  column_1
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1
        join test_3 t3 on t3.column_1 = t2.column_1;

--- I think this should fail as above, but again works
--- it has selected the "last?" column_2 being from test_3
select t2.column_1, column_2
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1
        join test_3 t3 on t1.column_1 = t3.column_1;

--- I think this should fail as above, but again works
--- it has selected the "last?" column_2 being from test_3
select t2.column_1, column_2
from    test_1 t1
        join test_2 t2 on t1.column_1 = t2.column_1
        join test_3 t3 on t1.column_1 = t3.column_1
order by 2;

--- I think this should fail as above, but again works
--- it has selected the "last?" column_2 being from test_2
--- This would appear to suggest it is the LAST table selected
select  t2.column_1, column_2
from    test_1 t1
        join test_3 t3 on t1.column_1 = t3.column_1
        join test_2 t2 on t1.column_1 = t2.column_1;
        
        
--- I think this should fail as above, but again works
--- it has selected the "last?" column_2 being from test_3
--- and also correctly returns data from t2.column_2
select  t1.column_1, column_2, t2.column_2
from    test_1 t1
        left outer join test_2 t2 on t1.column_1 = t2.column_1
        left outer join test_3 t3 on t1.column_1 = t3.column_1;
        
-- I think this mirrors the ansi, it fails on "ambiguous" column
select   column_1
from     test_1 t1
        ,test_2 t2
        ,test_3 t3 
where   t1.column_1 = t2.column_1        
and     t3.column_1 = t1.column_1
and     t3.column_1 = t2.column_1;


drop table test_1;
drop table test_2;
drop table test_3;

ops$tkyte%ORA11GR2> --- I think this should fail as above, but works
ops$tkyte%ORA11GR2> select  column_1
  2  from    test_1 t1
  3          join test_2 t2 on t1.column_1 = t2.column_1
  4          join test_3 t3 on t3.column_1 = t2.column_1;
select  column_1
        *
ERROR at line 1:
ORA-00918: column ambiguously defined

ops$tkyte%ORA11GR2> --- I think this should fail as above, but again works
ops$tkyte%ORA11GR2> select  t2.column_1, column_2
  2  from    test_1 t1
  3          join test_2 t2 on t1.column_1 = t2.column_1
  4          join test_3 t3 on t3.column_1 = t2.column_1;
select  t2.column_1, column_2
                     *
ERROR at line 1:
ORA-00918: column ambiguously defined

ops$tkyte%ORA11GR2> --- I think this should fail as above, but seems to work
ops$tkyte%ORA11GR2> select  column_1
  2  from    test_1 t1
  3          join test_2 t2 on t1.column_1 = t2.column_1
  4          join test_3 t3 on t3.column_1 = t2.column_1;
select  column_1
        *
ERROR at line 1:
ORA-00918: column ambiguously defined


ops$tkyte%ORA11GR2> --- I think this should fail as above, but again works
ops$tkyte%ORA11GR2> --- it has selected the "last?" column_2 being from test_3
ops$tkyte%ORA11GR2> select t2.column_1, column_2
  2  from    test_1 t1
  3          join test_2 t2 on t1.column_1 = t2.column_1
  4          join test_3 t3 on t1.column_1 = t3.column_1;
select t2.column_1, column_2
                    *
ERROR at line 1:
ORA-00918: column ambiguously defined


ops$tkyte%ORA11GR2> --- I think this should fail as above, but again works
ops$tkyte%ORA11GR2> --- it has selected the "last?" column_2 being from test_3
ops$tkyte%ORA11GR2> select t2.column_1, column_2
  2  from    test_1 t1
  3          join test_2 t2 on t1.column_1 = t2.column_1
  4          join test_3 t3 on t1.column_1 = t3.column_1
  5  order by 2;
select t2.column_1, column_2
                    *
ERROR at line 1:
ORA-00918: column ambiguously defined

ops$tkyte%ORA11GR2> --- I think this should fail as above, but again works
ops$tkyte%ORA11GR2> --- it has selected the "last?" column_2 being from test_3
ops$tkyte%ORA11GR2> --- and also correctly returns data from t2.column_2
ops$tkyte%ORA11GR2> select  t1.column_1, column_2, t2.column_2
  2  from    test_1 t1
  3          left outer join test_2 t2 on t1.column_1 = t2.column_1
  4          left outer join test_3 t3 on t1.column_1 = t3.column_1;
select  t1.column_1, column_2, t2.column_2
                     *
ERROR at line 1:
ORA-00918: column ambiguously defined