오라클 SQL조인, 외부조인, OUTER JOIN
http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=623
6.5 Outer Join
일반적인 조인(내부조인)은 두 테이블 모두 조인 조건을 만족시키는 레코드만 출력된다. 즉 두 테이블중 한쪽 테이블의 값이 일치하지 않는 다면 해당 레코드는 출력되지 않는데 이 경우에도 결과로 출력해야 되는 경우가 있다면 외부조인(Outer Join)을 사용하면 된다. 외부조인은 LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL OUTER JOIN이 있다.
EMP, DEPT를 조인하여 사원명(ename)과 부서명(dname)을 출력한다고 했을 때 EMP 테이블의 KING은 최고관리자로 부서코드가 NULL 값이다. 이 경우 emp.deptno = dept.deptno 로 조인을 한다면 KING은 출력되지 않는다.
내부 조인은 조인 조건을 만족하는 행들만 나타나게 된다. 그러므로 앞의 Self Join 예문처럼 조인 조건을 만족하지 않는다면(NULL 값을 가지는 경우) 해당 레코드는 출력되지 않을 것이다. Outer Join이란 조인에서 한쪽 테이블의 행에 대하여 다른 쪽 테이블에 일치하는 행이 없더라도 다른 쪽 행을 NULL로 하여 행을 반환하게 하는 것이다.
오라클에서 Outer Join 연산자는 “(+)”이며 MS의 SQL Server의 경우는 “*” 이다. 그럼 “(+)”를 조인되는 테이블 중 어디에다 둘 것 인가 이다. 오라클의 경우 모두 출력 되어야 하는 테이블의 반대쪽, 조인될 데이터가 없는쪽에 “(+)”를 표시하며 SQL Server의 경우 모두 출력 되어야 하는 곳 쪽에 “*”를 표시 한다. 모두 출력되어야 한다는 것의 의미는 조인 조건을 만족하지 않다고 해도 해당 레코드를 표시한다는 것을 의미하며, 조인되는 테이블의 해당 칼럼은 NULL로 표시된다.
앞의 Self Join 예문에서 사원 별 관리자 이름을 출력하는데 1번 사원의 레코드가 출력되지 않았던 것을 기억 할 것이다. 이 경우 어디에 “(+)”를 하여야 할까? 사원 쪽이 다 출력 되어야 한다. 즉 관리자가 있든지, 없든지 사원 리스트의 모든 사원은 나와야 하는 것이다. 그렇다면 관리자 쪽에 (+) 표시를 하면 될 것이다. 다음과 같이 SQL문을 수정하면 된다. 마지막에 (+)만 추가 되었다.
SQL> select "사원".sawon_id "사원ID", 2 "사원".name "사원명", 3 "사원".buseo "부서명", 4 "관리자".name "관리자명" 5 from sawon "사원", sawon "관리자" 6 where "사원".manager_id = "관리자".sawon_id(+);
사원ID 사원명 부서명 관리자명 ---------- ---------- -------------------- ---------- 1 가길동 관리부 2 나길동 영업부 가길동 3 다길동 관리부 가길동
또는 아래와 같이 표현해도 된다. 아래의 표기는 ANSI 표준에 부합 하므로 MS SQL Server, MySQL 등에서 똑같이 사용해도 무방 하다. 아래에서 유심히 볼 부분은 from 절 다음 부분이다. left outer join 이라고 한 것은 왼쪽 부분인 “사원” 테이블의 내용은 조인에 맞지 않더라도 관리자이름은 NULL을 채우면서 다 출력 되어야 한다는 것이다. left의 반대인 right Outer Join도 있다. 그러나 의미는 같은 것이니 예문을 통해 이해 하기 바란다.
SQL> select "사원".sawon_id "사원ID", 2 "사원".name "사원명", 3 "사원".buseo "부서명", 4 "관리자".name "관리자명" 5 from sawon "사원" left outer join sawon "관리자" 6 on "사원".manager_id = "관리자".sawon_id;
사원ID 사원명 부서명 관리자명 ---------- ---------- -------------------- ---------- 1 가길동 관리부 2 나길동 영업부 가길동 3 다길동 관리부 가길동
만약 여러분이 나중에 MS의 SQL Server등에서 질의를 하는 경우라면 다음과 같이 하면 된다. 물론 오라클에서 아래와 같이 사용하면 오류가 난다.
SQL> select "사원".sawon_id "사원ID", 2 "사원".name "사원명", 3 "사원".buseo "부서명", 4 "관리자".name "관리자명" 5 from sawon "사원", sawon "관리자" 6 where "사원".manager_id *= "관리자".sawon_id;
다른 예문을 보도록 하자.
[사원 테이블, EMP]
[부서 테이블, DEPT]
-- 부서별 사원수 출력, 사원이 한명도 없는 -- 40번 OPERATION 부서는 출력되지 않는다. select dname, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by dname;
-- right outer join select dname, count(e.empno) from emp e right outer join dept d on e.deptno = d.deptno group by dname;
select dname, count(e.empno) from emp e right outer join dept d using(deptno) group by dname;
-- 사원이름과 부서명을 출력 -- KING도 출력하고 40번부서도 동시에 출력 -- 오라클, MS-SQL 가능 select ename, dname from emp full outer join dept on emp.deptno = dept.deptno;
-- MySQL select ename, dname from emp right outer join dept on emp.deptno = dept.deptno union select ename, dname from emp left outer join dept on emp.deptno = dept.deptno;
-- MySQL은 오라클 형식(+)은 지원안함. select ename, dname from emp, dept where emp.deptno(+) = dept.deptno(+);
--이해가 되었다면 예문을 통해 Outer Join을 확실히 이해 하도록 하자. Outer Join을 이해 하지 못한 상태에서 실제 업무에서 조인을 사용한다면 대량의 데이터를 처리 할 때 조인 처리 미숙으로 인해 누락되는 데이터가 나와 원인을 찾으려면 어려운 경우를 만날 수 있을 것이다. 반드시 이해하고 넘어 가도록 하자.
--실습을 위해 SALES(매출 테이블)와 CUSTOMER(고객 테이블)을 먼저 만들자. 혹시 이미 만들어져 있다면 DROP TABLE 구문으로 삭제 후 다시 만들자.
SQL> create table customer ( 2 cust_id varchar2(4) not null primary key, 3 name varchar2(10), 4 tel varchar2(20) 5 );
테이블이 생성되었습니다.
SQL> create table sales ( 2 cust_id varchar2(4) not null constraints fk_cust_id references customer(cust_id), 3 ilja varchar2(8) not null, 4 amt number(7,0) not null 5 );
SQL> insert into customer values ('1004','가길동','111-1111'); 1 개의 행이 만들어졌습니다. SQL> insert into customer values ('1005','나길동','222-2222'); 1 개의 행이 만들어졌습니다. SQL> insert into customer values ('1006','다길동','333-3333'); 1 개의 행이 만들어졌습니다. SQL> insert into sales values ('1004','20140301',10000); 1 개의 행이 만들어졌습니다. SQL> insert into sales values ('1006','20140310',20000); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다.
SQL> select * from sales;
CUST ILJA AMT ---- -------- ---------- 1004 20140301 10000 1006 20140310 20000
SQL> select * from customer;
CUST NAME TEL ---- ---------- -------------------- 1004 가길동 111-1111 1005 나길동 222-2222 1006 다길동 333-3333
--고객성명, 매출일자, 매출액의 형태로 고객 테이블에 등록된 모든 고객에 대해 출력해야 한다고 하자. 즉 매출이 없더라도 매출액이 0원으로 매출리스트에 나타나야 한다는 의미이다. 매출내역 테이블에는 고객의 이름이 없으니 고객 테이블과 조인을 하여 고객의 이름을 가지고 와야 한다. 그런데? 매출을 일으키지 않은 1005번 고객이 누락된 것이 보이는가? EquiJoin 등을 구사하는 경우 종니 조건을 만족하지 않는 행은 출력 되지 않는 것이 기본이다. 그럼 어떻게 할 것 이다. 물론 이런 경우를 위해 Outer Join이 있는 것이다. 아래의 3가지 형태는 같은 기능을 하는 예문 이다. ON을 사용 한것과 USING을 사용 한 두 번째, 세 번째 예문은 ANSI 표준에 부합되므로 다른 데이터베이스등에서 동일하게 사용 할 수 있다. 주로 첫번째 형태를 많이 이용하며 두번째, 세번째 예문의 경우 from절 다음을 다음과 같이 from sales s inner join customer c 와 같이 써도 무방하다.
SQL> select c.name, s.ilja, s.amt 2 from sales s, customer c 3 where s.cust_id = c.cust_id;
NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000
SQL> select c.name, s.ilja, s.amt 2 from sales s join customer c 3 on s.cust_id = c.cust_id;
NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000
SQL> select c.name, s.ilja, s.amt 2 from sales s join customer c 3 using (cust_id);
NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000
--아래는 Left Outer Join의 예문이다. 왼쪽에 있는 것은 조인 조건이 맞지 않더라도 다 나타나야 한다는 것이다. 아래의 두 예문은 같은 결과를 나타내는 예문이다. 두번째에서 (+)의 위치를 잘 보라, CUSTOMER쪽에 있으니 SALES쪽이 조건에 맞지 않는 컬럼이 있더라도 모두 나타나야 한다는 것이다. SQL> select c.name, s.ilja, s.amt 2 from sales s left outer join customer c 3 on s.cust_id = c.cust_id;
NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000
SQL> select c.name, s.ilja, s.amt 2 from sales s, customer c 3 where s.cust_id = c.cust_id(+);
NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000
--아래 예문은 Right Outer Join의 예문이다. 바로 아래 예문을 보면 오른쪽에 있는 CUSTOMER 테이블의 내용은 조인에 맞지 않더라도 다 나와야 한다는 의미이다. 즉 매출이 없더라도 고객테이블에 등록되어 있는 고객 이라면 출력이 된다는 의미이다. 그 다음 예문을 보라 (+)의 위치가 어디인가? SALES 쪽이므로 CUSTOMER가 조인 조건에 맞지 않더라도 모두 출력 되어야 한다는 의미이다.
SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s right outer join customer c 3 on s.cust_id = c.cust_id;
NAME ILJA NVL(S.AMT,0) ---------- -------- ------------ 가길동 20140301 10000 다길동 20140310 20000 나길동 0
SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s, customer c 3 where s.cust_id(+) = c.cust_id;
NAME ILJA NVL(S.AMT,0) ---------- -------- ------------ 가길동 20140301 10000 나길동 0 다길동 20140310 20000
--다음은 Full Outer Join에 관련된 예문이다. 조인시 양쪽 테이블의 데이터를 전부 출력 하기 위해 사용하는 형식이다. 즉 서로 조인 조건에 맞지 않는 것이 있더라도 모두 표시 하라는 의미인데 두번째 예문에서 주의 할점은 (+) 표시는 한곳에만 할 수 있는데, (+)형태로 Full Outer Join을 표시 하지 못한다는 것을 기억하자.
--실습을 위해 sales 테이블에 고객테이블에 없는 고객으로 한건입력하자. SQL>> insert into sales values ('1007','20140301',20000);
SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s full outer join customer c 3 on s.cust_id = c.cust_id;
NAME ILJA NVL(S.AMT,0) ---------- -------- ------------ 가길동 20040301 10000 다길동 20040310 20000 나길동 0 20040301 20000
-- 오라클에서 양쪽 모두에 (+)사용은 오류. SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s, customer c 3 where s.cust_id(+) = c.cust_id(+); where s.cust_id(+) = c.cust_id(+) * 3행에 오류: ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
|
댓글 없음:
댓글 쓰기