DB/MySQL

JOIN (Self-Join / 비표준 join)

태로미 2023. 2. 2. 21:40

 

 

 

📍   JOIN

–  서로 다른 테이블의 정보를 조회할 수 있는 문법.
–   JOIN으로 여러 테이블을 묶어 나온 테이블 또한 하나의 테이블(가상의 테이블)처럼 사용할 수 있음.

 

•   table alias 사용 가능

–  구문이 실행되는 동안에만 적용되는 테이블에 대한 임시 별명값
–  FROM 테이블명 alias

 

•   테이블명 생략 가능

–  join하는 두 테이블 간 컬럼명이 고유한(유일한) 이름인 경우 table명이나 alias를 생략해도 됨.
–  두 테이블 간 컬럼명이 중복되는 경우는 꼭 테이블명, alias 값을 달아줘야 함.

 

 

 

1.   ON절을 사용한 JOIN

2.   ON절을 사용한 Self-Join

3.   비표준 JOIN

 

 

 

 

 

 

 

 

 

 

▶   ON절을 사용한 JOIN

–  JOIN절과 ON절을 사용하여 테이블의 정보를 연결하여 출력하는 문법
–  FROM절과 JOIN절에 연결할 테이블의 정보를 작성
–  ON절에는 의도한 두 테이블 간 행의 정보를 연결할 수 있는 조건을 작성
–  기본적으로 컬럼명 앞에는 테이블명을 달아서 작성함.

     →   어떤 테이블이 필요해서 어떤 규칙으로 묶어낼건지? 생각하기 !

EX1   )   ON + 연결 조건

 

–   만약 원하는 정보가 사원 사번 / 성 / 급여 / 소속부서 / 소속부서명 이라면, 보기 쉽게 해당 항목들을 컬럼명으로 적음.

      →    employee_id / last_name / salary / department_id / department_name

–   해당 컬럼들은 employees테이블과 departments테이블에 있는 컬럼들.

더보기

※   만약 어느 테이블에 속한 컬럼들인지 모르겠으면, 처음부터 조회해서 범위를 좁혀나가면 됨.

 

1 )   show tables;          —   테이블 리스트를 출력.

2 )   desc 테이블명        —   테이블이 가진 컬럼목록, 그에 따른 데이터타입, 기타 속성값들을 볼 수 있음.

 

* desc = describe

각 테이블에서 원하는 정보가 담긴 컬럼들만 골라 출력함.

Austin이라는 사람을 골라 쭈욱 따라가보니 오른쪽의 departments테이블에 있는 각 departments_id와 데이터가 일치함.

즉, 원하는 정보를 얻기 위해 위의 두 개의 테이블을 JOIN, 각 테이블의 department_id가 일치함을 조건으로 삼을 수 있음

→   join할 테이블 :  employees, departments
→   on 어떤 조건  :  employees.department_id = departments.department_id

mysql> SELECT employees.employee_id, employees.last_name, employees.salary,
    -> employees.department_id, departments.department_id, departments.department_name
    -> FROM employees JOIN departments
    -> ON employees.department_id = departments.department_id;
    
+-------------+-------------+----------+---------------+---------------+------------------+
| employee_id | last_name   | salary   | department_id | department_id | department_name  |
+-------------+-------------+----------+---------------+---------------+------------------+
|         200 | Whalen      |  4400.00 |            10 |            10 | Administration   |
|         201 | Hartstein   | 13000.00 |            20 |            20 | Marketing        |
|         114 | Raphaely    | 11000.00 |            30 |            30 | Purchasing       |
...
|         205 | Higgins     | 12000.00 |           110 |           110 | Accounting       |
|         206 | Gietz       |  8300.00 |           110 |           110 | Accounting       |
+-------------+-------------+----------+---------------+---------------+------------------+
106 rows in set (0.00 sec)

이렇게 원하는 정보를 얻을 수 있으나, 좀 더 간단하게 코드를 수정해보겠음.

 

 

1 )  table alias 사용하기

mysql> SELECT e.employee_id, e.last_name, e.salary, e.department_id,
    -> d.department_id, d.department_name
    -> FROM employees e JOIN departments d
    -> ON e.department_id = d.department_id;

→   원래 FROM절에 테이블 명을 적었으니, table alias를 정해줄 때도 마찬가지로 FROM절에서 정해줌.

       'employees' 테이블명을 'e'로 바꾸고, 'departments' 테이블명을 'd'로 바꿈. 

→    FROM절을 제외한 다른 절에 있는 테이블명을 table alias로 바꿔줌.

 

2 )  테이블명 생략 가능

mysql> SELECT employee_id, last_name, salary, e.department_id,
    -> d.department_id, department_name
    -> FROM employees e JOIN departments d
    -> ON e.department_id = d.department_id;

