[sql] SQL JOIN-WHERE 절과 ON 절

이것은 매우 일반적인 질문이므로이 답변은 내가 작성한 이 기사를 기반으로 합니다 .

테이블 관계

우리는 다음 post과 post_comment테이블을 고려하십시오 .

는 post다음과 같은 기록이있다 :

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

그리고 post_comment다음 세 행이 있습니다.

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

SQL 내부 가입

SQL JOIN 절을 사용하면 다른 테이블에 속하는 행을 연관시킬 수 있습니다. 예를 들어, CROSS JOIN 은 두 조인 테이블 사이에 가능한 모든 행 조합을 포함하는 카티 전 곱을 생성합니다.

CROSS JOIN은 특정 시나리오에서 유용하지만 대부분 특정 조건에 따라 테이블을 조인하려고합니다. 그리고 바로 INNER JOIN이 시작됩니다.

SQL INNER JOIN을 사용하면 ON 절을 통해 지정된 조건에 따라 두 테이블을 조인 한 데카르트 곱을 필터링 할 수 있습니다.

SQL INNER JOIN-ON “항상 참”조건

“항상 참”조건을 제공하면 INNER JOIN은 결합 된 레코드를 필터링하지 않으며 결과 세트에는 두 개의 결합 테이블의 카티 전 곱이 포함됩니다.

예를 들어 다음과 같은 SQL INNER JOIN 쿼리를 실행하면

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1

우리는 모든 조합 post과 post_comment기록을 얻을 것입니다 :

| p.id    | pc.id      |
|---------|------------|
| 1       | 1          |
| 1       | 2          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
| 2       | 3          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |

따라서 ON 절 조건이 “항상 true”인 경우 INNER JOIN은 CROSS JOIN 쿼리와 동일합니다.

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id

SQL INNER JOIN-ON “항상 거짓”조건

반면 ON 절 조건이 “always false”이면 결합 된 모든 레코드가 필터링되고 결과 집합이 비어있게됩니다.

따라서 다음 SQL INNER JOIN 쿼리를 실행하면

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id

결과가 다시 나타나지 않습니다.

| p.id    | pc.id      |
|---------|------------|

위의 쿼리는 다음 CROSS JOIN 쿼리와 동일하기 때문입니다.

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id

외래 키 및 기본 키 열을 사용하는 SQL INNER JOIN-ON 절

가장 일반적인 ON 절 조건은 다음 쿼리와 같이 자식 테이블의 외래 키 열과 부모 테이블의 기본 키 열을 일치시키는 조건입니다.

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id

위의 SQL INNER JOIN 쿼리를 실행하면 다음과 같은 결과 집합이 나타납니다.

| p.id    | pc.post_id | pc.id      | p.title    | pc.review |
|---------|------------|------------|------------|-----------|
| 1       | 1          | 1          | Java       | Good      |
| 1       | 1          | 2          | Java       | Excellent |
| 2       | 2          | 3          | Hibernate  | Awesome   |

따라서 ON 절 조건과 일치하는 레코드 만 쿼리 결과 집합에 포함됩니다. 이 경우 결과 집합에는 모든 레코드 post와 post_comment레코드가 포함됩니다. post아무 관련 한 행은 post_comment그들이 ON 절 조건을 충족 할 수 없기 때문에 제외됩니다.

위의 SQL INNER JOIN 쿼리는 다음 CROSS JOIN 쿼리와 동일합니다.

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id

타격되지 않은 행은 WHERE 절을 만족시키는 행이며 이러한 레코드 만 결과 집합에 포함됩니다. INNER JOIN 절의 작동 방식을 시각화하는 가장 좋은 방법입니다.

| p.id | pc.post_id | pc.id | p.title | pc.review |
| ------ | ------------ | ------- | ----------- | ----------- -|
| 1 | 1 | 1 | 자바 | 좋은 |
| 1 | 1 | 2 | 자바 | 우수 |
| 1 | 2 | 3 | 자바 | 대박 | 
| 2 | 1 | 1 | 최대 절전 모드 | 좋은 | 
| 2 | 1 | 2 | 최대 절전 모드 | 우수 |
| 2 | 2 | 3 | 최대 절전 모드 | 대박 |
| 3 | 1 | 1 | JPA | 좋은 | 
| 3 | 1 | 2 | JPA | 우수 | 
| 3 | 2 | 3 | JPA | 대박 |

결론

INNER JOIN 문은 INNER JOIN 조회의 ON 절에서 사용한 것과 동일한 조건과 일치하는 WHERE 절을 사용하여 CROSS JOIN으로 다시 작성할 수 있습니다.

이것은 외부 조인이 아닌 내부 조인에만 적용되는 것은 아닙니다.

답변

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K'
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)