[Oracle/SQL] 4. JOIN | OUTER JOIN | SELF JOIN | 다중 JOIN

목차

1. JOIN
   1-1. 오라클 전용 구문
   1-2. ANSI 표준 구문
2. OUTER JOIN
   2-1. LEFT OUTER JOIN
   2-2. RIGHT OUTER JOIN
   2-3. FULL OUTER JOIN
3. CROSS JOIN
4. NON EQU JOIN
5. SELF JOIN
6. 다중 JOIN
7. JOIN 사용 예제

---

학습점검 Check

✅ JOIN 의 사용 목적에 대해 이해할 수 있다.
✅ 오라클 전용 구문과 ANSI JOIN 구문을 이용하여 두 개의 테이블에서 데이터를 조회할 수 있다.
✅ INNER JOIN과 OUTER JOIN 에 대해 이해할 수 있다.
✅ OUTER JOIN의 종류를 이해할 수 있다.
✅ INNER JOIN과 OUTER JOIN을 구분하여 필요한 데이터를 조회할 수 있다.
✅ CROSS JOIN에 대해 이해할 수 있다.
✅ EQUAL JOIN과 NON EQUAL JOIN에 대해 이해할 수 있다.
✅ SELF JOIN에 대해 이해할 수 있다.
✅ 다중 JOIN에 대해 이해할 수 있다.
✅ 필요한 JOIN 종류를 선택하여 JOIN의 우선순위를 고려해서 원하는 결과를 조회할 수 있다.

---

1. JOIN

예) EMPLOYEE 테이블의 DEPT_CODE 대신 DEPARTMENT 테이블의 DEPT_TITLE을 조회하고 싶을 때 등등

• 2개 이상의 테이블에서 데이터를 조회하기 위해 쓰인다.
• 수행 결과는 1개의 Result Set으로 나오게 된다.

• JOIN 문법에는 두 가지가 있다: 1. 오라클 전용 구문, 2. ANSI 표준 구문

---

 

1-1. 오라클 전용 구문 FROM, WHERE

• 오라클에서만 사용 가능한 구문이다.
• FROM절에 쉼표(,)로 구분해 테이블명 나열한다.
• WHERE절에는 합치기에 사용할 컬럼명을 명시한다.

-- 연결에 사용할 두 컬럼명이 다른 경우
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , DEPT_CODE
　　　 , DEPT_TITLE
  FROM EMPLOYEE
　　　 , DEPARTMENT
 WHERE DEPT_CODE = DEPT_ID;

• 이때 DEPT_CODE의 NULL값들은 제외된다.
• 위와 같이 기준이 될 값을 WHERE절에서 반드시 명시해야 한다. 작성하지 않으면 1 * 1 곱해져서 200개 이상의 의도하지 않은 답이 출력된다.

-- 연결에 사용할 두 컬럼명이 같은 경우(테이블명 또는 별칭 표기)
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , E.JOB_CODE 같은 컬럼명에 대해서는 SELECT절에서도 테이블명. 써서 명시해야 한다.
　　　 , JOB_NAME
  FROM EMPLOYEE E
　　　 , JOB J
 WHERE JOB_CODE = JOB_CODE; 컬럼명이 같을 때 어느 테이블의 컬럼명인지 불분명하다.
 WHERE E.JOB_CODE = J.JOB_CODE; 테이블명 또는 별칭을 써서 구분한다.

• SELECT절에서 사용된 같은 컬럼명에 대해서도 테이블명.컬럼명 으로 명시해야 한다.

❗ 테이블명에 별칭 사용
연결하는 테이블이 많아질수록 컬럼의 출처를 명시해 구분하게 된다. 이때 테이블명.컬럼명 처럼 표기해도 무방하지만, 각 테이블마다 별칭을 지어 사용하면 보다 간편하다: 별칭.컬럼명

 

---

 

1-2. ANSI 표준 구문 USING() 또는 ON()

• ANSI 단체에서 지정한 구문으로 다른 DBMS 프로그램에서도 통용된다.

 

-- 연결에 사용할 컬럼명이 같은 경우 USING(컬럼명), ON()
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , JOB_CODE JOIN절에서 이미 명시했기에 여기서는 어느 테이블인지 명시하지 않아도 괜찮다.
　　　 , JOB_NAME
  FROM EMPLOYEE
  JOIN JOB USING(JOB_CODE);

• 컬럼명이 같은 경우에도 별칭과 함께 명시해 ON() 사용할 수 있다: JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);

 

-- 연결에 사용할 컬럼명이 다른 경우 ON()
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , DEPT_CODE
　　　 , DEPT_TITLE
  FROM EMPLOYEE
  JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

• DEPT_CODE 컬럼에서 NULL값을 가진 2명은 위 예시에서도 제외되고 출력된다.

 

