기존 테이블을 중복을 제거해서 두 테이블로 나눠서 그 테이블의 참조값만을 적어두는 것
data를 볼 때 그 행에 대한 별도의 표를 보면서 비교를 해야하는 불편함이 있지만
장점은 기존 표에서는 중복을 판별하기 어려웠던 것을 중복을 판별하기 쉬워진다.
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 | database 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 'table이름' left join 'table이름' on 'table이름'.column= 'table이름'.column;
근데 위의 표에는 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 | 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 | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
이렇게 해주면 된다. topic.id 하지 않고 그냥 id 하면 애매하다고 에러가 발생한다.
그리고 여기서 저 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 | 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 | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
| 5 | MongoDB | MongoDB is ... | 2018-01-30 12:31:03 | egoing | developer |
+----------+------------+-------------------+---------------------+--------+---------------------------+
이렇게 변경해주면 된다.