🗃️나의 SQL : JOIN

---

이제 우리는 TABLES에 있는 topic과 author를 연결해 줄 것이다. 

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

MariaDB [opentutorials]> SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| id | title      | description       | created             | author_id | id   | name   | profile                   |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |    1 | egoing | developer                 |
|  2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 |         1 |    1 | egoing | developer                 |
|  3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 |         2 |    2 | duru   | data administrator        |
|  4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 |         3 |    3 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 |         1 |    1 | egoing | developer                 |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
5 rows in set (0.001 sec)

 

SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

 SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

MariaDB [opentutorials]> SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | name   | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|  2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 | egoing | developer                 |
|  3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 | duru   | data administrator        |
|  4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

MariaDB [opentutorials]> SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----------+------------+-------------------+---------------------+--------+---------------------------+
| topic_id | title      | description       | created             | name   | profile                   |
+----------+------------+-------------------+---------------------+--------+---------------------------+
|        1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|        2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 | egoing | developer                 |
|        3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 | duru   | data administrator        |
|        4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|        5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 | egoing | developer                 |
+----------+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.001 sec)

현재 우리는 LEFT JOIN을 통해서 값들을 참조시키고 있다. 이제 값이 참조 되어 있는지 확인해보려 한다. 그러기 위해선 필요한 author 값을 변경하면 된다. 그 예를들 대상은 data administrator를 database administrator로 변경 할 것이다. 아래 코드를 참고하자.

 UPDATE author SET profile = 'database administrator' WHERE id = 2;

MariaDB [opentutorials]> SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----------+------------+-------------------+---------------------+--------+---------------------------+
| topic_id | title      | description       | created             | name   | profile                   |
+----------+------------+-------------------+---------------------+--------+---------------------------+
|        1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|        2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 | egoing | developer                 |
|        3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 | duru   | data administrator        |
|        4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|        5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 | egoing | developer                 |
+----------+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.001 sec)

MariaDB [opentutorials]> UPDATE author SET profile = 'database administrator' WHERE id = 2;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [opentutorials]> SELECT topic.id AS topic_id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----------+------------+-------------------+---------------------+--------+---------------------------+
| topic_id | title      | description       | created             | name   | profile                   |
+----------+------------+-------------------+---------------------+--------+---------------------------+
|        1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing | developer                 |
|        2 | Oracle     | Oracle is...      | 2018-01-03 13:01:10 | egoing | developer                 |
|        3 | SQL Server | SQL Server is...  | 2018-01-20 11:01:10 | duru   | database administrator    |
|        4 | PostgreSQL | PostgereSQL is... | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|        5 | MongoDB    | MongoDB is...     | 2018-01-30 12:31:03 | egoing | developer                 |
+----------+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.001 sec)

맨위의 스키마를 보면 duru라는 해당 profile이름이 변경된 것을 알 수 있다. 이렇게 서로 관계를 가지면서 데이터베이스들이 구성하고 연결되어 있는 것을 우리는 몸으로 느껴보았다.