[Oracle] 오라클 조인하는 방법 정리

오라클에서 사용되는 조인들에 대해 알아보자

우선 예제 테이블 데이터를 준비합니다.

CREATE TABLE DEPT
       (DEPTNO number(10),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) ,
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-11',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);

create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );

insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);

commit;

| EQUI JOIN

사원(EMP) 테이블과 부서(DEPT) 테이블을 조인하여 이름과 부서 위치를 출력해 보겠습니다.

SELECT E.ENAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

서로 다른 테이블에 있는 컬럼들을 하나의 결과로 출력하려면 조인(JOIN)을 사용해야 합니다.

ENAME은 EMP 테이블에도 있고 LOC는 DEPT 테이블에 존재하므로 ENAME과 LOC를 하나의 결과로 출력하기 위해서는 FROM 절에 EMP와 DEPT 둘 다 기술합니다.

EMP와 DEPT를 조인하기 위해서는 조인이 있어야 합니다. 조인 조건은 두 개의 테이블을 연결하기 위한 연결고리입니다.

EMP 테이블에도 DEPNO가 존재하고 DEPT 테이블에도 DEPTNO가 존재하므로 EMP 테이블의 부서 번호는 DEPT 테이블의 부서 번호와 같다는 조건 E.DEPTNO = D.DEPTNO를 주어 조인을 수행합니다.

만약 이 조인 조건을 주지 않고 다음과 같이 조인하게 되면 전부 다 조인이 되어 56개(14 x 4)의 행이 출력됩니다.

SELECT E.ENAME, D.LOC
FROM EMP E, DEPT D

WHERE 절에 조인 조건을 정확하게 작성하고 조인을 하게 되면 14개의 행만 출력됩니다. 조인이 되어 14개의 행이 출력되는 원리는 다음과 같습니다.

먼저 사원 테이블에서 첫 번째 이름 KING을 가져옵니다. 그리고 KING의 부서 위치를 출력하기 위해 KING의 부서 번호 10번으로 DEPT 테이블에서 해당 부서 번호인 10번인 부서의 위치인 NEW YORK을 찾아서 출력합니다. KING 부터 시작해 맨 아래 ADAMS까지 이 작업을 반복하여 조인합니다.

이러한 문법을 EQUI JOIN이라고 합니다. 조인 조건이 이퀄(=)이면 EQUI JOIN 입니다.

위의 결과에서 직업이 ANALYST인 사원들만 출력합니다.

SELECT E.ENAME, D.LOC, E.JOB 
FROM   EMP E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO
AND    E.JOB = 'ANALYST';

E.DEPTNO = D.DEPTNO 는 조인 조건이고 E.JOB = 'ANALYST' 는 검색 조건입니다. 조인 조건은 두 테이블을 조인하기 위해 필요한 조건이고, 검색 조건은 전체 데이터 중에 특정 데이터만 제한해서 보기 위한 조건입니다. 조인조건과 검색 조건을 AND 연산자로 연결하여 작성합니다.

| NON EQUI JOIN

사원(EMP) 테이블과 급여 등급(SALGRADE) 테이블을 조인하여 이름, 월급, 급여 등급을 출력해 보겠습니다.

SELECT E.ENAME, E.SAL, S.GRADE
FROM   EMP E, SALGRADE S
WHERE  E.SAL BETWEEN S.LOSAL AND S.HISAL

SALGRADE 테이블은 급여 등급 테이블입니다. GRADE는 등급이고 LOSAL은 등급을 나누는 월급 범위 하단, HISAL은 월급 범위의 상단을 나타냅니다. (5등급이 제일 높은 등급 입니다.)

SELECT * FROM SALGRADE

EMP 테이블과 SALGRDAE 테이블을 조인해서 이름(ENAME)과 급여 등급(GRADE)을 하나의 결과로 출력하고자 합니다. 그런데 EMP와 DEPT 사이의 DEPTNO처럼 동일한 컬럼이 없습니다. 그래서 eqaul(=)을 사용하여 equi join을 사용 할 수 없게 되었습니다. 이렇게 조인 조건에 equal 조건을 줄 수 없을때 사용하는 조인이 NON EQUI JOIN입니다.

