Oracle学习——子查询

　　本篇文章将介绍SQL中子查询的内容。

1、子查询

　　子查询是一个SELECT语句，它是嵌入在另一个SELECT语句中的子句。放置一个查询到另一个查询中，内查询或子查询返回一个值给外查询或主查询。使用子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

语法结构：

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

• 子查询会在主查询之前执行一次（只会执行一次）
• 子查询的结果会被用于主查询

　　子查询语句可以放在许多的SQL子句中。包括：WHERE、HAVING、FROM子句中

2、使用子查询

2.1、使用原则

• 子查询放在比较条件的右边
• 在单行子查询（返回的结果集是一个）中用单行运算符(=,>,<)，在多行子查询中（返回的结果是多行）使用多行运算符(ANY，ALL...)。

示例：在employees表中找出比Abel工资高的人。

SELECT em.name, em.salary
FROM employees em
WHERE em.salary >(SELECT m.salary 
                    FROM employees m 
                    WHERE m.name='Abel'
                    )

3、子查询的类型

单行子查询：返回的子查询结果只有一条数据返回。

多行子查询：返回的子查询结果有不止一条数据返回。

3.1、单行子查询

主查询对于子查询结果的单行比较运算符：

• =：等于
• >,>=：大于，大于等于
• <,<=：小于，小于等于

示例：显示jobID与雇员141(主键employeeID)相同的雇员名字和jobID。

SELECT em.jobID, em.name
FROM employees em
WHERE em.jobID =(SELECT jobID 
                 FROM employees e 
                 WHERE e.employeeID=141
                )

3.2、单行子查询中使用组函数

　　子查询中是可以使用组函数进行分析的。例如下面这示例：显示所有薪水=最低薪水的雇员name和jobID。

SELECT em.jobID, em.name
FROM employees em
WHERE em.salary =(SELECT MIN(salary) 
                 FROM employees
                )

3.3、HAVING子句中使用单行子查询

　　在HAVING子句中可以使用子查询。

示例：显示所有最低薪水小于 部门ID为50的最低薪水 的部门号与最低薪水。

SELECT em.department_id, MIN(em.salary)
FROM employees em
GROUP BY em.department_id
HAVING MIN(em.salary) > (SELECT MIN(e.salary) 
                         FROM employees e
                         WHERE e.department_id=50
                        )

3.5、多行子查询

　　主查询对子查询的多行比较运算符。

• IN：等于集合中的任何值，只能做相等判断。
• ANY：比较子查询返回的每个值 （OR），可以做大于或者小于判断。
• ALL：比较子查询返回的全部值 （AND），可以做大于或者小于判断。

3.6、多行子查询中使用IN

示例：查找各部门收入为部门最低的那些雇员，显示他们的名字、薪水、部门ID

分析；先需要找出每个部门中收入最低的雇员的name或者employee_id。

SELECT e.employeeID  
FROM employees e, (SELECT MIN(salary) mins, department_id FROM employees GROUP BY department_id) av
WHERE e.department_id=av.department_id 
AND e.salary=av.mins

　　再通过IN在主查询中，找到这些employee_id的雇员名称和薪水，部门id。

SELECT em.department_id, em.name, em.salary
FROM employees em
WHERE em.employeeID in (SELECT e.employeeID  
                        FROM employees e, (SELECT MIN(salary) mins, department_id 
                                           FROM employees 
                                           GROUP BY department_id
                                            ) av
                        WHERE e.department_id=av.department_id 
                        AND e.salary=av.mins
                        )

3.7、多行子查询中使用ANY

语法结构：

SELECT select_list
FROM table
WHERE salary <ANY (SELECT select_list
                    FROM table)

　　其中<ANY表示小于最大值的即可，>ANY表示大于最小值即可。

示例：显示工作岗位不是IT_PROG的雇员，并且这些雇员的薪水少于IT_PROG工作岗位的职员，显示这些职员的姓名，薪水。

select e.name, e.salary
from employees e
where e.salary <ANY (SELECT em.salary 
                     FROM employees em
                     WHERE em.job_id='IT_PROG')
and e.job_id <> 'IT_PROG';

3.8、多行子查询中使用ALL

语法上与ANY一致，只用把ANY位置转化为ALL即可。其中<ALL表示小于最小值的即可，>ALL表示大于最大值即可。

示例：显示那些薪水低于工作岗位是“IT_PROG”的最低薪水，并且工作岗位不是IT_PROG的所有雇员，显示这些雇员的姓名，薪水。

SELECT e.name, e.id
FROM employees e
WHERE e.salary <ALL (SELECT em.salary 
                     FROM employees em
                     WHERE em.job_id='IT_PROG')
AND e.job_id <> 'IT_PROG';

4、习题

示例1：显示与Zlotkey在同一部门的雇员name，并且结果中不包含有Zlotkey

SELECT e.name, e.id
FROM employees e
WHERE e.department_id = (SELECT em.department_id
                         FROM employees em
                         WHERE em.name='Zlotkey')
AND e.name <> 'Zlotkey';

　　

示例2：创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。

SELECT e.name, e.id
FROM employees e
WHERE e.salary > (SELECT AVG(em.salary)
                  FROM employees em
                  )
ORDER BY e.salary

　　

示例3：所有部门中有些部门的雇员名字中包含u，显示所有工作在这些部门的雇员名字和id。

SELECT e.name, e.id
FROM employees e
WHERE e.department_id IN (SELECT em.department_id
                          FROM employees em
                          WHERE em.name LIKE '%u%'
                          )

　　

示例4：显示所有部门地点号（department location ID）是1700的雇员的name、department和jobID。(部门地点号在department表中)

SELECT e.name, e.job_id, e.department_id
FROM employees e
WHERE e.department_id IN (SELECT de.department_id
                          FROM department de
                          WHERE de.location_id = 1700
                          )

　　

示例5：显示经理是King的雇员的名字和薪水。

SELECT e.name, e.salary
FROM employees e
WHERE e.manager_id IN (SELECT em.employee_id
                       FROM employees em
                       WHERE em.name = 'King'
                       )

　　这里采用IN是因为防止King有多个人。