Oracle SQL多表查询
曾经一段时间我对oracle的多表查询搞的云里雾里，究其原因：oracle自己的语法和SQL国际标准语法混用。此文章仅适合oracle 菜鸟，老鸟直接飞过…
多表连接类型（SQL 1999标准）
• Cross joins
• Natural joins
• USING clause
• Full (or two-sided) outer joins
• Arbitrary join conditions for outer joins
SQL1999语法：
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];
语法解释：
table1.column --指明从中检索数据的表和列
CROSS JOIN --返回两个表的笛卡尔集
NATURAL JOIN --根据相同的列名连接两个表
JOIN table
USING column_name --根据列名执行等值连接
JOIN table ON
table1.column_name --根据ON 子句中的条件执行等值连接
= table2.column_name
LEFT/RIGHT/FULL OUTER
一般来说，从数据显示方式来讲，分为内连接和外连接
内连接：只返回满足连接条件的数据。
外连接：除了返回满足连接条的行以外，还返回左（右）表中，不满足条件的行，
称为左（右）连接
演示，主要以SQL标准为主，oracle 写法作对比。示例用户为scott、HR
解锁这两个用户语句：
alter user scott identified by tiger account unlock;
alter user hr identified by hr account unlock;
内连接
--Oracle的写法
select empno,ename,sal,dname,loc from emp,dept
where emp.deptno=dept.deptno;
-- SQL 99标准的写法
select empno,ename,job,sal,dept.deptno,dname,loc
from emp join dept on emp.deptno=dept.deptno;
或把join改为inner join
外连接
左外连接
Oracle 外连接语法：
SELECT table1.column, table2.column --右外连接
FROM table1, table2
WHERE table1.column(+) = table2.column;
SELECT table1.column, table2.column --左外连接
FROM table1, table2
WHERE table1.column = table2.column(+);
SQL 1999标准外连接语法见上面SQL1999语法
--Oracle的写法
外连接的符号是（＋），（＋）要放在字段名后。（＋）对面的那个表，会全部显示。
左外连接时，加号在等号的右边
SQL> select d.dname,e.ename,e.deptno
from dept d,emp e
where d.deptno = e.deptno(+)
order by d.deptno;
DNAME ENAME DEPTNO
-------------- ---------- ------
ACCOUNTING CLARK 10
ACCOUNTING KING 10
ACCOUNTING MILLER 10
RESEARCH JONES 20
RESEARCH FORD 20
RESEARCH ADAMS 20
RESEARCH SMITH 20
RESEARCH SCOTT 20
SALES WARD 30
SALES TURNER 30
SALES ALLEN 30
SALES JAMES 30
SALES BLAKE 30
SALES MARTIN 30
OPERATIONS
15 rows selected
-- SQL 99标准的写法接
SQL> select d.dname,e.ename,e.deptno
from dept d
left join emp e
on d.deptno = e.deptno
order by d.deptno;
DNAME ENAME DEPTNO
-------------- ---------- ------
ACCOUNTING CLARK 10
ACCOUNTING KING 10
ACCOUNTING MILLER 10
RESEARCH JONES 20
RESEARCH FORD 20
RESEARCH ADAMS 20
RESEARCH SMITH 20
RESEARCH SCOTT 20
SALES WARD 30
SALES TURNER 30
SALES ALLEN 30
SALES JAMES 30
SALES BLAKE 30
SALES MARTIN 30
OPERATIONS
15 rows selected
右外连接
--oracle的写法
SQL> select empno, ename, job, sal, dept.deptno, dname, loc
2 from emp, dept
3 where emp.deptno(+) = dept.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
----- ---------- --------- --------- ------ -------------- -------------
7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS
7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS
7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS
7369 SMITH CLERK 800.00 20 RESEARCH DALLAS
7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected
--SQL1999标准写法
SQL> select empno, ename, job, sal, dept.deptno, dname, loc
2 from emp
3 right join dept
4 on emp.deptno = dept.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
----- ---------- --------- --------- ------ -------------- -------------
7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS
7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS
7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS
7369 SMITH CLERK 800.00 20 RESEARCH DALLAS
7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected
全连接
--SQL1999标准写法
SQL> select empno, ename, job, sal, d.deptno, dname, loc
2 from emp e
3 full join dept d
4 on e.deptno = d.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
----- ---------- --------- --------- ------ -------------- -------------
7369 SMITH CLERK 800.00 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS
7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS
7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
自连接
把表自身的镜像当成另外一个表
--oracle 的写法
SQL> select e.ename || ' works for ' || m.ename
2 from emp e, emp m
3 where e.empno = m.mgr;
E.ENAME||'WORKSFOR'||M.ENAME
-------------------------------
JONES works for FORD
JONES w
