多表查询
内连接
1). 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
案例: 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id
-- 为每一张表起别名,简化SQL编写
SELECT e.name,d.name FROM emp e , dept d WHERE e.dept_id = d.id;
2). 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
案例: 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...
ON ...
表结构: emp , dept
连接条件: emp.dept_id = dept.id
SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id = d.id;
外连接
1). 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
2). 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
案例: 查询emp表的所有数据, 和对应的部门信息
表结构: emp, dept
连接条件: emp.dept_id = dept.id
SELECT e.*, d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
案例: 查询dept表的所有数据, 和对应的员工信息(右外连接)
表结构: emp, dept
连接条件: emp.dept_id = dept.id
SELECT d.*, e.* FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;
SELECT d.*, e.* FROM dept d LEFT OUTER JOIN emp e ON e.dept_id = d.id;
自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
案例:查询员工 及其 所属领导的名字
表结构: emp
SELECT a.name , b.name FROM emp a , emp b WHERE a.managerid = b.id;
案例:查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b
SELECT a.name '员工', b.name '领导' FROM emp a LEFT JOIN emp b ON a.managerid =
b.id;
子查询
标量子查询
案例: 查询 "销售部" 的所有员工信息
完成这个需求时,我们可以将需求分解为两步:
①. 查询 "销售部" 部门ID
SELECT id FROM dept WHERE NAME = '销售部';
②. 根据 "销售部" 部门ID, 查询员工信息
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 = '小白');
列子查询
案例: 查询 "销售部" 和 "市场部" 的所有员工信息
分解为以下两步:
①. 查询 "销售部" 和 "市场部" 的部门ID
SELECT id FROM dept WHERE NAME = '销售部' OR NAME = '市场部';
②. 根据部门ID, 查询员工信息
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 = '宋远桥' );
案例: 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 "2006-01-01" 之后的员工信息
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 ;