Unnatural joins SQL

 
create table when_i_change_cols_in_this (some_unexpected_results integer, cy integer);
create table that_match_cols_in_this (some_unexpected_results integer);

insert into when_i_change_cols_in_this 
  select rownum, rownum
  from   dual connect by level <= 10;
  
insert into that_match_cols_in_this
  select rownum
  from   dual connect by level <= 10;

select some_unexpected_results
from   when_i_change_cols_in_this
natural join that_match_cols_in_this;

-- add another column with the same name as the other table
alter table that_match_cols_in_this add cy integer ;

-- and our query "magically" starts returning nothing - ouch!
select some_unexpected_results
from   when_i_change_cols_in_this
natural join that_match_cols_in_this;

drop table when_i_change_cols_in_this purge;
drop table that_match_cols_in_this purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)