두 테이블 사이에 동일한 컬럼은 없지만 비슷한 컬럼이 있습니다. 바로 EMP 테이블의 SAL 컬럼과 SALGRDAE 테이블의 LOSAL 과 HISAL 컬럼입니다. EMP 테이블의 월급은 SALGRDAE 테이블의 LOSAL과 HISAL 사이에 있습니다. 이 말을 영작하여 다음과 같이 WHERE절에 조인 조건을 작성하여 두 테이블을 조인합니다.

SELECT E.ENAME, E.SAL, S.GRADE
FROM   EMP E, SALGRADE S
WHERE  E.SAL BETWEEN S.LOSAL AND S.HISAL

| OUTER JOIN

사원(EMP) 테이블과 부서(DEPT) 테이블을 조인하여 이름과 부서 위치를 출력하는데, BOSTON(DEPT테이블) 도 같이 출력되게 해 보겠습니다.

SELECT E.ENAME, D.LOC 
FROM   EMP E, DEPT D
WHERE  E.DEPTNO(+) = D.DEPTNO

EQUI JOIN 과는 다르게 BOSTON이 출력되고 있습니다.

EQUI JOIN 시 EMP와 DEPT 테이블과의 조인 결과에서는 BOSTON이 출력되지 않은 이유는 EMP 테이블에 40번 부서 번호가 없어서 DEPT 테이블과 조인되지 않았기 때문입니다. 그런데 위의 결과와 같이 "BOSTON에는 사원이 배치되지 않았다"는 정보를 한눈에 확인하려면 OUTER JOIN을 사용해야 합니다. OUTER JOIN 은 기존 EQUI JOIN 문법에 OUTER 조인 사인(+)만 추가한 것입니다.

위의 결과에서는 EMP 테이블의 ENAME 데이터가 DEPT 테이블의 LOC 데이터보다 모자라게 출력되고 있으므로 EMP 테이블 쪽에 (+)를 붙여줍니다.

EQUI JOIN 은 양쪽에 다 존재하는 데이터만 출력됩니다. 그러나 RIGHT OUTER JOIN은 DEPT 테이블에는 존재하고 EMP 테이블에 존재하지 않는 데이터도 출력합니다. 그리고 LEFT OUTER JOIN은 EMP 테이블에는 존재하는데 DEPT 테이블에는 존재하지 않는 데이터도 출력합니다.

| SELF JOIN

사원(EMP) 테이블 자기 자신의 테이블과 조인하여 이름, 직업, 해당 사원의 관리자 이름과 관리자 직업을 출력해 보겠습니다.

SELECT  E.ENAME AS 사원
      , E.JOB   AS 직업
      , M.ENAME AS 관리자
      , M.JOB	AS 직업
FROM 	EMP E, EMP M
WHERE   E.MGR  = M.EMPNO 
AND 	E.JOB = 'SALESMAN'

MARTIN, ALLEN, TURNER, WARD의 관리자는 BLAKE 입니다.

MGR은 해당 사원의 직속 상사의 사원 번호입니다. 직속 상사는 바로 해당 사원의 관리자입니다.

그래서 BLAKE의 사원 번호 7698을 MGR 번호로 하고 있는 사원들은 MARTIN, ALLEN, TURNER, WARD입니다.

그래서 조인할 때 사원 번호 (EMPNO)과 관리자 번호(MGR)가 필요합니다.

FROM 절에 사원 테이블을 2개를 기술하고 하나는 E로 별칭을 주고 다른 하나는 M으로 별칭을 줍니다. EMP 테이블을 사원과 관리자가 섞여서 구성되어 있으므로 EMP 테이블은 사원 테이블이라고 할 수 있고 관리자 테이블이라고도 할 수 있습니다. 그래서 사원 테이블을 "E"라고 하고 관리자 테이블을 "E"라고 하고 관리자 테이블을 "M"이라고 별칭을 주었습니다.

| ON절

조인 작성법은 크게 오라클 조인 작성법과 ANSI(안시) 조인 작성법 두가지로 나뉩니다. 위에서는 오라클 조인 작성법 4가지를 보았습니다.

