생활코딩( SQL join ) (4) - FULL OUTER JOIN

1. DataSet

 

2. Full Outer Join

두 테이블의 데이터를 Join 하는 Column의 데이터 존재 여부에 상관 없이 돌려줌

합집합 처럼 동작

MySQL 에서는 FUll OUTER JOIN 구문이 없어서 다음을 활용

(SELECT * FROM <tbl_A>  \
    LEFT JOIN <tbl_B> ON <Join condition>)  \
    UNION DISTINCT  \
(SELECT * FROM <tbl_A>  \
    RIGHT JOIN <tbl_B> ON <Join condition>);

※ 추가 syntax  

UNION :  합집합 처리
DISTINCT :  중복 data 없앰

> 보통 full outer join은 성능 문제로 인해 잘 사용하지 않음

 

2-1) Outer Join

Syntax

SELECT <projection> FROM <tbl_A> 
        FULL OUTER JOIN <tbl_B>
        ON <join condition>
        [WHERE <where_condition>]
        [FULL OUTER JOIN tbl_c .... and so on]

1) A-B 순서로 하고, FULL OUTER는 A/B 둘다 적용되므로, 순서는 상관없음

2) ON : Join을 할 index등의 condition을 넣어줌

3) Where에서는 전체 output이 만족해야하는 조건을 지정해줄 수 있음

4) Project으로 column 지정 가능

 

2-2) Input SQL

use opentutorials;


SELECT * FROM topic;
SELECT * FROM author;
SELECT * FROM profile;

-- using 2 tables
(SELECT * FROM topic  \
    LEFT JOIN author ON topic.author_id = author.aid)  \
    UNION DISTINCT  \
(SELECT * FROM topic  \
    RIGHT JOIN author ON topic.author_id = author.aid);

 

2-3) Output SQL

+-----+------------+------------------+-----------+
| tid | title      | description      | author_id |
+-----+------------+------------------+-----------+
|   1 | HTML       | HTML is ...      | 1         |
|   2 | CSS        | CSS is ...       | 2         |
|   3 | JavaScript | JavaScript is .. | 1         |
|   4 | Database   | Database is ...  | NULL      |
+-----+------------+------------------+-----------+
4 rows in set (0.00 sec)

"Select * FROM author"
+-----+----------+--------+------------+
| aid | name     | city   | profile_id |
+-----+----------+--------+------------+
|   1 | egoing   | seoul  |          1 |
|   2 | leezche  | jeju   |          2 |
|   3 | blackdew | namhae |          3 |
+-----+----------+--------+------------+
3 rows in set (0.00 sec)

"Select * FROM profile"
+-----+-----------+------------------+
| pid | title     | description      |
+-----+-----------+------------------+
|   1 | developer | developer is ... |
|   2 | designer  | designer is ..   |
|   3 | DBA       | DBA is ...       |
+-----+-----------+------------------+
3 rows in set (0.00 sec)

"SELECT * FROM topic FULL OUTER JOIN author ON topic.author_id = author.aid"
+------+------------+------------------+-----------+------+----------+--------+------------+
| tid  | title      | description      | author_id | aid  | name     | city   | profile_id |
+------+------------+------------------+-----------+------+----------+--------+------------+
|    1 | HTML       | HTML is ...      | 1         |    1 | egoing   | seoul  |          1 |
|    2 | CSS        | CSS is ...       | 2         |    2 | leezche  | jeju   |          2 |
|    3 | JavaScript | JavaScript is .. | 1         |    1 | egoing   | seoul  |          1 |
|    4 | Database   | Database is ...  | NULL      | NULL | NULL     | NULL   |       NULL |
| NULL | NULL       | NULL             | NULL      |    3 | blackdew | namhae |          3 |
+------+------------+------------------+-----------+------+----------+--------+------------+
5 rows in set (0.00 sec)

---

참조

https://opentutorials.org/module/4118/25183

FULL JOIN - SQL Join