子查询

2020-11-16

优点：

  • 它们允许结构化的查询，以便可以隔离语句的每个部分。
  • 它们提供了执行操作的替代方法，否则将需要复杂的联接和联合。
  • 子查询比复杂的联接或联合更易读

限制：

1、不能修改表并在子查询中从同一表中选择。例如

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

如果对于修改后的表，您正在使用派生表，并且该派生表已实现，而不是合并到外部查询中，则上述禁止条件不适用

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...)

2、LIMIT在某些子查询运算符的子查询中不支持

mysql> SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

可用关键字：

关键字：[ANY | IN | SOME | ALL | EXISTS | NOT EXISTS]

运算符：=  >  <  >=  <=  <>  !=

ANY:必须遵循一个比较操作符，如果ANY列的子查询返回的值有任一值与子查询前的值比较为TRUE，则返回对应结果;SOME为ANY的同义词，比较少用

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

ALL：必须遵循一个比较操作符，如果ALL列的子查询返回的所值与子查询前的值比较都为TRUE，则返回对应结果

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

EXISTS:子查询的结果不为空，则表达式为TRUE，并返回对应结果

SELECT s1 FROM t1 WHERE EXISTS (SELECT s1 FROM t2);

NOT EXISTS:子查询的结果为空，则表达式为TRUE，并返回对应结果

SELECT s1 FROM t1 WHERE NOT EXISTS (SELECT s1 FROM t2);

IN与= ANY：两种查询方式结果相同，但是 IN可以使用表达式列表，但 = ANY不能

-- 查询结果相同
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

-- 查询结果相同
SELECT s1 FROM t1 WHERE (s1,s2) = ANY (SELECT s1,s2 FROM t2);
SELECT s1 FROM t1 WHERE (s1,s2) IN (SELECT s1,s2 FROM t2);

-- 可以执行
SELECT * FROM ce_view_user_fee WHERE ( table_name, fee_id ) in (( 'ectrust', 39 ),( 'ectrust', 4011 ));
-- 无法执行
SELECT * FROM ce_view_user_fee WHERE ( table_name, fee_id ) = ANY (( 'ectrust', 39 ),( 'ectrust', 4011 ));

NOT IN与<> ALL：两种查询方式结果相同

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE (s1,s2) <> ALL (SELECT s1,s2 FROM t2);
SELECT s1 FROM t1 WHERE (s1,s2) NOT IN (SELECT s1,s2 FROM t2);

行子查询：

行子查询是一种子查询变体，它返回单个行，因此可以返回多个列值。行子查询比较运算符：=  >  <  >=  <=  <>  !=  <=>

示例：

  • 子查询将返回表t2中id = 10的单行，如果t1表任何行中的col1和col2值等于此行的col3和col4值，则WHERE表达式为TRUE并返回这些行，反之WHERE表达式为FALSE返回空结果集
  • 如果子查询产生多行，则会发生错误，如果子查询不产生任何行，该表达式为NUll
  • 表达式(col1,col2)和ROWROW(col1,col2)也称为行构造器。两者是等效的。行构造函数和子查询返回的行必须包含相同数量的值。
SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

一下语句的语义是等效的（并且优化器会以相同的方式处理）

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

相关子查询：

相关子查询包含对同样出现在外部查询中的表的引用

示例：

  • 子查询包含对t1列的引用，即使子查询的FROM子句没有提到表t1。MySQL将查找子查询的外部，并在外部查询中找到t1
  • 对于HAVING或ORDER BY子句中的子查询，MySQL也会在外部选择列表中查找列名
  • 相关子查询中的聚合函数可以包含外部引用，前提是该函数只包含外部引用，并且该函数不包含在其他函数或表达式中
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

衍生表（派生表）：

派生表是查询结果组成的虚拟表,在外部查询的FROM子句中定义的，不需要手动创建。只要外部查询一结束，派生表也就不存在了。

示例：SELECT语句FROM子句中的子查询是派生表，[AS] tbl_name子句是强制性的，因为子句中的每个表都必须具有名称。派生表中的任何列都必须具有唯一的名称

SELECT ... FROM (subquery) [AS] tbl_name ...

限制：

  • 派生表不能是相关子查询。
  • 派生表的不能包含相同SELECT的其他表的引用
  • 派生表不能包含外部引用。这是MySQL的限制，不是SQL标准的限制。

优化子查询：

用子查询替换联接。注：在某些情况下，将子查询转换为连接可能会提高性能

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2);
代替此查询：
SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

将子句从外部移到内部子查询。

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
代替此查询：
SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

使用行子查询而不是相关子查询。

SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
代替此查询：
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2);

使用NOT (a = ANY (...))而不是a <> ALL (...)。

使用x = ANY (table containing (1,2))而不是x=1 OR x=2

使用= ANY而不是EXISTS。

对于总是返回一行的不相关子查询，IN比=慢

SELECT * FROM t1 WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);
代替此查询：
SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

LEFT [OUTER] JOIN可以比同等的子查询更快，因为服务器可能可以更好地对其进行优化

CATALOG
标签
mysql redis sqlserver
{/if}