子查询

一.在一个select中包含另外一个select,把包含在内部的select成为子查询标量查询

查询的结果只有一个值

-- 子查询:李老师所在的部门
-- 方法一:关联多表(一表查询)
select d.name
from employee as e left join department as d
on e.dept_id=d.id
where e.name='李老师'

-- 方法二:子查询模式
select d.name from  department as d
where d.id=
(select e.dept_id from  employee as e where e.name='李老师')

-- 查询所有市场部的老师
select e.name from employee as e
where e.dept_id=
(select d.id from department as d where d.name='市场部')

温馨小提示:在实际开发中,慎用子查询(查询速度慢),能用多表查询尽量不用子查询

它的实质在于:分表查询,最终要查询的那个结果字段(父查询),连接条件就是父查询的条件=子查询的查询结果

---

二.列查询:查询结果为多行

-- 查询所有在市场部或者就业部的所有员工姓名
select e.name  from employee as e
where e.dept_id in
(select d.id 
from department as d 
where d.name='市场部'or d.name='就业部')

温馨小提示:注意看使用in就不需要使用=,因为in就是表示一个结果集(查询到了市场部和就业部本身就代表着寻找到了这两个部门的id集合)

---

三.exists

-- 查出有员工的部门信息 方法一
select distinct d.name
from employee as e left join department as d
on e.dept_id=d.id
where d.name is not null

-- 查出有员工的部门信息 方法二
select distinct d.name
from department as d left join employee as e
on e.dept_id=d.id
where e.name is not null

-- exists
-- 实质就是分表查询,但是子查询的条件中:是否被关联到
select d.name from department as d
where EXISTS(select * from employee as e where e.dept_id=d.id)

---

四.合并查询union:将多个查询结果合并到一起,成为一个新的结果集

-- 合并查询union
select  e.name  from employee e
UNION
select d.name from department d