오라클 SQL 문법 공부17 - JOIN (OUTER JOIN)

서론

이번에는 조인 중 아우터 조인에 대해서 알아보고자 한다. 아우터 조인은 매칭되는 값이 없을 경우 표현되지 않았던 EQUI JOIN, NON EQUI JOIN에 비해 매칭이 되지 않아도 기준이 되는 테이블의 값들은 표시되는 것을 말한다. 아래 내용을 살펴보자.

본론

OUTER JOIN

--OUTER JOIN (아우터 조인)
--EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL이면
-- = 비교 결과가 거짓이 되어 NULL 값을 가진 행은 출력되지 않는다
--EQUI JOIN에서 양측 칼럼 값 중의 하나가 NULL 이지만
--조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 이 사용된다

--WHERE 절의 조인 조건에서 OUTER JOIN 연산자인 (+) 기호 사용
--조인 조건문에서 NULL 이 출력되는 테이블의 칼럼에(+) 기호 추가
SELECT S.NAME, S.PROFNO, P.NAME FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+);

기준이 되지 않은 테이블의 컬럼값에 (+)를 붙이면 된다. 예를들어 사원 테이블(사원번호, 부서번호)과 연봉테이블(사원번호, 연봉)이 있다고 하자. 사원테이블의 사원번호는 있지만 아직 연봉협상이 되질 않아 연봉테이블에는 해당 사원에 대한 데이터가 없다고 가정할 때, 각 부서별 전체 사원의 평균 연봉을 구하고 싶다고 생각을 하자.

이 경우 단순히 EQUI 조인을 통해 

SELECT (사원테이블. 부서번호) AS "부서", AVG(연봉테이블.연봉) AS "평균연봉" 

FROM 사원테이블, 연봉테이블

WHERE 사원테이블.사원번호 = 연봉테이블.사원번호

GROUP BY 사원테이블.부서번호;

라고 한다면 위에 연봉테이블에 아직 데이터가 없는 사원에 대해서는 제외가 되고 계산이 될 것이다. 해당 사원의 부서가 실제 총 10명일 경우 9명에 관해서 평균을 내버린다. 따라서 정확한 부서별 평균값이 나올 수가 없다.

이럴 경우 

SELECT (사원테이블. 부서번호) AS "부서", AVG(연봉테이블.연봉) AS "평균연봉" 

FROM 사원테이블, 연봉테이블

WHERE 사원테이블.사원번호 = 연봉테이블.사원번호(+)

GROUP BY 사원테이블.부서번호;

이렇게 아우터 조인을 해준다면 연봉정보가 없는 사원정보까지 포함되여 정확하게 계산이 될 것이다. 기준은 사원테이블의 사원번호 이기 때문에 조인을 하는 다른 테이블 컬럼값에 (+)를 붙여 해당 동일한 값이 존재하지 않더라도 표시되게 해줘야 한다는 말이다.

사원테이블.사원번호	사원테이블.부서번호	연봉테이블.사원번호	연봉테이블.연봉
0001	10	0001	100
0002	10	0002	200
0004	10	0004	300

모든 컬럼을 사원테이블에 사원번호가 0001~0004 까지만 있다는 전제하에 조회해 보면 위와같이 나올텐데 EQUI JOIN으로는 사원테이블.사원번호, 연봉테이블.사원번호가 같을 경우의 행만 출력되기 때문에 총 3건이 나오지만,

OUTER JOIN으로는 같은 값이 없더라도 사원테이블의 행 기준으로 매칭 되는 값이 없어도 4행 모두 출력이 된다는 말이다.

사원테이블.사원번호	사원테이블.부서번호	연봉테이블.사원번호	연봉테이블.연봉
0001	10	0001	100
0002	10	0002	200
0003	10	NULL	NULL
0004	10	0004	300

A테이블과 B테이블이 있다면 EQUI 조인은 위와 같이 교집합으로써 같은 행들만 출력이 된거라면

OUTER 조인 시 A.컬럼 = B.컬럼(+) 일 경우에는 위와 같이 A 테이블 기준으로 행이 출력되며

OUTER 조인 시 A.컬럼(+) = B.컬럼 일 경우에는 위와 같이 B테이블 기준으로 행이 출력 된다.

위와 같은 특징 때문에 특정 테이블을 기준으로 출력하고 싶을 경우 조인되는 다른 테이블에 (+)표시를 해준다고 얘기를 한 것이다.

FULL OUTER JOIN

--풀아우터 조인은 아래 처럼하면 된다.
SELECT S.NAME, S.PROFNO, P.NAME FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO(+) = P.PROFNO(+);

--하지만 성능상 좋지 않으므로 각각의 아우터조인 값을 UNION으로 묶어서 표현하는게 좋다.
--UNION ALL로 하면 중복된 행들까지 또 출력되므로 아우터조인과 결과는 다르다.
SELECT S.NAME, S.PROFNO, P.NAME
FROM STUDENT S, PROFESSOR P WHERE S.PROFNO = P.PROFNO(+)
UNION 
SELECT S.NAME, S.PROFNO, P.NAME
FROM STUDENT S, PROFESSOR P WHERE S.PROFNO(+) = P.PROFNO;

풀 아우터 조인은 두 테이블의 합집합으로 보면 쉽다.

