子查询表达式

概述

子查询表达式在SQL中用于在一个查询中嵌入另一个查询。子查询可以返回单个值、一行、一列或多行多列的结果，具体取决于其用途。

查值型子查询表达式

此类子查询表达式在整个表达式中充当一个值表达式，如同一个变量，这类表达式输出一列。

示例

从员工信息表empinfo中查找出员工编号与父查询记录相同的员工地址信息，子查询SELECT address FROM empinfo WHERE empinfo.id=emp.id作为一个值表达式。

SELECT id, name, (SELECT address FROM empinfo WHERE empinfo.id = emp.id)
  FROM emp
 WHERE emp.id = 10;

布尔型子查询表达式

布尔型子查询表达式在SQL中主要用于返回一个布尔值，以帮助主查询决定是否满足某个条件。

语法格式

EXISTS (SELECT ...)

NOT EXISTS (SELECT ...)

EXISTS在子查询结果集非空时返回真，否则返回假。同理，NOT EXISTS在子查询结果集为空时返回真，否则返回假。

示例

从员工表emp中查找地址信息（地址信息在另一表达式empinfo中）非空的员工信息。

SELECT name FROM emp WHERE EXISTS
(SELECT * FROM empinfo WHERE empinfo.address NOTNULL AND empinfo.id = emp.id);

比较型子查询表达式

比较型子查询表达式是SQL查询中用来基于子查询结果进行条件筛选的一种方式。

语法格式

expr op [ANY|ALL] (SELECT ...)

expr是一个值表达式，op表示比较操作符，子查询应只输出一个字段值，子查询结果可以是多行，若指定ALL选项，则在expr与所有子查询输出结果的关系比较都为真时，整个表达式返回真，否则返回假；若指定ANY选项，则只要expr与子查询结果集中至少一个结果值比较为真时，整个表达式返回真值。

示例

从员工表中找出工资在1000元以上的员工名字。子查询用于找出工资在1000元以上的员工的ID，只要emp表中记录的id字段值与子查询结果集中任意一个相等，则该员工的名字就将被选中输出。

SELECT name FROM emp WHERE id = ANY (SELECT id FROM salary WHERE sal > 1000);

多列比较型子查询表达式

多列比较型子查询允许在子查询中同时比较多个列的值，表达式的个数应等于子查询的列数，子查询的一行记录将与多个表达式进行比较。

语法格式

(expr1, ..., exprn) op [ANY|ALL] (SELECT ...)

通常，比较操作符只有=与<>两种，由于有多列值参与比较，若使用其它类型的操作符，将难以界定各字段比较结果与整体结果的关系。子查询结果可以是多行，若指定ALL选项，则在expr与所有子查询输出的记录行的关系比较都为真时，整个表达式返回真，否则返回假；若指定ANY选项，则只要expr与子查询结果集中至少一行进行比较为真时，整个表达式返回真值。

示例

从unit表中选择id和name列，其中(id, name)组合必须与unit1表中id1 > 1000的任意(id1, name1)组合完全匹配。

SELECT id, name FROM unit WHERE (id, name) = ANY (SELECT id1, name1 FROM unit1 WHERE id1 > 1000);

IN型子查询表达式

IN型子查询表达式是SQL中常用的一种子查询形式，用于检查某个值是否存在于子查询返回的结果集中。这种子查询通常用于基于子查询结果进行过滤。

语法格式

expr IN (SELECT ...)

expr NOT IN (SELECT ...)

expr是一个值表达式，expr IN (SELECT ...)相当于expr = ANY(SELECT ...)；expr NOT IN (SELECT ...)相当于expr<> ALL(SELECT ...)。

示例

首先找出所有工资大于1000的员工ID。使用子查询的结果作为过滤条件，从emp表中选择那些ID出现在内部查询结果中的员工名字。

SELECT name FROM emp WHERE id IN (SELECT id FROM salary WHERE sal > 1000);