{column:width=50%}

spool 2-33.lst

set timing off
set autotrace off
set linesize 1000
set pagesize 1000

prompt I. 아래와 같은 두 테이블이 있을 때 조인한 결과와 union과 group by 한 결과는 같다.

prompt 0. 기존테이블 삭제
drop table TAB7 cascade constraints purge;
drop table TAB8 cascade constraints purge;

prompt 1. TAB7테이블 생성
 
CREATE TABLE TAB7
       (CD VARCHAR2(10),SQ NUMBER,
        AT NUMBER,YM VARCHAR2(6));

INSERT INTO TAB7 VALUES('A',1,100,'199801');
INSERT INTO TAB7 VALUES('A',2,250,'199802');
INSERT INTO TAB7 VALUES('A',3,150,'199801');
INSERT INTO TAB7 VALUES('B',1,100,'199801');
INSERT INTO TAB7 VALUES('B',2,120,'199801');
INSERT INTO TAB7 VALUES('B',3,200,'199803');
INSERT INTO TAB7 VALUES('D',1,100,'199801');
INSERT INTO TAB7 VALUES('D',2,300,'199801');
INSERT INTO TAB7 VALUES('D',3,200,'199802');


prompt 2. TAB8 테이블 생성

CREATE TABLE TAB8(CD VARCHAR2(10), QT NUMBER, DS VARCHAR2(10));

INSERT INTO TAB8 VALUES('A',45,'1101');
INSERT INTO TAB8 VALUES('B',30,'1103');
INSERT INTO TAB8 VALUES('C',50,'1102');
INSERT INTO TAB8 VALUES('D',20,'1201');

prompt 3. UNION 개념과 특징

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77

set timing on
set autotrace on

prompt 1) join 예제
SELECT A.CD,AT,QT 
FROM (SELECT CD,SUM(AT) AT 
        FROM TAB7 
	GROUP BY CD) A, TAB8 B 
WHERE A.CD=B.CD;


prompt 2) union과 group by 예제
conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

SELECT CD,SUM(AT),SUM(QT) 
FROM (SELECT CD,AT,0 QT 
      FROM TAB7 
      UNION ALL 
      SELECT CD,0,QT 
      FROM TAB8) 
GROUP BY CD HAVING CD<>'C'; 

spool off

{column}