A.컬럼(+) = B.컬럼(+) 으로 표시를 하면 되며 A테이블의 모든행, B테이블의 모든 행이 나오되 중복된 행들은 하나로 출력된다고 보면 된다.

사원테이블.사원번호	사원테이블.부서번호	연봉테이블.사원번호	연봉테이블.연봉
0001	10	0001	100
0002	10	0002	200
0003	10	NULL	NULL
0004	10	0004	300
NULL	NULL	0011	200

실습문제

 --1)HR계정의 EMPLOYEES 테이블의 부서정보는 DEPARTMINTS 테이블에 있다.
--하지만 각 부서의 위치 정보는 LOCATIONS 테이블에 분리되어 있다.
--세 테이블을 조인하여 각 직원의 이름, 사원번호, 부서명, 부서의 CITY 정보를 함께 출력하도록 하여라
SELECT E.LAST_NAME,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME,
       L.CITY
  FROM EMPLOYEES E,
       DEPARTMENTS D,
       LOCATIONS L
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
   AND D.LOCATION_ID = L.LOCATION_ID;
 
--2)PROFESSOR 테이블에서 하반기에 입사한 교수들의 입사월별 평균연봉을 구하라
--단, 연봉은 PAY*12+BOUNUS로 나타내고, BONUS가 없는 교수는 100을 부여하여 계산하여라.
SELECT TO_CHAR(HIREDATE, 'MM') AS "입사",
       AVG(PAY * 12 + NVL(BONUS, 100))
  FROM PROFESSOR
 WHERE TO_CHAR(HIREDATE, 'MM') >= '07'
 GROUP BY TO_CHAR(HIREDATE,
               'MM')
 ORDER BY "입사";
 
--3)STUDENT 테이블과 EXAM_01 테이블을 이용하여 각 학과별 평균점수와 최고점수,
--최저 점수를 나타내되 학과이름, 학과번호와 함께 출력되도록 작성하여라.
SELECT D.DNAME,
       AVG(E.TOTAL),
       MAX(E.TOTAL),
       MIN(E.TOTAL)
  FROM STUDENT S,
       EXAM_01 E,
       DEPARTMENT D
 WHERE S.STUDNO = E.STUDNO
   AND S.DEPTNO1 = D.DEPTNO
 GROUP BY D.DNAME;
 
--S.DEPTNO1 컬럼을 사용하기 위해서는 복수행함수로 둘러 쌓여야 한다.
--하지만 S.DEPTNO1가 D.DNAME과 1:1이라면 GROUP BY 절에 또 추가 해주면 정상적으로 나온다.
SELECT D.DNAME,
       S.DEPTNO1,
       AVG(E.TOTAL),
       MAX(E.TOTAL),
       MIN(E.TOTAL)
  FROM STUDENT S,
       EXAM_01 E,
       DEPARTMENT D
 WHERE S.STUDNO = E.STUDNO
   AND S.DEPTNO1 = D.DEPTNO
 GROUP BY D.DNAME,
       S.DEPTNO1;
 
--4) 담당교수별 학생수를 구하고, 교수이름과 학과이름을 함께 출력하여라.
--단, 한명의 교수가 여러 학과에 소속될 수 없다고 가정한다.
SELECT P.NAME,
       D.DNAME,
       COUNT(1)
  FROM STUDENT S,
       PROFESSOR P,
       DEPARTMENT D
 WHERE S.PROFNO(+) = P.PROFNO
   AND P.DEPTNO = D.DEPTNO
 GROUP BY P.NAME,
       D.DNAME;
 
SELECT P.NAME,
       D.DNAME,
       COUNT(1)
  FROM PROFESSOR P,
       STUDENT S,
       DEPARTMENT D
 WHERE P.PROFNO = S.PROFNO(+)
   AND P.DEPTNO = D.DEPTNO
 GROUP BY P.NAME,
       D.DNAME;
 
--5)STUDENT, PROFESSOR, DEPARTMENT 테이블을 이용하여 각 학생의 이름, 학번, 담당교수 이름,
--담당교수의 학과명을 함께 출력하되, 모든 학생의 정보가 나타나도록 하여라.
SELECT S.NAME,
       S.STUDNO,
       P.NAME,
       D.DNAME
  FROM STUDENT S,
       PROFESSOR P,
       DEPARTMENT D
 WHERE S.PROFNO = P.PROFNO(+)
   AND P.DEPTNO = D.DEPTNO(+);
 SELECT S.NAME,
       S.STUDNO,
       P.NAME,
       D.DNAME
  FROM STUDENT S,
       PROFESSOR P,
       DEPARTMENT D
 WHERE S.PROFNO = P.PROFNO(+)
   AND S.DEPTNO1 = D.DEPTNO;

결론

OUTER JOIN에 대해서 알아보았다. OUTER JOIN은 특히 (+) 이걸 어디에다가 붙여야 되는지 헛갈리기 때문에 좀 더 자세하게 설명을 해보았다. 지금처럼 단순히 컬럼값 자체에 대해서만 비교하기 때문에 컬럼명 바로 뒤에 (+)를 붙였는데 혹시나 함수를 붙여 가공을 해야 할수도 있을 것이다. TO_DATE(컬럼, 'YYYY') 이런 식이라면 TO_DATE(컬럼, 'YYYY')(+) 이게 아니라 TO_DATE(컬럼(+), 'YYYY') 이런식으로 표현을 해주어야 맞는 표현이다.