[MySQL] DB Join에 대해

 JOIN (조인) 연산자

 - 두개 이상의 테이블을 연결하여 데이터를 검색할때 사용한다.
 - 데이터베이스 시스템과 그 환경의 성능을 확인하는데도 사용할 수 있다. (느린 쿼리를 사용하여 비교)
 - 조인에는 여러가지 종류가 있다.

조인 종류	설명
내부조인(INNER JOIN)	조건을 사용해서 두 테이블의 레코드를 결합한다.
외부조인(OUTER JOIN)	내부조인과 비슷한데 일치하지 않는 열까지 반환하며 그 열은 NULL로 반환한다.
동등 조인(EQUI JOIN)	내부조인이다. 두 테이블 사이의 같은 행들을 반환한다.
비동등 조인(NON-EQUI JOIN)	내부조인이다. 두 테이블 사이의 같지 않은 행들을 반환한다.
자연 조인(NATURAL JOIN)	'ON' 절이 없는 내부조인. 같은 열 이름을 가진 두 테이블을 조인할때만 작동
크로스 조인(CROSS JOIN)	한 테이블의 모든 행과 다른 테이블의 모든 행이 연결되는 모든 경우를 반환.
카티젼 조인(CARTESIAN JOIN)	크로스 조인의 한 종류. 조건이 없다. (크로스 프로덕트)
콤마 조인(COMMA JOIN)	콤마가 CROSS JOIN 키워드 대신 사용된다는 점을 제외하면 크로스 조인과 같다.
셀프 조인(SELF JOIN)	자기 자신을 조인한다.

1. 내부 조인(INNER JOIN)
 - 조건을 사용하여 두 테이블의 레코드를 결합한다.
 - 동등 조인, 비동등 조인, 자연 조인 등이 있다.

?

1 2 3 4

Select somecolumns From table1          -- 반환되는 열                 Inner Join                                     table2                 ON somecondition;   -- ON 또는 WHERE 조건절

 1) 동등 조인(EQUI JOIN)
      - 두 테이블 사이의 같은 행들을 반환한다.

 예) 각 boy가 어떤 toy를 가지고 있는지 조사한다. (매핑 확인)

?

1 2 3

Select boys.boy, toys.toy From boys INNER JOIN toys ON boys.toy_id = toys.toy_id;

 2) 비동등 조인(NON-EQUI JOIN)
     -두 테이블 사이의 같지 않은 모든 행들을 반환한다.

 예)

?

1 2 3 4

Select boys.boy, toys.toy From boys INNER JOIN toys ON boys.toy_id <> toys.toy_id -- <> :  같지 않음 ORDER BY boys.boy;          -- 정렬

 3) 자연 조인(NATURAL JOIN)
     - 두 테이블에 같은 이름의 열이 있을때만 동작한다.
     - ON이 필요없다.

  예) 

?

1 2	Select boys.boy, toys.toy From boys NATURAL JOIN toys;

2. 외부 조인(OUTER JOIN)
  - 내부 조인과 유사하며 일치하는 것이 없을 경우 NULL로 표시한다.
  - 왼쪽 테이블은 FROM 바로 다음에 나오는 테이블이고, JOIN 뒤에 나오는 테이블이 오른쪽 테이블이다.
  - 왼쪽 외부 조인(LEFT OUTER JOIN)과 오른쪽 외부 조인(RIGHT OUTER JOIN)이 있다.
  - 왼쪽 외부 조인을 사용할 경우 왼쪽 테이블을 오른쪽 테이블에 비교한다. 오른쪽 외부 조인도 그 반대로 동작한다. 일대다 관계에 유용하다.

 유용한 예) girls가 어느 toys를 가지고 있는지 알아보도록 쿼리를 작성한다.

  i) 왼쪽 테이블을 오른쪽 테이블에 비교하는 방법 

?

1 2 3

Select g.girl, t.toy From girls g Left Outer Join toys t ON g.toy_id = t.toy_id;

  ii) 반대로 했을때

?

1 2 3

Select g.girl, t.toy From toys t Left Outer Join girls g ON g.toy_id = t.toy_id;

3. 크로스 조인(CROSS JOIN)
  - 한 테이블의 모든 행과 다른 테이블의 모든 행을 짝지워 반환한다.
  - 카티전 조인, 카티전 프로덕트 등이 있다.

 예)

