Oracle表连接

语法格式:

Sql代码

1. select 字段列表  
2. from  table1,table2  
3. where table1.column1=table2.column2; 

select 字段列表from  table1,table2where table1.column1=table2.column2;

说明:

在where子句中指定连接条件

当被连接的多个表中存在同名字段时,必须在该字段前加上"表名"作为前缀.

连接的类型

Oracle8i之前的表连接：

等值连接(Equijoin)

非等值连接(Non-Equijoin)

外连接(Outer join):-->左外连接-->右外连接

自然连接(Self join)

Oracle9之后新引入的连接形式(支持SQL99规范)

交叉连接(Cross join)

自然连接(Natural join)

使用Using子句建立连接

使用on子句建立连接

外连接(Outer join):-->左外连接-->右外连接-->全外连接

等值连接(Equijoin)

Sql代码

1. select empno,ename,sal,emp.deptno,dname from emp,dept  
2. where emp.deptno = dept.deptno; 

select empno,ename,sal,emp.deptno,dname from emp,deptwhere emp.deptno = dept.deptno;

多表连接中：

• 可使用AND操作符增加查询条件
• 使用表别名可以简化查询
• 使用表名(表别名)前缀可提高查询效率
• 为了连接n个表,至少需要n-1个连接条件

非等值连接(Non-Equijoin)

外连接(Outer join)

使用外连接可以看到参与连接的某一方不满足连接条件的记录

外连接运算符为(+)

传统的外连接分为左外连接和右外连接两种

语法格式:

Sql代码

1. select 字段列表  
2. from  table1,table2  
3. where table1.column1(+)=table2.column2; 

select 字段列表from  table1,table2where table1.column1(+)=table2.column2;

Sql代码

1. select 字段列表  
2. from  table1,table2  
3. where table1.column1=table2.column2(+); 

select 字段列表from  table1,table2where table1.column1=table2.column2(+);

自连接(Self join)

Sql代码

1. select a.enpno,a.ename,a.ngr,b.ename  
2. from emp a,emp b  
3. where a.ngr = b.enpno; 

select a.enpno,a.ename,a.ngr,b.enamefrom emp a,emp bwhere a.ngr = b.enpno;

SQL99连接语法

SQL1999规范中规定的连接查询语法

Sql代码

1. select  字段列表  
2. from   table1  
3. [cross join table2] |  
4. [natural join table2] |  
5. [join table2 using(字段名)] |  
6. [join table2 on(table.column_name=table2.column_name)] |  
7. [(left | right | full out ) join table2  
8. on(table1.column_name=table2.column_name)]; 

select  字段列表from   table1[cross join table2] |[natural join table2] |[join table2 using(字段名)] |[join table2 on(table.column_name=table2.column_name)] |[(left | right | full out ) join table2on(table1.column_name=table2.column_name)];

交叉连接(Cross join)

Cross join 产生了一个笛卡尔集，其效果等同于再两个表进行连接时未使用where子句限定连接条件;

Sql代码

1. select empno,ename,sal,emp.deptno,dname  
2. from emp cross join dept; 

select empno,ename,sal,emp.deptno,dnamefrom emp cross join dept;

自然连接(Natural join)

Natural join基于两个表中的全部同名列建立连接

• 从两个表中选出同名列的值均对应相等的所有行
• 如果两个表中的同名列的所有数据类型不同,则出错
• 不允许在参照列上使用表名或者别名作为前缀

Sql代码

1. select empno,ename,sal,emp.deptno,dname  
2. from emp natural join dept; 

select empno,ename,sal,emp.deptno,dnamefrom emp natural join dept;

Using子句

如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列(参照列)名.

Sql代码

1. select empno,ename,sal,emp.deptno,dname  
2. from emp join dept  
3. using(deptno); 

select empno,ename,sal,emp.deptno,dnamefrom emp join deptusing(deptno);

不允许在参照列上使用表名或者别名作为前缀

On子句

如果要参照非同名的列进行等值连接,或想设置任意的连接条件,可以使用On子句

Sql代码

1. select empno,ename,sal,emp.deptno,dname  
2. from emp join dept  
3. on(emp.deptno=dept.deptno); 

