카테고리 없음
8. SQL로 데이터 조회하기 4
본투비곰손
2023. 8. 22. 22:40
728x90
테이블 조인(join)
- 두 개 이상의 table들에 있는 데이터를 한 번에 조회 하는 것
- 여러 종류의 JOIN이 존재한다.
implicit join 과 explicit join의 차이점
ID가 1인 임직원이 속한 부서의 이름은?
explicit join: from절에 JOIN 키워드와 함께 joined table들을 명시하는 방식
- from절에서 ON 뒤에 join condition이 명시 된다.
- 가독성이 좋다.
- 복잡한 join 쿼리 작성 중에도 실수할 가능성이 적다.
inner join 과 outer join의 차이점
ID가 1인 임직원이 속한 부서의 이름은?
inner join 사용
mysql> SELECT *
-> FROM employee E INNER JOIN department D ON e.dept_id = D.id;
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| id | name | birth_date | sex | position | salary | dept_id | id | name | leader_id |
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1003 | development | 1 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1003 | development | 1 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1003 | development | 1 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1004 | design | 3 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1004 | design | 3 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1004 | design | 3 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1005 | product | 13 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1005 | product | 13 |
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
8 rows in set (0.00 sec)inner join: 두 table에서 join condition을 만족하는 두 tuple들로 result table을 만드는 join
- FROM table1 [INNER] JOIN table2 ON join_condition
- join condition에 사용 가능한 연산자(operator): =, <, >, != 등 여러 비교 연산자가 가능하다.
- join condition에서 null값을 가지는 tuple은 result table에 포함 되지 못한다.(null 값은 제외됨)
mysql> SELECT *
-> FROM employee E LEFT OUTER JOIN department D ON e.dept_id = D.id;
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| id | name | birth_date | sex | position | salary | dept_id | id | name | leader_id |
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1003 | development | 1 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1004 | design | 3 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1003 | development | 1 |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | NULL | NULL | NULL |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | NULL | NULL | NULL |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | NULL | NULL | NULL |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1004 | design | 3 |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | NULL | NULL | NULL |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1003 | development | 1 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1005 | product | 13 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1004 | design | 3 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1005 | product | 13 |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | NULL | NULL | NULL |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | NULL | NULL | NULL |
+----+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
14 rows in set (0.00 sec)
mysql> SELECT *
-> FROM employee E RIGHT OUTER JOIN department D ON e.dept_id = D.id;
+------+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| id | name | birth_date | sex | position | salary | dept_id | id | name | leader_id |
+------+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1001 | headquarter | 4 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1002 | HR | NULL |
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1003 | development | 1 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1003 | development | 1 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1003 | development | 1 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1004 | design | 3 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1004 | design | 3 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1004 | design | 3 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1005 | product | 13 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1005 | product | 13 |
+------+---------+------------+------+-----------+-----------+---------+------+-------------+-----------+
10 rows in set (0.00 sec)outer join: 두 table에서 join condition을 만족하지 안는 tuple들도 result table에 포함하는 join
- FROM table1 LEFT [OUTER] JOIN table2 ON join_condition(왼쪽 테이블의 null값도 같이 출력)
- FROM table1 RIGHT [OUTER] JOIN table2 ON join_condition(오른쪽 테이블의 null값도 같이 출력)
- FROM table1 FULL [OUTER] JOIN table2 ON join_condition (양쪽의 null 값 포함 모두 출력 하지만 MySQL에는 FULL OUTER JOIN이 없음!!)
- join condition에 사용 가능한 연산자(operator): =, <, >, != 등 여러 비교 연산자가 가능하다.
equi join에 대한 두가지 시각
- inner join, outer join 상관없이 = 를 사용한 join이라면 모두 equi join으로 보는 경우
- inner join 에 한정하여 = 를 사용한 경우에만 equi join으로 보는 경우
using
두 테이블의 컬럼 이름을 같게 변경하고 합쳐 보았다.
이전과 동일하게 inner join을 사용한 equi join 의 경우 null 값을 제외하고 동일하게 출력 되지만 dept_id라는 동일한 column이 두개 출력 된다.
mysql> ALTER TABLE department CHANGE id dept_id int; // column의 이름과 속성을 변경해준다.
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT *
-> FROM employee E INNER JOIN department D ON e.dept_id = D.dept_id;
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
| id | name | birth_date | sex | position | salary | dept_id | dept_id | name | leader_id |
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1003 | development | 1 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1003 | development | 1 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1003 | development | 1 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1004 | design | 3 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1004 | design | 3 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1004 | design | 3 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1005 | product | 13 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1005 | product | 13 |
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
8 rows in set (0.00 sec)USING를 사용하면 dept_id가 하나로 합쳐지면서 맨앞으로 이동하게 된다.
mysql> SELECT *
-> FROM employee E INNER JOIN department D USING(dept_id);
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
| dept_id | id | name | birth_date | sex | position | salary | name | leader_id |
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
| 1003 | 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | development | 1 |
| 1003 | 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | development | 1 |
| 1003 | 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | development | 1 |
| 1004 | 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | design | 3 |
| 1004 | 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | design | 3 |
| 1004 | 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | design | 3 |
| 1005 | 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | product | 13 |
| 1005 | 13 | JISUNG | NULL | M | PO | 90000000 | product | 13 |
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
8 rows in set (0.00 sec)USING
- 두 table이 equi join 할 때만 join 하는 attribute의 이름이 같다면, USING으로 간단하게 작성 할 수 있다.
- 이 때 같은 이름의 attribute는 result table에 한번만 표시 된다.
- FROM table1 [INNER] JOIN table2 USING (attribute(s))
- FROM table1 LEFT [OUTER] JOIN table2 USING (attribute(s))(왼쪽 테이블의 null값도 같이 출력)
- FROM table1 RIGHT [OUTER] JOIN table2 USING (attribute(s))(오른쪽 테이블의 null값도 같이 출력)
- FROM table1 FULL [OUTER] JOIN table2 USING (attribute(s)) (양쪽의 null 값 포함 모두 출력 하지만 MySQL에는 FULL OUTER JOIN이 없음!!)
natural join
- 두 table에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행
- join condition을 따로 명시하지 않는다.
- FROM table1 NATURAL [INNER] JOIN table2
- FROM table1 NATURAL LEFT [OUTER] JOIN table2
- FROM table1 NATURAL RIGHT [OUTER] JOIN table2
- FROM table1 NATURAL FULL [OUTER] JOIN table2
mysql> SELECT *
-> FROM employee E NATURAL INNER JOIN department D;
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
| dept_id | id | name | birth_date | sex | position | salary | dept_name | leader_id |
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
| 1003 | 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | development | 1 |
| 1004 | 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | design | 3 |
| 1003 | 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | development | 1 |
| 1004 | 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | design | 3 |
| 1003 | 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | development | 1 |
| 1005 | 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | product | 13 |
| 1004 | 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | design | 3 |
| 1005 | 13 | JISUNG | NULL | M | PO | 90000000 | product | 13 |
+---------+----+---------+------------+------+-----------+-----------+-------------+-----------+
8 rows in set (0.00 sec)cross join
- 두 table의 tuple pair로 만들 수 있는 모든 조합(Cartesian product)을 result table로 반환한다.
- join condition이 없다.
- implicit cross join: FROM table1, table2
- explicit cross join: FROM table1 CROSS JOIN table2
mysql> alter table department change dept_name name varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT*
-> FROM employee CROSS JOIN department;
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
| id | name | birth_date | sex | position | salary | dept_id | dept_id | name | leader_id |
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1005 | product | 13 |
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1004 | design | 3 |
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1003 | development | 1 |
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1002 | HR | NULL |
| 1 | MESSI | 1987-02-01 | M | DEV_BACK | 100000000 | 1003 | 1001 | headquarter | 4 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1005 | product | 13 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1004 | design | 3 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1003 | development | 1 |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1002 | HR | NULL |
| 2 | JANE | 1996-05-05 | F | DSGN | 180000000 | 1004 | 1001 | headquarter | 4 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1005 | product | 13 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1004 | design | 3 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1003 | development | 1 |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1002 | HR | NULL |
| 3 | JENNY | 2000-10-12 | F | DEV_BACK | 50000000 | 1003 | 1001 | headquarter | 4 |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | 1005 | product | 13 |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | 1004 | design | 3 |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | 1003 | development | 1 |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | 1002 | HR | NULL |
| 4 | BROWN | 1996-03-13 | M | CEO | 240000000 | NULL | 1001 | headquarter | 4 |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | 1005 | product | 13 |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | 1004 | design | 3 |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | 1003 | development | 1 |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | 1002 | HR | NULL |
| 5 | DINGYO | 1990-11-05 | M | CTO | 120000000 | NULL | 1001 | headquarter | 4 |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | 1005 | product | 13 |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | 1004 | design | 3 |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | 1003 | development | 1 |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | 1002 | HR | NULL |
| 6 | JULIA | 1986-12-11 | F | CFO | 240000000 | NULL | 1001 | headquarter | 4 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1005 | product | 13 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1004 | design | 3 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1003 | development | 1 |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1002 | HR | NULL |
| 7 | MINA | 1993-06-17 | F | DSGN | 160000000 | 1004 | 1001 | headquarter | 4 |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | 1005 | product | 13 |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | 1004 | design | 3 |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | 1003 | development | 1 |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | 1002 | HR | NULL |
| 9 | HENRY | 1982-05-20 | M | HR | 82000000 | NULL | 1001 | headquarter | 4 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1005 | product | 13 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1004 | design | 3 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1003 | development | 1 |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1002 | HR | NULL |
| 10 | NICOLE | 1991-03-26 | F | DEV_FRONT | 180000000 | 1003 | 1001 | headquarter | 4 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1005 | product | 13 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1004 | design | 3 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1003 | development | 1 |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1002 | HR | NULL |
| 11 | SUZANNE | 1993-03-23 | F | PO | 75000000 | 1005 | 1001 | headquarter | 4 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1005 | product | 13 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1004 | design | 3 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1003 | development | 1 |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1002 | HR | NULL |
| 12 | CURRY | 1998-01-15 | M | PLN | 170000000 | 1004 | 1001 | headquarter | 4 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1005 | product | 13 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1004 | design | 3 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1003 | development | 1 |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1002 | HR | NULL |
| 13 | JISUNG | NULL | M | PO | 90000000 | 1005 | 1001 | headquarter | 4 |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | 1005 | product | 13 |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | 1004 | design | 3 |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | 1003 | development | 1 |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | 1002 | HR | NULL |
| 14 | SAM | NULL | M | DEV_INFRA | 70000000 | NULL | 1001 | headquarter | 4 |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | 1005 | product | 13 |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | 1004 | design | 3 |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | 1003 | development | 1 |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | 1002 | HR | NULL |
| 15 | SIMON | 1982-08-04 | M | PL | 90000000 | NULL | 1001 | headquarter | 4 |
+----+---------+------------+------+-----------+-----------+---------+---------+-------------+-----------+
70 rows in set (0.00 sec)MySQL 에서의 cross join
- MySQL에서는 cross join = inner join = join 이다.
- CROSS JOIN에 ON(or USING)을 같이 쓰면 inner join처럼 동작 한다.
- INNER JOIN(or JOIN)이 ON(or USING)없이 사용되면 cross join처럼 동작한다.
self join
- table이 자기 자신에게 join하는 경우
join example
- ID가 1003인 부서에 속하는 임직원 중 리더를 제외한 부서원의 ID, 이름, 연봉을 알고 싶다.
mysql> SELECT E.id, E.name, E.salary
-> FROM employee E JOIN department D ON E.dept_id= D.dept_id
-> WHERE E.dept_id = 1003 and E.id != D.leader_id;
+----+--------+-----------+
| id | name | salary |
+----+--------+-----------+
| 3 | JENNY | 50000000 |
| 10 | NICOLE | 180000000 |
+----+--------+-----------+
2 rows in set (0.00 sec)
mysql>- ID가 2001인 프로젝트에 참여한 임직원들의 이름과 직군과 소속 부서 이름을 알고 싶다.
mysql> SELECT E.name AS empl_name,
-> E.position AS empl_position,
-> D.name AS dept_name
-> FROM works_on W JOIN employee E ON W.empl_id = E.id // id값이 null 값이 있을 수 있음
-> LEFT JOIN department D ON E.dept_id = D.dept_id // LEFT JOIN을 사용해야함
-> WHERE W.proj_id = 2001;
+-----------+---------------+-------------+
| empl_name | empl_position | dept_name |
+-----------+---------------+-------------+
| MESSI | DEV_BACK | development |
| DINGYO | CTO | NULL |
| HENRY | HR | NULL |
| JISUNG | PO | product |
+-----------+---------------+-------------+
4 rows in set (0.00 sec)728x90