同时查询多张表，比如：

SELECT * FROM t1, t2;

虽然通过前面的子查询也可以同时查询多张表，但子查询的本质仍然是外层查询，子查询的结果只是被当作中间结果来使用。

在没有过滤条件的情况下，连接查询的结果是笛卡儿积，也就是一个表中的记录与另一个表中的记录两两组合。

上面的连接查询还可以有下面的写法：

SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
SELECT m1, n1, m2, n2 FROM t1, t2;
SELECT t1.*, t2.* FROM t1, t2;

连接查询还可以增加过滤条件，比如：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

上面的语句指定了3个过滤条件：

  • t1.m1 > 1
  • t1.m1 = t2.m2
  • t2.n2 < 'd'

这条语句的查询流程大致如下：

  • 首先确定处一个要查询的表，称为驱动表。假设使用t1作为驱动表，则先从t1中查找出满足 t1.m1 > 1的记录。
  • 针对满足 t1.m1 > 1 的每条记录，到t2表中查找匹配的记录。这里t2称为被驱动表。由于t1.m2 > 1的记录一共有两条，所以要查询两次t2表。
  • 每次查询t2表时，判断 t1.m1 = t2.m2 和 t2.n2 <'d' 两个条件，找到符合的行后，与t1中的行进行组合。
  • 两表连接查询，驱动表只需要查找一次，而被驱动表可能会被查询多次。

内连接和外连接

以下面的连接查询为例：

SELECT student_info.number, name, major, subject, score FROM student_info, student_score
->    WHERE student_info.number = student_score.number;

上面的连接查询，只有当student_socre中存在student_info.number时，才会显示结果。如果想查看全部学生的成绩，即使连缺考的同学也展示出来，则需要使用外连接。

  • 内连接，如果驱动表中的记录在被驱动表中找不到，则该记录不会加入到最终的结果集中。之前的连接都是内连接。
  • 外连接，即使驱动表中的记录在被驱动表中找不到，也仍然会加入到结果集中。

为了应对外连接的过滤条件，在WHERE子句之外，又引入了ON子句：

  • WHERE子句中的过滤条件
    • 不论是内连接还是外连接，凡是不符合WHERE子句中过滤条件的记录都不会加入到最终的结果集中。
  • ON子句中的过滤条件
    • 只在外连接中使用，内连接的ON和WHERE等价。对于外连接，如果驱动表中的记录无法在被驱动表中找到，那么该记录仍然会被加入到结果集中，对应的被驱动表记录会以NULL值填充。

连接语法

根据驱动表的不同，外连接可以细分为左（外）连接和右（外）连接。


左（外）连接语法：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 过滤条件 [WHERE 过滤条件];

右（外）连接语法：

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

对于左连接来说，LEFT JOIN左侧的是驱动表，右侧的是被驱动表。右连接与之相反。


外连接查询示例，这里把全部学生的成绩都查询出来，即使缺考的考生也应该被放到结果集合中：

SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN
student_score ON student_info.number = student_score.number;


内连接语法：

SELECT * FROM t1 [INNER | CORSS] JOIN t2 [ON 过滤条件] [WHERE 过滤条件];

除此外，直接把多个表放在FROM后面也是内连接，所以内连接有以下几种写法：

SELECT * FROM t1, t2;
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的过滤条件时，内连接不会把驱动表中的记录加入到结果集中，而外连接会。

由于内连接中的ON子句和WHERE子句是等价的，所以不要求强制写ON子句。

多表连接

示例：

SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.m1 = t2.m2 and t1.m1 = t3.m3;
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 INNER JOIN t3 ON t1.m1 = t3.m3;

不论是内连接还是外连接，两表连接产生的笛卡尔积肯定是一样的。对于内连接来说，凡是不符合ON子句或WHERE子句的记录会被过滤掉，相当于从两表连接的笛卡儿积中把不符合过滤条件的结果过滤掉，所以驱动表和被驱动表是否互换不影响内连接的查询结果。

但是对于外连接来说，即使驱动表中的记录在被驱动表中找不到符合ON子句连接条件的记录，也会被加入结果集。所以驱动表和被驱动表不能轻易互换，也就是要区分左连接和右连接。

执行连接查询的过程并不是先生成完整的笛卡尔积再过滤，上面的描述只是为了说明左右连接的区别。

表的别名

列的别名示例：

SELECT number AS xuehao FROM student_info;
SELECT number AS xuehao FROM student_info ORDER BY xuehao DESC;

表的别名示例：

SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1
INNER JOIN student_score AS s2 WHERE s1.number = s2.number;

自连接

错误示例：

SELECT * FROM t1, t1;

正确示例：

SELECT * FROM t1 AS table1, t1 AS table2;

现实示例：查找与狗哥专业相同的学生有哪些

SELECT s2.number, s2.name, s2.jajor FROM student_info AS s1 INNER JOIN student_info AS s2 
WHERE s1.major = s2.major AND s1.name = '狗哥';

上面这个示例等效于下面这两条语句：

SELECT major FROM student_info AS s1 WHERE s1.name = '狗哥';
SELECT number, name, major FROM student_info AS s1 WHERE '计算机科学与工程' = s2.major;

连接查询与子查询的替换

有的查询需求既可以使用连接查询解决，也可以使用子查询解决，比如：

SELECT s2.* FROM stduent_info AS s1 INNER JOIN student_score AS s2 
WHERE s1.number = s22.number AND s1.major = '计算机科学与工程';

可以替换为包含子查询的语句：

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');

MySQL服务器在内部可能会将子查询转换为连接查询来处理。















  • 无标签