테이블 결합
1. 교차결합
두개의 집합을 곱하여 연산하는 방법이다. 잘 쓰이진 않는다.
ex) {기묘한이야기 시즌1, 시즌2, 시즌3} X {1화, 2화, 3화, 4화}
(시즌1, 1화), (시즌1, 2화), (시즌1, 3화), (시즌1, 4화)
(시즌2, 1화), (시즌2, 2화), (시즌2, 3화), (시즌2, 4화)
(시즌3, 1화), (시즌3, 2화), (시즌3, 3화), (시즌3, 4화)
이런 식으로 드라마의 모든 시즌과 회차를 구할 때 쓰일 것 같다.
2. 내부 결합 (Inner Join)
보이는 그대로 교집합이다.
기준이 되는 테이블(left table) 과 join이 걸리는 테이블(right table)
양쪽 모두에 결합조건이 매칭되는 row만 검색하는 방법이다.
mysql> SELECT * FROM table_1 INNER JOIN table_2 ON search_condition WHERE where_condition
어떤 유저가 어떤 게시글을 썼는지 알고싶어 데이터를 뽑아오는 경우를 가정해봤다.
mysql> select u.id, u.nickname, p.contents, p.user_id from users u INNER JOIN postings p ON u.id = p.user_id;
+----+----------+--------------------------------------------------------------------------------------+---------+
| id | nickname | contents | user_id |
+----+----------+--------------------------------------------------------------------------------------+---------+
| 1 | codeKim | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 1 |
| 1 | codeKim | 오늘은 코딩하기 좋은날 🌷 | 1 |
| 2 | codeLee | db 모델링이 정말 재밌어요 ㅎㅎ | 2 |
| 2 | codeLee | 어려워도 우린 결국 해낼겁니다. | 2 |
+----+----------+--------------------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)이런 식으로 두개의 테이블 사이에 서로 구하고자 하는 row를 지정하여 결합하여 가져오는 방법이다.
3. 외부 결합
3-1 LEFT(OUTER) JOIN
기준이 되는 left table의 모든 row와 join이 걸리는 right 테이블 중
left table과 매칭되는 row만 검색한다. 기준 테이블의 값은 전부 가져오면서
join이 걸리는 right table에서 매칭되는 결과가 없으면 NULL로 표시한다.
mysql> SELECT u.id, u.nickname, p.contents, p.user_id FROM users u LEFT JOIN postings p ON u.id = p.user_id;
+----+----------+--------------------------------------------------------------------------------------+---------+
| id | nickname | contents | user_id |
+----+----------+--------------------------------------------------------------------------------------+---------+
| 1 | codeKim | 오늘은 코딩하기 좋은날 🌷 | 1 |
| 1 | codeKim | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 1 |
| 2 | codeLee | 어려워도 우린 결국 해낼겁니다. | 2 |
| 2 | codeLee | db 모델링이 정말 재밌어요 ㅎㅎ | 2 |
| 3 | codePark | NULL | NULL |
| 4 | codeChoi | NULL | NULL |
+----+----------+--------------------------------------------------------------------------------------+---------+
6 rows in set (0.00 sec)이렇게 유저의 아이디와 닉네임은 전부 가져오면서
join이 걸리는 postings 테이블에 매칭되는 값이 없다면 NULL로 반환한다.
유저의 이름과 게시물을 결합하고 싶은데 모든 유저는 그대로 유지하는 테이블을 만들 때 사용한다.
3-2 RIGHT(OUTER) JOIN
left outer join 과 반대로 join이 걸리는 테이블의 row값은 모두 가져오면서
기준이 되는 left 테이블에서 matching되는 데이터가 없는경우 NULL을 표시한다.
// jobs table
+----+-----------+
| id | job |
+----+-----------+
| 1 | 개발자 |
| 2 | 경찰 |
| 3 | 소방관 |
| 4 | 교사 |
| 5 | 건축가 |
+----+-----------+
// users table
+----+-----------------+-----+--------+
| id | name | age | job_id |
+----+-----------------+-----+--------+
| 1 | Rebekah Johnson | 21 | 1 |
| 2 | Fabian Predovic | 22 | 1 |
| 3 | Elenor Gottlieb | 23 | 1 |
| 4 | Madge Ledner | 20 | 2 |
| 5 | Zelma Kunde | 19 | NULL |
+----+-----------------+-----+--------+
예시를 들기위해 이런 테이블이 있을 때 rignt join을 하면
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users
RIGHT JOIN jobs ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| NULL | NULL | NULL | 건축가 |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
+------+-----------------+--------+-----------+위와 같이 join이 걸리는 jobs 테이블의 직업은 전부 출력되는 반면
매칭되는 결과가 없는 유저테이블의 값은 NULL로 표시된다.
이 경우 직업 종류는 전부 보면서 어떤 유저가 어떤 직업인지의 데이터를 가져올 때 사용하면 된다.
3-3 FULL (OUTER) JOIN
LEFT JOIN과 RIGHT JOIN을 합친 결합 방법으로 기준이 되는 left table과
join이 걸리는 right table 의 모든 row값을 검색한다.
MySQL은 FULL(OUTER)JOIN 이라는 키워드가 없기 때문에 UNION 하는 방식으로 구할 수 있다.
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users LEFT JOIN jobs ON users.job_id = jobs.id
UNION
SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users RIGHT JOIN jobs ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| 5 | Zelma Kunde | NULL | NULL |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
| NULL | NULL | NULL | 건축가 |
+------+-----------------+--------+-----------+보다시피 모든 유저의 데이터를 전부 가져오고 모든 직업의 데이터를 전부 가져왔다.
모든 유저와 모든 종류의 직업을 보면서 어떤 유저가 어떤 직업인지 또 어떤 직업의 유저가 없는지를
검색할 때 쓰면 된다.
📌오늘의 응용문제
내가 작성 해놓은 데이터 베이스를 바탕으로
1번 users의 이름과, 1번 users의 profile_image, 1번 user가 남긴 posting, 해당 posting에 포함된 댓글,
그리고 포함된 posting_images까지 불러오고자 한다면 어떤 SQL를 작성해야할까요?
내가 작성한 명령어는
mysql> select users.nickname, users.profile_image, postings.contents, comments.comment, posting_images.image_url FROM users
-> JOIN postings ON postings.user_id = users.id AND users.id = 1
-> JOIN comments ON comments.posting_id = postings.id
-> JOIN posting_images ON posting_images.posting_id = postings.id;
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| nickname | profile_image | contents | comment | image_url |
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 작은 성공이 모여 큰 성취가 되니까요! | http://posting_1_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 작은 성공이 모여 큰 성취가 되니까요! | http://posting_1_image_2.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 자다가 코딩하는 꿈꿀 것 같아요 | http://posting_1_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 자다가 코딩하는 꿈꿀 것 같아요 | http://posting_1_image_2.jpeg |
| codeKim | http://profile_image_1.jpeg | 오늘은 코딩하기 좋은날 🌷 | 햇살이 너무 좋아서 코딩하고 싶지 않아요 | http://posting_2_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 오늘은 코딩하기 좋은날 🌷 | 지금 비오는걸요 | http://posting_2_image_1.jpeg |
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
6 rows in set (0.00 sec)모두 포함된 데이터를 가져오는 것이기 때문에 INNER JOIN을 사용했고 ON 뒤에 AND로
users.id =1 이라는 조건을 걸어주었다.
어차피 users.id를 참조하는 값이 posting인데 posting에만 조건을 걸어두면
comment는 posting의 id를 참조하기에 자동으로 users.id = 1 인 값만 가져오게 되고
posting_images 테이블도 posting의 id를 참조하기 때문에 자동으로 user id가 1인 값만 가져오게 된다.
이런 방법 외에 where을 추가하여 users.id = 1을 추가하여 구해도 된다.
mysql> select users.nickname, users.profile_image, postings.contents, comments.comment, posting_images.image_url FROM users
-> JOIN postings ON postings.user_id = users.id
-> JOIN comments ON comments.posting_id = postings.id
-> JOIN posting_images ON posting_images.posting_id = postings.id
-> WHERE users.id =1;
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| nickname | profile_image | contents | comment | image_url |
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 작은 성공이 모여 큰 성취가 되니까요! | http://posting_1_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 작은 성공이 모여 큰 성취가 되니까요! | http://posting_1_image_2.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 자다가 코딩하는 꿈꿀 것 같아요 | http://posting_1_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 작은 성공이 주는 성취감으로 하루를 마무리 할 수 있기를 ❣️ | 자다가 코딩하는 꿈꿀 것 같아요 | http://posting_1_image_2.jpeg |
| codeKim | http://profile_image_1.jpeg | 오늘은 코딩하기 좋은날 🌷 | 햇살이 너무 좋아서 코딩하고 싶지 않아요 | http://posting_2_image_1.jpeg |
| codeKim | http://profile_image_1.jpeg | 오늘은 코딩하기 좋은날 🌷 | 지금 비오는걸요 | http://posting_2_image_1.jpeg |
+----------+-----------------------------+--------------------------------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
6 rows in set (0.00 sec)