[오라클] JOIN

 

JOIN

 

• 두 개 이상의 테이블을 가로로 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식입니다.
  
  
• 오라클에서만 사용 가능한 Oracle Join방식과
  
  
• SQL-99표준 문법의 ANSI Join 방식이 있습니다.
  
  
• From절에 조회할 테이블을 명시합니다.
  
  
• Oracle Join은 Where절에 Join조건을 명시합니다.
  
  
• ANSI Join은 From절에 Join조건을 명시합니다.

 

테이블 별칭 지정

JOIN을 사용할 때 두개 이상의 테이블을 연결하기 때문에 내가 조할 컬럼에 대해서 어떤 테이블에 존재하는지 명시하기 위해 FROM절에 TABLE명 뒤에 테이블 별칭을 지정해줍니다. (래퍼런스 변수같은 개념)

 

 

위의 테이블을 보면 EMP 테이블의 별칭을 E, DEPT 테이블의 별칭을 D 로 지정을 해주었습니다.

그 다음 WHERE절에 보면 JOIN 조건을 지정해 주었는데 공통열을 지정해주는 것이고 지정해 주지 않는다면 카티션곱이 발생하게 됩니다.

카티션 곱은 JOIN 작업에 참조되는 테이블의 행 수를 모두 곱한 값 만큼 결과가 출력됩니다.

어떤 차이가 있는지 확인해 보겠습니다.

*카티션곱발생

 

*JOIN조건을 사용해 카티션곱X

 

카티션곱이 발생 하면 행의 수와 열의 수를 모두 곱한 결과가 나오게 됩니다.
카티션곱이 발생하지 않고 정상적으로 수행이 된다면 최소한의 경우의 수만 조회가 되게 됩니다.

 

ORACLE JOIN

 

1. 등가조인

▶ JOIN 조건절에 Equal 연산자(=)를 사용합니다.

▶ 테이블 간 공통 열을 기준 열로 직접 명시하여 Join 합니다.

▶ 내부 조인 (Inner Join), 단순 조인 (Simple Join)이라고 부르기도 합니다.

 

기본 사용 예시

DEPT 테이블에서 DEPTNO, DNAME, LOC 컬럼을 참조,
EMP 테이블에서 ENAME,SAL 컬럼을 참조하여 두개의 테이블을 JOIN해서 출력해보겠습니다.

※단 SAL컬럼의 값은 2000이상인 것들만 출력하겠습니다. 

SELECT D.DEPTNO, D.DNAME, D.LOC, E.ENAME,E.SAL
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND E.SAL >=2000
ORDER BY E.DEPTNO;

위의 명령어를 실행하게 되면 아래와 같이 결과가 출력되게 됩니다.

두 개의 테이블을 참조하여 하나의 테이블 처럼 조회를 하였고,
SAL이 2000이상만 출력하라는 조건을 주어 출력을 하였습니다.
정렬은 DEPTNO를 기준으로 오름차순으로 정렬을 하였습니다.

 

ANSI JOIN 방식

ANSI 방식은 표준 방식입니다. 위의 오라클 방식으로 사용한것과 같은 기능을 합니다

 

ANSI JOIN ON

SELECT D.DEPTNO, D.DNAME, D.LOC, E.ENAME,E.SAL
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
AND E.SAL >=2000
ORDER BY DEPTNO;

Join기준 열을 직접 명시합니다.

테이블 간 공통 열이 여러 개 일 때 사용합니다.

기준 열에 테이블을 명시 합니다.

ANSI JOIN USING

SELECT DEPTNO, D.DNAME, D.LOC, E.ENAME,E.SAL
FROM EMP E JOIN DEPT D USING (DEPTNO) -- 기준열
WHERE E.SAL >=2000
ORDER BY DEPTNO;

Join기준 열을 직접 명시합니다.

테이블 간 공통 열이 여러 개 일 때 사용합니다.

기준 열에 테이블을 명시하지 않습니다.

 

예제1

 

다음과 같이 각 부서별 부서 번호, 부서 이름, 평균 급여, 최대 급여, 최소 급여, 사원 수를 출력해 보세요.
-단, 평균 급여는 소수점 둘째 자리에서 버림 하세요.

예제1 정답

SELECT D.DEPTNO, D.DNAME, 
	TRUNC(AVG(E.SAL),2)AVG_SAL, 
    MAX(E.SAL)MAX_SAL, MIN(E.SAL)
    MIN_SAL,
    COUNT(*) CNT
FROM DEPT D, EMP E
    WHERE D.DEPTNO = E.DEPTNO
    GROUP BY D.DEPTNO,D.DNAME;

SELECT 부터 FROM 까지 출력할 컬럼을 명시해줍니다. 기존 테이블을 조회할 때와 다른점은 함수를 사용하여 출력할 것이기 때문에 함수를 출력하는 부분과 테이블 별칭을 같이 써주여야 한다는것 말고는 같습니다.

FROM 에서 테이블1,2... 들의 별칭을 지정해주고

WHERE에서 JOIN 조건을 사용하게 됩니다.

그리고 그룹함수를 사용하지 않은 함수에 대해서는 GROUP BY절을 사용해 그룹화를 시켜줍니다.

 

2.비등가조인

Join 조건절에 Equal 연산자(=)를 사용하지 않고 , 다른 연산자나 함수를 사용하여 Join 합니다.

 

사용예시

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

결과

 

SALGRADE 테이블 구조 전체 조회

EMP 테이블의 SAL 컬럼만 조회

해석 (테이블은 생략하고 설명 E = EMP, S = SALGRADE)

등가조인과 다른 부분은 (=) 대신 다른 조건 (BETWEEN)을 사용을 한 것입니다.
E.SAL BETWEEN S.LOSAL AND S.HISAL
=> BETWEEN 조건을 사용하여 SAL 컬럼의 값이 LOSAL ~ HISAL값 사이에 있으면 그 값에 해당하는 GRADE가 붙게 됩니다. 조건을 사용하게 될 경우 조건에 맞는 행 끼리 하나의 행으로 합쳐지게 됩니다. 
이런식으로 다른 조건을 주어 사용이 가능합니다.

3.자체 조인

하나의 테이블의 별칭을 다르게 지정하여 여러 개의 테이블처럼 사용합니다.

하나의 테이블을 여러개의 테이블과 같이 사용하기 위해 EMP테이블의 별칭을 E1, E2 두개를 생성했습니다.
이번 예시는 EMP테이블에서 기본 사원의 직속상사(MGR)의 사번과 이름을 한 행에 출력을 시키려고 합니다. 

 

 

빨간 블록으로 처리한 부분을 JOIN을 시켜줄거고 하나의 테이블에서 이루어집니다.
결과를 확인해보겠습니다.

노란 부분이 E1, 빨간부분이 E2의 정보입니다. 첫 행을보면 FORD의 직속상관의 사번과 이름까지 한 행에 출력되는 것을 확인 할 수 있습니다.

 

예제2

다음과 같이 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관 정보를 사원 번호로 정렬하여 출력해 보세요.

 

 

정답

SELECT DISTINCT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, S.GRADE GRADE,E1.EMPNO MGR_NO,E1.ENAME MGR_NAME
FROM DEPT D, EMP E, SALGRADE S, EMP E1
WHERE D.DEPTNO = E.DEPTNO
AND E.MGR = E1.EMPNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
ORDER BY D.DEPTNO ASC;

 

SELECT - 조회할 컬럼
FROM - 참조할 테이블 및 별칭
WHERE ~ AND = JOIN 조건