[PGS9.2] 상관관계 서브쿼리에서의 Resolving Names 이슈

/********************************************************************************************
-- Title : [PGS9.2] 상관관계 서브쿼리에서의 Resolving Names 이슈
-- Reference : support.microsoft.com/kb/298674/en-us, sqlroad 김정선이사님
-- Key word : bug? 
********************************************************************************************/

-- Initiate table

drop table ttt;

drop table fff;

-- create table

create table ttt

( tkey varchar(30) not null

, seq smallint not null

, name varchar(300)

, constraint pk_ttt primary key (tkey, seq)

);

create table fff

( del_tkey varchar(30) not null

, kind char(2) not null

);

-- insert data

insert into ttt

select 'aaaaa', 1, 'aaaaaaaaaaaaaa 1111111111111' union all

select 'aaaaa', 2, 'aaaaaaaaaaaaaa 2222222222222' union all

select 'bbbbb', 1, 'bbbbbbbbbbbbbb 1111111111111' union all

select 'ccccc', 1, 'cccccccccccccc 1111111111111' union all

select 'ccccc', 2, 'cccccccccccccc 2222222222222' union all

select 'ccccc', 3, 'cccccccccccccc 3333333333333' union all

select 'ddddd', 1, 'dddddddddddddd 1111111111111' union all

select 'ddddd', 2, 'dddddddddddddd 2222222222222';

insert into fff

select 'bbbbb', 'b1' union all

select 'ddddd', 'd1';

-- confirm data

select * from ttt;

select * from fff;

-- delete data using subquery

-- there is no tkey column in fff table

delete from ttt where tkey in (select tkey from fff);

-- confirm data

-- you can confirm that all rows of ttt table are deleted.

select * from ttt;

-- come to the same thing on SQL Server and Oracle. 
-- That's the reason why

서브쿼리 내부에서 외부 테이블 열을 참조할 수 있고, 이 경우 상관 서브쿼리로 취급됩니다(ANSI).
그래서 Alias를 지정해 주는 것이 좋습니다.

delete from ttt where tkey in (select a.tkey from fff a);