[생활코딩] 4일차 - 4. JOIN

https://youtu.be/LeTeb3ImxI0

1. RENAME : 기존 테이블(topic) 이름을 topic_backup으로 변경

RENAME TABLE topic TO topic_backup;
SHOW TABLES;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| topic_backup            |
+-------------------------+

 

2. 테이블 topic, author을 신규 생성

- topic 테이블의 author_id와 author 테이블의 id로 연결할 예정

 

2-1. topic 테이블 생성

CREATE TABLE topic(
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(30) NOT NULL,
    -> description TEXT NULL,
    -> created DATETIME NOT NULL,
    -> author_id INT(11) NULL,
    -> PRIMARY KEY(id)
    -> );
    DESC topic;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| title       | varchar(30) | NO   |     | NULL    |                |
| description | text        | YES  |     | NULL    |                |
| created     | datetime    | NO   |     | NULL    |                |
| author_id   | int(11)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

 

2-2. author 테이블 생성

CREATE TABLE author(
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> profile VARCHAR(200) NULL,
    -> PRIMARY KEY(id)
    -> );
DESC author;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| profile | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

3. topic 테이블과 author 테이블에 값 입력

결과)

SELECT * FROM topic;
+----+------------+-----------------------+---------------------+-----------+
| id | title      | description           | created             | author_id |
+----+------------+-----------------------+---------------------+-----------+
|  1 | My SQL     | MySQL is...           | 2018-01-01 12:10:11 |         1 |
|  2 | Oracle     | Oracle is ...         | 2018-01-03 13:01:10 |         1 |
|  3 | SQL Server | SQL SQL Server is ... | 2018-01-20 11:01:10 |         2 |
|  4 | PostgreSQL | PostgreSQL is ...     | 2018-01-23 01:03:03 |         3 |
|  5 | MongoDB    | MongoDB is ...        | 2018-01-30 12:31:03 |         1 |
+----+------------+-----------------------+---------------------+-----------+
SELET * FROM author;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELET * FROM author' at line 1
MariaDB [opentutorials]> SELECT * FROM author;
+----+--------+---------------------------+
| id | name   | profile                   |
+----+--------+---------------------------+
|  1 | egoing | developer                 |
|  2 | duru   | data administrator        |
|  3 | taeho  | data scientist, developer |
+----+--------+---------------------------+

 

4. JOIN : topic 테이블과 author 테이블을 연결

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
+----+------------+-----------------------+---------------------+-----------+------+--------+---------------------------+
| id | title      | description           | created             | author_id | id   | name   | profile                   |
+----+------------+-----------------------+---------------------+-----------+------+--------+---------------------------+
|  1 | My SQL     | 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 SQL Server is ... | 2018-01-20 11:01:10 |         2 |    2 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL 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                 |
+----+------------+-----------------------+---------------------+-----------+------+--------+---------------------------+

SELECT * FROM topic LEFT JOIN author

: topic 테이블과 author 테이블을 연결

 

ON topic.author_id = author.id

: 기준 !! topic 테이블의 author_id와 author 테이블의 id가 동일

 

5. author_id, id는 안나오게 하고 싶을 때 : 보이는 열을 지정

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 | My SQL     | 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 SQL Server is ... | 2018-01-20 11:01:10 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ...     | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...        | 2018-01-30 12:31:03 | egoing | developer                 |
+----+------------+-----------------------+---------------------+--------+---------------------------+

 

5-1. 주의 ! topic.id가 아닌 id로 입력하면 is ambiguous 에러

SELECT id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous

id라는 column이 2개 있어서 sql 입장에서 어느 id를 표시해야할지 ambiguous한 것

따라서 topic이라는 테이블의 id 라고 입력 (topic.id)

 

5-2. AS : topic.id 열을 topic_id로 바꾸고 싶을 때

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 | My SQL     | 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 SQL Server is ... | 2018-01-20 11:01:10 | duru   | data administrator        |
|        4 | PostgreSQL | PostgreSQL is ...     | 2018-01-23 01:03:03 | taeho  | data scientist, developer |
|        5 | MongoDB    | MongoDB is ...        | 2018-01-30 12:31:03 | egoing | developer                 |
+----------+------------+-----------------------+---------------------+--------+---------------------------+