子查询:类型、语法、和注意事项

使用子查询能解决哪些问题？

子查询语法：

select select_list from table where expr operator (select select_list from table);

• 子查询（内查询）在主查询（外查询）之前执行。

• 主查询使用子查询结果。

• 位置：select，where，from，having

1、查询谁的工资比Abel高

select last_name, salary from employees 

where salary >

(select salary

from employees

where last_name = 'Abel');

使用子查询注意事项

• 子查询要包含在括号内。

• 将子查询放在比较条件的右侧增强可读性（子查询可以出现在比较运算符的两侧）

• 单行操作符对应单行子查询，多行操作符对应多行子查询

单行子查询:

– 子查询中的组函数

– HAVING 子句中的子查询

• 只返回一行

• 使用单行比较操作符

  
  

select last_name, job_id, salary from employees

where job_id  in  (select job_id from employees

where last_name like  'Taylor')

and salary in

(select salary

from employees

where last_name like 'Taylor');

在子查询中使用组函数

select last_name, job_id, salary from employees where 

salary = (select min(salary) from employees);

子查询中的HAVING 子句

• 首先执行子查询

• 向主查询中的 HAVING 子句返回结果

select department_id, min(salary)

from employees

group by department_id

having min(salary) >

(select min(salary)

from employees

where department_id = 50);

多行子查询使用单行比较符，以下为错误写法

select employee_id, last_name

from employees

where salary =

(select min(salary)

from employees

group by department_id);

子查询中的空值问题

select last_name, job_id from employees

where job_id =

(select job_id from employees

where last_name = 'haas');

多行子查询

– 使用 ALL 或 ANY

• 返回多行。

• 使用多行比较操作符。

使用范例：

在多行子查询中使用 ANY 

select employee_id, last_name, job_id, salary

from employees  where salary < any

(select salary

from employees

where job_id = 'IT_PROG')

and job_id < > 'IT_PROG';

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

select employee_id, last_name, job_id, salary

from employees

where salary < all

(select salary

from employees

where job_id = 'IT_PROG')

and job_id <> 'IT_PROG';

子查询中的空值

select emp.last_name

from employees emp

where emp.employee_id not in

(select mgr.manager_id

from employees mgr);

1、HR 部门的同事想要你帮忙写一个 SQL 语句，该 SQL 语句可以传入一个值（员工的 last_name），然后返回结果是该员工同一部门同事的 last_name 和 hire_date，且要求该员工不在返回结果中。

举个例子，如果用户输入”Zlotkey”，结果就会返回除了 Zlotkey 之外的同一部门的其他同事的

last_name 和 hire_date.

select last_name,hire_date

from employees

where department_id =(select department_id from employees

where last_name= '&&enter_name')

and last_name < > '&enter_name';

2、请查询出所有高于平均工资的员工的 employee_id,last_name,salary，并将最终结果根据salary 降序排列。

select employee_id,last_name,salary 

from employees 

where salary > (select avg(salary) 

            from employees) 

order by salary;

3、请写一条 SQL 语句，要求查询出那些同一部门员工 last_name 里面包含字母”u”的员工的employee_id，last_name。

select employee_id,last_name from employees where department_id in (select department_id from employees where last_name like '%u%');

4、请帮助HR部门的同事查出所有部门location_id是1700的员工的last_name,department_id,job_id。

select last_name,department_id,job_id

from employees

where department_id in(select department_id

from departments

where location_id=1700);

让用户可以选择输入一个 location_id,然后输出结果。

select last_name,department_id,job_id

from employees

where department_id in(select department_id

from departments

where location_id=&enter_location);

5、请查出所有需要向 King 汇报的员工的 last_name 以及 salary

select last_name,salary,manager_id

from employees

where manager_id = (select employee_id

from employees

where last_name like 'King' and manager_id is null);

6、请查出所有是执行部（Executive）的员工的 department_id,last_name,job_id

select department_id,last_name,job_id

from employees

where department_id in(select department_id

from departments

where department_name like 'Executive');

7、请查出比 department_id 是 60 的任何员工薪水高的所有的员工的 last_name。

select department_id,last_name,salary from employees 

where salary > any 

(select salary from employees

where department_id=60);

8、查询所有高于平均工资，并且同一部门员工 last_name 里面包含字母”u”的员工的 employee_id,last_name,salary。

select employee_id,last_name,salary

from employees

where department_id in(select department_id

from employees

where last_name like '%u%')

and salary > (select avg(salary) from employees);