JOIN

Table A

mysql> select * from A;
+------+------+
| idx  | col  |
+------+------+
|    1 | A    |
|    2 | A    |
|    3 | A    |
+------+------+
3 rows in set (0.00 sec)

Table B

mysql> select * from B;
+------+------+
| idx  | col  |
+------+------+
|    1 | B    |
|    4 | B    |
|    5 | B    |
+------+------+
3 rows in set (0.00 sec)

• 위와 같이 A B 두개의 테이블로 여러가지 JOIN에 대해 알아보도록 하겠다.
  • A는 1,2,3으로 구성되어있고
  • B는 1,4,5로 구성되어 있다.

INNER JOIN (A와B의 교집합)

• 두 테이블을 조인할 때 A와 B의 교집합인 부분만 출력.

SELECT a.idx,a.col,b.col
FROM A AS a INNER JOIN B AS b
ON a.idx = b.idx;

mysql> SELECT a.idx,a.col,b.col
    -> FROM A AS a INNER JOIN B AS b
    -> ON a.idx = b.idx;
+------+------+------+
| idx  | col  | col  |
+------+------+------+
|    1 | A    | B    |
+------+------+------+
1 row in set (0.04 sec)

• 조인하는 테이블의 ON절의 조건이 일치하는 결과만 출력.
  • 테이블 A의 idx와 B의 idx가 일치하는 1번 인덱스만 출력되었다.
• 이런식으로 ON절의 조건을 기준으로 테이블의 교집합을 INNER JOIN이라고 한다.

LEFT JOIN

• LEFT JOIN에는 두가지가 있으며, A와 B의 교집합을 포함 하느냐 안하느냐에 따라 나뉘어진다.

LEFT JOIN

• 두 테이블을 JOIN할 때 A와 B의 교집합을 포함한 A(왼쪽 테이블)의 데이터 전부를 출력.

SELECT a.idx,a.col,b.col
FROM A AS a LEFT JOIN B AS b
ON a.idx = b.idx;

mysql> SELECT a.idx,a.col,b.col
    -> FROM A AS a LEFT JOIN B AS b
    -> ON a.idx = b.idx;
+------+------+------+
| idx  | col  | col  |
+------+------+------+
|    1 | A    | B    |
|    2 | A    | NULL |
|    3 | A    | NULL |
+------+------+------+
3 rows in set (0.00 sec)

• A와 B의 교집합인 idx가 1인 부분을 포함하는 A의 전체 데이터가 출력되었다.

LEFT EXCLUSIVE JOIN (순수A)

• 두 테이블을 JOIN할 때 A와 B의 교집합을 포함하는 부분을 제외한 A(왼쪽 테이블)의 데이터 전체를 출력.

SELECT a.idx,a.col,b.col
FROM A AS a LEFT JOIN B AS b
ON a.idx = b.idx
WHERE b.idx IS NULL;

mysql> SELECT a.idx,a.col,b.col
    -> FROM A AS a LEFT JOIN B AS b
    -> ON a.idx = b.idx
    -> WHERE b.idx IS NULL;
+------+------+------+
| idx  | col  | col  |
+------+------+------+
|    2 | A    | NULL |
|    3 | A    | NULL |
+------+------+------+
2 rows in set (0.00 sec)

• A와 B의 교집합인 idx가 1인 부분을 제외한 A의 전체 데이터가 출력되었다.

RIGHT JOIN

• RIGHT JOIN에는 두가지가 있으며, A와 B의 교집합을 포함 하느냐 안하느냐에 따라 나뉘어진다.

RIGHT JOIN

• 두 테이블을 JOIN할 때 A와 B의 교집합을 포함한 B(오른쪽 테이블)의 데이터 전부를 출력.

SELECT b.idx,a.col,b.col
FROM A AS a RIGHT JOIN B AS b
ON a.idx = b.idx;

mysql> SELECT b.idx,a.col,b.col
    -> FROM A AS a RIGHT JOIN B AS b
    -> ON a.idx = b.idx;
+------+------+------+
| idx  | col  | col  |
+------+------+------+
|    1 | A    | B    |
|    4 | NULL | B    |
|    5 | NULL | B    |
+------+------+------+
3 rows in set (0.00 sec)

• A와 B의 교집합인 idx가 1인 부분을 포함한 B의 전체 데이터가 출력되었다.

RIGHT EXCLUSIVE JOIN (순수B)

• 두 테이블을 JOIN할 때 A와 B의 교집합을 제외하는 B(오른쪽 테이블)의 데이터 전부를 출력.

SELECT b.idx,a.col,b.col
FROM A AS a RIGHT JOIN B AS b
ON a.idx = b.idx
WHERE a.idx IS NULL;

mysql> SELECT b.idx,a.col,b.col
    -> FROM A AS a RIGHT JOIN B AS b
    -> ON a.idx = b.idx
    -> WHERE a.idx IS NULL;
+------+------+------+
| idx  | col  | col  |
+------+------+------+
|    4 | NULL | B    |
|    5 | NULL | B    |
+------+------+------+
2 rows in set (0.00 sec)

• A와 B의 교집합인 idx가 1인 부분을 제외한 B의 전체 데이터가 출력되었다.

UNION