---

 

2. OUTER JOIN

❗ INNER JOIN과 OUTER JOIN 차이
-- INNER JOIN
JOIN은 기본적으로 EQUAL JOIN이다. EQU JOIN이라고도 한다.
'D1'이니까 같은 'D1'을 찾아 붙여주는 등 연결되는 컬럼값이 일치하는 행들만 조인한다.
일치하는 값이 없는 행은 자동적으로 JOIN에서 제외되는 것을 INNER JOIN이라고 한다.
즉 JOIN의 기본은 INNER JOIN이자 EQU JOIN이다.

 

• OUTER JOIN은 두 테이블의 지정 컬럼값이 일치하지 않는 행에 대해서도 JOIN에 포함시킨다.
• 반드시 OUTER JOIN임을 명시해야 한다. JOIN의 기본은 INNER JOIN이기 때문이다.

 

• 이러한 OUTER JOIN의 종류에는 세 가지가 있다: 1. LEFT OUTER JOIN, 2. RIGHT OUTER JOIN, 3. FULL OUTER JOIN

 

2-1. LEFT OUTER JOIN ANSI, 오라클

• 합치기에 사용한 두 테이블 중 왼편에 기술된 테이블의 행의 수를 기준으로 JOIN한다.
• OUTER 키워드는 생략 가능하다.

-- ANSI 표준 구문
SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE 테이블 중 먼저 작성되었으므로 왼편에 해당
  LEFT OUTER JOIN DEPARTMENT ON(DEPT_COD = DEPT_ID); OUTER 키워드 생략 가능

-- 오라클 전용 구문
SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE
　　　 , DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+);

• LEFT 조인일 때, 오라클 전용 구문에서는 WHERE절 조건 중 오른쪽에 오는 테이블 컬럼명에게 (+) 작성한다. 왼편 기준에 맞춰 오른쪽 컬럼명을 늘린다는 뜻 정도로 해석된다.

 

---

 

2-2. RIGHT OUTER JOIN ANSI, 오라클

• 합치기에 사용한 두 테이블 중 오른편에 기술된 테이블의 행의 수를 기준으로 JOIN한다.
• OUTER 키워드는 생략 가능하다.

-- ANSI 표준 구문
SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE
 RIGHT OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

• DEPT_ID 기준이기에 소속 사원이 없는 마케팅부, 국내영업부, 해외영업3부도 출력된다.
• 출력 시 DEPT_CODE의 NULL값들은 제외된다.

-- 오라클 전용 구문
SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE
　　　 , DEPARTMENT
 WHERE DEPT_CODE(+) = DEPT_ID;

• (+) 키워드가 왼쪽 컬럼명 뒤에 자리한다. 오른편이 기준이 되었으니 왼쪽 컬럼명을 그에 맞춰 늘린다.

 

---

 

2-3. FULL OUTER JOIN ANSI

• 합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함하여 JOIN한다.
• OUTER 키워드는 생략 가능하다.
• 오라클 전용 구문으로는 FULL OUTER JOIN을 하지 못한다. 오라클에서 OUTER-JOIN 테이블은 1개만 지정할 수 있다.

-- ANSI 표준 구문
SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE
  FULL OUTER JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);

 

 

---

 

3. CROSS JOIN

• 카테이션곱(Cartensian projuct)이라고도 불린다.
• 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 조인 방법이다.
• 검색된 데이터 수는 행의 컬럼 수 * 행의 컬럼 수로 나오게 된다.
• 많이 쓰이는 방식은 아니다.

SELECT
　　　　EMP_NAME
　　　 , DEPT_TITLE
  FROM EMPLOYEE
 CROSS JOIN DEPARTMENT;

• EMPLOYEE 행 * DEPARTMENT 행에 대해 검색한다.

 

---

 

4. NON EQU JOIN

• NON EQUAL JOIN(NON EQU JOIN)은 지정한 컬럼값이 일치하는 경우가 아니라, 값의 범위에 포함되는 행들을 연결하는 조인 방법이다.
• 앞서 다룬 조인들은 D1 = D1일 때 또는 J1 = J1일 때를 조회해 왔다면 NON EQU JOIN은 값의 범위를 토대로 연결 짓는다.

-- ANSI 표준 구문
SELECT
　　　　EMP_NAME
　　　 , SALARY
　　　 , E.SAL_LEVEL "EMPLOYEE의 SAL_LEVEL"
　　　 , S.SAL_LEVEL "SAL_GRADE의 SAL_LEVEL"
  FROM EMPLOYEE E
  JOIN SAL_GRADE S ON(SALARY BETWEEN MIN_SAL AND MAX_SAL);

