[Oracle] 조인(join) + SQL-99 표준 문법

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

조인(join)

  2개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식을 조인이라고 한다. 집합 연산자와 비슷하지만 차이점이 있다. (집합 연산자를 사용한 결과가 두 개 이상의  SELECT 문의 결과를 세로로 연결한 것이라면, 조인을 사용한 결과는 두 개 이상의 테이블 데이터를 가로로 연결한 것이다.)

 

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO	--맞는 데이터들을 추려내기 위한 조건(WHERE절)
ORDER BY EMPNO;

  조인할 때는  FROM 절에 테이블 이름들을 명시하면 된다. (테이블이 아니어도 테이블 형태인 열과 행으로 구성된 데이터 집합이면 모두  FROM 절에 지정 가능하다.) 조인은 결과로 나올 수 있는 모든 행을 조합하기 때문에  WHERE 절에 출력 행을 선정하는 조건식을 넣어서 목적에 맞게 데이터들을 추려내는 것이 좋다.  WHERE 절 없이 조인하는 것을 크로스 조인(cross join)또는 교차 조인이라고 하고 각 집합을 이루는 모든 원소의 순서쌍을 데카르트 곱(카테시안 곱 : Cartesian product)라고 한다.

 

조인 테이블 개수와 조건식 개수의 관계  (기본적으로) 데카르트 곱 현상이 일어나지 않게 하는 데 필요한 조건식의 최소 개수 = 조인 테이블 개수 - 1

 

위의 SQL문은  EMP (사원) 테이블과  DEPT (부서) 테이블을 조인하는 예시이다.

 

 

 

WHERE절이 없을 때 실행결과

 WHERE 절이 없을 경우 결과로 나올 수 있는 모든 행을 조합하기 때문에(데카르트 곱) 사원 데이터와 부서 데이터가 정확히 맞아떨어지지 않는 데이터도 함께 출력된다. (같은 색으로 표시된 부분이 원래 매칭 되었어야 할 데이터들이다.)

 

 

 

WHERE절을 추가했을 때 실행결과

 WHERE 절을 추가했을 때의 실행결과이다. 두 테이블이 목적에 맞게 조인된 것을 확인할 수 있다.

 

 

 

  ■ 테이블의 별칭 설정

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

 

 SELECT 절에 사용하는 것처럼  FROM 절에 지정한 테이블에도 별칭을 지정할 수 있다.

명시한 테이블 이름에서 한 칸 띄운 후에 지정하면 된다.

 

 

 

 

조인의 종류

 

조인(join)의 종류

 

조인(join)의 벤 다이어그램

 

  1. 등가 조인(equi join) / 내부 조인(inner join) / 단순 조인(simple join)

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
    FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO	--WHERE절에 조인조건을 명시한다.
    AND SAL >= 3000;	--조건식을 추가하여 출력 범위를 설정할 수도 있다.

  일반적으로 가장 많이 사용되는 조인 방식으로, 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식이다. 특별한 이름이 없다면 조인을 사용한다는 건 대부분 등가 조인을 사용한다는 의미이다.

 

※ 등가 조인할 때 조인 조건이 되는 각 테이블의 열 이름들이 같을 경우 어느 테이블에 속해있는 열인지 반드시 명시해야 한다.

 

 

 

  2. 비등가 조인(non-equi join)

  등가 조인 방식 외의 방식들을 비등가 조인이라고 한다. (조인 조건이 특정 열의 일치 여부를 검사하는 방식 외에 다른 방식들을 의미한다.)

 

 

사원의 월급이 있는  EMP 테이블과 최대, 최소 급여가 있는  SALGRADE 테이블을 조인하는 경우에는  SAL 이  LOSAL 과  HISAL 사이의 범위에 있어야 하기 때문에 등가 조인을 사용할 수 없다. 

 

 

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

 WHERE 절에  BETWEEN A AND B 연산자를 이용해서  EMP 와  SALGRADE 테이블을 조인할 수 있다. 조건을 명시하지 않으면 등가 조인과 마찬가지로 데카르트 곱(Cartesian product)이 발생한다. ( EMP 테이블 14행 *  SALGRADE 테이블 5행 = 70행)

 

 