이번에는 ANSI 조인 방법에 대해서 소개하겠습니다.

ON절을 사용한 조인 방법으로 이름과 직업, 월급, 부서 위치를 출력해 보겠습니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E JOIN DEPT D 
ON     (E.DEPTNO = D.DEPTNO)
WHERE  E.JOB = 'SALESMAN'

ON 절에서는 EQUI JOIN에서 WHERE 절에 작성했던 조인 조건을 작성합니다.

직업이 SALESMAN인 사원들만 제한하는 검색 조건은 WHERE 절에 작성합니다.

오라클 EQUI JOIN과 ON 절을 사용한 조인 작성법의 차이는 다음과 같습니다.

오라클 EQUI JOIN	ON 절을 사용한 조인
SELECT E.ENAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;	SELECT E.ENAME, D.LOC FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

WHERE 절에 작성했던 조인 조건을 ON 절에 작성했습니다. 여러 개의 테이블을 조인할 때 조인 작성법은 아래와 같습니다.

오라클 EQUI JOIN	ON 절을 사용한 조인
SELECT E.ENMAE, D.LOC FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;	SELECT E.ENMAE, D.LOC, S.GRDADE FROM EMP E JOIN DEPT D ON (E.DETPNO = D.DEPTNO) JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

| USING절

USING 절을 사용한 조인 방법으로 이름, 직업, 월급, 부서 위치를 출력해 보겠습니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E 
JOIN   DEPT D
USING  (DEPTNO)
WHERE  E.JOB = 'SALESMAN'

위의 예제는 WHERE 절 대신 USING 절을 사용하여 EMP와 DEPT 테이블을 조인하는 쿼리입니다.

USING 절에는 조인 조건 대신 두 테이블을 연결 할 떄 사용할 컬럼인 DEPTNO 만 기술하면 됩니다.

DEPTNO 앞에는 테이블 명이나 테이블 별칭을 사용할 수 없습니다. 사용하게 되면 다음과 같은 오류가 발생합니다.

SQL Error [1748] [42000]: ORA-01748: 열명 그 자체만 사용할 수 있습니다

Error position: line: 8 pos: 143

오라클 EQUI JOIN과 USING 절을 사용한 작성법의 차이는 다음과 같습니다.

오라클 EQUI JOIN	USING절을 사용한 조인
SELECT E.NAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;	SELECT E.ENAME, D.LOC FROM EMP E JOIN DEPT D USING (DEPTNO);

USING 절에는 반드시 괄호를 사용해야 합니다. 괄호를 사용하지 않으면 다음과 같은 에러가 발생합니다.

SQL Error [906] [42000]: ORA-00906: 누락된 좌괄호

Error position: line: 8 pos: 141

| NATURAL JOIN

NATURAL 조인 방법으로 이름, 직업, 월급과 부서위치를 출력해 보겠습니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E NATURAL JOIN DEPT D
WHERE  E.JOB = 'SALESMAN';

위의 예제는 조인 조건을 명시적으로 작성하지 않아도 FROM 절에 EMP 와 DEPT 사이에 NATURAL JOIN 하겠다고 기술하면 조인이 되는 쿼리입니다. 두 테이블에 둘 다 존재하는 동일한 컬럼을 기반으로 암시적인 조인을 수행합니다.

둘 다 존재하는 동일한 컬럼인 DEPTNO를 오라클이 알아서 찾아 이를 이용하여 조인을 수행합니다. 이 때 다음과 같이 WHERE 절에 조건을 기술 할 때 조인의 연결고리가 되는 컬럼인 DEPTNO는 테이블명을 테이블 별칭 없이 기술해야 합니다.

테이블 별칭을 사용하게 되면 다음과 같이 오류가 발생합니다.

ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음

다음과 같이 테이블 별칭 없이 작성해야 에러 없이 수행됩니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E NATURAL JOIN DEPT D
WHERE  E.JOB = 'SALESMAN' AND DEPTNO = 30;

| LEFT/RIGHT OUTER JOIN

