多表查询(子查询)

子查询, 也称嵌套查询

子查询的语句可以是insert/update/delete/select中的任何一个

根据子查询的结果不同,可以分为:

• 标量子查询(结果为单个值)

• 列子查询(结果为一列)

• 行子查询(子查询结果为一行)

• 表子查询(子查询结果为多行多列)

  select * from t1 where column1 = (select column1 from t2)

标量子查询

查询研发部所有员工的信息

select * from emp
    where dept_id in
        (select id from dept where name='研发部');

查询在韦一笑之后(2002-02-05)入职的员工

select * from emp
    where entryDate >
          (select entryDate from emp where name='韦一笑');

列子查询

in, not in, any, some, all

查询总经办和市场部的员工

select * from emp
    where dept_id in
    (select id from dept where name in ('总经办','市场部'));

查询比市场部所有员工(最大值9000)工资都高的员工

#可以这样写，>max
select * from emp
    where salary >
          (select max(salary) from emp where dept_id = (select id from dept where name='市场部'));
#也可以这样写, 用all修饰词
select * from emp
    where salary > all (select salary from emp where dept_id = (select id from dept where name='市场部'));

查询比研发部任意一人(最小值6600)工资高的员工

select * from emp
    where salary > any (select salary from emp where dept_id = (select id from dept where name='研发部'));