오라클에서 조인은 복수개의 테이블 사이에서 관계되는 컬럼을 이용하여 조합한 결과를 도출하는 것을 뜻합니다. 조인은 크게 이너 조인(내부 조인), 아우터 조인(외부 조인)이 존재합니다. 이번 시간에는 조인을 중심으로 조인의 종류와 예제를 간단히 보며 학습하는 시간입니다. 주제는 다음과 같습니다.
- ORACLE JOIN vs ANSI JOIN
- INNER JOIN operation
- OUTER JOIN operation (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN)
- CROSS JOIN operation
- NATURAL JOIN operation
ORACLE JOIN vs ANSI JOIN
조인은 Inner Join, Outer Join 두 가지로 크게 나눌 수 있습니다. 그리고 조인을 표현하는 방법은 오라클 조인과 ANSI 조인이 각각 존재합니다. 오라클 조인은 문자 그대로 오라클 데이터베이스에서 단독으로 제공하는 조인 방식이며 조인 기호로 (+) 표시를 사용하는 특징이 있습니다. 그에 반해 ANSI 조인은 표준 협회에서 권고하는 조인 방법입니다. 그리하여 대개의 DBMS에서 ANSI 조인을 통한 표준 표시 방법은 호환이 가능한 편입니다. 다중 데이터베이스 사용 또는 마이그레이션을 염두에 둔다면 오라클조인보다는 ANSI조인 방법을 권장합니다. 오라클 조인 방식으로 작성된 코드도 해석을 할 필요가 있으므로 해당 방식 또한 기억해 두시는 것이 좋습니다.😘 어떠한 조인 방식을 써야 되는지는 회사 내 코드 컨벤션(코딩 규칙)을 적용하시면 될 겁니다.
INNER JOIN operation
내부 조인 또는 이너 조인이라고 하는데 "이너 조인"이라고 지칭하겠습니다. 일반적으로 "조인"이라고 말할 때에는 보통 이너 조인을 의미합니다. 이너 조인은 테이블 사이에 열거하는 모든 조건이 일치하는 결과 집합을 반환합니다. 내부 조인의 INNER 구문을 생략이 가능하여 INNER JOIN 대신 JOIN을 단독으로 사용할 수 있습니다.
예시 자료는 EMP테이블의 EMPNO와 EMP테이블의 MGR을 이너 조인한 결과를 조회합니다.
매니저가 있는 모든 사원의 정보를 조회하는 것이지요.
이너 조인을 오라클 조인으로 표현하면 JOIN ON 구문 대신 WHERE에 조건 컬럼을 표시합니다.
--ANSI JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1 JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO;
--ORACLE JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1, EMP EMP2
WHERE EMP1.MGR = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO; EMPNO ENAME JOB MGR MGREMPNO MGRENAME MGRJOB MGRMGR
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
7788 SCOTT ANALYST 7566 7566 JONES MANAGER 7839
7902 FORD ANALYST 7566 7566 JONES MANAGER 7839
7499 ALLEN SALESMAN 7698 7698 BLAKE MANAGER 7839
7521 WARD SALESMAN 7698 7698 BLAKE MANAGER 7839
7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839
7844 TURNER SALESMAN 7698 7698 BLAKE MANAGER 7839
7900 JAMES CLERK 7698 7698 BLAKE MANAGER 7839
7934 MILLER CLERK 7782 7782 CLARK MANAGER 7839
7876 ADAMS CLERK 7788 7788 SCOTT ANALYST 7566
7566 JONES MANAGER 7839 7839 KING PRESIDENT
7698 BLAKE MANAGER 7839 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 7839 KING PRESIDENT
7369 SMITH CLERK 7902 7902 FORD ANALYST 7566
13 개의 행이 선택되었습니다.OUTER JOIN operation (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN)
외부 조인 또는 아우터 조인이라고 하는데 "아우터 조인"이라고 지칭하겠습니다. 이너 조인이 모든 조건을 만족하는 조합만을 조회하는 것에 반해, 아우터 조인은 컬럼 값이 NULL 등 알 수 없는 경우에도 결과 집합을 조회할 수 있도록 지원합니다. 아우터 조인은 조회 결과의 중심을 어디에 두느냐에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 나눌 수 있습니다. OUTER는 생략할 수 있기에 보통 레프트 조인, 라이트 조인, 풀 조인이라고 지칭하기도 합니다.
LEFT OUTER JOIN operation
예시 자료는 EMP테이블의 EMPNO와 EMP테이블의 MGR을 레프트 아우터 조인한 결과를 조회합니다. 이너 조인(13개 행 조회)과 비교하였을 때 매니저가 없는 사장(ENAME = 'KING')까지 총 14개의 행을 조회합니다. 오라클 레프트 아우터 조인의 경우 우측 컬럼에 (+) 표현을 추가합니다.
--ANSI JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1 LEFT JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO;
--ORACLE JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1, EMP EMP2
WHERE EMP1.MGR = EMP2.EMPNO(+)
ORDER BY MGREMPNO, EMPNO; EMPNO ENAME JOB MGR MGREMPNO MGRENAME MGRJOB MGRMGR
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
7788 SCOTT ANALYST 7566 7566 JONES MANAGER 7839
7902 FORD ANALYST 7566 7566 JONES MANAGER 7839
7499 ALLEN SALESMAN 7698 7698 BLAKE MANAGER 7839
7521 WARD SALESMAN 7698 7698 BLAKE MANAGER 7839
7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839
7844 TURNER SALESMAN 7698 7698 BLAKE MANAGER 7839
7900 JAMES CLERK 7698 7698 BLAKE MANAGER 7839
7934 MILLER CLERK 7782 7782 CLARK MANAGER 7839
7876 ADAMS CLERK 7788 7788 SCOTT ANALYST 7566
7566 JONES MANAGER 7839 7839 KING PRESIDENT
7698 BLAKE MANAGER 7839 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 7839 KING PRESIDENT
7369 SMITH CLERK 7902 7902 FORD ANALYST 7566
7839 KING PRESIDENT
14 개의 행이 선택되었습니다.RIGHT OUTER JOIN operation
예시 자료는 EMP테이블의 EMPNO와 EMP테이블의 MGR을 라이트 아우터 조인한 결과를 조회합니다. 라이트 아우터 조인이므로 본인이 매니저가 아닌 사원을 포함하여 조회를 합니다. 오라클 라이트 아우터 조인의 경우 좌측 컬럼에 (+) 표현을 추가합니다.
--ANSI JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1 RIGHT JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO;
--ORACLE JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1, EMP EMP2
WHERE EMP1.MGR(+) = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO; EMPNO ENAME JOB MGR MGREMPNO MGRENAME MGRJOB MGRMGR
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7788 SCOTT ANALYST 7566 7566 JONES MANAGER 7839
7902 FORD ANALYST 7566 7566 JONES MANAGER 7839
7654 MARTIN SALESMAN 7698
7499 ALLEN SALESMAN 7698 7698 BLAKE MANAGER 7839
7521 WARD SALESMAN 7698 7698 BLAKE MANAGER 7839
7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839
7844 TURNER SALESMAN 7698 7698 BLAKE MANAGER 7839
7900 JAMES CLERK 7698 7698 BLAKE MANAGER 7839
7934 MILLER CLERK 7782 7782 CLARK MANAGER 7839
7876 ADAMS CLERK 7788 7788 SCOTT ANALYST 7566
7566 JONES MANAGER 7839 7839 KING PRESIDENT
7698 BLAKE MANAGER 7839 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 7839 KING PRESIDENT
7844 TURNER SALESMAN 7698
7876 ADAMS CLERK 7788
7900 JAMES CLERK 7698
7369 SMITH CLERK 7902 7902 FORD ANALYST 7566
7934 MILLER CLERK 7782
21 개의 행이 선택되었습니다.FULL OUTER JOIN operation
예시 자료는 EMP테이블의 EMPNO와 EMP테이블의 MGR을 풀 아우터 조인한 결과를 조회합니다. 이너 조인 결과에 매니저가 없는 사장 및 매니지 대상이 없는 사원을 포함하여 조회를 합니다. 오라클 풀 아우터 조인은 별도의 (+) 표시를 할 필요가 없이 ANSI 표준을 따르면 됩니다.
양쪽에 (+) 표현을 추가하면 ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다 오류가 발생합니다. 양쪽의 (+)를 제외하고 FULL JOIN 으로 변경해 주세요. (+)는 레프트, 라이트에 사용하는 오라클 조인 사인입니다.
--ANSI JOIN
SELECT EMP1.EMPNO EMPNO,
EMP1.ENAME ENAME,
EMP1.JOB JOB,
EMP1.MGR MGR,
EMP2.EMPNO MGREMPNO,
EMP2.ENAME MGRENAME,
EMP2.JOB MGRJOB,
EMP2.MGR MGRMGR
FROM EMP EMP1 FULL JOIN EMP EMP2 ON EMP1.MGR = EMP2.EMPNO
ORDER BY MGREMPNO, EMPNO; EMPNO ENAME JOB MGR MGREMPNO MGRENAME MGRJOB MGRMGR
---------- ---------- --------- ---------- ---------- ---------- --------- ----------
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7788 SCOTT ANALYST 7566 7566 JONES MANAGER 7839
7902 FORD ANALYST 7566 7566 JONES MANAGER 7839
7654 MARTIN SALESMAN 7698
7499 ALLEN SALESMAN 7698 7698 BLAKE MANAGER 7839
7521 WARD SALESMAN 7698 7698 BLAKE MANAGER 7839
7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839
7844 TURNER SALESMAN 7698 7698 BLAKE MANAGER 7839
7900 JAMES CLERK 7698 7698 BLAKE MANAGER 7839
7934 MILLER CLERK 7782 7782 CLARK MANAGER 7839
7876 ADAMS CLERK 7788 7788 SCOTT ANALYST 7566
7566 JONES MANAGER 7839 7839 KING PRESIDENT
7698 BLAKE MANAGER 7839 7839 KING PRESIDENT
7782 CLARK MANAGER 7839 7839 KING PRESIDENT
7844 TURNER SALESMAN 7698
7876 ADAMS CLERK 7788
7900 JAMES CLERK 7698
7369 SMITH CLERK 7902 7902 FORD ANALYST 7566
7934 MILLER CLERK 7782
7839 KING PRESIDENT
22 개의 행이 선택되었습니다.CROSS JOIN operation
크로스 조인은 이너 조인 중에서 곱집합 결과를 조회합니다. 보통 Cartesian product라고 지칭하며 명시적인 조인 절을 지정하지 않습니다. 사용을 많이 하는 편이 아니니까 곱집합을 의미하는 "카티지언 프로덕트"의 개념 정도만 간단히 알고 가시면 됩니다.
EMP 테이블에는 14개의 행이 있고, DEPT 테이블에는 4개의 행이 있기에 크로스 조인을 하면 n*m의 결과를 도출합니다.
SELECT EMPNO,
ENAME,
EMP.DEPTNO EMPDEPTNO,
DEPT.DEPTNO DEPTDEPTNO,
DNAME
FROM EMP CROSS JOIN DEPT;=> 56 개의 행이 선택되었습니다.
NATURAL JOIN operation
내추럴 조인은 이너 조인의 한 형태입니다. 크로스 조인과 마찬가지로 자주 사용하지는 않습니다. 크로스와 마찬가지로 명시적인 조인 절은 별도로 없으며 두 테이블 간의 공통 컬럼을 자동으로 이너 조인 대상으로 지정합니다. USING을 통해 조인이 되는 컬럼을 직관화 할 수 있습니다. 내추럴 조인의 특징은 조인에 사용된 열은 식별자를 가질 수 없습니다. 식별자로 지정하면 다음과 같은 오류가 발생합니다.
ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음
ORA-25154: USING 절의 열 부분은 식별자를 가질 수 없음
=> 식별자에서 조인에 사용된 공통 부분이 있을 겁니다. 조회 부분에서 제외해 주세요.
--내추럴 조인
SELECT *
FROM EMP NATURAL JOIN DEPT;
--내추럴 조인 USING
SELECT *
FROM EMP JOIN DEPT USING (DEPTNO);* 조인 이미지 출처는 www.w3schools.com 입니다.
INNER JOIN, OUTER JOIN 예제 (내부조인, 외부조인)
내부조인과 외부조인 예제 자료를 만들어 보았습니다. TA, TB, TC 테이블이 있으며 각각 다음과 같은 레코드가 있습니다.
- TA 테이블 방문정보
- TB 테이블 고객정보
- TC 테이블 특이사항
방문정보(TA)와 고객정보(TB)는 아우터(외부) 조인으로, 고객정보(TB)와 특이사항(TC)은 이너(내부) 조인으로 조회하는 예시를 안내해 드리려고 합니다. 4개의 예시구문을 맨 아래에 적어두었으며, 3번 째 구문만 결과가 다르고 나머지는 동일한 결과를 조회합니다.
* 고객 아이디 '4'는 '2019년 02월 12일'에 방문한 정보(TA 테이블 5번 째 로우)는 있으나, 고객정보 테이블에서는 누락된 것으로 가정하겠습니다.
WITH TA AS
(
SELECT '1' 고객아이디, '20191009' 방문일자 FROM DUAL
UNION ALL SELECT '1', '20190505' FROM DUAL
UNION ALL SELECT '2', '20190322' FROM DUAL
UNION ALL SELECT '3', '20190408' FROM DUAL
UNION ALL SELECT '4', '20190212' FROM DUAL
),
TB AS
(
SELECT '1' 고객아이디, 'A' 특이사항 FROM DUAL
UNION ALL SELECT '2', 'B' FROM DUAL
UNION ALL SELECT '3', 'B' FROM DUAL
),
TC AS
(
SELECT 'A' 특이사항, 'A등급' 특이사항설명 FROM DUAL
UNION ALL SELECT 'B', 'B등급' FROM DUAL
UNION ALL SELECT 'C', 'C등급' FROM DUAL
)
SELECT * FROM
-- TA LEFT JOIN (TB JOIN TC ON TC.특이사항 = TB.특이사항) ON TB.고객아이디 = TA.고객아이디
-- TA LEFT JOIN TB JOIN TC ON TC.특이사항 = TB.특이사항 ON TB.고객아이디 = TA.고객아이디
-- TA LEFT JOIN TB ON TB.고객아이디 = TA.고객아이디 JOIN TC ON TC.특이사항 = TB.특이사항
TA LEFT JOIN TB JOIN TC ON TC.특이사항 = TB.특이사항 ON TB.고객아이디 = TA.고객아이디;
고 방문일자 고 특 특 특이사항설명
-- ---------------- -- -- -- --------------
1 20190505 1 A A A등급
1 20191009 1 A A A등급
3 20190408 3 B B B등급
2 20190322 2 B B B등급
4 20190212ANSI join 과 Oracle join
ANSI 는 표준, 기준을 뜻합니다. (미국국가표준원 : American National Standards Institute) 오라클 데이터베이스는 자체에서 제공하는 조인 규칙으로 작성할 수 있습니다. 무엇으로 하든 상관 없으나, 다른 데이터베이스관리시스템과의 연계를 생각한다면 ANSI join 을 추천해드립니다.
오라클에서는 조인 하려는 테이블을 , 형태로 열거한 이후 WHERE 절에 조인조건을 기술할 수 있습니다. 해당의 경우, 조회 조건을 추가할 때는 AND 부터 시작하면 됩니다. 조인조건을 AND 에 기술할 수도 있습니다. (개발내규에 따라 다를 것입니다~)
SELECT *
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND 0 = 0;
ANSI 조인은 오라클 조인과 다르게 쉼표(,) 가 아닌 JOIN 문을 명시적으로 작성합니다. 그리고 조인 조건은 ON ~ 으로 기술합니다. 조회조건은 WHERE 부터 시작합니다.
SELECT *
FROM EMP INNER JOIN DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
WHERE 0 = 0;참고로 내부조인 INNER 는 아래와 같이 생략해도 됩니다.
SELECT *
FROM EMP JOIN DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
WHERE 0 = 0;Inner JOIN + Outer JOIN 하는 방법
내부 조인과 외부 조인을 교차하는 방법에 대해 간단히 알아보겠습니다. (INNER JOIN, OUTER JOIN) (join + left join / join + right join)
이너 조인을 먼저 하고 아우터 조인을 진행하는 방법, 반대로, 아우터 조인을 먼저 하고 이너 조인을 나중에 하는 방법에 대한 안내입니다.
- Inner Join & Outer Join
- (Inner Join) & Outer Join
- Inner Join & (Outer Join)
Inner Join & Left Join
SELECT *
FROM DUAL TA
JOIN DUAL TB
ON TB.DUMMY = TA.DUMMY
LEFT JOIN DUAL TC
ON TC.DUMMY = TB.DUMMY;(Inner Join) & Left Join
SELECT *
FROM (SELECT TA.DUMMY
FROM DUAL TA JOIN DUAL TB ON TB.DUMMY = TA.DUMMY) TATB
LEFT JOIN
DUAL TC
ON TC.DUMMY = TATB.DUMMY;Inner Join & (Left Join)
SELECT *
FROM DUAL TA
JOIN
(SELECT TB.DUMMY
FROM DUAL TB LEFT JOIN DUAL TC ON TC.DUMMY = TB.DUMMY) TBTC
ON TBTC.DUMMY = TA.DUMMY;실전 예제, 아우터 조인 사용하기
'없어진 기록을 찾아보기'라는 주제로 아우터 조인 예제를 한 번 생각해보겠습니다. 입력한 데이터가 저장된 테이블이 'TAB_IN', 출력한 데이터가 저장된 테이블이 'TAB_OUT'이라고 가정해 봅시다. 일부 데이터가 유실되어 입력된 기록(TAB_IN)이 없거나, 출력된 기록(TAB_OUT)이 없는 데이터를 찾아야 한다고 했을 때, 해당 데이터를 조회하는 쿼리를 작성해 보도록 하겠습니다.
조회의 기준이 되는 컬럼으로 TAB_IN이 없거나 TAB_OUT이 없는 모든 데이터를 조회해야 하므로 FULL OUTER JOIN이 적합합니다.
SELECT *
FROM TAB_IN FULL JOIN TAB_OUT ON TAB_OUT.ID = TAB_IN.ID
WHERE TAB_IN.ID IS NULL OR TAB_OUT.ID IS NULL입력된 기록만 없는 경우를 조회할 때에는 라이트 조인 (IN 테이블이 좌측)
SELECT *
FROM TAB_IN RIGHT JOIN TAB_OUT ON TAB_OUT.ID = TAB_IN.ID
WHERE TAB_IN.ID IS NULLSELECT *
FROM TAB_IN FULL JOIN TAB_OUT ON TAB_OUT.ID = TAB_IN.ID
WHERE TAB_IN.ID IS NULL출력된 기록만 없는 경우를 조회할 때에는 레프트 조인 (IN 테이블이 좌측)
SELECT *
FROM TAB_IN LEFT JOIN TAB_OUT ON TAB_OUT.ID = TAB_IN.ID
WHERE TAB_OUT.ID IS NULLSELECT *
FROM TAB_IN FULL JOIN TAB_OUT ON TAB_OUT.ID = TAB_IN.ID
WHERE TAB_OUT.ID IS NULL