更多情况下, 我们查询的数据来源于多张表, 所有有必要了解一下 MySQL 中的连接查询.
SQL 中将连接查询分成四类: 交叉连接, 内连接, 外连接和自然连接.
数据准备
student 表
- -- ----------------------------
- -- Table structure for `student`
- -- ----------------------------
- DROP TABLE IF EXISTS `student`;
- CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(10) DEFAULT NULL,
- `age` tinyint(4) DEFAULT NULL,
- `classId` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
- -- ----------------------------
- -- Records of student
- -- ----------------------------
- INSERT INTO `student` VALUES ('1', 's1', '20', '1');
- INSERT INTO `student` VALUES ('2', 's2', '22', '1');
- INSERT INTO `student` VALUES ('3', 's3', '22', '2');
- INSERT INTO `student` VALUES ('4', 's4', '22', null);
class 表
- -- ----------------------------
- -- Table structure for `class`
- -- ----------------------------
- DROP TABLE IF EXISTS `class`;
- CREATE TABLE `class` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `cname` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
- -- ----------------------------
- -- Records of class
- -- ----------------------------
- INSERT INTO `class` VALUES ('1', '一班');
- INSERT INTO `class` VALUES ('2', '二班');
- INSERT INTO `class` VALUES ('3', '');
score 表
- -- ----------------------------
- -- Table structure for `score`
- -- ----------------------------
- DROP TABLE IF EXISTS `score`;
- CREATE TABLE `score` (
- `id` int(11) DEFAULT NULL,
- `name` varchar(10) DEFAULT NULL,
- `score` decimal(4,1) DEFAULT NULL,
- `studentId` int(11) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- -- ----------------------------
- -- Records of score
- -- ----------------------------
- INSERT INTO `score` VALUES ('1', '语文', '90.0', '1');
- INSERT INTO `score` VALUES ('2', '数学', '95.0', '1');
- INSERT INTO `score` VALUES ('3', '语文', '92.0', '2');
- INSERT INTO `score` VALUES ('4', '数学', '88.0', '2');
- INSERT INTO `score` VALUES ('5', '语文', '96.0', '3');
- INSERT INTO `score` VALUES ('6', '数学', null, '3');
交叉连接
交叉连接 (CROSS JOIN) 是用左表中的每一行与右表中的每一行进行连接, 不能使用 ON 关键字. 所得到的结果将是这两个表中各行数据的所有组合, 即这两个表所有数据的笛卡尔积. 如果 A 表有 4 条记录, B 表有 3 条, 则结果有 4*3=12 条记录.
- mysql> SELECT * FROM student CROSS JOIN class;
- +----+------+-----+---------+----+-------+
- | id | name | age | classId | id | cname |
- +----+------+-----+---------+----+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 1 | s1 | 20 | 1 | 2 | 二班 |
| 1 | s1 | 20 | 1 | 3 | |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 2 | 二班 |
| 2 | s2 | 22 | 1 | 3 | |
| 3 | s3 | 22 | 2 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
| 3 | s3 | 22 | 2 | 3 | |
| 4 | s4 | 22 | NULL | 1 | 一班 |
| 4 | s4 | 22 | NULL | 2 | 二班 |
- | 4 | s4 | 22 | NULL | 3 | |
- +----+------+-----+---------+----+-------+
- 12 rows in set
如果给交叉连接加上 WHERE 关键字, 此时将返回符合条件的结果集, 这时候与内连接的执行结果一样.
- mysql> SELECT * FROM student CROSS JOIN class WHERE student.classId = class.id;
- +----+------+-----+---------+----+-------+
- | id | name | age | classId | id | cname |
- +----+------+-----+---------+----+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- +----+------+-----+---------+----+-------+
- 3 rows in set
内连接
内连接 (INNER JOIN) 是用左表中的每一行与右表中的所有记录进行匹配, 查询的结果为两个表经过 ON 条件过滤后的笛卡尔积
mysql> SELECT * FROM student INNER JOIN class ON student.classId = class.id; -- 推荐写法, INNER 可写可不写
- +----+------+-----+---------+----+-------+
- | id | name | age | classId | id | cname |
- +----+------+-----+---------+----+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- +----+------+-----+---------+----+-------+
- 3 rows in set
等价于
- mysql> SELECT * FROM student, class WHERE student.classId = class.id;
- +----+------+-----+---------+----+------+
- | id | name | age | classId | id | name |
- +----+------+-----+---------+----+------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- +----+------+-----+---------+----+------+
- 3 rows in set
可以连接多张表
- mysql> SELECT * FROM student
- -> JOIN class ON student.classId = class.id
- -> JOIN score ON student.id = score.studentId;
- +----+------+-----+---------+----+-------+----+------+-------+-----------+
- | id | name | age | classId | id | cname | id | name | score | studentId |
- +----+------+-----+---------+----+-------+----+------+-------+-----------+
| 1 | s1 | 20 | 1 | 1 | 一班 | 1 | 语文 | 90 | 1 |
| 1 | s1 | 20 | 1 | 1 | 一班 | 2 | 数学 | 95 | 1 |
| 2 | s2 | 22 | 1 | 1 | 一班 | 3 | 语文 | 92 | 2 |
| 2 | s2 | 22 | 1 | 1 | 一班 | 4 | 数学 | 88 | 2 |
| 3 | s3 | 22 | 2 | 2 | 二班 | 5 | 语文 | 96 | 3 |
| 3 | s3 | 22 | 2 | 2 | 二班 | 6 | 数学 | NULL | 3 |
- +----+------+-----+---------+----+-------+----+------+-------+-----------+
- 6 rows in set
外连接
左外连接
左外连接包含 LEFT JOIN 左表所有行, 如果左表中某行在右表没有匹配, 则结果中对应行右表的部分全部为空(NULL).
mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id; -- 或者 LEFT OUTER JOIN
- +----+------+-----+---------+------+-------+
- | id | name | age | classId | id | cname |
- +----+------+-----+---------+------+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- | 4 | s4 | 22 | NULL | NULL | NULL |
- +----+------+-----+---------+------+-------+
- 4 rows in set
右外连接
右外连接包含 RIGHT JOIN 左表所有行, 如果右表中某行在左表没有匹配, 则结果中对应行左表的部分全部为空(NULL).
mysql> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id; -- 或者 RIGHT OUTER JOIN
- +------+------+------+---------+----+-------+
- | id | name | age | classId | id | cname |
- +------+------+------+---------+----+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- | NULL | NULL | NULL | NULL | 3 | |
- +------+------+------+---------+----+-------+
- 4 rows in set
全外连接
如果左表中某行在右表没有匹配, 则结果中对应行右表的部分全部为空(NULL), 如果右表中某行在左表没有匹配, 则结果中对应行左表的部分全部为空(NULL).MySQL 不支持 FULL JOIN, 但是我们可以对左连接和右连接的结果做 UNION 操作来实现
- mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id
- -> UNION
- -> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id;
- +------+------+------+---------+------+-------+
- | id | name | age | classId | id | cname |
- +------+------+------+---------+------+-------+
| 1 | s1 | 20 | 1 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 1 | 一班 |
| 3 | s3 | 22 | 2 | 2 | 二班 |
- | 4 | s4 | 22 | NULL | NULL | NULL |
- | NULL | NULL | NULL | NULL | 3 | |
- +------+------+------+---------+------+-------+
- 5 rows in set
自然连接
自然连接无需指定连接列, SQL 会检查两个表中是否有相同名称的列, 且相同的列名称只能有一个, 自然连接基本不用.
- mysql> SELECT * FROM student NATURAL JOIN class;
- +----+------+-----+---------+-------+
- | id | name | age | classId | cname |
- +----+------+-----+---------+-------+
| 1 | s1 | 20 | 1 | 一班 |
| 2 | s2 | 22 | 1 | 二班 |
- | 3 | s3 | 22 | 2 | |
- +----+------+-----+---------+-------+
- 3 rows in set
可以看到只有一列 id 了, 因为 student 与 class 两张表中相同的 id 列自动合并了, 相当于内连接
SELECT * FROM student INNER JOIN class ON student.id = class.id
如果更改 class 表的 cname 字段名称为 name 会出现什么情况呢?
- mysql> ALTER TABLE class CHANGE cname name VARCHAR(10);
- Query OK, 3 rows affected
- Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student NATURAL JOIN class; -- 因为两张表同名字段有两个, 所有结果为空
Empty set
由于现在 student 与 class 表有两个同名的字段, 所有结果为空
MySQL 教程(四) SQL 连接查询
来源: http://www.bubuko.com/infodetail-2688292.html