관련 무료 학습 권장 사항: mysql video tutorial
- JOIN문 실행 순서
- 내부/왼쪽/ RIGHT/ FULL JOIN
- ON과 WHERE
완전한 SQL 문은 여러 절로 분할되며, 해당 절을 실행하는 동안 가상 테이블이 생성됩니다( vt ), 그러나 결과는 마지막 가상 테이블만 반환합니다. 이 아이디어에서 시작하여 JOIN 쿼리의 실행 프로세스를 이해하고 몇 가지 일반적인 질문에 답하려고 노력합니다.
이전에 다양한 JOIN의 실행 결과를 모르셨다면 아래 글을 읽어보세요
다음은 JOIN 쿼리의 일반적인 구조입니다
SELECT <row_list> FROM <left_table> <inner|left|right> JOIN <right_table> ON <join condition> WHERE <where_condition>
실행 순서는 다음과 같습니다 (SQL 문 One의 1번은 항상 FROM 절에서 실행됩니다.):
사용자 정보 테이블 만들기:
CREATE TABLE `user_info` ( `userid` int(11) NOT NULL, `name` varchar(255) NOT NULL, UNIQUE `userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
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;
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)
ON i.userid = a. userid 필터링 후 vt2는 다음과 같습니다.
+--------+------+--------+-------+ | userid | name | userid | money | +--------+------+--------+-------+ | 1001 | x | 1001 | 22 | | 1002 | y | 1002 | 30 | | 1003 | z | 1003 | 8 | +--------+------+--------+-------+
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 | +--------+------+--------+-------+
SELECT i.name, a.money vt5 생성:
+------+-------+ | name | money | +------+-------+ | z | 8 | +------+-------+
소개 테이블 조인 과정을 마친 후 일반적으로 사용되는
JOIN4 INNER/LEFT/RIGHT/FULL JOIN
을 INNER JOIN으로 바꾸면 이 단계를 건너뛰고 생성된 테이블 vt3은 vt2와 정확히 동일합니다. +--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
| 1001 | x | 1001 | 22 |
| 1002 | y | 1002 | 30 |
| 1003 | z | 1003 | 8 |
+--------+------+--------+-------+
RIGHT JOINIf
으로 바꾸면 생성된 테이블 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 |
上文引用的文章中提到了标准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吧
上文把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 중국어 웹사이트의 기타 관련 기사를 참조하세요!