三、多表查询语句

多表查询语句

1、连接查询
根据表的连接方式来划分，包括：

• 内连接：

       等值连接
       非等值连接
       自连接
  

• 外连接：

       左外连接（左连接）：表示左边的那张表示主表。
       右外连接（右连接）：表示右边的那张表是主表。
       全连接（使用的很少）：
  

问题：外连接和内连接有什么区别？
内连接：假设A和B表进行连接，使用内连接的话，只把A表和B表能够匹配的上的记录查询出来，A、B两张表没有主副之分，两张表示平等关系。

外连接：假设A和B表进行连接，使用内连接的话，A、B两张表中一张是主表，一张是副表， 主要查询主表中的数据，附带查询副表数据，当副表中的数据没有和主表中的数据匹配是，副表字段自动模拟为null与之匹配，显示主表中的全部数据。

• 笛卡尔积现象

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘机。

select e.ename,d.dname from emp e,dept d;（emp14条记录*dept4条记录=56条记录）

问题：怎么避免笛卡尔积现象？避免笛卡尔积现象可以提高执行效率吗？
加条件进行过滤可以避免笛卡尔积现象。避免笛卡尔积现象不会提高执行效率，匹配次数没有变化，只是结果显示有效记录。

• 表的别名

优点：
1.执行效率高 ：当存在多张表的时候，设置表名，查询字段名会直接到别名表匹配，不设置别名会去多张表中都匹配。

2.可读性好：两张表可能存在相同字段名

• 内连接之等值连接

特点：条件是等量关系。

SQL92（太老，不建议使用）：

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

SQL99（建议使用）：

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;（inner可省略，加上可读性更好）

提示：SQL99语法结构更清晰：表的连接条件与where条件分离。

• 内连接之非等值连接

特点：连接条件中的条件是非等量关系。

SQL99：select s.grade,e.ename,e.sal from emp e inner join  salgrade s on e.sal between s.losal and s.hisal;
（ 查询e.sal的值在s.losal和s.hisal之间的数据）

• 内连接之自连接

特点：一张表看做两张表，自己连接自己。

SQL92：select e.ename as '员工名', m.ename as '领导名' from emp e, emp m where e.mgr=m.empno;

（领导也是员工，员工有直属领导，所以根据empno可以把表看成是员工表（emp e），根据mgr可以把表看做领导表（emp m））

SQL99: select e.ename as '员工名', m.ename as '领导名' from emp e join emp m on e.mgr=m.empno;

领导表记录（去重）：（共七条数据： 7839 | KING | NULL 是老板没有直属领导，没查出来）

select empno,ename from emp where empno in (select mgr from emp group by mgr) order by empno desc;

员工表记录：

`select empno,ename,mgr from emp order by mgr desc;`

• 外连接之左外连接

特点：join左边的是主表，显示左边表的全部数据。
查询所有员工的直属领导：

`select e.ename as '员工名', m.ename as '领导名' from emp e left  outer join emp m on e.mgr=m.empno;（outer可以省略）`

• 外连接之右外连接

特点：join右边的是主表，显示右边表的全部数据。
查询所有领导的直属员工：

`select e.ename as '员工名', m.ename as '领导名' from emp e right join emp m on e.mgr=m.empno;`

• 外连接之全连接

特点：左右两张表都是主表，查出左右表的全部数据，不匹配的值填充null。

• 多于两张表的连接

select  字段  from 表名  join  表名 on  连接条件  join  表名  on  连接条件 .....
四张表连接：
select e.ename as '员工',d.dname,s.grade,e1.ename as '领导' from 
emp e join dept d on e.deptno=d.deptno 
join salgrade s on e.sal between s.losal and s.hisal 
left join emp e1 on e.mgr = e1.empno;

2、子查询
select语句中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现的位置：select … (select) from … (select) where … (select);

• where后面嵌套子查询

select * from emp where sal >(select avg(sal) from emp);（找出大于平均薪资的记录）

• from后面嵌套子查询

select t.*,s.grade from 
(select deptno,avg(sal) as avgsal from emp group by deptno) t 
join salgrade s on t.avgsal between s.losal and s.hisal;
（找出每个部门平均薪资等级）

• select后面嵌套子查询

select e.ename,e.deptno,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

提示：先查出emp表的字段，再根据查询出来的字段匹配dept表中的数据，最后得到dept表中的数据。

3、union（将查询结果集相加）
合并结果集的时候，需要查询字段对应个数相同，合并结果集字段名以第一张表为准，在oracle中更加严格，不但要求个数相同，而且还要求类型对应相同。

mysql> select * from emp where job='MANAGER'
    -> union
    -> select * from emp where job='SALESMAN'；

mysql> select ename from emp where job='MANAGER'
    -> union
    -> select job from emp;

4、limit（取结果集中的部分数据）
limit是mysql中特有的，其他数据库中没有，不通用，作用是取结果集中的部分数据。（Oracle中有一个相同的机制，叫做rownum）
格式：limt startIndex ， length；startIndex表示起始位置，从0开始，0表示第一条数据。length表示取几条。

SQL执行顺序：limit是sql语句中最后执行的环节。

• 取前五条记录

select * from emp  limit 5;

• 从第二条开始，取两条记录

select * from emp  limit 1,2;