[Oracle数据库学习]六、子查询

D4

子查询

解决需要多次查询的问题

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list 
FROM table);

1）子查询(内查询)在主查询之前一次执行完成。
2）子查询的结果被主查询(外查询)使用。

 

SELECT employee_name
FROM employees
WHERE salary > (
  SELECT salary FROM employees WHERE employee_name = 'Cindy'
);

EMPLOYEE_NAME

Alice

Elsa

等同于进行两次查询：

子查询：

SELECT salary 
FROM employees 
WHERE employee_name = 'Cindy';

SALARY

6000

父查询：

SELECT employee_name
FROM employees
WHERE salary > 6000;

EMPLOYEE_NAME

Alice

Elsa

 

注意事项

1）子查询要包含在括号内，并放在比较条件的右侧。
2）除非进行Top-N分析，否则不要在子查询中使用ORDER BY子句。
3）单行操作符对应单行子查询，多行操作符对应多行子查询。

 

子查询类型

分为单行子查询和多行子查询。

单行子查询

返回一行数据，要使用单行比较操作符。

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

SELECT employee_name
FROM employees
WHERE job_id = (
    SELECT job_id 
    FROM employees 
    WHERE employee_name = 'Elsa'
)
AND salary > (
    SELECT salary 
    FROM employees 
    WHERE employee_name = 'Donald'
);

EMPLOYEE_NAME

Bob

Elsa

查询结果为：job_id与Elsa相同，且salary大于Donald的员工。

 

在子查询中使用组函数

SELECT employee_name
FROM employees
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees
);

EMPLOYEE_NAME

Alice

Elsa

查询结果为：salary大于平均值的员工。

 

在HAVING子句中使用子查询

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)> (
  SELECT AVG(salary)
  FROM employees
);

JOB_ID	AVG(SALARY)
PM	10000

查询结果为：平均工资大于所有员工平均工资的job_id

 

非法使用子查询

对多行子查询的结果使用单行比较符

错误示例：

SELECT employee_name
FROM employees
WHERE salary> (
  SELECT AVG(salary) 
  FROM employees 
  GROUP BY job_id
);

报错：ORA-01427: single-row subquery returns more than one row

 

子查询空值

SELECT employee_name
FROM employees
WHERE salary> (
  SELECT salary 
  FROM employees 
  WHERE employee_name = 'No one'
);

查询结果为空，因为'No one'不存在，子查询结果为空，父查询结果也为空。

 

多行子查询

返回多行数据，使用多行比较操作符。

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

在多行子查询中使用ANY操作符

SELECT employee_name, job_id, salary
FROM employees
WHERE salary < ANY (
  SELECT salary 
  FROM employees 
  WHERE job_id = 'DEV'
)
AND job_id <> 'DEV';

EMPLOYEE_NAME	JOB_ID	SALARY
Donald	AM	4000
Cindy	QA	6000

查询结果为：非DEV且salary小于任意一个DEV的员工。

 

在多行子查询中使用ALL操作符

SELECT employee_name, job_id, salary
FROM employees
WHERE salary < ALL (
  SELECT salary FROM employees WHERE job_id = 'DEV'
)
AND job_id <> 'DEV';

EMPLOYEE_NAME	JOB_ID	SALARY
Donald	AM	4000

查询结果为：非DEV且salary小于所有DEV的员工。

 

在多行子查询中使用IN操作符

SELECT employee_name, job_id, salary
FROM employees
WHERE employee_id IN (
  SELECT manager_id 
　FROM employees
);

EMPLOYEE_NAME	JOB_ID	SALARY
Alice	PM	10000
Bob	DEV	5000

 查询结果为：是manager的员工。

 

子查询中的空值问题

SELECT employee_name, job_id, salary
FROM employees
WHERE employee_id NOT IN (
  SELECT manager_id
  FROM employees
);

查询结果为空，因为子查询中包含了空值，如果改为：

SELECT employee_name, job_id, salary
FROM employees
WHERE employee_id NOT IN (
  SELECT manager_id 
  FROM employees 
  WHERE manager_id IS NOT NULL
);

EMPLOYEE_NAME	JOB_ID	SALARY
Elsa	DEV	8000
Donald	AM	4000
Cindy	QA	6000

查询结果为：不作为manager的员工。

 

总结：

本节介绍了子查询：

1）包括单行子查询（返回单行数据，需使用单行比较操作符）和多行子查询（返回多行数据，需使用多行比较操作符）；

2）当查询基于未知的、需要通过子查询得到的值时使用。