idx	col
1	A
2	A
3	A
1	B
4	B
5	B

• UNION 연산자는 여러개의 쿼리에서 반환되는 결과를 통합해준다.
• UNION을 사용하려면 다음 조건을 만족해야 한다.
  • 모든 SELECT 구문에서 나타나는 컬럼의 수와 순서는 같아야 한다.
  • 컬럼의 데이터 타입은 같거나 호환 가능해야 한다.

(SELECT * FROM A)
UNION
(SELECT * FROM B);

mysql> (SELECT * FROM A)
    -> UNION
    -> (SELECT * FROM B);
+------+------+
| idx  | col  |
+------+------+
|    1 | A    |
|    2 | A    |
|    3 | A    |
|    1 | B    |
|    4 | B    |
|    5 | B    |
+------+------+
6 rows in set (0.01 sec)

• 위 조건을 만족하는 두개의 테이블을 위아래 방향으로 병합해서 출력해준다.

UNION DISTINCT / ALL

• 추가로 UNION 연산자는 기본적으로 DISTINCT연산자를 명시하지 않아도 UNION DISTINCT로 자동 적용 된다.
  • DISTINCT: 중복되는 값은 알아서 하나만 반환해준다.

mysql> (SELECT idx FROM A) UNION (SELECT idx FROM B);
+------+
| idx  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> (SELECT idx FROM A) UNION DISTINCT (SELECT idx FROM B);
+------+
| idx  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.01 sec)

• 위의 쿼리를 보면 UNION만 사용했을때와 UNION DISTINCT를 사용했을때 차이가 없다.

mysql> (SELECT idx FROM A) UNION ALL (SELECT idx FROM B);
+------+
| idx  |
+------+
|    1 |
|    2 |
|    3 |
|    1 |
|    4 |
|    5 |
+------+
6 rows in set (0.00 sec)

• 그러나 위처럼 UNION ALL을 붙히면 중복되는 값도 전부 반환해준다.
  • ALL은 반복인자를 처리할 필요가 없기 때문에 DISTINCT보다 빠른 처리가 가능하다고 한다.

FULL OUTER JOIN

FULL OUTER JOIN

• 전체 외부 조인, FULL OUTER JOIN이라고 부르며 왼쪽과 오른쪽 두개의 테이블에서 매칭되는 모든 레코드를 반환 한다.
• 하지만 MySQL에서는 이를 직접적으로 지원하지 않아서 LEFT JOIN과 RIGHT JOIN을 UNION 하는 형태로 간접 구현할 수 있다.

(SELECT * FROM A LEFT JOIN B ON A.idx = B.idx) 
UNION 
(SELECT * FROM A RIGHT JOIN B ON A.idx = B.idx);

mysql> (SELECT * FROM A LEFT JOIN B ON A.idx = B.idx)
    -> UNION
    -> (SELECT * FROM A RIGHT JOIN B ON A.idx = B.idx);
+------+------+------+------+
| idx  | col  | idx  | col  |
+------+------+------+------+
|    1 | A    |    1 | B    |
|    2 | A    | NULL | NULL |
|    3 | A    | NULL | NULL |
| NULL | NULL |    4 | B    |
| NULL | NULL |    5 | B    |
+------+------+------+------+
5 rows in set (0.00 sec)

FULL OUTER JOIN (교집합 제외)

• A와 B 전체에서 교집합을 뺀 상태를 출력한다.
  • 이번에도 MySQL에선 이를 지원하지 않으므로 간접적으로 구현해야 한다.

(SELECT * FROM A LEFT JOIN B ON A.idx = B.idx WHERE B.idx IS NULL) 
UNION 
(SELECT * FROM A RIGHT JOIN B ON A.idx = B.idx WHERE A.idx IS NULL);

mysql> (SELECT * FROM A LEFT JOIN B ON A.idx = B.idx WHERE B.idx IS NULL)
    -> UNION
    -> (SELECT * FROM A RIGHT JOIN B ON A.idx = B.idx WHERE A.idx IS NULL);
+------+------+------+------+
| idx  | col  | idx  | col  |
+------+------+------+------+
|    2 | A    | NULL | NULL |
|    3 | A    | NULL | NULL |
| NULL | NULL |    4 | B    |
| NULL | NULL |    5 | B    |
+------+------+------+------+
4 rows in set (0.00 sec)

CROSS JOIN

index	A.col	index	B.col
1	A	1	B
1	A	4	B
1	A	5	B
2	A	1	B
2	A	4	B
2	A	5	B
3	A	1	B
3	A	4	B
3	A	5	B

• 모든 경우의 수를 전부 표현하는 방식이다.

mysql> SELECT * FROM A CROSS JOIN B;
+------+------+------+------+
| idx  | col  | idx  | col  |
+------+------+------+------+
|    3 | A    |    1 | B    |
|    2 | A    |    1 | B    |
|    1 | A    |    1 | B    |
|    3 | A    |    4 | B    |
|    2 | A    |    4 | B    |
|    1 | A    |    4 | B    |
|    3 | A    |    5 | B    |
|    2 | A    |    5 | B    |
|    1 | A    |    5 | B    |
+------+------+------+------+
9 rows in set (0.00 sec)

• 3*3=9가지 경우의 수 모두가 출력되었다.