23일차(2022.01.24) _ Join 기본

Join

 

- 하나의 쿼리로 여러 개의 테이블의 컬럼들을 한번에 조회
- 여러 개의 테이블을 하나로 합쳐서 가상의 테이블로 사용
- Oracle 전용, ANSI 표준 문법 두 가지가 있음

1. ANSI Join 문법
SELECT
FROM 테이블명 A(별칭)
         {INNER, LEFT, RIGHT, FULL, OUTER, NATURAL} JOIN
         테이블명 B(별칭)
         ON (A . 컬럼명 = B . 컬럼명) , USING (컬럼명)  -- 조인 조건
         ... 
WHERE
GROUP BY
...

- SELECT의 컬럼명을 나열할 때, 같은 컬럼명을 나열할 때는 테이블명을 명시해야 함


2. 조인 연산자(조건)에 따른 구분
  1) Equi Join(=) : 조인하는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치하는 행을 연결
   - On 조건절에 = 연산자를 이용하면 Equi Join
  2) Non Equi Join(>, <) : 공통적 컬럼없이 범위( >,< )를 사용해서 조인할 때
   - On 조건절에 >, < 등의 연산자를 이용하면 Non Equi Join
  3) Cartesian Join (Cross Join) : 조인 조건이 없을 때 또는 잘못 적용 했을 때
   - On 조건이 생략된 경우

3. 조인 데이터가 있는 테이블에 따른 구분 : Inner Join, Outer Join(Left, Right, Full), Semi Join
 1) Inner Join : 테이블 간의 공통 컬럼에서 값이 같은 것을 연결하는 조인 방법 (교집합)
  - 조인 조건을 만족하는 컬럼 기준 조회
  - NULL은 조회 결과에서 제외 (그래서 NULL인 데이터는 조인이 안됨)
  - 조인하려는 테이블 양쪽 모두에 데이터가 무조건 있어야 함

 2) Natural Join
  - Inner Join과 비슷
  - 조인 조건의 컬럼명 같을 경우 사용하는 Join
  - 조인 조건을 명시하지 않음, 알아서 공통의 컬럼을 찾아 비교함
  - 공통 컬럼명이 조인 조건이 되었을 경우 SELECT에 사용할 때는 테이블명을 명시하지 않음

3) Self Join
 - 같은 테이블을 Join
 - 별칭을 이용해서 다른 테이블로 인식
 - Self 라는 키워드는 없음, 일반적인 Join 키워드 사용
 - 단지 이런 방식을 Self Join이라고 일반적으로 지칭하는 것 뿐

4) Outer Join
 - 조인 조건을 만족하지 않는 경우에 조회가 되지 않음
 - 조건을 만족하지 않더라도 조회하고자 하는 경우 사용
 - 데이터가 있는 방향을 지정
 - 테이블이 선언된 순서
 - 왼쪽에 데이터가 있는 경우 : LEFT
 - 오른쪽에 데이터가 있는 경우 : RIGHT
 - 두 방향 전부를 나타내고 싶을 경우 : FULL 
 - 단, FULL Join 은 ANSI 표준 문법에만 존재, Oracle 전용 문법에는 없음

4. Join 조건
 1) ON
  - 조인 조건 명시
  - ( ) 생략 가능
  - 컬럼명이 다를 수도 있음

 2) USING
  - 조인 조건의 컬럼명이 같을 경우 ON 대신 사용
  - USING ( 조인 조건으로 사용할 공통 컬럼명)
  - 컬럼명 앞에 테이블명은 명시하면 안됨 : USING ( D.DEPARTMENT_ID ) X

 

설명1. 조인과 서브쿼리의 차이점, EquI 조인, Inner Join

 

만약에 사원번호가 200인 사원의 부서명을 조회한다고 해보자

부서명이라는 컬럼은 EMPLOYEES 테이블에 없는 컬럼이기 때문에 서브쿼리를 사용했을 때에는

조건문을 사용해서 DEPARTMENTS 테이블의 DEPARTMENT_ID의 값을 조건문에 사용해서 그 값과 비교해서

