1.等值连接
当需要的数据需要从多个表中查询得到时,需要采用多表连接查询,其语法形式如下:
select 表的字段名(可以是多张表的字段名)from 表(多个表) 查询条件;
可以在表名后面添加表的别名;例如 emp e;e为emp表的别名,使用emp表的时候可以使用表的别名。
例如查询员工的姓名,工资和部门编号。
select ename,sal,d.deptno from emp e,dept d where e.deptno = d.deptno;
结果:
为表起别名可以提高性能,可以避免表的字段名等等发生冲突。
2.多表等值连接
即,当需要的条件在多表之间时,进行的连接,
例如:select a.字段名,b.字段名,c.字段名 from A表 a,B表 b,C表 c where 条件1(a中值和b中的相等) and 条件2(a中值和c中的相等) and 条件3(c中值和b中的相等);
查询员工编号大于10,且奖金不为空的员工姓名,工资和编号。
select e.ename,m.sal,d.deptno from emp e,myemp m,dept d where e.deptno = d.deptno and m.comm=e.comm and d.deptno>10;
结果:
3.自然连接、USING子句和ON子句
如果两个表中有相同的字段名且字段的数据类型相同,则可以使用Oracle的自然连接实现两表之间的等值连接。自然连接是一种特殊的等值连接。
在使用自然连接的时候需要注意,如果两个表中的字段名相同,但数据类型不同,用自然连接会返回一个错误,
select e.ename,e.sal,d.dname from emp e natural join dept d;
结果:
上面采用NATIVE JOIN关键字实现了自然连接,也可以不使用自然连接,而使用USING子句明确指定连接字段,其SQL语句如下:
select e.ename,e.sal,d.dname from emp e join dept d using(deptno);
结果:
另外一点注意的是,不管是自然连接还是使用USING子句,在使用连接字段时,都不能在前面加上表的前缀,因为此时这个字段已经是连接字段,不再属于某个单独的表。
除了USING子句指定连接字段外,还可以使用ON子句完成类似的功能,上面使用USING子句的SQL语句,等价使用的ON子句的SQL语句如下:
select e.ename,e.sal,d.dname from emp e join dept d on(e.deptno=d.deptno);
结果:
ON子句比USING子句更加灵活,因为USING子句中只有一个字段名,也就是只有两个表中都有相同的字段名时,才能使用USING子句指定连接字段。而ON子句和等值连接一样,用于连接两个字段,其字段名可以不同。
4.自关联
还可以通过自关联,用一张表实现多表查询,SQL语句如下:
查询员工姓名和相应员工领导姓名:
select e.ename,e.sal from emp e,emp p where p.empno=e.mgr;
结果:
5.内连接和外连接
在标准SQL语句中,将两个表连接,仅返回匹配条件的行的连接称为内连接。等值连接就是内;连接的一种,因为等值连接仅返回满足等值条件的行。相对于等值连接而言,另一种连接就是非等值连接,也就是说连接条件不是“=”,而返回的行仅为满足这个非“=”连接条件的行。
6.非等值内连接
例如:查询员工入职日期在一定区间内的员工信息。(此处不做演示)
7.左外连接、右外连接、全外连接、笛卡尔积
1.左外连接
左外连接又称左连接,指在两个表之间的连接,返回内连接的结果,同时还返回左表中的未匹配的行,右表中 相应字段置空(右边加“(+)”号)。
例如:
select e.ename,e.sal,e.job from emp e,dept d where e.deptno=d.deptno(+);
结果:
2.右外连接
右外连接又称右连接,和左连接正好相反。
例如:
select e.ename,e.sal,e.job from emp e,dept d where e.deptno(+)=d.deptno;
结果:
3.全外连接
所谓全外连接,也称全连接,是指两个表在返回内连接结果的基础上,还返回左表及右表中未匹配的行,右表及左表中的字段置空,其SQL语句如下:
select e.ename,e.sal,e.job from emp e full outer join dept d using(deptno);
结果:
4.笛卡尔积
截止到现在为止,所介绍的多表连接查询都有连接条件,通过连接条件对连接进行限制,如果连接条件无效或者遗漏,结果是一个笛卡尔积,其中所有行的组合都被显示出来,即第一个表中的所有行连接到第二个表中的所有行。
笛卡尔积会产生大量的行。