?

1 2	Select t.toy, b.boy From toys AS t CROSS JOIN boys AS b;

4. 셀프 조인(SELF JOIN)
  - 자기 자신을 조인한다.
  - 자기 자신을 하나씩 비교하기 위해 사용한다.
  - 하나의 테이블로 같은 정보를 가진 테이블이 두 개 있는 것처럼 쿼리를 보낼 수 있다.

  예) 각 사람의 이름(name)과 그 사람을 담당하는 보스(boss)의 이름을 가져온다. 

?

1 2 3 4

Select c1.name, c2.name AS boss From clown_info c1 Inner Join clown_info c2 ON c1.boss_id = c2.id;

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다. Stackoverflow에 올린 답변 중 한국에 있는 다른 개발자들에게도 도움이 될만한 Q&A를 보기 쉽게 정리했습니다. 가능한 경우는 SQLFiddle에 샘플 데이터도 같이 올려서 실습도 해 볼 수 있도록 하였습니다. 또한 전체 Q&A를 묶어서 PDF 파일로도 배포하고 있습니다. 방문해 주시는 많은 분들에게 도움이 되었으면 좋겠습니다.

Stackoverflow URL

http://stackoverflow.com/questions/20206488/fetching-data-from-three-different-tables-but-it-is-duplicating-records/20206570

질문

아래 SELECT문과 같이 3개의 테이블을 JOIN하고 있다. 테이블들의 관계가 1:n이기 때문에 레코드가 출력되는데, 중복을 제거할 수 있는 방법은 없는가?

SELECT c.categoriestype, s.SubCatName,
  p.productname, p.productprice, p.id, p.productimage,
  p.productthumbnail
FROM tbl_ProCategories c, tbl_ProSubCategories s, tbl_products p
WHERE p.subcat_id = s.cat_id
  AND p.cat_id = c.id
  AND c.id = s.cat_id
LIMIT 0 , 30

답변

다음과 같이 DISTINCT를 붙여서 쉽게 중복을 제거할 수 있다.

SELECT DISTINCT c.categoriestype, s.SubCatName,
  p.productname, p.productprice, p.id, p.productimage, p.productthumbnail
FROM tbl_ProCategories c, tbl_ProSubCategories s, tbl_products p
WHERE p.subcat_id = s.cat_id
  AND p.cat_id = c.id
  AND c.id = s.cat_id
LIMIT 0 , 30

DISTINCT가 함수인 줄 알고 DISTINCT(col)과 같이 함수처럼 사용하는 경우가 있는데 엄밀히 말하면 DISTINCT는 함수가 아니다. 중복을 제거할 컬럼이 1개인 경우는 함수 표현처럼 사용해도 에러가발생하지 않는다. 다음 예를 보자

mysql> SELECT * FROM test;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    1 |
|    2 |    2 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT(a) FROM test;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

DISTINCT(a) 처럼 함수 형식으로 사용해도 중복 제거가 잘 되었다. 하지만 a, b 컬럼을 동시에 중복 제거하는 경우 다음과 같이 DISTINCT(a, b)를 입력하면 에러가 발생하게 된다.

mysql> SELECT DISTINCT(a, b) FROM test;
ERROR 1241 (21000): Operand should contain 1 column(s)

DISTINCT를 함수라고 생각하기 보다는 "SELECT DISTINCT"를 묶어서 중복을 제거하는 SELECT라고 인식하는 것이 좋다. 다음과 같이 사용하면 복수 개의 컬럼에 대해서도 중복을 제거할 수 있다.

mysql> SELECT DISTINCT a, b FROM test;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

참고로 MySQL 매뉴얼에 따르면 우리가 평소 무심코 사용하는 SELECT는 사실 "SELECT ALL"과 같이 "ALL"이 생략된 것이다. 따라서 다음과 같은 질의도 사용 가능하다. "SELECT ALL"을 생각해본다면 "SELECT DISTINCT"의 사용법도 이해가 될 것이다.

mysql> SELECT ALL a, b FROM test;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    1 |
|    2 |    2 |
+------+------+
3 rows in set (0.00 sec)

출처: http://ellehu.com/mysql/3235