생활코딩( SQL join ) (5) - EXCLUSIVE JOIN

1. DataSet

 

 

2. Exclusive Join

2-1) LEFT JOIN과 비교

LEFT JOIN, RIGHT IS NULL 과 동일한 결과

순수 A에 해당하는 것만 JOIN하게 됨

 

2-2) EXCLUSIVE JOIN

LEFT JOIN + WHERE IS NULL : A, B 테이블 중 A에만 있는 데이터를 원할 때

RIGHT JOIN + WHERE IS NULL : A, B 테이블 중 B에만 있는 데이터를 원할 때

Syntax

SELECT <projection> FROM <tbl_A> 
        LEFT JOIN <tbl_B>
        ON <join condition>
        <WHERE <JOIN column in tbl_B> IS NULL>
        [LEFT JOIN tbl_c .... and so on]

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

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

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

4) Project으로 column 지정 가능

 

2-3) Input SQL

use opentutorials;


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

-- exclusive and only get data that exists in Author
SELECT * FROM topic LEFT JOIN author  \
    ON topic.author_id = author.aid  \
    WHERE author.aid IS NULL;

 

2-4) Output SQL

Database changed
"Select * FROM topic"
+-----+------------+------------------+-----------+
| 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)

+-----+----------+-----------------+-----------+------+------+------+------------+
| tid | title    | description     | author_id | aid  | name | city | profile_id |
+-----+----------+-----------------+-----------+------+------+------+------------+
|   4 | Database | Database is ... | NULL      | NULL | NULL | NULL |       NULL |
+-----+----------+-----------------+-----------+------+------+------+------------+
1 row in set (0.00 sec)

 

---

참조

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

EXCLUSIVE JOIN - SQL Join