RIGHT OUTER 조인 방법으로 이름, 직업, 월급, 부서 위치를 출력해 보겠습니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E RIGHT OUTER JOIN DEPT D
ON     (E.DEPTNO = D.DEPTNO)

위의 예제는 오라클 조인 작성법 중 OUTER 조인을 ANSI 조인의 RIGHT OUTER JOIN 으로 작성한 SQL 입니다. RIGHT OUTER JOIN을 그래프로 시각화 하면 다음과 같습니다.

EMP 와 DEPT를 조인할 때 오른쪽의 DEPT 테이블의 데이터는 전부 출력됩니다.

오라클 아우터 조인과 ANSI 조인 SQL 작성법 차이는 다음과 같습니다.

오라클 OUTER JOIN	ANSI RIGHT OUTER JOIN
SELECT E.ENAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO (+) = D.DEPTNO	SELECT E.ENAME, D.LOC FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPNO = D.DEPTNO)

LEFT OUTER JOIN 을 수행하기 위해 DEPT 테이블에는 없는 부서 번호 50번을 다음과 같이 사원 테이블에 입력합니다.

INSERT INTO EMP(EMPNO, ENAME, SAL, JOB, DEPTNO) VALUES (8282, 'JACK', 3000, 'ANALYST', 50)

다음과 같이 ANSI의 LEFT OUTER JOIN을 수행합니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E LEFT OUTER JOIN DEPT D
ON     (E.DEPTNO = D.DEPTNO)

EMP 테이블에 데이터는 전부 출력되었습니다. DEPT 테이블에는 DEPTNO가 50인 데이터가 존재하지 않아서 부서위치가 NULL로 출력되었습니다.

오라클 OUTER 조인과 ANSI의 LEFT OUTER JOIN 작성법 차이는 다음과 같습니다.

오라클 OUTER JOIN	ANSI LEFT OUTER JOIN
SELECT E.ENAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO(+);	SELECT E.ENAME, D.LOC FROM EMP E LEFT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)

오라클 OUTER 조인의 아우터 조인 사인(+) 는 데이터가 덜 출력되는 쪽에 붙혀줍니다.

| FULL OUTER JOIN

FULL OUTER 조인 방법으로 이름, 직업, 월급, 부서위치를 출력해보겠습니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E FULL OUTER JOIN DEPT D
ON     (E.DEPTNO = D.DEPTNO)

위의 예제는 RIGHT OUTER JOIN 과 LEFT OUTER JOIN 을 한번에 수행하여 출력하는 쿼리입니다.

EMP 테이블에만 있는 부서 번호 50번이 JACK 데이터와 DEPT 테이블에만 있는 부서 번호 40번에 대한 데이터인 BOSTON 을 출력하며 조인하고 있습니다.

오라클 조인 작성법으로 아우터 조인을 작성할 때 다음과 같이 아우터 조인 사인을 (+) 를 양쪽에 작성하면 다음과 같은 에러가 발생합니다.

FULL OUTER JOIN 을 사용하지 않고 동일한 결과를 출력하려면 다음과 같이 쿼리를 작성해야 합니다.

SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E LEFT OUTER JOIN DEPT D
ON     (E.DEPTNO = D.DEPTNO)
UNION 
SELECT E.ENAME AS 이름
     , E.JOB AS 직업
     , E.SAL AS 월급
     , D.LOC AS 부서위치
FROM   EMP E RIGHT OUTER JOIN DEPT D
ON     (E.DEPTNO = D.DEPTNO)

UNION 연산자를 이용하여 두개의 쿼리 결과를 위아래로 이어 붙여 출력하여 FULL OUTER JOIN 과 같은 결과를 얻는 것 입니다.

저작자표시

'RDBMS' 카테고리의 다른 글

[Oracle] 오라클 소수점 반올림(ROUND), 절사(TRUNC) (0)	2023.02.15
[Oracle] 오라클 데이터 복구 (0)	2023.01.30
[Oracle] MERGE INTO -> 데이터가 있는지 확인(SELECT) 하고 데이터를 삽입하거나 업데이트(INSERT, UPDATE)하자 (2)	2022.09.17

'RDBMS' 카테고리의 다른 글

검색 태그

티스토리툴바