JOIN (Self-Join / 비표준 join)

티스토리 뷰

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 연산자로 이어서 비교할 데이터를 적어줌.

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

'DB > MySQL' 카테고리의 다른 글

단일 행 함수 — 숫자 (0)	2023.02.07
단일 행 함수 — 문자 (0)	2023.02.07
ORDER BY절 (0)	2023.02.01
논리 연산자 (AND / OR / NOT) (0)	2023.02.01
비교 연산자(between A and B / IN / LIKE / IS NULL) (0)	2023.02.01

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/06 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

글 보관함

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

TAErogramming_____태로그래밍 *

티스토리 뷰