oracle中的连接查询总结

连接查询是指基于多张表或视图的查询。使用连接查询时，应指定有效的查询条件，不然可能会导致生成笛卡尔积。如现有部门表dept，员工表emp,以下查询因查询条件无效，而产生笛卡尔积：   (各语句中字段不做解释，主要显示逻辑关系）

select dept.dname,emp.ename from dept,emp where dept.name = ‘sales‘;

 有效查询条件应该指定dept表与emp表之间的连接关系。表之间连接关系多如下：

1.相等连接（=）

相等连接主要用于查询主从表之间的相关数据，如下：

select table1.col1,table2.col2 from table1,table2 where table1.col1 = table2.col2  ;

 同时可以用and指定其他查询条件，如修改第一句：

select dept.dname,emp.ename from dept,emp where dept.deptno = emp.deptno and dept.name = ‘sales‘;

 2.不等连接

不等连接指连接条件中使用除相等比较的其他比较操作，多用于在不同表之间显示特定范围信息。如下：

select a.enma, a.sal .b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal;

 3.自连接

指在同一张表中的连接查询，主要用在自参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或者主从关系的表。如下，emp表中包含具有主从关系的两个字段，empno（雇员号），mgr（管理者号），为显示雇员与管理者之间的对应关系，可以用自连接：

select manager.ename  from emp manager, emp worker where manager.empno = worker.mgr and worker.ename = ‘KING‘;

4.内连接与外连接

select table1.column, table2.column from table1 [inner | left | right | full] join table2 on table1.column1 = table2.column2;

 以上分别为内连接、左连接，有链接，完全连接，on子句用于指定连接条件。

内连接：用于返回满足查询条件的所有记录，默认情况下，在指定连接查询时如果没有指定任何连接操作，那其就为内连接，目前以上查询语句皆为内连接，举例，以下两句相同：

select dept.dname,emp.ename from dept,emp where dept.deptno = emp.deptno and dept.name = ‘sales‘;

select dept.dname,emp.ename from dept inner join emp on dept.deptno = emp.deptno and dept.name = ‘sales‘;

 左外链接：返回满足连接条件的所有记录，同时返回不满足连接条件的连接操作符左边表的其他行，即左边返回所有记录，右表只返回满足查询条件的记录。为方便，引用他人的例子说明查询效果（参考：http://www.blogjava.net/hello-yun/archive/2011/04/08/347890.html），如下：

SELECT * FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A；

查询效果如下：

多表左外连接：

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTA.A=TESTC.A

 右外连接：如左外连接，即右表返回所有记录，坐标只返回满足查询条件的记录，如：

SELECT * FROM TESTA RIGHT JOIN TESTB ON TESTA.A=TESTB.A;

 查询效果如下：　

完全外连接：即返回满足查询条件的记录，也返回不满足查询条件的其他记录，举例如下：

SELECT * FROM TESTA FULL JOIN TESTB ON TESTA.A=TESTB.A;

查询效果如下： 

（+）操作：(+)执行外连接，将其放在显示较少行（完全满足连接条件行）的一端，需要注意的是(+)操作只能用于列，而不能用在表达式上，不能与or和in操作同时使用。

  用（+）实现左外连接：将上面左连接语句改为用(+)实现。

SELECT * FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A；
==
SELECT * FROM TESTA ，TESTB WHERE TESTA.A=TESTB.A(+)；

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTA.A=TESTC.A
==
SELECT * FROM TESTA, TESTB,TESTC where TESTA.A=TESTB.A(+) and TESTA.A=TESTC.A(+);

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTB.A=TESTC.A
==
SELECT * FROM TESTA, TESTB,TESTC where TESTA.A=TESTB.A(+) and TESTB.A=TESTC.A(+);

select a.dname, b.ename from dept a left join emp b on a.deptno = b.deptno and a.deptno =10;
==
select a.dname, b.ename from dept a, emp b where a.deptno = b.deptno(+) and a.deptno(+) =10;

 用（+）实现右外连接方式同左连接。

用（+）实现完全连接：

SELECT * FROM TESTA FULL JOIN TESTB ON TESTA.A=TESTB.A;
==
SELECT  TESTA.*,TESTB.* FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A
UNION
SELECT TESTA.*,TESTB.* FROM TESTB LEFT OUTER JOIN TESTA ON TESTA.A=TESTB.A

oracle中的连接查询总结

标签：