子查询表达式

📄字数 1.8K

👁️阅读量 加载中...

概述

子查询表达式是嵌套在主查询内部的查询，可以作为条件、字段、值列表等参与主查询逻辑。通常出现在WHERE、SELECT、FROM中。

子查询类型

类型	说明	示例
标量子查询	只返回单个值的子查询，通常作为一个普通的值参与表达式计算	(SELECT COUNT(*) FROM ...)
表子查询	返回多行多列结果的子查询，通常作为一张临时表参与主查询	(SELECT ... FROM ...) AS alias
IN子查询	判断集合包含关系	col IN (SELECT ...)
EXISTS/NOT EXISTS子查询	判断是否存在记录	EXISTS/NOT EXISTS (SELECT ...)
UNIQUE子查询	判断子查询结果是否不包含重复行	UNIQUE (SELECT ...)
= ANY子查询	判断某值是否等于子查询结果中的任一值	= ANY (SELECT ...)
<> ALL子查询	判断某值是否与子查询结果中所有值都不相等	<> ALL (SELECT ...)

提示

1. 标量子查询必须返回一个值（一行一列），否则报错
2. IN和EXISTS子查询通常比JOIN性能差
3. 子查询在SELECT中通常用于派生字段

示例

-- 建表
SQL> CREATE TABLE employees (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       department_id INT
     );

-- 建表
SQL> CREATE TABLE departments (
       id INT PRIMARY KEY,
       name VARCHAR(50)
     );

-- 插入数据
SQL> INSERT INTO employees VALUES
     (1, 'Alice', 1),
     (2, 'Bob', 1),
     (3, 'Charlie', 2),
     (4, 'Diana', 2),
     (5, 'Eve', 3);

-- 插入数据
SQL> INSERT INTO departments VALUES
     (1, 'HR'),
     (2, 'Engineering'),
     (3, 'Marketing');


-- 标量子查询表达式
SQL> SELECT
       name,
       department_id,
       CASE
         WHEN department_id > (
           SELECT MAX(department_id) / 2 FROM employees
         )
         THEN 'High'
         ELSE 'Low'
       END AS dept_level
     FROM employees;

+---------+---------------+------------+
|  NAME   | DEPARTMENT_ID | DEPT_LEVEL |
+---------+---------------+------------+
| Alice   | 1             | Low        |
| Bob     | 1             | Low        |
| Charlie | 2             | High       |
| Diana   | 2             | High       |
| Eve     | 3             | High       |
+---------+---------------+------------+

-- 表子查询表达式
SQL> SELECT d.name AS department_name, dept_stats.emp_count
     FROM (
       SELECT department_id, COUNT(*) AS emp_count
       FROM employees
       GROUP BY department_id
     ) AS dept_stats
     JOIN departments d ON dept_stats.department_id = d.id
     WHERE dept_stats.emp_count >= 2;

+-----------------+-----------+
| DEPARTMENT_NAME | EMP_COUNT |
+-----------------+-----------+
| HR              | 2         |
| Engineering     | 2         |
+-----------------+-----------+

-- IN子查询表达式
SQL> SELECT name
     FROM employees
     WHERE department_id IN (
       SELECT id FROM departments
     );

+---------+
|  NAME   |
+---------+
| Alice   |
| Bob     |
| Charlie |
| Diana   |
| Eve     |
+---------+

-- EXISTS子查询表达式
SQL> SELECT name AS department_name
     FROM departments d
     WHERE EXISTS (
       SELECT 1
       FROM employees e
       WHERE e.department_id = d.id
     );

+-----------------+
| DEPARTMENT_NAME |
+-----------------+
| HR              |
| Engineering     |
| Marketing       |
+-----------------+

-- UNIQUE子查询表达式
SQL> SELECT name 
     FROM employees e 
     WHERE UNIQUE (
         SELECT name 
         FROM employees 
         WHERE department_id = e.department_id
     );

+------+
| NAME |
+------+
| Eve  |
+------+

-- = ANY子查询表达式
SQL> SELECT name
     FROM employees
     WHERE department_id = ANY (
         SELECT id
         FROM departments
         WHERE name IN ('HR', 'Engineering')
     );

+---------+
|  NAME   |
+---------+
| Alice   |
| Bob     |
| Charlie |
| Diana   |
+---------+

-- <> ALL子查询表达式
SQL> SELECT name
     FROM employees
     WHERE department_id <> ALL (
         SELECT id
         FROM departments
         WHERE name IN ('HR', 'Engineering')
     );

+------+
| NAME |
+------+
| Eve  |
+------+

应用场景

• 场景一：WHERE子句中的子查询，用于限定某列值在另一个表中是否存在
• 场景二：SELECT子句中作为计算字段，返回单个值，用作输出列
• 场景三：FROM子句中嵌套为虚拟表，将子查询作为派生表参与后续联接或过滤
• 场景四：HAVING子句中使用子查询，用于对分组结果进行进一步限定