>  기사  >  데이터 베이스  >  MySQL의 JOIN에 대한 자세한 설명

MySQL의 JOIN에 대한 자세한 설명

coldplay.xixi
coldplay.xixi앞으로
2020-12-04 14:45:561753검색

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

MySQL의 JOIN에 대한 자세한 설명

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

0 index

  • JOIN문 실행 순서
  • 내부/왼쪽/ RIGHT/ FULL JOIN
  • ONWHERE

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로 지정된 필드를 꺼냅니다.
  • 다음은 예를 사용하여 위의 테이블 조인 프로세스를 소개합니다(이 예는 단지 조인 구문을 설명하기 위한 좋은 사례는 아닙니다)
  • 3 예를 들어

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

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 JOINRIGHT 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 |
+--------+------+--------+-------+

想了解更多编程学习,敬请关注php培训栏目!

위 내용은 MySQL의 JOIN에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 jianshu.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제