→   join하는 두 테이블 간의 컬럼명이 유일할 경우 테이블명 생략 가능함.

      그러나 department_id 컬럼은 두 테이블에 중복되는 컬럼명이므로, 앞에 테이블명이나 alias를 달아줘야 함.

 

 

 

EX2   ) 

출력해야 할 정보는 "부서 번호, 부서명, 사무실 위치코드, 도시"  해당 항목들 컬럼명으로 적음.

→   department_id, department_name, location_id, city

→   join 할 테이블  :  departments & locations
→   on 어떤 조건   :  각 테이블의 location_id가 일치함.
                                 departments.location_id = locations.location_id

mysql> SELECT department_id, department_name, d.location_id, l.location_id, city
    -> FROM departments d JOIN locations l
    -> ON d.location_id = l.location_id;
+---------------+----------------------+-------------+-------------+---------------------+
| department_id | department_name      | location_id | location_id | city                |
+---------------+----------------------+-------------+-------------+---------------------+
|            10 | Administration       |        1700 |        1700 | Seattle             |
|            20 | Marketing            |        1800 |        1800 | Toronto             |
|            30 | Purchasing           |        1700 |        1700 | Seattle             |
...
|           260 | Recruiting           |        1700 |        1700 | Seattle             |
|           270 | Payroll              |        1700 |        1700 | Seattle             |
+---------------+----------------------+-------------+-------------+---------------------+
27 rows in set (0.00 sec)

✓   SELECT   —   출력해야 할 컬럼명

      FROM      —   JOIN하는 테이블명

      ON           —   행의 정보를 연결할 수 있는 조건

✓   table alias 사용 및 유일한 컬럼명은 테이블명 생략함.

 

 

 

EX3   )   JOIN의 결과 = 가상의 테이블

mysql> SELECT department_id, department_name, d.location_id, l.location_id, city
    -> FROM departments d JOIN locations l
    -> ON d.location_id = l.location_id;
+---------------+----------------------+-------------+-------------+---------------------+
| department_id | department_name      | location_id | location_id | city                |
+---------------+----------------------+-------------+-------------+---------------------+
|            10 | Administration       |        1700 |        1700 | Seattle             |
|            20 | Marketing            |        1800 |        1800 | Toronto             |
|            30 | Purchasing           |        1700 |        1700 | Seattle             |
...
|           260 | Recruiting           |        1700 |        1700 | Seattle             |
|           270 | Payroll              |        1700 |        1700 | Seattle             |
+---------------+----------------------+-------------+-------------+---------------------+
27 rows in set (0.00 sec)

→   위의 코드대로 출력한 결과물에서 특정 '도시명'이 들어간 데이터만 보고 싶다면?

 

mysql> SELECT department_id, department_name, d.location_id, l.location_id, city
    -> FROM departments d JOIN locations l
    -> ON d.location_id= l.location_id
    -> WHERE city = 'Seattle';
+---------------+----------------------+-------------+-------------+---------+
| department_id | department_name      | location_id | location_id | city    |
+---------------+----------------------+-------------+-------------+---------+
|            10 | Administration       |        1700 |        1700 | Seattle |
|            30 | Purchasing           |        1700 |        1700 | Seattle |
|            90 | Executive            |        1700 |        1700 | Seattle |
...
|           260 | Recruiting           |        1700 |        1700 | Seattle |
|           270 | Payroll              |        1700 |        1700 | Seattle |
+---------------+----------------------+-------------+-------------+---------+
21 rows in set (0.00 sec)

→   앞에서 배운 WHERE절을 추가하여 작성해줌. 

✓   JOIN으로 만들어 출력한 결과는 하나의 가상의 테이블로 생각하기. WHERE절, ORDER BY절 원하는대로 사용 가능.

 

 

 

 

Q   )   employees테이블, departments테이블을 조인하여 모든 사원의 정보, 부서 정보를 함께 출력하시오.
          출력형식 - employee_id,  last_name,  salary,  department_id,  department_name

 

A   )

각 테이블의 구조를 조회하여 출력해야 할 컬럼들이 어느 테이블에 속했는지 알아봄.

 

employee_id,  last_name,  salary,  department_id,  department_name

      (직원)          (직원)       (직원)     ( 직원&부서)              (부서)                    →   department_id 값 일치

 

✓   join 어떤 테이블 :  employees & departments
✓   on 어떤 조건      :  department_id 가 일치함.

mysql> SELECT employee_id, last_name, salary, d.department_name
    -> FROM employees e JOIN departments d
    -> ON e.department_id = d.department_id;
