189 8069 5689
下面讲讲关于MySQL子查询Subquery，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL子查询Subquery这篇文章你一定会有所受益。
成都创新互联公司是一家企业级云计算解决方案提供商,超15年IDC数据中心运营经验。主营GPU显卡服务器，站群服务器，成都服务器托管，海外高防服务器，成都机柜租用，动态拨号VPS，海外云手机，海外云服务器，海外服务器租用托管等。
子查询,是指在SQL语句中内嵌了一个SELECT查询,该SELECT称为内层查询,包含SELECT的SQL称为外层查询.其按照内层查询是否依赖于外层查询,可以分为独立子查询和相关子查询.
为了演示方便,有测试表tb1和tb2,数据如下:
mysql> SELECT * FROM tb1;
+------+
| col1 |
+------+
| 3 |
| 9 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM tb2;
+------+
| col1 |
+------+
| 2 |
| 3 |
| 8 |
+------+
3 rows in set (0.00 sec)
独立子查询,是指内层和外层查询没有关联,不用进行联合查询.和其相关的有三组关键字: ANY/ IN/ SOME, ALL和EXISTS.
ANY/ IN/ SOME的语法是:
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
a. ANY关键字的含义是,对于在子查询返回的列中的任一值,如果表达式结果为TRUE的话,则返回TRUE.
对于如下SQL,表tb1包含(3, 9), tb2包含(2, 3, 8),表达式结果为TRUE.
mysql> SELECT col1 FROM tb1 WHERE col1> ANY (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 3 |
| 9 |
+------+
2 rows in set (0.00 sec)
b.在子查询中, = ANY的别名是IN,下面两个SQL返回是相同的.
mysql>SELECT col1 FROM tb1WHERE col1 = ANY (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql>SELECT col1 FROM tb1WHERE col1 IN (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
c. ANY的别名是SOME,下面两个SQL返回是相同的.
mysql>SELECT col1 FROM tb1WHERE col1 <> ANY (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 3 |
| 9 |
+------+
2 rows in set (0.00 sec)
mysql>SELECT col1 FROM tb1WHERE col1 <> SOME (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 3 |
| 9 |
+------+
2 rows in set (0.00 sec)
ALL的语法是:
operand comparison_operator ALL (subquery)
a. ALL关键字的含义是,对于在子查询返回的列中的所有值,如果表达式结果为TRUE的话,则返回TRUE.
mysql>SELECT col1 FROM tb1WHERE col1 > ALL (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
b. <> ALL的别名是NOT IN,下面两个SQL返回是相同的.
mysql>SELECT col1 FROM tb1 WHERE col1 <> ALL (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
mysql> SELECT col1 FROM tb1WHERE col1 NOT IN (SELECT col1 FROM tb2);
+------+
| col1 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
最后一组关键字是EXISTS.
a.EXISTS的含义是,若子查询返回非空集,则EXISTS为TRUE, NOTEXISTS为FALSE.下面的SQL只是为了便于理解EXISTS,一般不这样用.
mysql>SELECT col1 FROM tb1 WHERE EXISTS (SELECT * FROMtb2);
+------+
| col1 |
+------+
| 3 |
| 9 |
+------+
rows in set (0.00 sec)
b.如下EXISTS子查询比较接近实际情况,获取表tb1和tb2中相同的记录.可以看到其内层关联了外层表,这也就是下面说的相关子查询.
mysql>SELECTcol1 FROM tb1 WHERE EXISTS (SELECT * FROM tb2 WHERE tb2.col1 = tb1.col1);
+------+
| col1 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
相关子查询,是指内层查询需要和外层查询的表相关联,进行联合查询.在上面已经看到了相关子查询的例子,仔细体会下其和独立子查询语法上的差异.
另外,如上面的SELECT col1 FROM tb1 WHEREEXISTS(SELECT * FROM tb2WHERE tb2.col1 = tb1.col1),是和再上面的SELECT col1 FROM tb1 WHERE col1 IN (SELECT col1 FROM tb2)等价的,即可将IN的独立子查询和EXISTS的相关子查询相互改写,那么两者有什么不同呢 …
在表tb2中加入一条记录(NULL),其数据如下, tb1的不变:
mysql> SELECT * FROM tb2;
+------+
| col1 |
+------+
| 2 |
| 3 |
| 8 |
| NULL |
+------+
4 rows in set (0.00 sec)
找出在表tb1,不在tb2中的记录,目测结果应为9,但返回却为空.
mysql> SELECT * FROM tb1 WHERE col1 NOTIN (SELECT col1 FROM tb2);
Empty set (0.00 sec)
为什么呢,测试如下,原来在有NULL值的情况下, NOT IN只返回NOT TRUE和NULL,即FALSE.就是在NULL的情况下, NOT IN永远不会返回结果.
mysql> SELECT 'a' NOT IN ('a', 'b',NULL);
+-----------------------------+
| 'a' NOT IN ('a', 'b', NULL) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT 'c' NOT IN ('a', 'b',NULL);
+-----------------------------+
| 'c' NOT IN ('a', 'b', NULL) |
+-----------------------------+
| NULL |
+-----------------------------+
1 row in set (0.00 sec)
若想NOT IN返回结果,要先过滤掉NULL值,这里也说明了不建议把数据存为NULL的原因.
mysql> SELECT * FROM tb1 WHERE col1 NOTIN (SELECT col1 FROM tb2 WHERE col1 IS NOT NULL);
+------+
| col1 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
使用EXISTS来写的话,由于其是相关子查询，不用特殊考虑NULL的情况.
mysql> SELECT * FROM tb1 WHERE NOTEXISTS (SELECT col1 FROM tb2 WHERE tb2.col1 = tb1.col1);
+------+
| col1 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
对于子查询的性能优化,以及改写为JOIN等,稍后会进行整理,感兴趣可关注订阅号”数据库最佳实践”(DBBestPractice).
对于以上MySQL子查询Subquery相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。