실행결과

 

 

 

  3. 자체 조인(self join)

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2 	--1개의 테이블을 다른테이블처럼 사용
WHERE E1.MGR = E2.EMPNO;    

  하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식이다.  FROM 절에 같은 테이블을 여러 번 명시하되 테이블의 별칭만 다르게 지정하는 방식으로 사용한다. ( SELECT 문에서도 별칭을 다르게 주어 다른 테이블인 것처럼 사용한다.)

 

실행결과

실행결과에서  MGR (관리자) 열이  NULL 인  KING 은 제외된다.

자체 조인도 2개 테이블에서 지정한 열 중 일치한 데이터를 기준으로 조인되었으므로 큰 범위에서는 등가 조인이라고 볼 수 있다.

 

 

 

  4. 외부 조인(outer join)

  두 테이블 간의 조인에서 조인 기준 열의 어느 한쪽이  NULL 이어도 강제로 출력하는 방식을 외부 조인이라고 한다.(등가 조인(내부 조인, 단순 조인)에서는  NULL 값인 경우 출력에서 제외됨) 외부 조인은 조인 기준 열의  NULL 을 처리하는 것을 목적으로 자주 사용하는 조인 방식이다.

 

외부조인 차이점

(기호가 붙는 쪽과 조인 이름이 반대니 주의 !)외부 조인은 좌우를 따로 나누어 지정한다.  WHERE 절에 조인 기준 열 중 한쪽에  (+) 기호를 붙여 준다. 그러나  (+) 를 붙이는 외부 조인 방식으로는 양쪽 모든 열이 외부 조인되는 전체 외부 조인(full outer join)은 불가능하다. (왼쪽 외부 조인을 사용한  SELECT 문과 오른쪽 외부 조인을 사용한  SELECT 문을  UNION 집합 연산자로 합쳐서 같은 효과를 낼 수는 있다...)

 

비등가 조인을 하면서 외부 조인을 할 경우 : ex) TABLE1.COL BETWEEN TABLE2.COL1(+) AND TABLE2.COL2(+)

 

 

외부조인의 방향별 차이점

기준이 되는 테이블에 따라 결과값이 달라지는 것을 확인할 수 있다.

 

 

 

 

 

---

SQL-99 표준 문법의 조인(join)

 SQL문은 ISO/ANSI에서 관계형 데이터베이스 표준언어로 지정(SQL-82)된후 SQL-99 표준 문법이 나왔다. Oracle은 9i 버전부터 SQL-99 방식의 문법을 지원하므로 함께 알아두면 좋다. Oracle문법의 조인과 SQL-99 문법의 조인 방식은 서로 같지만 문법이 약간 다른데, 대표적으로 이전의 조인방식은 조인 조건을  WHERE 절에 명시하는 반면, SQL-99 방식의 조인은  FROM 절에 조인 조건을 명시한다는 차이점이 있다.

 SQL-99 문법의 조인은 이전의 조인 방식보다 더 간략하고, 명시적으로 어떤 방식의 조인을 사용하고 있는지 알 수 있다. 또한 조인 조건식과 출력 행을 선정하는 조건식을 구별할 수 있으므로 여러 테이블을 조인해야 하는 복잡한  SELECT 문에서 유용하게 사용할 수 있다.

 

 

SQL-99문법의 조인(join)

  1. NATURAL JOIN

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

  등가 조인(내부 조인, 단순 조인)과 같은 조인 방식이다. 두 테이블에서 이름과 자료형이 같은 열을 찾고, 그 열을 기준으로 등가 조인을 한다. 위의 SQL문은  EMP (사원) 테이블과  DEPT (부서) 테이블을 조인하는 예시이다. 조인할 때 기준 열인  DEPTNO (부서 번호)를  SELECT 절에 명시할 때 테이블 이름을 붙이면 안 되므로 주의한다. 이전 방식의 조인에서 등가 조인(내부 조인, 단순 조인)은 조인 조건이  WHERE 절에 있는 반면에 SQL-99 방식은  FROM 절에 조인 키워드를 사용하는 부분이 다르다.

 

 

 

  2. JOIN ~ USING

