SQL 기초 - 2

create table subject(
    -> id int(11) not null auto_increment,
    -> title varchar(30) not null,
    -> description text,
    -> created datetime not null,
    -> author_id int(11) default null,
    -> primary key(id));

desc subject;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| title       | varchar(30) | NO   |     | NULL    |                |
| description | text        | YES  |     | NULL    |                |
| created     | datetime    | NO   |     | NULL    |                |
| author_id   | int         | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

create table author (
    -> id int(11) not null auto_increment,
    -> name varchar(20) not null,
    -> profile varchar(200) default null,
    -> primary key(id));

desc author;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| profile | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

insert into author (id, name, profile) values(1, 'lee', 'developer');
insert into author (id, name, profile) values(2, 'kim', 'CEO');
insert into author (id, name, profile) values(3, 'park', 'Data scientist');

select * from author
+----+------+----------------+
| id | name | profile        |
+----+------+----------------+
|  1 | lee  | developer      |
|  2 | kim  | CEO            |
|  3 | park | Data scientist |
+----+------+----------------+

insert into subject(title, description, created, author_id) values('C#', 'C# is ...', NOW(), 1);
insert into subject(title, description, created, author_id) values('HTML5', 'HTML5 is ...', NOW(), 1);
insert into subject(title, description, created, author_id) values('JAVA', 'JAVA is ...', NOW(), 2);
insert into subject(title, description, created, author_id) values('CPlus', 'CPlus is ...', NOW(), 1);
insert into subject(title, description, created, author_id) values('MySQL', 'MySQL is ...', NOW(), 3);

select * from subject;
+----+-------+--------------+---------------------+-----------+
| id | title | description  | created             | author_id |
+----+-------+--------------+---------------------+-----------+
|  1 | C#    | C# is ...    | 2022-05-24 15:41:34 |         1 |
|  2 | HTML5 | HTML5 is ... | 2022-05-24 16:02:59 |         1 |
|  3 | JAVA  | JAVA is ...  | 2022-05-24 16:03:19 |         2 |
|  4 | CPlus | CPlus is ... | 2022-05-24 16:04:01 |         1 |
|  5 | MySQL | MySQL is ... | 2022-05-24 16:04:17 |         3 |
+----+-------+--------------+---------------------+-----------+

subject 와 author 은 분리되어 있지만, 실질적으로 subject테이블의 author_id 컬럼과 author테이블의 id컬럼으로 연결되어있다. 두 테이블을 두 컬럼을 통해 연결할 수 있다.

select * from subject;
+----+-------+--------------+---------------------+-----------+
| id | title | description  | created             | author_id |
+----+-------+--------------+---------------------+-----------+
|  1 | C#    | C# is ...    | 2022-05-24 15:41:34 |         1 |
|  2 | HTML5 | HTML5 is ... | 2022-05-24 16:02:59 |         1 |
|  3 | JAVA  | JAVA is ...  | 2022-05-24 16:03:19 |         2 |
|  4 | CPlus | CPlus is ... | 2022-05-24 16:04:01 |         1 |
|  5 | MySQL | MySQL is ... | 2022-05-24 16:04:17 |         3 |
+----+-------+--------------+---------------------+-----------+

select * from author;
+----+------+----------------+
| id | name | profile        |
+----+------+----------------+
|  1 | lee  | developer      |
|  2 | kim  | CEO            |
|  3 | park | Data scientist |
+----+------+----------------+

select * from subject left join author on subject.author_id = author.id;
+----+-------+--------------+---------------------+-----------+------+------+----------------+
| id | title | description  | created             | author_id | id   | name | profile        |
+----+-------+--------------+---------------------+-----------+------+------+----------------+
|  1 | C#    | C# is ...    | 2022-05-24 15:41:34 |         1 |    1 | lee  | developer      |
|  2 | HTML5 | HTML5 is ... | 2022-05-24 16:02:59 |         1 |    1 | lee  | developer      |
|  3 | JAVA  | JAVA is ...  | 2022-05-24 16:03:19 |         2 |    2 | kim  | CEO            |
|  4 | CPlus | CPlus is ... | 2022-05-24 16:04:01 |         1 |    1 | lee  | developer      |
|  5 | MySQL | MySQL is ... | 2022-05-24 16:04:17 |         3 |    3 | park | Data scientist |
+----+-------+--------------+---------------------+-----------+------+------+----------------+

중간의 author_id와 id컬럼을 제외하고 출력하고 싶어 다음과 같이 입력하면 에러가 난다.

select id, title, description, created, name, profile from subject left join author on subject.author_id = author.id;

subject테이블과 author 테이블 모두 id라는 컬럼을 갖고있기 때문에 모호성 에러가 발생하는 것이다. 이를 해결하기 위해 id컬럼을 더욱 자세히 적어줄 필요가 있다.

select subject.id, title, description, created, name, profile from subject left join author on subject.author_id = author.id;
+----+-------+--------------+---------------------+------+----------------+
| id | title | description  | created             | name | profile        |
+----+-------+--------------+---------------------+------+----------------+
|  1 | C#    | C# is ...    | 2022-05-24 15:41:34 | lee  | developer      |
|  2 | HTML5 | HTML5 is ... | 2022-05-24 16:02:59 | lee  | developer      |
|  3 | JAVA  | JAVA is ...  | 2022-05-24 16:03:19 | kim  | CEO            |
|  4 | CPlus | CPlus is ... | 2022-05-24 16:04:01 | lee  | developer      |
|  5 | MySQL | MySQL is ... | 2022-05-24 16:04:17 | park | Data scientist |
+----+-------+--------------+---------------------+------+----------------+