search:

MySQL - 0:n 관계의 3개 테이블 JOIN하기

05 Mar 2014

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다.

Stackoverflow URL

http://stackoverflow.com/questions/20277401/mysql-select-from-3-tables-where-2-of-them-could-be-null/20277515

SQLFiddle URL

http://www.sqlfiddle.com/#!2/2c463/1

질문

다음과 같이 상품평 테이블(reviews), 상품의 장점 테이블(pros), 상품의 단점 테이블(cons)가 존재한다.

mysql> SELECT * FROM reviews;
+----+--------------------------+
| id | content                  |
+----+--------------------------+
|  1 | content of first review  |
|  2 | content of second review |
+----+--------------------------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM pros;
+----+------------+-----------------+
| id | reviews_id | pros            |
+----+------------+-----------------+
|  1 |          1 | good service    |
|  2 |          1 | nice look       |
|  3 |          1 | not bad service |
|  4 |          2 | good service    |
|  5 |          2 | design          |
+----+------------+-----------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM cons;
+----+------------+------------+
| id | reviews_id | cons       |
+----+------------+------------+
|  1 |          1 | delays     |
|  2 |          1 | high price |
|  3 |          2 | mistakes   |
|  4 |          2 | troubles   |
+----+------------+------------+
4 rows in set (0.00 sec)

1번 상품평에 대해 다음과 같은 형식으로 출력을 하고 싶다. 1개의 상품평은 0개 이상의 장점 혹은 단점을 가질 수 있다. 즉, 장점이나 단점이 아예 없을 수도 있다.

content of first review
   + good service       - delays
   + nice look          - high cost
   + not bad price

답변

SELECT문의 결과는 2차원 배열 형식이기 때문에 위와 같은 형식으로 출력하기는 어렵다. 1개의 상품평이 0개 혹은 1개의 장점, 단점을 가진 경우는 LEFT JOIN을 이용하면 되지만, 앞의 질문은 복수 개의 장점과 복수 개의 단점을 가질 수 있으므로 LEFT JOIN을 사용하면 출력 결과도 원하는 것과 다르게 출력된다. 따라서 필자는 다음과 같이 UNION을 사용하길 추천한다.

SELECT 'content',r.content as value
FROM reviews r
WHERE r.id = 1
 
UNION
 
SELECT 'pros', p.pros
FROM reviews r LEFT JOIN pros p
  ON r.id = p.reviews_id
WHERE r.id = 1
 
UNION
 
SELECT 'cons', c.cons
FROM reviews r LEFT JOIN cons c
  ON r.id = c.reviews_id
WHERE r.id = 1;
+---------+-------------------------+
| content | value                   |
+---------+-------------------------+
| content | content of first review |
| pros    | good service            |
| pros    | nice look               |
| pros    | not bad service         |
| cons    | delays                  |
| cons    | high price              |
+---------+-------------------------+
6 rows in set (0.00 sec)

질문자가 채택한 답변 - GROUP_CONCAT() 활용

질문자가 채택한 답변은 다음과 같다.

SELECT content, p.pros, c.cons
FROM reviews
  LEFT JOIN
  (
    SELECT reviews_id, GROUP_CONCAT(pros) AS pros
    FROM pros
    GROUP BY reviews_id
  ) AS p ON p.reviews_id = reviews.id
  LEFT JOIN
  (
    SELECT reviews_id, GROUP_CONCAT(cons) AS cons
    FROM cons
    GROUP BY reviews_id
  ) AS c ON c.reviews_id = reviews.id
WHERE reviews.id = 1;
+-------------------------+----------------------------------------+-------------------+
| content                 | pros                                   | cons              |
+-------------------------+----------------------------------------+-------------------+
| content of first review | good service,nice look,not bad service | delays,high price |
+-------------------------+----------------------------------------+-------------------+
1 row in set (0.00 sec)

질문자가 원하는 출력 결과와 거의 동일한 형태로 결과를 출력한다. 단, GROUP_CONCAT()에서 반환할 수 있는 값의 길이에 제한이 있다는 점 (MySQL 설정 중 group_concat_max_len, 초기 값은102400), GROUP_CONCAT()은 SQL 표준이 아니라는 점, GROUP_CONCAT()을 사용한 앞의 SQL이 UNION에 비해 복잡하다는 점은 단점이라 생각된다. Stackoverflow의 답변을 보다 보면GROUP_CONCAT()을 이용하여 어려운 문제를 쉽게 풀 수 있는 경우가 있던 것으로 기억한다. 필자는 실무에서 GROUP_CONCAT()을 사용하지 않지만 GROUP_CONCAT()의 사용 방법과 장단점을 잘 기억하고 있으면 언제가 꼭 필요한 경우도 있을 것이라 생각된다.

“MySQL” 카테고리의 추천 글