FROM TABLE1 JOIN TABLE2 USING (조인에 사용한 기준열)

   JOIN ~ USING 도 마찬가지로 등가 조인(내부 조인, 단순 조인)과 같은 조인 방식이다.  NATURAL JOIN 과 다르게  USING 키워드에 조인 기준으로 사용할 열을 명시해서 사용해야 한다.  NATURAL JOIN 과 마찬가지로 조인 기준 열로 명시된 열은  SELECT 절에서 테이블 이름을 붙이지 않아야 하고,  WHERE 절에 조건식을 추가하여 조인된 결과행을 추가로 제한할 수 있다.

 

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
        DEPTNO, D.DNAME, D.LOC	--조인의 기준이 되는 열은 테이블이름을 붙이지 않는다.
    FROM EMP E JOIN DEPT D USING (DEPTNO)	--USING 옆에 조인기준이 되는 열을 명시해야한다.
WHERE SAL >= 3000   --WHERE절에 조건식을 추가로 명시하여 조인결과를 제한할 수 있다.
ORDER BY DEPTNO, E.EMPNO;

 JOIN ~ USING 을 사용하는 예시이다.

 

 

 

 3. JOIN ~ ON

FROM TABLE1 JOIN TABLE2 ON (조인 조건식)

  이전의 조인 방식에서는  WHERE 절에 조인 조건식을 작성했지만  JOIN ~ ON 방식에서는 조인 기준 조건식을  ON 키워드 옆에 작성한다. 필요할 경우 출력행을 걸러내기 위해  WHERE 절에 조건식을 추가로 사용한다. 다른 조인 방식들도 이 방식으로 변환할 수 있기 때문에 가장 범용성이 있다. 아래 SQL문은 위에 나왔던 이전의 조인 방식들을  JOIN ~ ON 방식으로 변환한 것들이다.

 

    1) 등가 조인(내부 조인, 단순 조인) →  JOIN ~ ON 변환

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

 

    2) 비등가 조인(non-equi join) →  JOIN ~ ON 변환

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO,
       S.GRADE, S.LOSAL, S.HISAL   	-- SELECT *해도 같은결과가 출력된다.
FROM EMP E JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

 

    3) 자체 조인(self join) →  JOIN ~ ON 변환

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
FROM EMP E1 JOIN EMP E2 ON (E1.MGR = E2.EMPNO);

 

 

  4. OUTER JOIN

외부조인방식의 차이점

  이전 방식의 외부 조인과 같은 방식이다. 다른 SQL-99 방식의 조인과 마찬가지로  WHERE 절이 아닌  FROM 절에 외부 조인을 선언한다. 전체 외부 조인은 왼쪽·오른쪽 외부 조인을 모두 적용(왼쪽 열이  NULL 인 경우와 오른쪽 열이  NULL 인 경우를 모두 출력)하는 조인 방식이다. 이전 방식으로는 각각의 왼쪽 외부 조인 결과와 오른쪽 외부 조인 결과를  UNION 집합 연산자로 합쳐 같은 효과를 내는 것만 가능했는데, SQL-99 문법에서는  FULL OUTER JOIN ~ ON 키워드가 따로 있어서 전체 외부 조인을 한 번에 할 수 있다.

 

 

외부조인(OUTER JOIN)의 방향별 차이점

SQL-99 문법의 조인 방식도 마찬가지로 기준이 되는 테이블에 따라 결과값이 달라지는 것을 확인할 수 있다.

전체 외부 조인이 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 형태인 것도 확인할 수 있다.

 

 

 

  ■ 3개 이상의 테이블을 조인할 때

FROM TABLE1 JOIN TABLE2 ON (조건식)
JOIN TABLE3 ON (조건식)

  이전 방식의 조인에서 여러 개의 테이블을 조인할 때는  WHERE 절에 조건식을 명시하기 때문에  AND 로 묶어주면 되었지만 SQL-99 문법에서는  FROM 절에 조인 조건을 명시하기 때문에 이전 방식의 조인과는 조금 다르다. 여러 가지 조인 키워드 방식이 있지만 위의 SQL문처럼  FROM 절에 2개 테이블을 키워드로 조인한 바로 옆에 SQL-99 방식의 조인 내용을 추가로 작성하면 여러 개의 테이블도 한꺼번에 조인할 수 있다.