oracle多表关联查询技巧,Oracle SQL 多表关联查询

多个表之间关系：一对多|(多对一) 多对多 一对一

3种

关系的完整性约束：实体完整性、参照完整性、用于定义的完整性。

必须满足实体完整性和参照完整性.

实体完整性：规定了字段|属性的约束

参照完整性：关系与关系之间的引用

某个字段的约束 外键

一．笛卡尔集

笛卡尔集会在下面条件下产生:

• 省略连接条件

• 连接条件无效

• 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE

加入有效的连接条件。

//查询员工及部门的详细信息 但是会产生一个笛卡尔积的效果

SQL> select *

from emp,dept;

二．

Oracle连接

使用来接在多个表中查询数据

//别名查询 为表起别名

采用别名查询

SQL> select *

from emp e,dept d where

e.deptno=d.deptno;

综上所述

创建连接查询时应遵循如下规则：

1、 from子句应当包括所有的表名

2、 where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。

l 备注：连接 n个表,至少需要 n-1个连接条件。

例如：连接三个表，至少需要两个连接条件。

3、 当列名为多个表共有时,列名必须被限制。

非等值连接

SELECT e.last_name, e.salary, j.grade_level

FROM employees e, job_grades j

WHERE e.salary

BETWEEN j.lowest_sal AND

j.highest_sal;

三 内连接和外连接

1、 内连接：

内连接根据所使用的比较方式不同,把内连接分为了：

1) 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SQL> select *

from emp e inner join dept d on e.deptno = d.deptno;

2) 不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、、!。

SQL> select *

from emp e inner join dept d on e.deptno>d.deptno;

3) 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SQL> select *

from emp natural join dept;

SQL>

select e.*,d.dname,d.loc from

emp e inner join dept d on e.deptno = d.deptno;

SQL> select

d.*,e.ename,e.empno,e.job,e.mgr,e.hiredate,e.sal,e.comm from emp e

inner join dept d on e.deptno=d.deptno;

备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。

2、 外连接

内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道

这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足

连接条件的连接方式。3种外连接：

1) 左外连接(LEFT OUTER

JOIN)

如果在连接查询中，连接表左端的表中所有的记录都列出来，并且能在右端的表中找到匹配的记录，那么连接成功。如果在

右端的表中，没能找到匹配的记录，那么对应的记录是空值(NULL)。这时，查询语句使用关键字 LEFT OUTER

JOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内

容。

例如：要查询所有部门的员工信息查询语句为

SQL> select *

from dept d left outer join emp e on e.deptno=d.deptno order by

d.deptno;　　左外连接查询中左端表中的所有记录的信息都得到了保留。

备注：部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。

2)右外连接(RIGHT OUTER

JOIN)

右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。

例如：同上例内容，查询语句为

SQL> select *

from emp e right outer join dept d on e.deptno=d.deptno order by

d.deptno;

右外连接查询中右端表中的所有元组的信息都得到了保留。

3)全外连接(FULL OUTER

JOIN)

全外连接查询的特点是左、右两端表中的记录都输出，如果没能找到匹配的记录，就使用NULL来代替。

例如：同左外连接例子内容，查询语句为

SQL> select *

from emp e full outer join dept d on e.deptno=d.deptno order by

d.deptno;

全外连接查询中所有表中的元组信息都得到了保留。

备注：一定分清：左与右 在join后边的是右

3、 交叉联接

交叉连接即笛卡儿乘积，是指两个关系中所有记录的任意组合。一般情况下，交叉查询是没有实际意义的。

SQL> select *

from emp e cross join dept d;

备注：可以添加where子句筛选出有意义的数据。建议不使用。

4、 自连接查询

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

例如：要求检索出员工编号为7369的上司的详细信息，查询语句为

SQL> select e1.*

from emp e inner join emp e1 on e.mgr=e1.empno where

e.empno=7369;

注意：对于连接查询中使用到的 inner

outer是可以省略的。但为规范最好不要省略。

提高部分：

SQL> select *

from emp e inner join dept d on

e.deptno(+)=d.deptno;的理解?

l

使用外连接可以查询不满足连接条件的数据。

l 外连接的符号是

(+)。

2、

