前言：这几天学习oracle，把自己对于关联查询的理解，记录下。如有错误请指正！
交欢连接又称为“笛卡儿积连接”，是两个或多个表之间的无条件连接。一个表中所有的记录与其它表的所有的记录进行连接。
如果进行连接的表中的数据行数分别为“n1，n2、n3”,那么交叉连接后结果集中有n1xn2xn3条记录。
内连接是种常用的关联查询方式，使用inner join来实现，其中inner是可以省略的语法如下：
select colums_list
from table_name1 [inner] join table_name2
on join_codition
参数说明：
SELECT table1.column,talbe2.column[,…]
FROM table1,table2[,…]
WHERE condition;
例如：
SQL> select e.empno 员工号,e.ename 员工姓名,e.job 职务,d.dname 2 from emp e inner join dept d 3 on e.deptno = d.deptno; ------------------------------------------------或者------------------------------------------------------------------------------- SQL> select e.empno 员工号,e.ename 员工姓名,e.job 职务,d.dname 2 from emp e ,dept d where e.deptno = d.deptno; 员工号 员工姓名 职务 DNAME ---------- -------------------- ------------------ ---------------------------- 7369 SMITH CLERK RESEARCH 7499 ALLEN SALESMAN SALES 7521 WARD SALESMAN SALES 7566 JONES MANAGER RESEARCH 7654 MARTIN SALESMAN SALES 7698 BLAKE MANAGER SALES 7782 CLARK MANAGER ACCOUNTING 7788 SCOTT ANALYST RESEARCH 7839 KING PRESIDENT ACCOUNTING 7844 TURNER SALESMAN SALES 7876 ADAMS CLERK RESEARCH 员工号 员工姓名 职务 DNAME ---------- -------------------- ------------------ ---------------------------- 7900 JAMES CLERK SALES 7902 FORD ANALYST RESEARCH 7934 MILLER CLERK ACCOUNTING 已选择14行。
外连接分为：左外连接和右外连接、完全连接
语法：
SELECT table1.column, table2.column[,…]
FROM table1 LEFT JOIN table2[,]
ON table1.column <operator> table2.column[,…];
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column <operator>
table2.column(+)[…];
例如：
SQL> select e.empno,e.ename,e.job,d 2 .dname 3 from emp e left join dept d 4 on e.deptno = d.deptno; -----------------------------------------------------------或者---------------------------------------------------------------------------------- SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno = d.deptno(+); EMPNO ENAME JOB DNAME ---------- -------------------- ------------------ ---------------------------- 7934 MILLER CLERK ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7782 CLARK MANAGER ACCOUNTING 7902 FORD ANALYST RESEARCH 7876 ADAMS CLERK RESEARCH 7788 SCOTT ANALYST RESEARCH 7566 JONES MANAGER RESEARCH 7369 SMITH CLERK RESEARCH 7900 JAMES CLERK SALES 7844 TURNER SALESMAN SALES 7698 BLAKE MANAGER SALES EMPNO ENAME JOB DNAME ---------- -------------------- ------------------ ---------------------------- 7654 MARTIN SALESMAN SALES 7521 WARD SALESMAN SALES 7499 ALLEN SALESMAN SALES 9527 LIKE PRINTER
SELECT table1.column, table2.column[,…]
FROM table1 RIGHT JOIN table2[,…]
ON table1.column <operator> table2.column[…];
例如：
语法：
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column (+)<operator>
table2.column[…];
例如：
SQL> select e.empno,e.ename,e.job,d 2 .dname 3 from emp e left join dept d 4 on e.deptno = d.deptno; -------------------------------------------------------------或者-------------------------------------------------------------------- SQL> select e.empno,e.ename,e.job,d.dname 2 from emp e,dept d 3 where e.deptno (+)= d.deptno; EMPNO ENAME JOB DNAME ---------- -------------------- ------------------ ---------------------------- 7369 SMITH CLERK RESEARCH 7499 ALLEN SALESMAN SALES 7521 WARD SALESMAN SALES 7566 JONES MANAGER RESEARCH 7654 MARTIN SALESMAN SALES 7698 BLAKE MANAGER SALES 7782 CLARK MANAGER ACCOUNTING 7788 SCOTT ANALYST RESEARCH 7839 KING PRESIDENT ACCOUNTING 7844 TURNER SALESMAN SALES 7876 ADAMS CLERK RESEARCH EMPNO ENAME JOB DNAME ---------- -------------------- ------------------ ---------------------------- 7900 JAMES CLERK SALES 7902 FORD ANALYST RESEARCH 7934 MILLER CLERK ACCOUNTING OPERATIONS 已选择15行。
在Oracle数据库中，全外连接的表示方式为
SELECT table1.column, table2.column[,…]
FROM table1 FULL JOIN table2[,…]
ON table1.column1 = table2.column2[…];
例如：
SQL> select e.empno,e.ename,d.deptno,d.dname from emp e full join dept d on d.deptno = e.deptno; EMPNO ENAME DEPTNO DNAME ---------- -------------------- ---------- ----------------------------9528 LIKE 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING EMPNO ENAME DEPTNO DNAME ---------- -------------------- ---------- ---------------------------- 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 40 OPERATIONS 已选择17行。
对于（+）操作符：
在应用开发中，用户可能拥有“自引用式”的外键，“自引用式”外键是指表中的一个列可以是该表主键的一个外键。
例如：
SQL> select em2.ename 管理者,em1.ename 下属员工 2 from emp em1 left join emp em2 3 on em1.mgr = em2.empno 4 order by em1.mgr; 管理者 下属员工 -------------------- -------------------- JONES FORD JONES SCOTT BLAKE JAMES BLAKE TURNER BLAKE MARTIN BLAKE WARD BLAKE ALLEN CLARK MILLER SCOTT ADAMS KING CLARK KING BLAKE 管理者 下属员工 -------------------- -------------------- KING JONES FORD SMITH KING LIKE EAST 已选择16行。
交叉连接的执行结果是一个笛卡儿积，那么这种查询结果是非常冗余的，但可以通过where子句来过滤。
QL> select count(*) from emp; COUNT(*) ---------- 16 SQL> select count(*) from dept; COUNT(*) ---------- 4 SQL> select count(*) from emp cross join dept; COUNT(*) ---------- 64
多表关联查询（ORACLE版）,布布扣,bubuko.com
原文：http://www.cnblogs.com/zxdBlog/p/3614548.html