oracle连接类型

oracle连接类型

实现多表的简单连接时，如果仅仅通过select子句和from子句连接多个表，那么查询的结果将是一个通过笛卡儿积所生成的表。
所谓的笛卡儿积所生成的表，就是一个基本表中每一行与另一个基本表中的每一行连接在一起所生成的表，查询结果的行数是两个基本表的行数的积。
如下，使用oracle中的emp和dept两个表连接查询：

SQL> select * from emp,dept;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
------ --------- --------- ----- ---------- ------ ----- ------ ------ ------------ -------------
7369 SMITH CLERK 7902 17-12月-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 24-1月 -87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 02-4月 -87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-12月-81 1800 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 20 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 24-1月 -87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 02-4月 -87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1800 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1300 10 20 RESEARCH DALLAS
7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 24-1月 -87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-11月-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 02-4月 -87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 1800 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-1月 -82 1300 10 30 SALES CHICAGO
7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-12月-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-4月 -81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 24-1月 -87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-11月-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 02-4月 -87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-12月-81 1800 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-12月-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1300 10 40 OPERATIONS BOSTON
7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 20 40 OPERATIONS BOSTON

已选择 60 行。
以上得到60行数据，但是上面所得到的数据中冗余的数据很多，我们可以用where子句中消除部分冗余数据：

SQL> select * from emp,dept where emp.deptno=dept.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
------ --------- --------- ----- ---------- ------ ----- ------ ------ ------------ -------------
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 24-1月 -87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 02-4月 -87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 1800 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 20 20 RESEARCH DALLAS

已选择 15 行。

SQL>
结果比上面的简洁得多。

在连接查询的from子句中，多个表之间可以使用英文逗号进行分割。除了这种形式外，sql还支持使用关键字join进行连接。
在from子句中，使用join连接的语法形式如下：
from join_table1 join_type join_table2 [on(join_condition)]
[join_type...on join_condition,...]
join_table1,join_table2表示参与连接操作的表名。
join_type连接类型，连接类型有inner join(内连接),outer join(外连接)和cross join（交叉连接）
join_condition连接条件，由被连接表中的列和比较运算符，逻辑运算符等构成。还可以使用多组的join_type...on join_condition...子句，实现多个表的连接。


内连接
内连接时最常用的连接查询方式，使用inner join关键字进行指定。如果知识使用join关键字，默认表示内连接。
内连接使用比较运算符，在连接表的某些列之间进行比较操作，并列出表中与连接条件相匹配的数据行。
根据使用的比较方式不同，内连接又分为等值连接，自然连接和不等连接。
1.等值连接
所谓的等值连接，是指在连接条件中使用等号运算符比较被连接的值，也就是通过相等的列值连接起来的查询。
例：使用inner join连接两个不同的表emp和dept，on用来设置连接条件，使用where子句限制查询范围。检索accounting部门的员工信息，如下：

SQL> select empno,ename,sal,d.deptno,dname from emp e inner join dept d on e.deptno=d.deptno where dname='ACCOUNTING';
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7782 CLARK 2450 10 ACCOUNTING
7839 KING 5000 10 ACCOUNTING
7934 MILLER 1300 10 ACCOUNTING

确实从上面可以看出数据库中只用三条dname为accounding的记录。
同样的，我们也可以选择部门编号大于20的记录。

SQL> select empno,ename,sal,d.deptno,dname from emp e inner join dept d on e.deptno=d.deptno where d.deptno>20;
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7499 ALLEN 1600 30 SALES
7521 WARD 1250 30 SALES
7654 MARTIN 1250 30 SALES
7698 BLAKE 2850 30 SALES
7844 TURNER 1500 30 SALES
7900 JAMES 950 30 SALES

已选择 6 行。

2.不等连接
所谓的不等连接，就是在连接条件中使用除了等号之外的其他比较运算符，构成非等值连接查询。可以使用的比较运算符包括>,<,>=,<=,<>(不等于),!=,like,in和between等。
例如：通过emp表和salgrade表，查询员工的工资等级。使用between运算符，建立不等连接，
salgrade为工资等级，该表中的数据如下，其中工资分为5的等级，每条数据中有个最低工资和最高工资：

SQL> select * from salgrade;


GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select empno,ename,sal,grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

EMPNO ENAME SAL GRADE
------ --------- ------ ----------
7369 SMITH 800 1
7876 ADAMS 1100 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7900 JAMES 1800 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
7935 XIAOXUE 5000 5

已选择 15 行。



3.自然连接
自然连接是在两个表中寻找列名和数据类型都相同的字段，通过相同的字段将两个表连接在一起，并返回所有符合条件的结果。
使用自然连接，需要指定natural join连接关键字，但不需要指定连接的条件。
使用自然连接需要注意以下的几点：
（1）如果自然连接的两个表中有多个字段都满足名称和数据类型相同，那么它们都会被作为自然连接的条件。
（2）如果自然连接的两个表中，仅仅是字段名称相同，而字段的数据类型不同，那么使用该字段进行连接将会返回一个错误。
(3)由于oracle支持自然连接，那么在设计表时，应该尽量子啊不同的表中将具有相同含义de字段使用相同的名字和数据类型，如果总是对主键和外键使用相同的名字，嘛呢就可以满足自然连接。

SQL> select e.ename,e.sal,deptno,d.dname from emp e natural join dept d;


ENAME SAL DEPTNO DNAME
--------- ------ ------ ------------
SMITH 800 20 RESEARCH
ALLEN 1600 30 SALES
WARD 1250 30 SALES
JONES 2975 20 RESEARCH
MARTIN 1250 30 SALES
BLAKE 2850 30 SALES
CLARK 2450 10 ACCOUNTING
SCOTT 3000 20 RESEARCH
KING 5000 10 ACCOUNTING
TURNER 1500 30 SALES
ADAMS 1100 20 RESEARCH
JAMES 1800 30 SALES
FORD 3000 20 RESEARCH
MILLER 1300 10 ACCOUNTING
XIAOXUE 5000 20 RESEARCH

由上面可知在emp和dept表中有deptno字段相同

SQL> select * from emp e natural join dept d;


DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
------ ------ --------- --------- ----- ---------- ------ ----- ------------ -------
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 24-1月 -87 3000 RESEARCH DALLAS
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
20 7876 ADAMS CLERK 7788 02-4月 -87 1100 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 1800 SALES CHICAGO
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
20 7935 XIAOXUE MANAGER 7839 01-3月 -98 5000 500 RESEARCH DALLAS

已选择 15 行。