+-------------+-------------+----------+---------------+------------------+
| employee_id | last_name   | salary   | department_id | department_name  |
+-------------+-------------+----------+---------------+------------------+
|         200 | Whalen      |  4400.00 |            10 | Administration   |
|         201 | Hartstein   | 13000.00 |            20 | Marketing        |
|         202 | Fay         |  6000.00 |            20 | Marketing        |
...
|         205 | Higgins     | 12000.00 |           110 | Accounting       |
|         206 | Gietz       |  8300.00 |           110 | Accounting       |
+-------------+-------------+----------+---------------+------------------+
106 rows in set (0.00 sec)

✓   SELECT절에 e.department_id,  d.department_name 둘 다 쓸 필요없음.  하나만 골라서 쓰면 됨.

 

 

 

 

 

 

 

 

 

 

▶   ON절을 사용한 Self - Join

–  동일한 테이블의 다른 행을 JOIN하여 연결하는 문법.

–  동일한 테이블이라 구분이 가지 않으므로 필수적으로 Table Alias 사용함.

 

EX   )   한 테이블 내에서의 JOIN

 

1 )   직원의 정보

mysql> SELECT employee_id, last_name, department_id, manager_id
    -> FROM employees;
+-------------+-------------+---------------+------------+
| employee_id | last_name   | department_id | manager_id |
+-------------+-------------+---------------+------------+
|         100 | King        |            90 |       NULL |
|         101 | Kochhar     |            90 |        100 |
|         102 | De Haan     |            90 |        100 |
|         103 | Hunold      |            60 |        102 |
|         104 | Ernst       |            60 |        103 |
|         105 | Austin      |            60 |        103 |
|         106 | Pataballa   |            60 |        103 |
|         107 | Lorentz     |            60 |        103 |
|         108 | Greenberg   |           100 |        101 |
|         109 | Faviet      |           100 |        108 |
|         110 | Chen        |           100 |        108 |
|         111 | Sciarra     |           100 |        108 |
...

 

2 )   매니저의 정보

mysql> SELECT employee_id, last_name, department_id
    -> FROM employees;
+-------------+-------------+---------------+
| employee_id | last_name   | department_id |
+-------------+-------------+---------------+
|         100 | King        |            90 |
|         101 | Kochhar     |            90 |
|         102 | De Haan     |            90 |
|         103 | Hunold      |            60 |
|         104 | Ernst       |            60 |
|         105 | Austin      |            60 |
|         106 | Pataballa   |            60 |
|         107 | Lorentz     |            60 |
|         108 | Greenberg   |           100 |
|         109 | Faviet      |           100 |
|         110 | Chen        |           100 |
|         111 | Sciarra     |           100 |
...

 

3 )   매니저 입장에서의 테이블 / 직원 입장에서의 테이블과 컬럼 구분하기

→   join 어떤 테이블 :  employees worker & employees manager
→   on 어떤 조건      :  worker.manager_id = manager.employee_id

 

4 )   출력하기

mysql> SELECT worker.employee_id, worker.last_name, worker.department_id, worker.manager_id, 
    -> manager.employee_id, manager.last_name, manager.department_id
    -> FROM employees worker JOIN employees manager
    -> ON worker.manager_id = manager.employee_id;
+-------------+-------------+---------------+------------+-------------+-----------+---------------+
| employee_id | last_name   | department_id | manager_id | employee_id | last_name | department_id |
+-------------+-------------+---------------+------------+-------------+-----------+---------------+
|         101 | Kochhar     |            90 |        100 |         100 | King      |            90 |
|         102 | De Haan     |            90 |        100 |         100 | King      |            90 |
|         103 | Hunold      |            60 |        102 |         102 | De Haan   |            90 |
|         104 | Ernst       |            60 |        103 |         103 | Hunold    |            60 |
|         105 | Austin      |            60 |        103 |         103 | Hunold    |            60 |
|         106 | Pataballa   |            60 |        103 |         103 | Hunold    |            60 |
|         107 | Lorentz     |            60 |        103 |         103 | Hunold    |            60 |
|         108 | Greenberg   |           100 |        101 |         101 | Kochhar   |            90 |
|         109 | Faviet      |           100 |        108 |         108 | Greenberg |           100 |
|         110 | Chen        |           100 |        108 |         108 | Greenberg |           100 |
...
|         205 | Higgins     |           110 |        101 |         101 | Kochhar   |            90 |
|         206 | Gietz       |           110 |        205 |         205 | Higgins   |           110 |
+-------------+-------------+---------------+------------+-------------+-----------+---------------+
106 rows in set (0.00 sec)

 

5 )   사장님의 소속 부하 직원 보기

 

→   self join으로 만들어낸 테이블 또한 하나의 가상의 테이블로서 사용할 수 있음.
→   만약, 사장님(mamager_id = 100) 소속인 부하직원들이 누구인지 알려면 WHERE절로 조건을 걸어 다시 추려내면 됨.

