1.1 多表连接查询的概念
- 由于数据库中很多数据被分散到多个数据库表中。在查询数据时就经常出现要查的数据来自多个表中,此时就必须采用多表连接查询。多表连接查询是数据库查询中常见的查询方式。
- 多表连接查询分为内连接和外连接。
1.2 内连接的概念
- 内连接就是两张表处于同等地位,只显示两张表能够匹配上的记录。
- 内连接主要方式:
- 等值连接,连接条件为两个或者多个表的字段相等,大多数多表连接查询是等值连接。
- 非等值连接,连接条件不是两个或者多个表的字段相等,而是其他运算符(>,<,>=,<=,<>,between and等)。
- 自连接,用别名将一个表看成两张表进行多表连接。
1.3 外连接的概念
- 外连接有主表和副表的区别,两张表中有一张表是主表,另一张表是副表,主要查询主表中的数据(主表的数据一定会显示),匹配查询副表,当副表中的数据没有和主表中的数据匹配时,副表自动模拟出null与之匹配。
- 外连接主要方式:
- 左外连接,左边的表是主表,主表的意思是左边的表数据不会为空,但是可能会重复,查出的数据可能比主表的行数还要多。
- 右外连接,右边的表是主表。
- 全外连接,两边都是主表(基本用不到)。
1.4 案例说明
- 接下来以oracle数据库中scott用户下emp(员工表)和dept(部门表)以及salgrade(薪水等级表)为例,解析一下多表连接查询对应的SQL语句。
- 其中emp(员工表)表结构和表数据如下:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4) -- 员工编号
ENAME VARCHAR2(10) -- 员工姓名
JOB VARCHAR2(9) -- 员工职位
MGR NUMBER(4) -- 员工领导
HIREDATE DATE -- 入职日期
SAL NUMBER(7,2) -- 员工薪水
COMM NUMBER(7,2) -- 员工提成
DEPTNO NUMBER(2) -- 部门编号
- 其中dept(部门表)表结构和表数据如下:
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- --------------
DEPTNO NOT NULL NUMBER(2) -- 部门编号
DNAME VARCHAR2(14) -- 部门名称
LOC VARCHAR2(13) -- 办公地点
- 其中salgrade(薪水等级表)表结构和表数据如下:
SQL> desc salgrade;
Name Null? Type
----------------------------------------- -------- ----------
GRADE NUMBER -- 等级编号
LOSAL NUMBER -- 最低薪水
HISAL NUMBER -- 最高薪水
1.4.1 交叉查询
- 交叉查询没有任何实际意义,得到的是2张表的笛卡尔积,也就是让表1的每一项都组合表2的每一项:笛卡尔乘积现象(没有条件限制),结果条数是2张表记录条数的乘积。
select e.ename,d.dname from emp e,dept d;
- 使用第一张表的第一条记录和第二张表的每一条记录都匹配,然后第一张表的每一条记录也一样去找表2的每一条记录进行匹配。
- 如果不用别名,若两张表存在一模一样的字段时,会出现混淆。
1.4.2 内连接查询
(1)如何避免笛卡尔积现象
- 加条件进行过滤。避免了笛卡尔积现象,会减少记录的匹配次数吗?不会,次数还是56次。只不过显示的是有效记录。
(2)内连接查询
- 内连接之等值连接:最大特点是:条件是等量关系。
- 案例:查询每个员工的部门名称,要求显示员工名和部门名。
-- 语法太老,基本不用了
SQL92: select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
-- 常用,inner可以省略,带着inner目的是可读性好一些。
SQL99: select e.ename,d.dname
from emp e inner join dept d
on e.deptno=d.deptno;
-
语法:… A join B on 连接条件 where 查询条件;
-
表连接的条件和数据进行过滤的where条件进行分离。语法结构更清晰。
-
内连接之非等值连接的最大特点是:连接条件中的关系是非等量关系。
-
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select e.ename, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
- 自连接:最大的特点是:一张表看做两张表。自己连接自己。
- 案例:找出每个员工的上级领导,要求显示员工名和对应的领导名,希望mgr显示的是领导名,而不是编号。
- 其实都是同一张表,员工的领导编号=领导的员工编号。(核心点,在于找这个等量关系)。
select a.ename as ‘员工名’, b.ename as ‘领导名’
from emp a inner join emp b
on a.mgr=b.empno;
1.4.3 外连接查询
- 外连接最重要的特点是:主表的数据无条件的全部查询出来。
- 左连接有右连接的写法,右连接也会有对应的左连接的写法。
- 案列:找出每个员工的上级领导?(所有员工必须全部查询出来。)
-- 外连接(左):outer可以省略。
select a.ename ‘员工’, b.ename ‘领导’
from emp a left outer join emp b
on a.mgr=b.empno;
-- 外连接(右):
select a.ename ‘员工’, b.ename ‘领导’
from emp b right outer join emp a
on a.mgr=b.empno;
更多精彩欢迎关注微信公众号《格子衫007》!