MySQL子查询改写为表连接

子查询如递归函数一样,有时侯能达到事半功倍的效果,但是其执行效率较低。与表连接相比,子查询比较灵活,方便,形式多样,适合作为查询的筛选条件,而表连接更适合查看多表的数据。

一般情况下,子查询会产生笛卡儿积,表连接的效率要高于子查询。因此在编写 SQL 语句时应尽量使用连接查询。

我们在《MySQL子查询》一节介绍表连接(内连接和外连接等)都可以用子查询替换,但反过来却不一定,有的子查询不能用表连接来替换。下面我们介绍哪些子查询的查询命令可以改写为表连接。

在检查那些倾向于编写成子查询的查询语句时,可以考虑将子查询替换为表连接,看看连接的效率是不是比子查询更好些。同样,如果某条使用子查询的 SELECT 语句需要花费很长时间才能执行完毕,那么可以尝试把它改写为表连接,看看执行效果是否有所改善。

下面讨论具体该如何做。

1. 改写用来查询匹配值的子查询

下面这条示例语句包含一个子查询,它会把 score 表里的考试成绩查询出来:

SELECT * FROM score
WHERE grade_id IN (SELECT id FROM grade WHERE category = 'Java');

在编写以上语句时,可以不使用子查询,而是把它转换为一个简单的连接:

SELECT score.* FROM score INNER JOIN grade
ON score.grade_id = grade.id WHERE grade.category = 'Java';

再来看另一个示例。下面这条查询语句可以把所有女生的考试成绩查询出来:

SELECT * from score
WHERE student_id IN (SELECT student_id FROM student WHERE sex = 'F') ;

这条语句可以转换为以下连接:

SELECT score.* FROM score INNER JOIN student
ON score.student_id = student.student_id WHERE student.sex = 'F' ;

我们可以发现这些子查询语句都遵从这样一种形式:

SELECT * FROM table1
WHERE column1 IN (SELECT column2a FROM table2 WHERE column2b = value);

其中,column1 代表 table1 中的字段,column2a 和 column2b 代表 table2 表中的字段。这类查询都可以被转换为下面这种形式的连接查询:

SELECT table1. * FROM table1 INNER JOIN table2
ON table1. column1 = table. column2a WHERE table2. column2b = value;

在某些场合,子查询和关联查询可能会返回不同的结果。比如,当 table2 包含 column2a 的多个实例时,就会发生这种情况。这种形式的子查询只会为每个 column2a 值生成一个实例,而连接操作会为所有值生成实例,并且其输出会包含重复行。如果想要防止这种重复记录出现,就要在编写连接查询语句时使用 SELECT DISTINCT,而不能使用 SELECT。

2. 改写用来查询非匹配(缺失)值的子查询

另一种常见的子查询语句类型是:把存在于某个表里,但在另一个表里并不存在的那些值查找出来。“哪些值不存在”有关的问题通常都可以用 LEFT JOIN 来解决。

如下语句用来测试哪些学生没有出现在 absence 表里(用于查找全勤学生):

SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence) ;

以上查询语句可以使用 LEFT JOIN 来改写:

SELECT student.* FROM student LEFT JOIN absence
ON student.student_id = absence.student_id WHERE absence.student_ id IS NULL;

通常情况下,如果子查询语句符合如下所示的形式:

SELECT * FROM table1
WHERE column1 NOT IN ( SELECT column2 FROM table2) ;

那么可以把它改写为下面这样的连接查询:

SELECT table1.* FROM table1 LEFT JOIN table2
ON table1.column1 = table2.column2 WHERE table2.column2 IS NULL;

这里需要假设 table2.column2 被定义成了 NOT NULL 的。

与 LEFT JOIN 相比,子查询更加直观。大部分人都可以毫无困难地理解“没被包含在…里面”的含义,因为它不是数据库编程技术带来的新概念。而“左连接”有所不同,很难用自然语言直观地描述出它的含义。

原创文章,作者:奋斗,如若转载,请注明出处:https://blog.ytso.com/24100.html

(0)
上一篇 2021年7月20日
下一篇 2021年7月20日

相关推荐

发表回复

登录后才能评论