mysql join

mysql에서 테이블을 연결시키는데 필요한 것이 join입니다.

join에도 여러가지 방법이 있는데 예제와 함께 살펴보겠습니다.

처음 보시는 분이라도 제 이전글과 이 글만 보시면 join을 사용할 수 있습니다.

 

먼저 데이터베이스와 테이블을 생성하겠습니다. 코드를 복사 읽고 복사 붙여넣기 하시면 됩니다.

 

CREATE DATABASE ex;

USE ex

CREATE TABLE category(
    id INT NOT NULL auto_increment,
    name VARCHAR(30) NOT NULL ,
    PRIMARY KEY(id)
);

CREATE TABLE post(
    id INT NOT NULL auto_increment,
    title VARCHAR(30) NOT NULL,
    category_id INT NOT NULL,
    content TEXT NULL,
    PRIMARY KEY(id)
);

INSERT INTO category(name) VALUES ('한식');

INSERT INTO category(name) VALUES ('중식');

INSERT INTO category(name) VALUES ('일식');

INSERT INTO post(title, category_id, content) VALUES ('김치찌개', 1, '김치찌개는 ...');

INSERT INTO post(title, category_id, content) VALUES ('된장찌개', 1, '된장찌개는 ...');

INSERT INTO post(title, category_id, content) VALUES ('짜장면', 2, '짜장면은 ...');

INSERT INTO post(title, category_id, content) VALUES ('짬뽕', 2, '짬뽕은 ...');

INSERT INTO post(title, category_id, content) VALUES ('스테이크', 4, '스테이크는 ...');

 

1. A

SELECT * FROM post LEFT JOIN category ON category_id=category.id;

(id라는 컬럼이 중복되기 때문에 테이블명.id라고 써야 됩니다.)

+----+--------------+-------------+---------------------+------+--------+
| id | title        | category_id | content             | id   | name   |
+----+--------------+-------------+---------------------+------+--------+
|  1 | 김치찌개     |           1 | 김치찌개는 ...      |    1 | 한식   |
|  2 | 된장찌개     |           1 | 된장찌개는 ...      |    1 | 한식   |
|  3 | 짜장면       |           2 | 짜장면은 ...        |    2 | 중식   |
|  4 | 짬뽕         |           2 | 짬뽕은 ...          |    2 | 중식   |
|  5 | 스테이크     |           4 | 스테이크는 ...      | NULL | NULL   |
+----+--------------+-------------+---------------------+------+--------+

 

2. B

SELECT * FROM post RIGHT JOIN category ON category_id=category.id;

+------+--------------+-------------+---------------------+----+--------+
| id   | title        | category_id | content             | id | name   |
+------+--------------+-------------+---------------------+----+--------+
|    1 | 김치찌개     |           1 | 김치찌개는 ...      |  1 | 한식   |
|    2 | 된장찌개     |           1 | 된장찌개는 ...      |  1 | 한식   |
|    3 | 짜장면       |           2 | 짜장면은 ...        |  2 | 중식   |
|    4 | 짬뽕         |           2 | 짬뽕은 ...          |  2 | 중식   |
| NULL | NULL         |        NULL | NULL                |  3 | 일식   |
+------+--------------+-------------+---------------------+----+--------+

 

3. A-B(집합 기호를 모르셔도 괜찮습니다. 위에 그림을 보시면 됩니다.)

SELECT * FROM post LEFT JOIN category ON category_id=category.id WHERE category.id is null;

+----+--------------+-------------+---------------------+------+------+
| id | title        | category_id | content             | id   | name |
+----+--------------+-------------+---------------------+------+------+
|  5 | 스테이크     |           4 | 스테이크는 ...      | NULL | NULL |
+----+--------------+-------------+---------------------+------+------+

 

4. B-A

SELECT * FROM post RIGHT JOIN category ON category_id=category.id WHERE category_id is null;

+------+-------+-------------+---------+----+--------+
| id   | title | category_id | content | id | name   |
+------+-------+-------------+---------+----+--------+
| NULL | NULL  |        NULL | NULL    |  3 | 일식   |
+------+-------+-------------+---------+----+--------+

 

5. A∩B(교집합)

SELECT * FROM post INNER JOIN category ON category_id=category.id; (INNER는 생략 가능합니다.)

+----+--------------+-------------+---------------------+----+--------+
| id | title        | category_id | content             | id | name   |
+----+--------------+-------------+---------------------+----+--------+
|  1 | 김치찌개     |           1 | 김치찌개는 ...      |  1 | 한식   |
|  2 | 된장찌개     |           1 | 된장찌개는 ...      |  1 | 한식   |
|  3 | 짜장면       |           2 | 짜장면은 ...        |  2 | 중식   |
|  4 | 짬뽕         |           2 | 짬뽕은 ...          |  2 | 중식   |
+----+--------------+-------------+---------------------+----+--------+

 

6. A∪B

mysql은 OUTER JOIN이 없다. 그래서 LEFT JOIN과 RIGHT JOIN을 이용해야 한다.

SELECT * FROM post LEFT JOIN category ON category_id=category.id

union

SELECT * FROM post RIGHT JOIN category ON category_id=category.id;

+------+--------------+-------------+---------------------+------+--------+
| id   | title        | category_id | content             | id   | name   |
+------+--------------+-------------+---------------------+------+--------+
|    1 | 김치찌개     |           1 | 김치찌개는 ...      |    1 | 한식   |
|    2 | 된장찌개     |           1 | 된장찌개는 ...      |    1 | 한식   |
|    3 | 짜장면       |           2 | 짜장면은 ...        |    2 | 중식   |
|    4 | 짬뽕         |           2 | 짬뽕은 ...          |    2 | 중식   |
|    5 | 스테이크     |           4 | 스테이크는 ...      | NULL | NULL   |
| NULL | NULL         |        NULL | NULL                |    3 | 일식   |
+------+--------------+-------------+---------------------+------+--------+

 

7. A∪B-(A∩B)

SELECT * FROM post LEFT JOIN category ON category_id=category.id WHERE category.id is null

union

SELECT * FROM post RIGHT JOIN category ON category_id=category.id WHERE category_id is null;

(3번과 4번을 union 했습니다.)

+------+--------------+-------------+---------------------+------+--------+
| id   | title        | category_id | content             | id   | name   |
+------+--------------+-------------+---------------------+------+--------+
|    5 | 스테이크     |           4 | 스테이크는 ...      | NULL | NULL   |
| NULL | NULL         |        NULL | NULL                |    3 | 일식   |
+------+--------------+-------------+---------------------+------+--------+

 

글 읽어주셔서 감사합니다

 

그림 출처 : https://developpaper.com/mysql-foundation-04-query/

MySQL foundation 04 query - Develop Paper