[2k5] INTERSECT 및 EXCEPT 연산자 활용

1. /**********************************************************************************************
   -- Title : [2k5] INTERSECT 및 EXCEPT 연산자 활용
   -- Reference : mcpworld.com
   -- Key word : intersect except
   **********************************************************************************************/
   use pubs
   go;
2. -- intersect
   select distinct city from authors
   where exists (select * from publishers
                 where authors.city = publishers.city);
   select city from authors
   intersect
   select city from publishers;
   go  
   -- except
   select distinct city from authors
   where not exists (select * from publishers
         where authors.city = publishers.city);
3. select city from authors
   except
   select city from publishers
   go
4. -- 쌍을 이룬 값의 intersect, except 처리
   drop table t1,t2;
   go
5. create table t1 (a int, b int, c char(1));
   create table t2 (a int, b int, c char(1));
   go
6. insert into t1 values (1,1,'a');
   insert into t1 values (1,2,'a');
   insert into t1 values (2,1,'a');
   insert into t1 values (2,2,'a');
7. insert into t2 values (1,1,'a');
   insert into t2 values (2,2,'a');
   insert into t2 values (3,3,'a');
   insert into t2 values (4,4,'a');
   go
8. select a, b from t1
   intersect
   select a, b from t2;
   go
9. select a, b from t1
   except
   select a, b from t2;
   go