oracle 连接 join at

--1 等值连接
SELECT * FROM EMP e, dept d where e.deptno = d.deptno;


SELECT * FROM EMP e join DEPT d on e.deptno = d.deptno;


--2 外连接
--Dept 是主表,全部显示,把dept写在前面
SELECT * FROM SCOTT.DEPT D ,EMP E
WHERE D.DEPTNO = E.DEPTNO(+)

-- Dept 是主表,全部显示,把dept写在前面
SELECT D.DEPTNO, COUNT(E.EMPNO)
  FROM DEPT D
  LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
 GROUP BY D.DEPTNO
 
 
 --3全连接 ((左右两表都不加限制) )
 SELECT * FROM DEPT D FULL JOIN EMP E ON D.DEPTNO = E.DEPTNO;
 

--4自然连接  NATURAL (自动找到匹配的字段)
SELECT * FROM EMP E NATURAL JOIN  DEPT D


SELECT DEPTNO, COUNT(E.EMPNO)
  FROM DEPT D
  NATURAL LEFT JOIN EMP E   -- 重点
 GROUP BY DEPTNO

SELECT * FROM DEPT D NATURAL FULL JOIN EMP E;

--5 自连接

--6 不等连接

--7笛卡尔集

 

常见的有下面五种连接方式:按照查询计划
嵌套循环连接(nestedloops),
排序合并连接(sort-merge),
群集连接(cluster join),
哈希连接(hash join),
索引连接(index join)


连接类型：
内连接，相等连接
外联结，包括左外连接，右外连接，全外连接
自连接
自然连接（相等连接的一种特列）
不等连接

 

 

 

http://www.kaoshi365.com/oraclerz/show_1180_4101_2.html

 

　首先还是说明一下，为什么要使用连接(join)?原因有两点：

　　1)经常有需要访问多个表中字段的情况

　　2)用子查询也可以实现上面的功能，但是效率相当低下。使用连接可以大大提高效率。

　　连接(Join)的基本结构：

　　select ... from table1 t1 join table2 t2 on ti.id = t2.id;

　　join前后是两个需要连接的表，on表示的是连接的条件。传统的写法是不使用join关键字，使用where表示条件：

　　select ... from table1 t1, table2 t2 where ti.id = t2.id;

　　一般是两个表进行连接，三个表的连接语法稍有不同:

　　普通写法：select ... from a join b on a.id = b.id

　　join c on b.id = c.id;

　　传统写法：select ... from a, b, c where a.id = b.id and b.id = c.id;

　　1，对等连接

　　上面的就是一种对等连接，即默认的join方式。特点是只显示连接的表中存在而且相等的记录。其它的

　　记录均不显示。

　　传统的写法也是一种对等连接，只显示匹配条件的记录。

　　2，左/右连接

　　有时需要将进行连接的其中一个表作为基准显示全部的数据，根据连接条件在其它表中可能会出现无法

　　匹配的数据，此时就用空值来代替。比如显示员工信息时，有的员工可能还没有分配到某个具体的部门

　　(新进员工)，其所属部门一项就没有数据，但是员工仍然需要显示，即员工表数据需要全部显示。

　　具体语法：

　　select ... from table1 t1 left join table2 t2 on ti.id = t2.id; --左连接

　　或

　　select ... from table1 t1 right join table2 t2 on ti.id = t2.id; --右连接

　　左还是右的区别，简单地说左(右)连接就是指join的左(右)边是基准表，全部数据都要显示，根据

　　连接条件无法进行连接的记录就用空值代替。

　　比如:

　　select e.empno, e.ename, d.dname from emp e left join dept d on e.deptno = d.deptno;

　　还没有分配到某个具体的部门的员工的e.deptno肯定是没有值的，无法满足“e.deptno = d.deptno”的

　　条件，但由于是左连接，左边的员工表的数据必然会显示，这时该员工的部门名称就是空值。

　　若是right join ... on，就表示部门表的数据全部显示，员工表中无法连接的数据就是空值代替，意思

　　就是说这个部门还没有员工，因为在员工表中没有一条记录的deptno与该部门的deptno相匹配(可能是

　　刚刚成立的新部门)

　　3，全连接

　　简单说就是左右连接的全部表示，根据连接条件在任意一方表中出现无法匹配的情况，不满足条件的部

　　分均用空值代替。

　　具体语法：

　　select ... from table1 t1 full join table2 t2 on ti.id = t2.id;

用2中例子来说，可能有时候需要把整个公司的员工和部门信息做一个总览，可以对员工表和部门表做一

　　个全连接：

　　select e.empno, e.ename, d.dname from emp e full join dept d on e.deptno = d.deptno;

　　就可以知道员工所属的部门以及部门下面的员工，那些新进员工和刚刚成立的新部门也会显示在其中，

　　也就是结果中有空值的部分。

　　但需要注意的是，全连接的效率比较低，通常不推荐使用。

　　PS：

　　在Oracle中，对于左/右连接和全连接还有一种特殊的表示方法，使用“(+)”符号，例如：

　　select ... from table1 t1, table2 t2 where ti.id = t2.id(+); --左连接

　　select ... from table1 t1, table2 t2 where ti.id(+) = t2.id; --右连接

　　select ... from table1 t1, table2 t2 where ti.id(+) = t2.id(+); --全连接

　　4，自然连接

　　这是Oracle的一种特有的连接方式。它自动连接两个表中数据类型和名称相同的字段，然后根据条件自

　　动地将他们连接起来。

　　具体语法：

　　select ... from table1 t1 left natural join table2 t2;

　　比如：

　　select emp.ename, dept.dname from emp natural join dept;

　　这里并没有指定连接的条件，实际上oracle自动的将员工表中的deptno和部门表中的deptno做了连接。

　　也就是实际上相当于：

　　select emp.ename,dept.dname from emp join dept on emp.deptno = dept.deptno;

　　因为这两张表的这两个字段deptno的类型和个名称完全相同。所以使用natural join时被自然的连接在

　　一起了。

　　PS：

　　如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

　　5，自连接

　　简单地说就是自己和自己进行连接，连接的条件就是本表的主键。通常认为在数据库中存在父子关系

　　时，应该设计成两张表。但如果父子都是同一实体，就可以简化设计成一张表。比如，查看员工及直接

　　上级的名字: (上级也是员工，上级的数据也是员工数据，也应该存储在员工表中。)