개발 공부/Oracle

[Oracle] - 조인

징_ 2023. 11. 30. 12:06

*조인

: 두 개 이상의 테이블을 연결해 하나의 테이블처럼 출력할 때 사용하는 방식

- 집합 연산자와 조인의 차이점

  • 조인 : 가로 연결 
  • 집합 연산자 : 세로 연결

 

 

- FROM절

=> 여러 개 테이블을 지정하는 것이 가능

SELECT
  FROM 테이블1, 테이블2,..., 테이블N

 

<예시>

SELECT *
  FROM EMP, DEPT
 ORDER BY EMPNO;

 

 

 

 

- 조인 조건 없을 때 문제점

  • 결과로 나올 수 있는 모든 행을 조합하기 때문에 데이터가 정확히 맞아 떨어지지 않는 데이터도 함께 출력된다.
  • 따라서 어떤 데이터를 가로로 정확히 연결해야 하는지의 기준은 WHERE절에 출력행 선정 조건식을 명시한다.

* 데카르트 곱(Cartesian product) 때문에 정확히 연결되지 않아서 필요 없는 데이터까지 모두 조합되어 출력된다. 

연결해 주는 열이 하나 필요하므로 조건식의 최소 개수는 조인 테이블 개수에서 하나를 뺀 값

 

*같은 이름의 열을 구별하는 방법

- 열 앞에 테이블 이름 명시

테이블 이름.열이름

 

<예시>

EMP.DEPTNO

 

 

 

 

- 테이블 별칭 설정

FROM 테이블 이름1 별칭1, 테이블 이름2 별칭2...

=> 한 칸 공백을 두어 지정한다.

 

<예시>

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

 

 

 

 

*조인 종류

- 등가 조인(equi join)

  • = 내부조인(inner join) = 단순 조인(simple join) 
  • 테이블 연결 후 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식 
  • '=' 사용

<예시>

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND SAL >= 3000;

=> SELECT절에서 DEPTNO의 테이블은 D가 되어도 E가 되어도 상관 없음

하지만 테이블 명시 없이 DEPTNO만으로는 못 씀

=> WHERE절의 등가 조인 조건식 이외의 조건식을 쓰고 싶다면 AND쓰기

 

 

 

 

- 비등가 조인

  • 등가 조인 외의 방식
  • 범위로 조인을 하는 방식
  • 'BETWEEN A AND B' 사용

<예시>

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

 

- 결과값

- SALGRADE 테이블

=> E.SAL이 S.LOSAL~S.HISAL 사이의 등급에 들어갈 때 GRADE 구하기

 

 

 

 

- 자체 조인

  • 하나의 테이블을 여러 테이블처럼 사용하는 방식
  • 같은 테이블을 두 번 사용
  • 큰 범위에서는 등가 조인임

<예시>

SELECT E.EMPNO, E.ENAME, E.SAL, E.MGR, M.ENAME, M.SAL
  FROM EMP E, EMP M
 WHERE E.MGR = M.EMPNO;

=> KING은 MGR이 NULL이기 때문에 13명만 출력됨. 따라서 외부 조인을 사용한다.

 

 

 

 

 

- 외부 조인

  • 어느 한쪽이 NULL이어도 강제로 출력하는 방식
  • 좌우를 따로 나누어 WHERE절에 조인 기준 열 중 한쪽에 (+) 기호를 붙여준다. (기호가 없는 쪽이 기준)
왼쪽 외부 조인(Left Outer Join) WHERE TABLE1.COL1 = EABLE2.COL1(+) 왼쪽 열 기준으로 출력
(왼쪽 행 개수에 맞춤)
오른쪽 외부 조인(Left Outer Join) WHERE TABLE1.COL1(+) = EABLE2.COL1 오른쪽 열 기준으로 출력(오른쪽 행 개수에 맞춤)

 

<예시>

- 왼쪽 외부조인

SELECT E.EMPNO, E.ENAME, E.SAL, E.MGR, M.ENAME, M.SAL
  FROM EMP E, EMP M
 WHERE E.MGR = M.EMPNO(+);

=>MGR이 NULL값이어도 오른쪽 외부 조인을 사용하여 KING의 정보도 출력

 

-오른쪽 외부조인

SELECT E.EMPNO, E.ENAME, E.SAL, E.MGR, M.ENAME, M.SAL
  FROM EMP E, EMP M
 WHERE E.MGR(+) = M.EMPNO;

 

 

 

 

 

 

