多表查询之联合查询及子查询

联合查询定义

案例

 

去重

代码

-- 联合查询
select * from emp where salary < 5000
union all
select * from emp where age > 50;

select * from emp where salary < 5000
union
select * from emp where age > 50;

 子查询定义

 分类及案例

 

代码

-- 子查询
-- 标量子查询
select id from dept where name = '销售部';
select * from emp where dept_id = (select id from dept where name = '销售部');

select entrydate from emp where name = '方东白';
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

 

代码

-- 列子查询
select id from dept where name = '销售部' or name = '市场部';
select * from emp where dept_id in (select id from dept where name = '销售部' or
name = '市场部');

select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );

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

代码

-- 行子查询
select salary, managerid from emp where name = '张无忌';
select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');

 

 代码

-- 表子查询
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );

select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left
join dept d on e.dept_id = d.id ;

select e.*, d.* from emp e left join dept d on e.dept_id = d.id where entrydate > '2006-01-01';