<출처 : http://www.mysqlkorea.co.kr/sub.html?mcode=develop&scode=01&lang=k&m_no=21522&cat1=13&cat2=380&cat3=401>
MySQL 은 SELECT문 과 다중 테이블 DELETE 그리고 UPDATE문의 table_reference 부분을 위해 다음과 같이 JOINT을 지원한다:
table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor:
tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)]
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
하나의 테이블 참조는 하나의 JOIN 표현으로 알려져 있다.
table_factor 구문은 SQL 표준에 비해 확장 되었다. 후자는 한 쌍의 괄호 안의 리스트가 아닌 단지 table_reference를 받아들인다.
우리가 table_reference 아이템의 한 리스트 안의 각 콤마를 inner join과 동등하게 여긴다면 이것은 convervative extension이다. 예를 들면:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
는 아래 문장과 동등하다:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
MySQL에서, CROSS JOIN 은 INNER JOIN과 동등하다. 표준 SQL에서는 그렇지 않다. INNER JOIN은 ON 절과 함께 쓰이며 CROSS JOIN은 그렇지 않다.
5.0.1이전의 MySQL에서는 table_references안의 괄호들이 생략되고 모든 join 연산들은 왼쪽으로 그룹지어진다. 일반적으로 괄호들은 단지 inner join연산을 포함하는 join 표현 안에서 무시 될 수 있다. 5.0.1부터는 nested join이 허락된다 (7.2.10장 “nested join optimization”부분을 참조하라.
join연산 에서 좀더 많은 변경들이 5.0.12에서 이루어 졌으며 MySQL을 표준 SQL을 따르도록 했다. 이들 변경들은 이 장의 후반부에서 설명된다.
다음 리스트는 join을 쓸 때 유념 해야 할 일반적인 사실들을 설명한다.
- 테이블 참조는 tbl_name as alias_name 이나 tbl_name alias name을 사용하여 alias될 수 있다.
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
INNER JOIN과 콤마(,)는 join 조건의 결여로 의미론적으로 동등하다. 양자는 명시된 테이블 사이에서 데카르트 곱을 만들어 낸다. (즉, 첫 번째 테이블의 각각 모든 레코드는 두 번째 테이블의 각각 모든 레코드와 join된다.)
그러나 콤마 연산자의 서열은 INNSER JOIN, CROSS JOIN, LEFT JOIN등 보다 낮다. Join 조건이 있는 경우 다른 join 형들과 콤마를 혼합하면 Unknown column ‘col_name’ in ‘on clause”의 형태의 에러가 발생한다. 이 문제 처리에 관한 정보는 이 장의 후반부에 다루어진다.
- ON 조건은 WHERE 절에서 사용될 수 있는 형태의 조건 표현이다. 일반적으로 테이블들을 어떻게 join하는 지를 명시하는 조건을 위해선 ON조건을 결과 집합에서 어떤 레코드를 원하는 가에 대한 제한을 위해서는 WHERE절을 사용해야 한다.
- LEFT JOIN에서 ON이나 USING부분에서 오른쪽 테이블과 매칭 되는 레코드가 없는 경우 모든 컬럼들이 NULL로 설정된 레코드가 오른쪽 테이블을 위해 사용된다. 다른 테이블과 대응을 갖지 못하는 테이블에서 레코드를 찾기 위해 이 사실을 사용할 수 있다.
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
이 예제는 table2에 나타나지 않은 id 값을 가지고 table1에서 모든 레코드를 찾는다.( 이것은 table1에 있는 모든 레코드가 table2에 있는 어떤 것과도 일치 하지 않는다.) 이것은 table2.id가 NOT NULL로 선언 되었음을 의미한다. 7.2.9장 “LEFT JOIN과 RIGHT JOIN 최적화” 부분을 참조 하라.
USING(column_list)절은 양 테이블들에 존재해야 하는 컬럼들을 지정한다. 테이블 a와 b가 컬럼 c1, c2 그리고 c3를 포함하고 있다면 이어지는 join은 두 테이블의 컬럼들을 비교한다.
a LEFT JOIN b USING (c1,c2,c3)
- 두 테이블의
NATURAL [LEFT] JOIN은 두 테이블에 존재하는 모든 컬럼을 지정하는 USING절의 INNSER JOIN 이나 LEFT JOIN과 의미적으로 동일하도록 정의 된다. RIGHT JOIN는 LEFT JOIN과 유사하게 작용한다.LEFT JOIN. 데이터베이스 들 간에 이식 가능한 코드를 유지 하기 위해서는 RIGHT JOIN보다 LEFT JOIN을 사용하는 것이 좋다.- JOIN문 설명에서 보여지는
{ OJ ... LEFT OUTER JOIN ...}문은 ODBC와의 호환성을 위해 존재 한다. 이 문장에서 중괄호는 정확히 쓰여져야 하는데 다른 구문 표현들에서 보여지는 포괄적 의미로 사용되는 것과는 다르다. STRAIGHT_JOIN은 왼쪽 테이블이 오른쪽 테이블보다 항상 먼저 읽힌 다는 점을 제외하면 JOIN과 동일하다. JOIN optimizer가 테이블을 잘못된 순서로 놓을 경우에 사용될 수 있다.
다음은 JOIN연산을 사용하는 예문들이다:
SELECT * FROM table1, table2; SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
테이블로부터 정보를 가져올 때 MySQL이 사용할 인덱스에 대한 힌트를 제공 할 수 있다. USE INDEX (key_list)를 사용하여 MySQL이 테이블의 레코드들을 찾기 위해 가능한 인덱스들중 단지 하나만 사용하도록 할 수 있다. 선택적 구문인 IGNORE INDEX(key_list)문은 MySQL이 특정 인덱스를 사용하지 못하도록 할 수 있다. 이러한 힌트들은 EXPLAIN문이 MySQL이 인덱스들 중 잘못된 인덱스를 사용하고 있다는 것을 보여준다면 유용하다.
여러분은 FORCE INDEX 문을 사용 할 수 있는데 이것은 USE INDEX(key_list)와 같이 작용하며 테이블 스캔이 추가적이다. 즉, 테이블에서 레코드를 찾기 위해 주어진 인덱스들을 사용할 수 없는 경우에만 테이블 스캔이 사용될 수 있다.
USE INDEX, IGNORE INDEX와FORCE INDEX 문은 테이블에서 레코드를 어떻게 찾고 JOIN을 하기 위해 어떻게 해야 하는지 MySQL이 결정 할 때에 어떤 인덱스들이 사용될 것인지에 영향을 미친다. 이들은 ORDER BY 나 GROUP BY를 결정할 때 사용할 인덱스에는 영향을 주지 않는다.
USE KEY, IGNORE KEY 와 FORCE KEY 는 USE INDEX, IGNORE INDEX, FORCE INDEX 와 동일하다.
예:
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
MySQL 5.0.12에서 변경된 JOIN연산
MySQL 5.0.12에서 natural joins 과 외부 join 변수들을 포함한 USING을 사용한 join은 SQL:2003 표준에 따라 처리 된다. 그 목적은 MySQL의 구문들과 의미들을 SQL:2003에 따라 NATURAL JOIN과 JOIN… USING 을 조절하는 것이다. 그러나 이들 join연산에서의 변화들은 어떤 join들에 대해서는 다른 출력물을 결과로 내놓을 수 있다. 또한 이전 버전들에서 정확히 동작하는 것처럼 보여진 몇몇 질의들이 표준에 맞게 새롭게 씌여 져야만 한다.
이들 변화들은 다섯 가지 주요 측면을 가지고 있다:
- MySQL이 NATURAL 혹은 USING Join 연산에서 결과 컬럼을 결정하는 방법(이는 전체 FROM절의 결과이다.)
SELECT *과SELECT tbl_name.*의 선택된 컬럼 들의 리스트로의 확장.- NATURAL과 USING join에서 컬럼 이름들의 결정.
NATURAL이나USING에서JOIN ... ON. 으로의 전이- JOIN…ON에서 ON조건에 있는 컬럼 이름의 결정.
다음의 리스트는 join 연산에 관한 5.0.12에서의 변화의 몇 가지 효과에 관한 상세한 정보를 제공한다. “previously”의 의미는 “MySQL 5.0.12 이전 “ 을 의미한다.
- NATURAL 이나 USING join의 컬럼들은 이전 버전과 다를 수 있다. Redundant한 결과들은 더 이상 나타나지 않으며 SELECT *확장에서의 컬럼 순서는 전과 다를 수 있다.
다음 문장들을 고려해 보자:
CREATE TABLE t1 (i INT, j INT);CREATE TABLE t2 (k INT, j INT);INSERT INTO t1 VALUES(1,1);INSERT INTO t2 VALUES(1,1);SELECT * FROM t1 NATURAL JOIN t2;SELECT * FROM t1 JOIN t2 USING (j);
이전 버전에서 문장들은 다음 결과를 만들어 낸다.
+------+------+------+------+| i | j | k | j |+------+------+------+------+| 1 | 1 | 1 | 1 |+------+------+------+------++------+------+------+------+| i | j | k | j |+------+------+------+------+| 1 | 1 | 1 | 1 |+------+------+------+------+
처음 SELECT문에서는 컬럼 i가 양쪽 테이블들에 나타나며 표준 SQL에 따라 join 컬럼이 되는데 output에서 단지 한번만 나타 나야 한다. 두번째 SELECT문 역시 유사하게 컬럼 j가 USING절 안에서 몇 명되고 output으로 한번만 나와야 한다. 그러나 두가지 경우 모두 redundant 컬럼이 제거 되지 않는다. 또한 컬럼들의 순서도 표준 SQL과 정확히 일치 하지 않는다.
이제 결과물들은 아래와 같은 결과를 생산 한다.:
+------+------+------+| j | i | k |+------+------+------+| 1 | 1 | 1 |+------+------+------++------+------+------+| j | i | k |+------+------+------+| 1 | 1 | 1 |+------+------+------+
Redundant 컬럼이 제거 되고 컬럼 순서가 정확하게 표준 SQL을 따른다.:
- 첫번째로, 두개의 join된 테이블들의 일반 컬럼들을 첫번째 테이블의 컬럼 들 순서에 따라 합체 시킨다.
- 두번째로, 컬럼들은 첫 번째 테이블에서의 순서대로 유일하게 되어야 한다.
- 세번째로, 컬럼들은 두 번째 테이블에서의 순서대로 유일하게 되어야 한다.
두개 일반 컬럼들을 대체하는 하나의 결과 컬럼은 COALESCE연산을 통해정의 된다. 즉 t1.a와 t2.a의 단일 join 컬럼은 a로 정의 되는데 구문은
a = COALESCE(t1.a, t2.a)과 같다. where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
만일 join연산이 다른 join연산이라면 그 join의 결과 컬럼들은 join된 테이블들의 모든 컬럼의 연결로 이루어 진다. 이것은 이전 버전의 MySQL와 동일하다.
coalesced컬럼의 정의에 대한 결과는 그 컬럼이 두개의 컬럼중 하나가 항상NULL이라면 coalesced컬럼은 NULL이 아닌 컬럼의 값을 포함한다. 두 컬럼이 NULL이거나 NULL이 아닌경우, 양쪽 일반 컬럼들은 같은 값을 갖게 되고 coalesced 컬럼의 값으로 어떤 것이 선택 되었는지는 문제가 되지 않는다. 이것을 해석하는 가장 간단한 방법은 outer join의 coalesced컬럼이 JOIN의 JOIN의 inner테이블의 일반 컬럼에 의해 재 표현 된다. 다음 두개의 테이블 t1(a,b)와 t2(a,c)이 다음과 같은 값을 가지고 있다면::
t1 t2---- ----1 x 2 z2 y 3 w
그러면:
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+| a | b | c |+------+------+------+| 1 | x | NULL || 2 | y | y |+------+------+------+
a컬럼의 값은 t1.a를 포함하고 있다.
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+| a | c | b |+------+------+------+| 2 | y | y || 3 | z | NULL |+------+------+------+
여기에서 컬럼 a는 t2.a의 값을 포함 하고 있다.
이들 결과들을 다른 JOIN…ON을 가진 동등한 질의들과 비교하면
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+| a | b | a | c |+------+------+------+------+| 1 | x | NULL | NULL || 2 | y | 2 | y |+------+------+------+------+mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+| a | b | a | c |+------+------+------+------+| 2 | y | 2 | y || NULL | NULL | 3 | z |+------+------+------+------+- 이전 버전에서, USING절은 다음 컬럼들을 비교 하기 위해 ON절로 다시 쓰여 질 수 있다. 예를 들면, 다음 두 절들은 의미적으로 동일하다.:
a LEFT JOIN b USING (c1,c2,c3)a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
이제 두 절들은 더 이상 같지 않다:
- join조건을 어떤 레코드가 만족 시키는지를 결정하는 것과 관련하여 두가지 모두의 join은 의미적으로 동일하다.
- SELECT * 확장을 위해 어떤 컬럼을 display할지 결정하는 것과 관련하여 두개의 join은 의미적으로 동일 하지 않다. USING join은 ON join이 모든 테이블로부터 모든 컬럼을 선택하는데 반해, 컬럼들 중 coalesced 값을 선택한다. USING join에 선행자로 SELECT* 는 이들 값들을 선택한다:
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
ON join을 위해 SELET * 문이 이들 값들을 선택한다.
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
inner join과 함께 COALESCE(a.c1,b.c1) 는 두 컬럼 모두가 같은 값을 갖게 되므로 a.c1과 b.c1은 같게 된다. Out join 에서 두 컬럼들중 하나가 NULL이 될 수 있다. 그 컬럼은 결과에서 삭제 될 것이다.
- 다중 natural join에 대한 평가는 질의를 다시 쓰도록 요구하는 NATURAL이나 USING join의 결과에 영향을 미치는 매우 중요한 방법 에서 다르다. 예를 들어 세개의 테이블 t1(a,b), t2(c,b) 그리고 t3(a,c)가 있고 각각의 테이블은 t1(1,2), t2(10,2) 그리고 t3(7,10)으로 하나의 레코드를 갖는다고 가정하자. 또한 세개의 테이블들에서 다음과 같이 NATURAL JOIN을 갖는다고 가정해보자:
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
이전 버전의 MySQL에서는 그 문장이 nested join 인 (t1 NATURAL JOIN t2)이므로 두번째 JOIN의 연산 대상자는 t2로 인식되었다. 결과적으로 t3의 컬럼들은 t2에서 일반 컬럼들로 체크되고 t3가 t1과 함께 일반 컬럼들을 갖는다면 이들 컬럼들은 equi-join컬럼 들로 사용되지 않는다. 따라서 이전버전의 MySQL에서는 선행적인 질의가 다음 equi-join으로 변환 된다.:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;
그 JOIN은 (t1.a = t3.a) 동등 질의 속성을 빠뜨리고 있다. 결과적으로 그것이 가져야 할 empty 결과를 갖지 못하고 하나의 레코드를 생산한다. 정확한 동등 질의는 다음과 같다:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
여러분이 이전 버전과 현재 버전에서 같은 결과를 갖는 질의를 요구한다면 첫번째 동등 join으로 natural join을 다시 쓰도록 하라.
- 이전 버전에서, 컴마 연산자(,)와 JOIN 연산자 모두는 같은 우선 순위를 가졌다. 따라서 join 연산 t1,t2 JOIN t3는 ((t1,t2) JOIN t3)로 해석 되었다. 현재 버전은 JOIN이 좀더 높으며 따라서 그 SQL문은 (t1, (t2 JOIN t3))로 해석 된다. 이 변경은 ON절을 사용하는SQL문들에 영향을 주는데 그 절이 JOIN의 피 연산자 안에 있는 컬럼 들만을 참조 할 수 있고 우선순위의 변화는 그들 피 연산자의 해석에 변화를 주기 때문이다.
예:
CREATE TABLE t1 (i1 INT, j1 INT);CREATE TABLE t2 (i2 INT, j2 INT);CREATE TABLE t3 (i3 INT, j3 INT);INSERT INTO t1 VALUES(1,1);INSERT INTO t2 VALUES(1,1);INSERT INTO t3 VALUES(1,1);SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
이전 버전에서 SELECT는 t1,t2를 (t1,t2)와 같이 암시적 그룹으로 인식 하므로 문법적으로 맞다. 현재 버전에서는 JOIN이 우선순위를 가지며 ON 절에 있는 피 연산자로 t2와 t3가 된다. t1.i1이 t2와 t3에 속한 컬럼 조건에 해당 하지 않으며 이는 Unknown column ‘t1.i1’ in ‘on clause’ 에러를 만들게 된다. 그 JOIN이 처리 되도록 하기 위해서는 처음 두 테이블을 괄호로 그룹을 만들어 주어야 하며 이렇게 되면 ON절의 피 연산자는 (t1,t2)와 t3가 된다:
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
콤마 연산자의 사용을 피하고 JOIN연산을 대신 쓸 수 이다.
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
이 변경은 INNER JOIN, CROSS JOIN, LEFT JOIN, 그리고 RIGHT JOIN 와 콤마 연산자가 혼합된 SQL문에 적용 되며 이들 모두는 콤마 연산자보다 높은 우선순위를 갖는다.
- 이전 버전에서 ON 절은 오른쪽에 있는 테이블 안에 있는 컬럼 들을 참조 할 수 있었으나 현재 버전에서는 오직 피 연산자들만 참조 할 수 있다.
예:
CREATE TABLE t1 (i1 INT);CREATE TABLE t2 (i2 INT);CREATE TABLE t3 (i3 INT);SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
이전 버전에서 SELECT문은 맞는 표현이었으나 현재 버전에서는 Unknown column 'i3' in 'on clause' 에러와 함께 실패한다. i3 컬럼이 t3 안에 없기 때문인데 이는 ON절의 피 연산자가 아니다. 이 문장은 다음과 같이 다시 쓰여져야 한다.
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
- NATURAL이나 USING join들 안에서 컬럼 이름 들의 결과는 이전 버전과 다르다. FROM절의 외부에 있는 컬럼 이름들에 대해, MySQL은 이전 버전과 대비 되는 질의들의 집합을 다룬다. 즉, MySQL이 어떤 컬럼이 모호 하다는 에러를 공식적으로 발생 시킬 경우, 그 질의는 정확히 다루어 진다. 이것은 MySQL이 이제 NATURAL 이나 USING join의 일반 컬럼 들을 하나의 컬럼으로 다루기 때문이며 따라서 한 질의가 그런 컬럼 들을 참조할 경우 질의 compiler는 그것들을 모호하게 여기지 않는다.
예:
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
이전 버전에서 이 질의는 ERROR 1052 (23000): Column 'b' in where clause is ambiguous 에러를 출력한다. 현재 버전에서 질의는 다음과 같이 정확한 결과를 출력한다:
+------+------+------+| b | c | y |+------+------+------+| 4 | 2 | 3 |+------+------+------+
SQL:2003표준과 비교하여 MySQL에서의 확장은 MySQL이 NATURAL이나USING join의 common 컬럼 들을 조절 할수 있게 하였는데 이는 표준 SQL문에서는 허용되지 않는다.