생활코딩( SQL join ) (3) - INNER JOIN

1. DataSet

 

 

2. Inner Join

2-1) Left, Right Join 결과와 비교

Left, Rigjt Join의 결과에는 data row에 NULL 값 있을 수도 있음
※ INNER JOIN은 엄격하게 교집합을 결과로 return 받음

ex) Left join 결과

"Using 3 tables and a projection!"
+-----+------------+------------------+-----------+---------+-----------+------------------+
| tid | title      | description      | author_id | name    | job_title | description      |
+-----+------------+------------------+-----------+---------+-----------+------------------+
|   1 | HTML       | HTML is ...      | 1         | egoing  | developer | developer is ... |
|   2 | CSS        | CSS is ...       | 2         | leezche | designer  | designer is ..   |
|   3 | JavaScript | JavaScript is .. | 1         | egoing  | developer | developer is ... |
|   4 | Database   | Database is ...  | NULL      | NULL    | NULL      | NULL             |
+-----+------------+------------------+-----------+---------+-----------+------------------+
4 rows in set (0.00 sec)

 

2-2) Inner Join

두 테이블 모두 Join하는 column값에 대해 값이 존재해야 됨

Syntax

SELECT <projection> FROM <tbl_A> 
        INNER JOIN <tbl_B>
        ON <join condition>
        [WHERE <where_condition>]
        [INNER 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;


-- using 2 tables
SELECT * FROM topic INNER JOIN author ON topic.author_id = author.aid;

-- using 3 tables
SELECT tid, topic.title, topic.description, name, city, profile.title, profile.description FROM topic  \
    INNER JOIN author ON topic.author_id = author.aid  \
    INNER JOIN profile ON author.profile_id = profile.pid;

 

2-4) Output SQL

"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)

"SELECT * FROM topic INNER 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 |
+-----+------------+------------------+-----------+-----+---------+-------+------------+
3 rows in set (0.00 sec)

"Using 3 tables and projections"
+-----+------------+------------------+---------+-------+-----------+------------------+
| tid | title      | description      | name    | city  | title     | description      |
+-----+------------+------------------+---------+-------+-----------+------------------+
|   1 | HTML       | HTML is ...      | egoing  | seoul | developer | developer is ... |
|   2 | CSS        | CSS is ...       | leezche | jeju  | designer  | designer is ..   |
|   3 | JavaScript | JavaScript is .. | egoing  | seoul | developer | developer is ... |
+-----+------------+------------------+---------+-------+-----------+------------------+
3 rows in set (0.00 sec)

※ inner Join 에는 NULL이 없는게 point!

 

---

참조

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

INNER JOIN - SQL Join