*SQL-99 표준 문법 조인

SQL- 99 = ANSI SQL

: DBMS(Oracle, My-SQL, DB2 등등)들에서 각기 다른 SQL를 사용하므로, 미국 표준 협회(American National Standards Institute)에서 이를 표준화하여 표준 SQL문을 정립 시켜 놓은 것 (다른 DBMS 제품에서도 사용 가능)

 

- NATURAL JOIN

: 두 테이블의 같은 열 기준(등가 조인 방식)

  • DBMS가 알아서 같은 열을 찾아주므로 같은 열명을 굳이 입력하지 않아도 된다.
  • SELECT문에서 같은 열명을 테이블을 명시하지 않고 써도 된다.

- FROM절에 명시

FROM 테이블1 NATURAL JOIN 테이블2

 

<예시> 

 SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE
      , DEPTNO, D.DNAME, D.LOC
   FROM EMP E NATURAL JOIN DEPT D
  ORDER BY DEPTNO, E.EMPNO;

 

 

- JOIN ~ USING

: 조인에 사용할 기준열 명시

  • SELECT문에서 같은 열명을 테이블을 명시하지 않고 써도 된다.

- FROM절에 명시

FROM 테이블1 JOIN 테이블2 USING (기준열)

 

<예시> 

 SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE
      , DEPTNO, D.DNAME, D.LOC
   FROM EMP E JOIN DEPT D USING(DEPTNO)
  WHERE E.SAL >= 3000
  ORDER BY DEPTNO, E.EMPNO;

 

:

- JOIN ~ ON

: 조인 조건 직접 명시

  • 테이블 명을 꼭 명시해줘야 한다.

- FROM절에 명시

FROM 테이블1 JOIN 테이블2 ON (조건식 : 테이블1.기준열 = 테이블2.기준열)

 

<예시> 

 SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE
      , E.DEPTNO, D.DNAME, D.LOC
   FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
  WHERE E.SAL <= 3000
  ORDER BY E.DEPTNO, E.EMPNO;

 

 

 

 

 

 

 

- OUTER JOIN

=> 기준이 되는 테이블/열의 위치로 왼쪽/오른쪽을 구분

=> (+)는 기준이 아닌 것에 붙인다.

=> 테이블 1 '     ' OUTER JOIN 테이블2 ON (테이블1.열 = 테이블2.열)

'    '안에 왼쪽/오른쪽이 들어가는데 이때는 조건식의 왼쪽/오른쪽이 기준이 되는 열이 된다.

=> 기준이 된다는 것은 해당 테이블의 열에 다른 테이블을 맞추겠다는 의미

 

 

<예시>

- 왼쪽 외부 조인

 SELECT E.EMPNO, E.ENAME, E.MGR
      , M.EMPNO AS MGR_EMPNO
      , E.ENAME AS MGR_ENAME
   FROM EMP E LEFT OUTER JOIN EMP M ON(E.MGR = M.EMPNO)
  ORDER BY E.EMPNO;

 

-오른쪽 외부 조인

 SELECT E.EMPNO, E.ENAME, E.MGR
      , M.EMPNO AS MGR_EMPNO
      , E.ENAME AS MGR_ENAME
   FROM EMP E RIGHT OUTER JOIN EMP M ON(E.MGR = M.EMPNO)
  ORDER BY E.EMPNO;

 

- 전체 외부 조인

 SELECT E.EMPNO, E.ENAME, E.MGR
      , M.EMPNO AS MGR_EMPNO
      , E.ENAME AS MGR_ENAME
   FROM EMP E FULL OUTER JOIN EMP M ON(E.MGR = M.EMPNO)
  ORDER BY E.EMPNO;

 

 

 

 

 

 

- SQL-99 조인 방식에서 세 개 이상의 테이블 조인

- (기존) WHERE절에 명시

 FROM 테이블1, 테이블2, 테이블3
WHERE 테이블1.열 = 테이블2.열
  AND 테이블2.열 = 테이블3.열

 

- (SQL-99) FROM절에 명시

FROM 테이블1 JOIN 테이블2 ON (조건식 : 테이블1.열 = 테이블2.열)
             JOIN 테이블3 ON (조건식 : 테이블2.열 = 테이블3.열)