mySql / JOIN / AS

JOIN

mysql> SELECT * FROM 테이블1 LEFT JOIN 테이블2 ON 비교값1 = 비교값2;

 

예시

mysql> SELECT * FROM contacts;
+----+---------+------------+-----------+
| id | name    | number     | job       |
+----+---------+------------+-----------+
|  1 | jinhwan | 1012341234 | student   |
|  2 | jiwoo   | 1012341334 | developer |
|  3 | minsoo  | 1012341224 | developer |
|  4 | dami    | 1032322323 | singer    |
|  5 | jisoo   | 1012223324 | model     |
|  6 | sangsoo | 1012223444 | banker    |
|  7 | seulgi  | 1013333344 | singer    |
+----+---------+------------+-----------+

mysql> SELECT * FROM id_job;
+----+-----------+
| id | name_job  |
+----+-----------+
|  1 | singer    |
|  2 | student   |
|  3 | developer |
|  4 | model     |
|  5 | banker    |
+----+-----------+

  - id_job의 name_job과 contacts의 job을 비교해서 같은 값을 갖는 데이터를 붙임  

mysql> SELECT * FROM contacts LEFT JOIN id_job ON contacts.job = id_job.name_job;
+----+---------+------------+-----------+------+-----------+
| id | name    | number     | job       | id   | name_job  |
+----+---------+------------+-----------+------+-----------+
|  1 | jinhwan | 1012341234 | student   |    2 | student   |
|  2 | jiwoo   | 1012341334 | developer |    3 | developer |
|  3 | minsoo  | 1012341224 | developer |    3 | developer |
|  4 | dami    | 1032322323 | singer    |    1 | singer    |
|  5 | jisoo   | 1012223324 | model     |    4 | model     |
|  6 | sangsoo | 1012223444 | banker    |    5 | banker    |
|  7 | seulgi  | 1013333344 | singer    |    1 | singer    |
+----+---------+------------+-----------+------+-----------+

  - 레이블을 지정하여 필요한 레이블만 출력

mysql> SELECT contacts.id,name,id_job.id FROM contacts LEFT JOIN id_job ON contacts.job = id_job.name_job;
+----+---------+------+
| id | name    | id   |
+----+---------+------+
|  1 | jinhwan |    2 |
|  2 | jiwoo   |    3 |
|  3 | minsoo  |    3 |
|  4 | dami    |    1 |
|  5 | jisoo   |    4 |
|  6 | sangsoo |    5 |
|  7 | seulgi  |    1 |
+----+---------+------+

  - 왼쪽의 id는 주소록 index, 오른쪽의 id는 직업 키값을 의미하는데 id라는 속성이 겹치므로 보기 불편하다.

 

 

AS

 

  - 속성 값을 별칭으로 처리 

mysql> SELECT contacts.id,name,id_job.id AS job_id FROM contacts LEFT JOIN id_job ON contacts.job = id_job.name_job;
+----+---------+--------+
| id | name    | job_id |
+----+---------+--------+
|  1 | jinhwan |      3 |
|  2 | jiwoo   |      3 |
|  3 | minsoo  |      3 |
|  4 | dami    |      1 |
|  5 | jisoo   |      4 |
|  6 | sangsoo |      5 |
|  7 | seulgi  |      1 |
+----+---------+--------+

   id_job.id AS job_id로 id가 아닌 job_id로 처리