【SQL】多表查询
多表查询，即查询可以从两个或多个表中获取数据。在Oracle中，有两种类型的连接格式：ANSI SQL连接格式和Oracle特有的连接格式。Oracle建议采用符合ANSI标准的连接格式。
1、内连接
（1）Oracle中特有的连接语法为：
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column1=table2.column2;
如：
SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname
2 FROM emp e ,dept d
3 WHERE e.deptno=d.deptno;
EMPNO ENAME JOB HIREDATE SAL DNAME
---------- ---------- --------- ------------------- ---------- --------------
7782 CLARK MANAGER 1981-06-09 00:00:00 2450 ACCOUNTING
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING
7934 MILLER CLERK 1982-01-23 00:00:00 1300 ACCOUNTING
7566 JONES MANAGER 1981-04-02 00:00:00 2975 RESEARCH
7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 RESEARCH
7876 ADAMS CLERK 1987-05-23 00:00:00 1100 RESEARCH
7902 FORD ANALYST 1981-12-03 00:00:00 3000 RESEARCH
7844 TURNER SALESMAN 1981-09-08 00:00:00 1500 SALES
7521 WARD SALESMAN 1981-02-22 00:00:00 1250 SALES
7499 ALLEN SALESMAN 1981-02-20 00:00:00 1600 SALES
7900 JAMES CLERK 1981-12-03 00:00:00 950 SALES
7698 BLAKE MANAGER 1981-05-01 00:00:00 2850 SALES
7654 MARTIN SALESMAN 1981-09-28 00:00:00 1250 SALES
13 rows selected.
（2）ANSI SQL的标准内连接语法为：
SELECT table1.column,table2.column
FROM table1 INNER JOIN table2
ON table1.column1=table2.column2;
如：
SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname
2 FROM emp e INNER JOIN dept d
3 ON e.deptno=d.deptno;
EMPNO ENAME JOB HIREDATE SAL DNAME
---------- ---------- --------- ------------------- ---------- --------------
7782 CLARK MANAGER 1981-06-09 00:00:00 2450 ACCOUNTING
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING
7934 MILLER CLERK 1982-01-23 00:00:00 1300 ACCOUNTING
7566 JONES MANAGER 1981-04-02 00:00:00 2975 RESEARCH
7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 RESEARCH
7876 ADAMS CLERK 1987-05-23 00:00:00 1100 RESEARCH
7902 FORD ANALYST 1981-12-03 00:00:00 3000 RESEARCH
7844 TURNER SALESMAN 1981-09-08 00:00:00 1500 SALES
7521 WARD SALESMAN 1981-02-22 00:00:00 1250 SALES
7499 ALLEN SALESMAN 1981-02-20 00:00:00 1600 SALES
7900 JAMES CLERK 1981-12-03 00:00:00 950 SALES
7698 BLAKE MANAGER 1981-05-01 00:00:00 2850 SALES
7654 MARTIN SALESMAN 1981-09-28 00:00:00 1250 SALES
13 rows selected.
2、外连接
（1）Oracle中特有的连接语法为：
--右外连接
SELECT table1.column,table2.column
FROM table1, table2
WHERE table1.column1(+)=table2.column2;
--左外连接
SELECT table1.column,table2.column
FROM table1, table2
WHERE table1.column1=table2.column2(+);
注意：
右外连接：当（+）符号在等号左边，将返回table2表中的所有数据。
左外连接：当（+）符号在等号右边，将返回table1表中的所有数据。
等号两边不能同时都加（+）符号
（2）ANSI SQL标准中的连接、右连接和全连接语法为：
SELECT table1.column,table2.column
FROM table1
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column=table2.column)];
3、交叉连接（笛卡尔积）
如果在进行连接的时候没有指定连接条件，则会产生笛卡尔积，返回的记录数是两个表中记录数的乘积。
4、自然连接
前提要求：两个表中有相同名称、相同数据类型的列。如果两个标的列名相同而数据类型不相同，也不能进行自然连接。自然连接使用NATRUAL JOIN关键字
如：
SQL> SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname
2 FROM emp e NATURAL JOIN dept d;
EMPNO ENAME JOB HIREDATE SAL DNAME
---------- ---------- --------- ------------------- ---------- --------------
7782 CLARK MANAGER 1981-06-09 00:00:00 2450 ACCOUNTING
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING
7934 MILLER CLERK 1982-01-23 00:00:00 1300 ACCOUNTING
7566 JONES MANAGER 1981-04-02 00:00:00 2975 RESEARCH
7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 RESEARCH
7876 ADAMS CLERK 1987-05-23 00:00:00 1100 RESEARCH
7902 FORD ANALYST 1981-12-03 00:00:00 3000 RESEARCH
7844 TURNER SALESMAN 1981-09-08 00:00:00 1500 SALES
7521 WARD SALESMAN 1981-02-22 00:00:00 1250 SALES
7499 ALLEN SALESMAN 1981-02-20 00:00:00 1600 SALES
7900 JAMES CLERK 1981-12-03 00:00:00 950 SALES
7698 BLAKE MANAGER 1981-05-01 00:00:00 2850 SALES
7654 MARTIN SALESMAN 1981-09-28 00:00:00 1250 SALES
13 rows selected.
