생활코딩( SQL join ) (2) - LEFT JOIN & RIGHT JOIN

1. DataSet

 

2. LeftJoin

2-1) Right Join

Right Join도 결과는 동일, 단지 오른쪽 table을 기준으로 하는 것이 다름

 

Syntax

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

1) A-B 순서로 하고, LEFT는 A를 기준으로 함

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;

-- using 3 tables
Select * FROM topic LEFT JOIN author ON topic.author_id = author.aid  \
                    LEFT JOIN profile ON author.profile_id = profile.pid;


-- using 3 tables and projection
Select tid, topic.title, topic.description, author_id, name, profile.title AS job_title, profile.description FROM topic LEFT JOIN author ON topic.author_id = author.aid  \
                    LEFT JOIN profile ON author.profile_id = profile.pid;

-- using 3 tables and projection and a data selection
Select tid, topic.title, topic.description, author_id, name, profile.title AS job_title, profile.description FROM topic LEFT JOIN author ON topic.author_id = author.aid  \
                    LEFT JOIN profile ON author.profile_id = profile.pid  \
                    WHERE author.name = 'egoing';

 

2-3) 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)

"Select * FROM topic LEFT JOIN author ON topic.author_id = author.id"
"LEFT JOIN  => aka) LEFT OUTER JOIN"
+-----+------------+------------------+-----------+------+---------+-------+------------+
| 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 |
+-----+------------+------------------+-----------+------+---------+-------+------------+
4 rows in set (0.00 sec)

"Using 3 tables"
+-----+------------+------------------+-----------+------+---------+-------+------------+------+-----------+------------------+
| tid | title      | description      | author_id | aid  | name    | city  | profile_id | pid  | title     | description      |
+-----+------------+------------------+-----------+------+---------+-------+------------+------+-----------+------------------+
|   1 | HTML       | HTML is ...      | 1         |    1 | egoing  | seoul |          1 |    1 | developer | developer is ... |
|   2 | CSS        | CSS is ...       | 2         |    2 | leezche | jeju  |          2 |    2 | designer  | designer is ..   |
|   3 | JavaScript | JavaScript is .. | 1         |    1 | egoing  | seoul |          1 |    1 | developer | developer is ... |
|   4 | Database   | Database is ...  | NULL      | NULL | NULL    | NULL  |       NULL | NULL | NULL      | NULL             |
+-----+------------+------------------+-----------+------+---------+-------+------------+------+-----------+------------------+
4 rows in set (0.00 sec)

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

"Using 3 tables and a projection and a specific row-data!"
+-----+------------+------------------+-----------+--------+-----------+------------------+
| tid | title      | description      | author_id | name   | job_title | description      |
+-----+------------+------------------+-----------+--------+-----------+------------------+
|   1 | HTML       | HTML is ...      | 1         | egoing | developer | developer is ... |
|   3 | JavaScript | JavaScript is .. | 1         | egoing | developer | developer is ... |
+-----+------------+------------------+-----------+--------+-----------+------------------+
2 rows in set (0.00 sec)

 

---

참조

https://opentutorials.org/course/3884/25181

LEFT JOIN - 생활코딩