l 在NATURAL JOIN

子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。

l 使用 USING

可以在有多个列满足条件时进行选择。

l

不要给选中的列中加上表名前缀或别名。

l NATURAL JOIN 和 USING

子句经常同时使用。

有时一个查询结果需要从两个或两个以上表中提取字段数据，此时需要使用多表关联查询。

1)笛卡尔积关联

create table a(

id number(7),

name varchar2(20));

create table b(

id number(7),

name varchar2(20));

insert into a(id,name) values(1,'a1');

insert into a(id,name) values(2,'a2');

insert into a(id,name) values(3,'a3');

insert into b(id,name) values(1,'b1');

insert into b(id,name) values(2,'b2');

select * from a,b;

笛卡尔积特点：

--*代表from后面表中所有列

--返回结果数量是各个表记录的乘积

--结果是a每条记录与b每条记录结合形成

*2)等值连接

参与等值条件的两个字段值，相等时才作为结果返回。

select a.id,a.name,b.name //3.提取显示的字段

from a,b //1.形成笛卡尔积结果

where a.id=b.id; //2.返回id相等的记录

---使用[INNER] JOIN...ON...语法-----

select a.id,a.name,b.name

from a join b on(a.id=b.id);

提示：建议采用JOIN...ON语法,INNER

JOIN和JOIN作用等价。内连接,等值连接是一个意思。

----使用JOIN...USING语法(了解)------

select id,a.name,b.name

from a join b using(id);

JOIN...USING使用注意事项：

--关联的两个表中需要有相同的字段.(名字和类型相同)

--关联的字段在使用时不能加别名

//查询员工名称，工资,所在部门编号,部门名称

select e.ename,e.sal,e.deptno,d.dname

from emp e join dept d

on(e.deptno=d.deptno);

3)外连接

等值连接，需要两个表的关联字段等值才将结果返回。如果需要将某一个表记录全部返回，即使

另一个表找不到对等字段记录，此时可以使用外连接。

*a.左外连接

----使用LEFT OUTER JOIN...ON...语法------

select e.ename,e.sal,e.deptno,d.dname

from emp e left outer join dept d

on(e.deptno=d.deptno);

A left outer join B on(...)

以A表记录显示为主,B表记录为补充.当A表记录在B表找不到对等记录时,B以NULL方式补充。

b.右外连接

select e.ename,e.sal,e.deptno,d.dname

from emp e right outer join dept d

on(e.deptno=d.deptno);

上面语句是以dept表显示为主,emp为补充.如果emp没有对等记录,字段值以NULL补充。

select * from a

right outer join b on(a.id=b.id);

等价于

select * from b

left outer join a on(a.id=b.id)

----在JOIN...ON之前的外连接写法--------

//(+)所在表为补充表,另一方是主表

select * from a,b

where a.id(+)=b.id; //jb为主,ja为补充

c.全外连接

全外连接=左外连接+右外链接-(重复记录)

select * from a

full outer

join b on(a.id=b.id);

//查询部门编号,部门名称,部门员工人数

select d.deptno,

d.dname,

count(e.ename) num

from DEPT d left outer join EMP e

on(d.deptno=e.deptno)

group by d.deptno,d.dname

order by d.deptno;

EMPNO ENAME DEPTNO DNAME

... ... 10 ...

NULL NULL 40 ...

NULL NULL 50 ...

//按部门分组统计,count(*)和count(ename)的区别

count(*) = 1

count(ename) = 0

//查询部门在NEW YORK和CHICAGO的员工编号和员工名称

select e.empno,e.ename

from DEPT d join EMP e on(d.deptno=e.deptno)

where d.loc in ('NEW YORK','CHICAGO');

4)自连接

关联双方的表是同一个表。

//查询员工编号,员工名,上级编号,上级名称

select e.empno,e.ename,e.mgr,e1.ename

from EMP e left outer join

EMP e1 on(e.mgr=e1.empno);

//查询员工编号,员工名,所在部门名,上级编号,上级名称

select

e.empno,e.ename,d.dname,e.mgr,e1.ename

from EMP e

left outer join EMP e1 on(e.mgr=e1.empno)

left outer join DEPT d on(d.deptno=e.deptno);