子查询

当进行一个查询时，需要的条件或者数据要用另一个select语句得结果

可分为两种：

（1）where型

子查询嵌套在where里面                

1.单值：> < = <= >= != 后面接查询的结果必须是单值

#select name,salary from employee where salary=(select max(salary) from employee)

2.多值：in =any  >=all <=all

# select name,salary from employee where salary in (select salary from employee where name in ('a','b','c'))

# select name,salary from employee where salary =any (select salary from employee where name in ('a','b','c'))

（2）from型

select t1.name,t2.salary_avg from
(select id,name from department)t1
left join
(select depart,avg(salary) as salary_avg from employee group by depart)t2
on t1.id=t2.depart

（3）exists型