select empno,ename,sal,emp.deptno,dnamefrom emp join depton(emp.deptno=dept.deptno);

多表连接

使用SQL99连接语法,两个以上的表进行连接时应依次/分别指定相临的两个表之间的连接条件.

Sql代码

1. select  字段列表  
2. from   table1  
3. [cross join table2] |  
4. [natural join table2] |  
5. [join table2 using(字段名)] |  
6. [join table2 on(table.column_name=table2.column_name)] |  
7. [(left | right | full out ) join table2  
8. on(table1.column_name=table2.column_name)]  
9. [cross join table3] |  
10. [natural join table3] |  
11. [join table3 using(字段名)] |  
12. [join table3 on(table.column_name=table3.column_name)] |  
13. [(left | right | full out ) join table3  
14. on(table2.column_name=table3.column_name)]...; 

select  字段列表from   table1[cross join table2] |[natural join table2] |[join table2 using(字段名)] |[join table2 on(table.column_name=table2.column_name)] |[(left | right | full out ) join table2on(table1.column_name=table2.column_name)][cross join table3] |[natural join table3] |[join table3 using(字段名)] |[join table3 on(table.column_name=table3.column_name)] |[(left | right | full out ) join table3on(table2.column_name=table3.column_name)]...;

内连接和外连接

内连接(Inner join)

在SQL99规范中,内连接只返回满足连接条件的数据.

外连接(Outer join)

左外连接(Left Outer Join)

两个表在连接过程中除返回满足连接条件的行为外,还返回左表中不满足条件的行为，这种连接称为左外连接.

右外连接(Right Outer Join)

两个表在连接过程中除返回满足连接条件的行为外,还返回右表中不满足条件的行为,这种连接称为右外连接.

满外连接(Full Outer Join)

Oracle9开始新增功能,两个表在连接过程中除返回满足连接条件的行为外，还返回两个表中不满足条件的所有行为，这种连接称为满外连接.

子查询(Sub Query)

子查询子查询在主查询前执行一次

主查询使用子查询的结果

Sql代码

1. select 字段列表  
2. from table
3. where 表达式 operator (select 字段列表 from table); 

select 字段列表from tablewhere 表达式 operator (select 字段列表 from table);

使用子查询注意事项

• 在查询时基于未知时应考虑使用子查询

• 子查询必须包含在括号内

• 将子查询放在比较运算符的右侧,以增强可读性.

• 除非进行Top-N分析,否则不要再子查询中使用Order by子句

• 对单行子查询使用单行运算符

• 对多行子查询使用多行运算符

单行子查询

单行子查询只返回一行记录

对单行子查询可使用单行记录比较运算符

=--------------等于

>--------------大于

>=------------大于等于

<--------------小于

<=--------------小于等于

<>--------------不等于

Sql代码

1. select * from emp  
2. where sal>(select sal from emp where empno=7000); 

select * from empwhere sal>(select sal from emp where empno=7000);

子查询空值/多值问题

• 如果子查询未返回任何行,则主查询页不会返回任何结果

• 如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符

• 如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符

多行子查询

多行子查询返回多行记录

对多行子查询只能使用多行记录比较运算符

in--------------等于列表中的任何一个

any--------------和子查询返回的任意一个值比较

all--------------和子查询返回的所有值比较

Sql代码

1. select * from emp  
2. where sal>any(select avg(sal) from emp group by deptno); 

select * from empwhere sal>any(select avg(sal) from emp group by deptno);

Sql代码

1. select * from emp  
2. where sal>all(select avg(sal) from emp group by deptno); 

select * from empwhere sal>all(select avg(sal) from emp group by deptno);

Sql代码

1. select * from emp  
2. where job in(select job from emp where ename='martin' or ename='ssss'); 

select * from empwhere job in(select job from emp where ename='martin' or ename='ssss');

TopN查询

在oracle中通常采用子查询的方式来实现Top n查询

Sql代码

1. select 字段列表  
2. from(select 字段列表 from table order by 排序字段)  
3. where rownum <=n;  
4. ------------------------------------------------------------
5. select *  
6. from(select * from emp order by sal desc)  
7. where rownum <=5;