MySQL의 JOIN에 대한 자세한 설명-MySQL 튜토리얼-php.cn

mysql video tutorialcolumn Join에 대한 자세한 설명

MySQL의 JOIN에 대한 자세한 설명

관련 무료 학습 권장 사항: mysql video tutorial

0 index

JOIN문 실행 순서

내부/왼쪽/ RIGHT/ FULL JOIN

ON과 WHERE

1의 차이점 개요

완전한 SQL 문은 여러 절로 분할되며, 해당 절을 실행하는 동안 가상 테이블이 생성됩니다( vt ), 그러나 결과는 마지막 가상 테이블만 반환합니다. 이 아이디어에서 시작하여 JOIN 쿼리의 실행 프로세스를 이해하고 몇 가지 일반적인 질문에 답하려고 노력합니다.
이전에 다양한 JOIN의 실행 결과를 모르셨다면 아래 글을 읽어보세요

2 JOIN의 실행 순서

다음은 JOIN 쿼리의 일반적인 구조입니다

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join condition> 
        WHERE <where_condition>

로그인 후 복사

실행 순서는 다음과 같습니다 (SQL 문 One의 1번은 항상 FROM 절에서 실행됩니다.):

FROM: 왼쪽 테이블과 오른쪽 테이블의 데카르트 곱을 수행하여 첫 번째 테이블 vt1을 생성합니다. 행 수는 n*m입니다. (n은 왼쪽 테이블의 행 수, m은 오른쪽 테이블의 행 수
ON: vt1을 ON 조건에 따라 행별로 필터링하고 결과를 삽입합니다. into vt2
JOIN: 외부 행 추가, LEFT JOIN(LEFT OUTER JOIN)이 지정되면 왼쪽 테이블의 각 행이 먼저 통과되고, 그렇지 않은 행은 vt2에서는 vt2에 삽입되고 행의 나머지 필드는 NULL로 채워지며, RIGHT JOIN이 지정된 경우에도 마찬가지입니다. 지정하면 외부 행은 추가되지 않으며 위의 삽입 프로세스는 vt2=vt3이므로 INNER JOIN의 필터 조건을 ON에 배치하면 실행 결과에 차이가 없습니다. 또는 WHERE(자세한 내용은 아래에서 설명함) WHERE: vt3 필터의 조건, 조건에 맞는 행은 vt4
SELECT: vt4에서 vt5로 지정된 필드를 꺼냅니다.
다음은 예를 사용하여 위의 테이블 조인 프로세스를 소개합니다(이 예는 단지 조인 구문을 설명하기 위한 좋은 사례는 아닙니다)

사용자 정보 테이블 만들기:

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

로그인 후 복사

Create a 사용자 잔액 테이블:

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

로그인 후 복사

일부 데이터 가져오기:

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
|   1001 | x    |
|   1002 | y    |
|   1003 | z    |
|   1004 | a    |
|   1005 | b    |
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+
8 rows in set (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)

로그인 후 복사

총 8명의 사용자가 사용자 이름을 가지고 있고 4명의 사용자가 계정 잔액을 가지고 있습니다.

사용자 ID 1003으로 사용자 이름과 잔액을 가져옵니다. SQL은 다음과 같습니다

SELECT i.name, a.money 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON i.userid = a.userid 
        WHERE a.userid = 1003;

로그인 후 복사

1단계: FROM 절을 실행하여 두 테이블에 대해 데카르트 곱 연산을 수행합니다. 데카르트 곱 연산 후 두 테이블의 모든 행이 반환됩니다. 왼쪽 테이블 user_info의 조합에는 8개의 행이 있고 오른쪽 테이블 user_account에는 4개의 행이 있습니다. 생성된 가상 테이블 vt1은 8*4=32개 행입니다.

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1001 |    22 |
|   1003 | z    |   1001 |    22 |
|   1004 | a    |   1001 |    22 |
|   1005 | b    |   1001 |    22 |
|   1006 | c    |   1001 |    22 |
|   1007 | d    |   1001 |    22 |
|   1008 | e    |   1001 |    22 |
|   1001 | x    |   1002 |    30 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1002 |    30 |
|   1004 | a    |   1002 |    30 |
|   1005 | b    |   1002 |    30 |
|   1006 | c    |   1002 |    30 |
|   1007 | d    |   1002 |    30 |
|   1008 | e    |   1002 |    30 |
|   1001 | x    |   1003 |     8 |
|   1002 | y    |   1003 |     8 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   1003 |     8 |
|   1005 | b    |   1003 |     8 |
|   1006 | c    |   1003 |     8 |
|   1007 | d    |   1003 |     8 |
|   1008 | e    |   1003 |     8 |
|   1001 | x    |   1009 |    11 |
|   1002 | y    |   1009 |    11 |
|   1003 | z    |   1009 |    11 |
|   1004 | a    |   1009 |    11 |
|   1005 | b    |   1009 |    11 |
|   1006 | c    |   1009 |    11 |
|   1007 | d    |   1009 |    11 |
|   1008 | e    |   1009 |    11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

로그인 후 복사

2단계: ON 절을 실행하여 조건을 충족하지 않는 행을 필터링합니다

ON i.userid = a. userid 필터링 후 vt2는 다음과 같습니다.

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

로그인 후 복사

3단계: JOIN으로 외부 행 추가

LEFT JOIN

은 vt2에 표시되지 않는 왼쪽 테이블의 행을 vt2에 삽입하고 각 행의 나머지 필드는 NULL로 채워지면

RIGHT JOIN

Similarly이 예에서는 LEFT JOIN이 사용되므로 왼쪽 테이블 user_info
의 나머지 행이 생성된 테이블 vt3에 추가됩니다.

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

로그인 후 복사

4단계: WHERE 조건 필터 WHERE a.userid = 1003 테이블 vt4 생성:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

로그인 후 복사

5단계: SELECT

SELECT i.name, a.money vt5 생성:

+------+-------+
| name | money |
+------+-------+
| z    |     8 |
+------+-------+

로그인 후 복사

가상 테이블 vt5가 최종 결과로 클라이언트에 반환됩니다

소개 테이블 조인 과정을 마친 후 일반적으로 사용되는

JOIN

4 INNER/LEFT/RIGHT/FULL JOIN

INNER JOIN...ON...

의 차이점을 살펴보겠습니다. 서로 일치하는 왼쪽 및 오른쪽 테이블 모든 행 (위의 ON 필터링 2단계만 수행되고 외부 행 추가 3단계는 수행되지 않기 때문)

LEFT JOIN...ON...: 일부 행이 있는 경우 왼쪽 테이블의 모든 행을 반환합니다. 오른쪽 테이블에 해당하는 일치 행이 없으면 새 테이블에서 오른쪽 테이블의 열을 NULL로 설정합니다
RIGHT JOIN...ON... : 왼쪽에 일부 행이 있는 경우 오른쪽 테이블의 모든 행을 반환합니다. 테이블에 해당하는 일치 행이 없으면 새 테이블에서 왼쪽 테이블의 열을 NULL로 설정합니다.
INNER JOIN

외부 행 추가

예를 들어,

LEFT JOIN

을 INNER JOIN으로 바꾸면 이 단계를 건너뛰고 생성된 테이블 vt3은 vt2와 정확히 동일합니다.

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

로그인 후 복사

RIGHT JOINIf

LEFT JOIN

을

RIGHT JOIN

으로 바꾸면 생성된 테이블 vt3은 다음과 같습니다.

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
+--------+------+--------+-------+

로그인 후 복사

user_account(오른쪽 테이블)에는 userid=1009 행이 있지만 이 행의 레코드는 user_info( 왼쪽 테이블), 따라서 다음 행이 세 번째 단계에 삽입됩니다:

|   NULL | NULL |   1009 |    11 |

로그인 후 복사

FULL JOIN

上文引用的文章中提到了标准SQL定义的FULL JOIN，这在mysql里是不支持的，不过我们可以通过LEFT JOIN + UNION + RIGHT JOIN 来实现FULL JOIN：

SELECT * 
  FROM user_info as i 
    RIGHT JOIN user_account as a 
      ON a.userid=i.userid
union 
SELECT * 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON a.userid=i.userid;

로그인 후 복사

他会返回如下结果：

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

로그인 후 복사

ps：其实我们从语义上就能看出LEFT JOIN和RIGHT JOIN没什么差别，两者的结果差异取决于左右表的放置顺序，以下内容摘自mysql官方文档：

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

所以当你纠结使用LEFT JOIN还是RIGHT JOIN时，尽可能只使用LEFT JOIN吧

5 ON和WHERE的区别

上文把JOIN的执行顺序了解清楚之后，ON和WHERE的区别也就很好理解了。
举例说明:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

로그인 후 복사

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

로그인 후 복사

第一种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid and i.userid = 1003的行，生成表vt2，然后执行第三步JOIN子句，将外部行添加进虚拟表生成vt3即最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   NULL |  NULL |
|   1002 | y    |   NULL |  NULL |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

로그인 후 복사

而第二种情况LEFT JOIN在执行完第二步ON子句后，筛选出满足i.userid = a.userid的行，生成表vt2；再执行第三步JOIN子句添加外部行生成表vt3；然后执行第四步WHERE子句，再对vt3表进行过滤生成vt4，得的最终结果：

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

로그인 후 복사

如果将上例的LEFT JOIN替换成INNER JOIN，不论将条件过滤放到ON还是WHERE里，结果都是一样的，因为INNER JOIN不会执行第三步添加外部行

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

로그인 후 복사

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

로그인 후 복사

返回结果都是:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

로그인 후 복사