DEPARTMENT_NAME을 가져왔었다.  

순서가 결국 EMPLOYEES테이블의 DEPARTMENT_ID와 DEPARTMENTS 테이블의 DEPARTMENT_ID를 비교하고,

DEPARTMENTS 테이블의 DEPARTMENT_ID에 맞는 DEPARTMENT_NAME을 가져오게 된다.

즉, 서브쿼리는 결과값이 최종적으로 하나의 테이블있어서 하나의 테이블에서만 결과를 조회할때는 사용가능하다.

 

근데 만약에 사원번호가 200인 사원의 이름, 월급, 부서명 조회한다고 해보자

조회하고자하는 컬럼이 이름, 월급은 EMPLOYEES 테이블에 있고, 부서명 테이블은 DEPARTMENTS테이블에 있다.

이게 부서명 컬럼만 조회를 한다면 조건문을 서브쿼리로 연결시키면 되는데,

이처럼 SELECT의 결과물들이 서로 다른테이블에 있어서

값을 하나의 ResultSet으로 합쳐서 조회를 해야한다면 Join을 사용해야 한다.

 

한번 사용법을 알아보자

 

SELECT FIRST_NAME, SALARY, DEPARTMENT_NAME

 

우선, 조회할 컬럼들은 이름, 급여, 부서명이다.

 

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME -- 각 컬럼마다 어느 테이블 소속인지를 명시주는걸 권장
FROM EMPLOYEES E
    INNER JOIN
    DEPARTMENTS D -- 둘 다 데이터가 있는 경우에는 INNER 조인으로
    ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID)

그 다음에 처음으로 조인할 테이블인 EMPLOYEES 테이블을 FROM절에 적고 별칭으로 앞 글자 E를 별칭으로 준다.

여기서는 AS를 사용하면 오류가 날 가능성이 있어서 잘 안쓴다고 한다.

 

그리고 비교해야할 데이터를 잘 생각해봐야 하는데,

서브쿼리에서 EMPLOYEES테이블과 DEPARTMENTS 테이블을 연결시킬 때 처럼

사원번호가 200번인 사원의 DEPARTMENT_NAME을 알기 위해서는 해당 사원의 DEPARTMENTS 테이블 컬럼중에

어떤 컬럼과 비교를 해야하는가? 이 상황에서는 DEPARTMENT_ID와 1:1로 비교하는 것이 확실하다.

그리고 지금 비교해야 할 데이터들은 DEPARTMENT_ID이다. 

그래서 둘 다 데이터가 있는 경우이기 때문에 INNER 조인으로 FROM절에 선언하고

DEPARMENTS 테이블도 FROM절에 선언을 한 다음에 

조인 조건에 EMPLOYEES테이블의 DEPARTMENT_ID와 DEPARTMENTS테이블의 DEPARTMENTS_ID가

같은 컬럼들만 조인을 하겠다고 선언을 해준다. 같은 값들을 찾는 것이기 때문에 Equi 조인이다.

 

여기까지만 보면 이제 EMPLOYEES 테이블과 DEPARTMENTS테이블이 DEPARTMENTS_ID로 연결되어서

새로운 테이블로 합쳐진 상태와 같은 것이다.

그 아래에 이제 WHERE절을 추가해서

 

SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME -- 각 컬럼마다 어느 테이블 소속인지를 명시주는걸 권장
FROM EMPLOYEES E
    INNER JOIN
    DEPARTMENTS D -- 둘 다 데이터가 있는 경우에는 INNER 조인으로
    ON(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.EMPLOYEE_ID = 200;

 

최종적으로 그 합쳐진 테이블에서 E.EMPLOY_ID가 200인 데이터만을 출력해달라고 해주면 된다.

 

만약 사원번호가 192인 사원의 이름, 성, 부서명, 도시명을 구해야 해서 조인할 테이블이 3개라고 한다면 

 

SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E
    INNER JOIN
    DEPARTMENTS D
    ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
    INNER JOIN
    LOCATIONS L
    ON (D.LOCATION_ID = L.LOCATION_ID)
WHERE E.EMPLOYEE_ID = 192;

 

위와 같이 쿼리를 이어주면 된다.

---

설명2. Natural Join

 

Natural Join의 경우 우리가 지금까지 예로들었던 Inner Join과 상당히 유사하다.

아래의 쿼리를 보자

 

SELECT C.COUNTRY_NAME, R.REGION_NAME, R.REGION_ID
FROM COUNTRIES C
     INNER JOIN
     REGIONS R
     ON (C.REGION_ID = R.REGION_ID)

 

위에서 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 DEPARTMENTS_ID 공통컬럼을 이용해서 

Join 조건으로 비교하여 Inner Join을 했었는데, 이것도 똑같다.

근데 위의 경우도 DEPARTMENT_ID 라는 컬럼명이 똑같고, 지금의 경우에도 REGION_ID 라는 컬럼이 이름이 똑같다.

이런 경우에는 NATURAL JOIN을 사용해도 된다.

 

SELECT COUNTRY_NAME, REGION_NAME, REGION_ID
FROM COUNTRIES C
     NATURAL JOIN
     REGIONS R;

 

Natural Join은 Join 조건문을 사용하지 않아도 컬럼명이 같다면 공통컬럼임을 알아서 찾아서 비교해주어서

조인해준다. 그리고 SELECT절에 어느 테이블의 컬럼인지 명시하지 않아야 한다.

위의 Inner Join의 경우에는 특히나, 조건문의 공통컬럼으로 활용하고 있는 REGION_ID의 경우에는

양쪽 테이블 모두에 들어가있기 때문에 어느 테이블 소속인지를 꼭 앞에 테이블명. 을 통해 명시를 해야했지만,

Natural Join의 경우에는 오히려 명시를 하면 오류가 난다.

 

그리고, Natural Join의 경우에는 공통컬럼이 하나일때만 사용하는 것을 권장한다.

왜 여기서 예제를 갑자기 REGIONS, COUNTRIES 테이블로 잡았냐면

EMPLOYEES 테이블과 DEPARTMENTS 테이블은 DEPARTMENT_ID 외에도,

MANAGER_ID라는 공통컬럼이 또 있기 때문이다.

이경우에는 Natural Join을 사용할 경우에 어떤 컬럼으로 비교할지 애매해지기 때문에 사용했을 때 

원하는 결과를 출력하지 않을 수가 있다.

 

---

설명3. Self Join

 

사원번호가 192번인 사원의 이름, 급여, 매니저 이름, 매니저 급여의 정보를 출력한다고 해보자

우선 사원번호가 192번인 사원의 이름과 급여를 조회하는 쿼리는

 

SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 192;

 

위처럼 작성하면 될 것이고, 매니저의 이름과 급여를 조회하는 쿼리는

 

SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 
    (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 192);

 

위와같이 작성하면 될 것이다.

서브쿼리는 WHERE절 뿐만아니라, SELECT절에도 작성할 수 있었는데,

이 두 쿼리를 서브쿼리를 이용하여 하나의 쿼리로 작성해보자면 이렇게 된다.

 

SELECT FIRST_NAME, SALARY, 
    (SELECT FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = 
        (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 192)) AS MANAGER_NAME
    ,(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 
        (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 192)) AS MANAGER_SALARY
FROM EMPLOYEES WHERE EMPLOYEE_ID = 192;

 

근데 이렇게 해버리면 쿼리문이 너무 복잡해버린다.

이렇게 같은 테이블에서 조인을 해야할 때는 Self Join을 사용하면 더 간단하게 쿼리를 작성할 수 있다.

 

SELECT E1.FIRST_NAME, E1.SALARY, E2.FIRST_NAME, E2.SALARY
FROM EMPLOYEES E1
     INNER JOIN
     EMPLOYEES E2
     ON (E1.MANAGER_ID = E2.EMPLOYEE_ID)
WHERE E1.EMPLOYEE_ID = 192;

 

Self Join 은 여타 다른 Join처럼 작성하면 된다.

단지 그냥 같은 테이블 내에서 Join을 한다고 해서 Self Join이라고 부르는 것 뿐이다.

같은 테이블을 별칭을 통해서 다른 테이블처럼 나누어버린다. E1과 E2는 사실 같은 EMPLOYEES 테이블이지만

별칭으로 서로 다른테이블로 만들었고 조건문으로 E1의 MANAGER_ID 와, E2의 EMPLOYEE_ID를 비교해서

Join을 하는 것이다. 그러면, 해당 직원의 매니져 이름이랑 급여가 Join이 되어서 출력이 되게 되는데

이 말은 즉, Inner Join이기 때문에 교집합으로 매칭이 된다는 소리이다.

만약 10번 사원의 매니저 번호가 20번이면 20번의 이름과 급여가 10번 사원의 정보로 Join이 된다는 소리이다.

그걸 다시 WHERE절을 통해서 192번 사원의 데이터만을 출력하게 한 것이다.

 

---

설명4. Outer Join

 

SELECT E1.FIRST_NAME, E1.SALARY, E2.FIRST_NAME, E2.SALARY
FROM EMPLOYEES E1
     LEFT OUTER JOIN
     EMPLOYEES E2
     ON (E1.MANAGER_ID = E2.EMPLOYEE_ID)
WHERE E1.EMPLOYEE_ID = 100;

 

위의 설명3에서 사용했던 쿼리에서 근데 WHERE절의 조건에서 사원번호를 100으로 바꾸면 값이 출력이 되지 않는다. 

그 이유를 보자면,

 

 

사원번호가 100번인 직원은 매니저가 없기 때문이다. 매니저번호가 NULL 값이라서 위의 쿼리가 출력이 되지 않는다.

쿼리를 잘 따라가 보자면, EMPLOYEE_ID가 100번인 사원의 매니저번호와 또 다른 테이블 E2에서의 

사원번호를 비교해야하는데, 애초에 매니저번호가 NULL이니까 조건자체가 False라서 출력이 안되는 것이다.

이럴 때에는 Outer Join을 사용하면 조건에 부합하지 않아도 결과를 출력할 수 있다.

단, 어느쪽에서 Join을 하는지를 판단을 해야한다.

지금은 E1 테이블에서 조인 조건에 부합한다면 E2 테이블을 조인하는 것이기 때문에 

E1 테이블이 먼저 나온 상태이고 E2가 조인을 하는 상태이다. 근데, E1에 NULL이기 때문에 Join이 안되는 상황이다.

근데 E2는 애초에 조인이 안되어서 데이터가 없고, E1은 이미 먼저 나온상태이기 때문에 왼쪽에 먼저 있는 상태이다

그래서 LEFT JOIN을 명시해주면 된다.

 

SELECT E1.FIRST_NAME, E1.SALARY, E2.FIRST_NAME, E2.SALARY
FROM EMPLOYEES E1
     LEFT OUTER JOIN
     EMPLOYEES E2
     ON (E1.MANAGER_ID = E2.EMPLOYEE_ID)
WHERE E1.EMPLOYEE_ID = 100;

 

이렇게 쿼리를 작성해주면

 

 

이렇게 E1에 있는 데이터는 출력이 되고, E2는 Join이 안되었기 때문에 출력이 안되는 모습이다.

 

아래의 경우는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 각각 LEFT, RIGHT, FULL OUTER 조인했을 때 경우이다.

 

	-- 부서가 없는 사원정보 까지 출력     
SELECT *
FROM EMPLOYEES E
     LEFT JOIN
     DEPARTMENTS D
     ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
	-- 있는 부서지만 사원이 없어서 조회가 안됨

	-- 사원이 없는 부서까지 출력
SELECT *
FROM EMPLOYEES E
     RIGHT JOIN
     DEPARTMENTS D
     ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

	-- 사원이 없는 부서와 부서가 없는 사원정보 모두 출력     
SELECT *
FROM EMPLOYEES E
     FULL JOIN
     DEPARTMENTS D
     ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);