-- 오라클 전용 구문
SELECT
　　　　EMP_NAME
　　　 , SALARY
　　　 , E.SAL_LEVEL "EMPLOYEE의 SAL_LEVEL"
　　　 , S.SAL_LEVEL "SAL_GRADE의 SAL_LEVEL"
  FROM EMPLOYEE E
　　　 , SAL_GRADE S
 WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;

 

---

 

5. SELF JOIN

• 같은 테이블을 조인하는 경우를 말한다. 즉 자기자신과 조인을 맺는 것이다.
• 구분이 필요하기에 별칭 사용된다.

-- ANSI 표준 구문
SELECT
　　　　E1.EMP_ID
　　　 , E1.EMP_NAME 사원이름
　　　 , E1.DEPT_CODE
　　　 , E1.MANAGER_ID
　　　 , E2.EMP_NAME 관리자이름
  FROM EMPLOYEE E1
  JOIN EMPLOYEE E2 ON(E1.MANAGER_ID = E2.EMP_ID);

-- 오라클 전용 구문
SELECT
　　　　E1.EMP_ID
　　　 , E1.EMP_NAME 사원이름
　　　 , E1.DEPT_CODE
　　　 , E1.MANAGER_ID
　　　 , E2.EMP_NAME 관리자이름
  FROM EMPLOYEE E1
　　　 , EMPLOYEE E2
 WHERE E1.MANAGER_ID = E2.EMP_ID;

 

---

 

6. 다중 JOIN

• n개의 테이블을 조회할 때 사용되는 조인 방법이다.

 

• ANSI 표준 구문에서는 특히 조인 순서 고려하여 작성하는 것이 중요하다.

-- ANSI 표준 구문 순서 중요!
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , DEPT_CODE
　　　 , DEPT_TITLE
　　　 , LOCAL_NAME
  FROM EMPLOYEE
  JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID) JOIN 순서 바뀌면 오류
  JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);

• 만약 DEPARTMENT보다 LOCATION을 먼저 JOIN한다면, 순서대로 동작하던 중 EMPLOYEE 테이블에는 LOCATION_ID나 LOCAL_CODE라는 컬럼명 없으므로 순서 오류가 발생한다.

-- 오라클 전용 구문 순서 상관 X
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , DEPT_CODE
　　　 , DEPT_TITLE
　　　 , LOCAL_NAME
  FROM EMPLOYEE
　　　 , LOCATION
　　　 , DEPARTMENT
 WHERE DEPT_CODE = DEPT_ID
    AND LOCATION_ID = LOCAL_CODE;

• JOIN하고자 하는 테이블마다 WHERE 조건 서술해야 한다.
• FROM절, WHERE절 나열 순서 상관 없다. 오라클에서는 한꺼번에 동작하기 때문이다.

 

---

 

7. JOIN 사용 예제

 

-- 부서 테이블과 지역 테이블을 조인하여 테이블에 있는 모든 데이터를 조회 ✅
-- ANSI 표준
SELECT
　　　　*
  FROM DEPARTMENT
  JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);

-- 오라클 전용
SELECT
　　　　*
  FROM DEPARTMENT
　　　 , LOCATION
 WHERE LOCATION_ID = LOCAL_CODE;

 

-- 직급이 대리이면서 아시아 지역에 근무하는 직원 조회 ✅✅
-- 사번, 이름, 직급명, 부서명, 근무지역명, 급여 조회
-- (조회 시에는 모든 컬럼에 테이블 별칭을 사용하는 것이 좋다)

-- ANSI 표준
SELECT
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , J.JOB_NAME 직급명
　　　 , D.DEPT_TITLE 부서명
　　　 , L.LOCAL_NAME 근무지역명
　　　 , E.SALARY 급여
  FROM EMPLOYEE E
  JOIN JOB ON(E.JOB_CODE = J.JOB_CODE)
  JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
  JOIN LOCATION L ON(D.LOCATION_ID = L.LOCAL_CODE)
 WHERE J.JOB_NAME = '대리'
    AND L.LOCAL_NAME LIKE 'ASIA%';

-- 오라클 전용
SELECT
　　　　E.EMP_ID 사번
　　　 , E.EMP_NAME 이름
　　　 , J.JOB_NAME 직급명
　　　 , D.DEPT_TITLE 부서명
　　　 , L.LOCAL_NAME 근무지역명
　　　 , E.SALARY 급여
  FROM EMPLOYEE E
　　　 , JOB J
　　　 , DEPARTMENT D
　　　 , LOCATION L
 WHERE E.JOB_CODE = J.JOB_CODE
    AND E.DEPT_CODE = D.DEPT_ID
    AND D.LOCATION_ID = L.LOCAL_CODE
    AND J.JOB_NAME = '대리'
    AND L.LOCAL_NAME LIKE 'ASIA%';