数据库系统（五）：笛卡尔积、内连接、左外连接、右外连接、子查询

一、笛卡尔积

从多张表中提取数据必须指定关联的条件，如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积。在数据库查询中，笛卡尔积用于联接操作，生成所有可能的组合，然后通过条件筛选出需要的结果。如果规定了连接条件的表连接语句，就不会出现笛卡尔积，关联条件的数据为N-1个

二、内连接

表连接分为内连接和外连接，内连接是结果集中只保留符合连接条件的记录，是最常见的一种表连接，用于查询多张关系表符合连接条件的记录。语法结构：

select ... from 表1 (inner) join 表2 on 连接条件;

select ... from 表1 (inner) join 表2 where 连接条件;

select ... from 表1,表2 where 连接条件;

练习1：查询每个员工的工号、姓名、部门名称、底薪和职位

select e.empno,e.ename,d.dname,e.sal,e.job

from t_emp e join t_dept d

on e.deptno=d.deptno

练习2：查询底薪超过公司平均底薪员工的工号、姓名、底薪和职位

select e.empno,e.ename,e.sal,e.job

from t_emp e join (select AVG(sal) avg from t_emp) t

注：此处把平均薪资的查询结果作为一张表

on e.sal>t.avg

练习3：查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪

select count(*),MAX(e.sal),MIN(e.sal),AVG(e.sal)

from t_emp e join t_dept d

on e.deptno=d.deptno where d.dname='RESEARCH'

练习4：查询底薪超过自己本部门平均底薪的员工的工号、姓名、底薪和职位

select e.empno,e.ename,e.sal,e.job

from t_emp e join (select deptno,AVG(sal) as avg from t_emp group by deptno) t

注：此处把每个部门的平均薪资的查询结果作为一张表

on e.deptno=t.deptno and e.sal>t.avg

三、外连接

表连接分为内连接和外连接，外连接不管符不符合连接条件，记录都保留在结果集中。语法结构：

左外连接：select ... from 表1 left join 表2 on 条件

右外连接：select ... from 表1 right join 表2 on 条件

练习1：查询所有员工的工号、姓名和所在部门

select e.empno,e.ename,d.dname

from t_emp e right join t_dept d

on e.deptno=d.deptno

练习2：查询每个部门的名称和部门的人数

select d.dname,count(e.deptno)

from t_dept d left join t_emp e

on d.deptno=e.deptno group by d.deptno

练习3：查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替

(select d.dname,COUNT(e.deptno)

from t_dept d left join t_emp e

on d.deptno=e.deptno group by d.deptno)

union

(select d.dname,COUNT(*)

from t_dept d right join t_emp e

on d.deptno-e.deptno group by d.deptno)

注：查询每个部门的名称和部门的人数，使用LEFT JOIN确保所有部门被列出，UNION关键字可以将多个查询语句的结果集进行合并

四、子查询

子查询是一种查询中嵌套查询的语句，子查询可以写在三个地方: WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的

练习1：查询底薪超过公司平均底薪的员工的信息

select empno,ename,sal from t_emp

where sal>=(select AVG(sal) from t_emp)

单行子查询的结果集只有一条记录，多行子查询结果集有多行记录，多行子查询只能出现在WHERE子句和FROM子句中

练习2：子查询查找FORD和MARTIN两个人的同事

select ename from t_emp

where depno in (select deptno from t_emp where ename in ('FORD','MARTIN'))

and ename not in ('FORD','MARTIN')

WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断

练习3：查询比FORD和MARTIN底薪都高的员工信息

select ename from t_emp

where sal> all(select sal from t_emp where ename in ('FORD','MARTIN'))

练习4：查询比FORD和MARTIN底薪任意高的员工信息

select ename from t_emp

where sal> any(select sal from t_emp where ename in ('FORD','MARTIN'))