mysql> SELECT worker.employee_id, worker.last_name, worker.department_id, worker.manager_id,
    -> manager.employee_id, manager.last_name, manager.department_id
    -> FROM employees worker JOIN employees manager
    -> ON worker.manager_id = manager.employee_id
    -> WHERE worker.manager_id = 100;
+-------------+-----------+---------------+------------+-------------+-----------+---------------+
| employee_id | last_name | department_id | manager_id | employee_id | last_name | department_id |
+-------------+-----------+---------------+------------+-------------+-----------+---------------+
|         101 | Kochhar   |            90 |        100 |         100 | King      |            90 |
|         102 | De Haan   |            90 |        100 |         100 | King      |            90 |
|         114 | Raphaely  |            30 |        100 |         100 | King      |            90 |
|         120 | Weiss     |            50 |        100 |         100 | King      |            90 |
...
|         149 | Zlotkey   |            80 |        100 |         100 | King      |            90 |
|         201 | Hartstein |            20 |        100 |         100 | King      |            90 |
+-------------+-----------+---------------+------------+-------------+-----------+---------------+
14 rows in set (0.00 sec)

 

 

 

 

Q   )    employees 테이블로부터,

          모든 사원의 last_name, employee_id, 매니저 이름, manager_id를 함께 출력하시오.
          출력형식 = Employee / Emp# / Manager / Mgr#

 

A   )

<출력 형식>        <알맞는 데이터>

Employee       —         e.last_name

Emp#              —        e.employee_id

Manager         —        m.last_name

Mgr#               —        m.employee_id == e.manager_id


✓   join 어떤 테이블  :  employees
✓   on 어떤 조건       :  e.manager_id = m.employee_id
※ 직원 입장에서는 manager_id지만 매니저 입장에선 본인의 사번이므로 employee_id가 됨.

mysql> SELECT e.last_name "Employee", e.employee_id "Emp#", m.last_name "Manager", m.manager_id "Mgr#"
    -> FROM employees e JOIN employees m
    -> ON e.manager_id = m.employee_id;
+-------------+------+-----------+------+
| Employee    | Emp# | Manager   | Mgr# |
+-------------+------+-----------+------+
| Kochhar     |  101 | King      | NULL |
| De Haan     |  102 | King      | NULL |
| Hunold      |  103 | De Haan   |  100 |
| Ernst       |  104 | Hunold    |  102 |
| Austin      |  105 | Hunold    |  102 |
| Pataballa   |  106 | Hunold    |  102 |
| Lorentz     |  107 | Hunold    |  102 |
...
| Higgins     |  205 | Kochhar   |  100 |
| Gietz       |  206 | Higgins   |  101 |
+-------------+------+-----------+------+
106 rows in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

▶   비표준 JOIN

–  표준의 문법은 아니지만 대부분의 데이터베이스에서 사용 가능한 JOIN문법.

–  JOIN문법은 ON과 짝궁이기 때문에 둘은 항상 같이 써야하지만,

    만약 비표준 JOIN을 쓴다면 테이블끼리는 ,(콤마)로 나눠주고 WHERE절에 조건을 적고 필요하면 AND연산자 사용.

EX   )   표준문법과 비표준문법의 차이

 

1 )   표준문법 JOIN-ON 사용

–  직원이 본인의 매니저보다 먼저 입사한 사람들을 출력함.

SELECT w.employee_id, w.last_name, w.hire_date, w.manager_id, 
m.employee_id, m.last_name, m.hire_date
FROM employees w JOIN employees m
ON w.manager_id = m.employee_id
WHERE w.hire_date < m.hire_date;


>>>
employee_id|last_name |hire_date |manager_id|employee_id|last_name|hire_date |
-----------+----------+----------+----------+-----------+---------+----------+
        103|Hunold    |1990-01-03|       102|        102|De Haan  |1993-01-13|
        109|Faviet    |1994-08-16|       108|        108|Greenberg|1994-08-17|
        131|Marlow    |1997-02-16|       121|        121|Fripp    |1997-04-10|
        137|Ladwig    |1995-07-14|       123|        123|Vollman  |1997-10-10|
        141|Rajs      |1995-10-17|       124|        124|Mourgos  |1999-11-16|
 ...

→   FROM절에 테이블명을 적고 JOIN으로 연결해줌.

→   ON절에 JOIN의 조건을 적어줌.

→   WHERE절에 비교할 데이터를 적어줌.

 

2 )   비표준문법 WHERE-AND 사용

SELECT w.employee_id, w.last_name, w.hire_date, w.manager_id, 
m.employee_id, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id
AND w.hire_date < m.hire_date;

→   FROM절에 있는 테이블을 ,(콤마)로 묶어줌.

→   WHERE절에 조건을 써줌.

→   비교할 데이터가 있기 때문에 AND 연산자로 이어서 비교할 데이터를 적어줌.

 

 

 

 

 

 

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)