使用学生表和成绩表来演示连接，SQL架构如下：
Create table If Not Exists student_info (id int, name varchar(255));
Create table If Not Exists student_score (id int, score int);
Truncate table student_info;
insert into student_info (id, name) values ('1', 'Wang');
insert into student_info (id, name) values ('2', 'Alice');
insert into student_info (id, name) values ('3', 'Allen');
Truncate table student_score;
insert into student_score (id, score) values ('1', '65');
insert into student_score (id, score) values ('2', '70');
insert into student_score (id, score) values ('3', '75'); |
执行连接查询的语句如下：
mysql> SELECT * FROM student_score, student_info; +------+-------+------+-------+ | id | score | id | name | +------+-------+------+-------+ | 3 | 75 | 1 | Wang | | 2 | 70 | 1 | Wang | | 1 | 65 | 1 | Wang | | 3 | 75 | 2 | Alice | | 2 | 70 | 2 | Alice | | 1 | 65 | 2 | Alice | | 3 | 75 | 3 | Allen | | 2 | 70 | 3 | Allen | | 1 | 65 | 3 | Allen | +------+-------+------+-------+ 9 rows in set (0.00 sec) |
上面的连接查询直接把多表放在了FROM后面，用逗号进行间隔，所以是内连接，与下面的语句是等效的：
mysql> SELECT * FROM student_info JOIN student_score; +------+-------+------+-------+ | id | name | id | score | +------+-------+------+-------+ | 3 | Allen | 1 | 65 | | 2 | Alice | 1 | 65 | | 1 | Wang | 1 | 65 | | 3 | Allen | 2 | 70 | | 2 | Alice | 2 | 70 | | 1 | Wang | 2 | 70 | | 3 | Allen | 3 | 75 | | 2 | Alice | 3 | 75 | | 1 | Wang | 3 | 75 | +------+-------+------+-------+ 9 rows in set (0.00 sec) |
外连接一定要on，参考：https://cloud.tencent.com/developer/ask/206061
参考：