转自：http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=4095208
Oracle语法&SQL1999语法：
1.笛卡尔乘积(CROSS JOIN)
第一张表的所有行会与第二张表的所有行进行连接，即n*m方式进行连接
oracle语法：
select count(*) from hr.employees a,hr.departments b;
2889行
雇员表的107行记录与部门表的27行记录相乘积（107*27），得到2889条记录
SQL1999语法：
select count(*) from hr.employees a cross join hr.departments b
2889行
2.等价连接
select a.department_id,b.department_id, a.employee_id, b.department_name
from hr.employees a, hr.departments b
where a.department_id = b.department_id
and a.department_id = 90
a.DEPARTMENT_ID | b.DEPARTMENT_ID | EMPLOYEE_ID | DEPARTMENT_NAME |
90 | 90 | 100 | Executive |
90 | 90 | 101 | Executive |
90 | 90 | 102 | Executive |
把在a表和b表，部门相同，且部门号为90的员工匹配出来。
SQL1999语法：
在sql1999语法中的naturaljion连接中，与oracle语法中的等值连接是有区别的.
先举例说明：
natural jion连接的条件是：两表连接中的列名要有相同的、相同的列名的值要相同的、相同的列名的数据类型要相同的。
sql-1:
selecta.employee_id, a.manager_id, a.department_id
from hr.employees a
where a.employee_id in (114, 115, 116, 117, 118, 119)
雇员表雇员114,115,116,117,118,119的相关结果集如下：
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
114 100 30
115 114 30
116 114 30
117 114 30
118 114 30
119 114 30
sql-2:
等值连接：
selecta.employee_id, a.manager_id, a.department_id
from hr.employees a, hr.departments b
where a.department_id = b.department_id
and a.department_id = 30
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
114 100 30
115 114 30
116 114 30
117 114 30
118 114 30
119 114 30
sql-3:
netural join:
selectemployee_id, manager_id, department_id
from hr.employees a natural
join hr.departments b
where department_id = 30
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
115 114 30
116 114 30
117 114 30
118 114 30
119 114 30
大家注意看，在sql-1中，我列出了雇员114~119的一些情况，在sql-2中，进行的是等值连接，而在sql-3中，进行的是NATURALJOIN连接.
sql-2与sql-3的结果集的区别在哪里？
sql-2的等值连接中有雇员114的记录，而sql-3的NATURALJOIN连接中没有雇员114的记录，为什么？
natural jion连接的条件是：
(1) 两表连接的列名要有相同的
(2) 两表连接的列名的数据类型要相同的
(3) 两表连接的列名的值要相同的，原因就在这里。
原因就是雇员表中部门30的雇员114的manager_id的值是100，而部门表中30号部门中没有manager_id=100的记录，所以，不符合条件(3)，故而neturaljoin连接中没有匹配雇员为为114的记录了，然而，等值连接则没有这样的条件，故而能匹配雇员为114的记录。
这就是NATURALJOIN与等值连接的区别。
其实NATURAL JOIN自然连接是两表中的相同的列的自然连接，换句话说，就是不需要人工干预，比方说，A表与B表有3列名相同，那么oracleNATURAL JOIN自然连接就连接相同的三列，自然连接的这相同的三列的列值是AND的关系，三列的条件必须同时满足才匹配，如果其中一列值不匹配，那么该列的列值就被抛弃。如果进行人工干预，比方说，我只需要使用其中一列作为连接列，那么这就是不是自然连接了，可以是USING子句，oracle等价连接来完成。
再举例一个：
看一个需要人工干预的等值连接：
selectb.department_id "b-manager_id",
a.department_id"a-department_id",
b.manager_id "b-department_id",
a.manager_id "a-manager_id"
from hr.employees a, hr.departments b
where a.department_id = b.department_id
and a.department_id = 30
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 100 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
看上述表格的第一行，a表列department_id,manager_id与b表department_id
,manager_id两列相同，但是这里我使用人工控制的方式来进行连接，就是说我选择了其中一个相同列deparament_id来进行连接，只有两表department_id匹配，就返回记录，而不管manager_id匹配不匹配.
自然连接则不同，下面举例：
selectdepartment_id "b-department_id",
department_id"a-department_id",
manager_id "b-manager_id",
manager_id "a-manager_id"
from hr.employees a natural join hr.departments b
where department_id = 30
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
看上图，自然连接就是两表所有相同的列进行连接，其实看sql语句也看出来了，上面的sql并没有连接条件，这也表示了是用两表中所有的相同的列进行连接，这是自然连接的第一个点，再有就是，比较两张表格的结果集，有所不同，b-manager_id=114与a-manager_id=100的结果集不符合自然连接的要求，故而自然连接的结果集中并没有显示，因为必须满足b-manager_id= a-manager_id的要求，其实，上述自然连接的sql可以等价如下语句：
selectb.department_id "b-department_id",
a.department_id"a-department_id",
b.manager_id "b-manager_id",
a.manager_id "a-manager_id"
from hr.employees a, hr.departments b
where a.department_id = 30
and a.department_id = b.department_id
and a.manager_id = b.manager_id
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
注意的是，自然连接的select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
上面讲的是两表连接。
自然连接中的多表（2张表以上）连接也是一样的。
selectdepartment_id "b-department_id",
department_id"a-department_id",
manager_id "b-manager_id",
manager_id "a-manager_id",
job_id "c-job_id"
from hr.employees a natural join hr.departments b natural join hr.jobs c
where department_id = 30
b-department_id a-department_id b-manager_id a-manager_id c-job_id |
30 30 114 114 PU_CLERK |
30 30 114 114 PU_CLERK |
30 30 114 114 PU_CLERK |
30 30 114 114 PU_CLERK |
30 30 114 114 PU_CLERK |
3.外连接
Oracle语法：
3.1 左外连接
select a.employee_id,a.department_id, b.department_id, b.department_name
from hr.employees a, hr.departments b
where a.department_id = b.department_id(+)
and a.employee_id in (100, 101, 178)
order by a.employee_id
EMPLOYEE_ID | DEPARTMENT_ID | DEPARTMENT_ID | DEPARTMENT_NAME |
100 | 90 | 90 | Executive |
101 | 90 | 90 | Executive |
178 | 空值 | 空值 | 空值 |
雇员178号没有部门号，这个如果连接条件是a.department_id =b.department_id,则不会匹配出雇员178，因为雇员表没有雇员178的的部门号，部门表也没有雇员178的部门号，空值与空值的匹配仍然是空值.但是如果是部门表b.department_id(+)这样与雇员表a.department_id进行连接，那么就会把雇员178给匹配出来.这就是oracle的左外连接.
SQL1999语法：
selecta.employee_id, a.department_id, b.department_id, b.department_name
from hr.employees a left outer join hr.departments b
on a.department_id = b.department_id
where a.employee_id in (100, 101, 178)
order by a.employee_id
得出一样的结果集
3.2 右外连接
selecta.employee_id, a.department_id, b.department_id, b.department_name
from hr.employees a, hr.departments b
where a.department_id(+) = b.department_id
and b.department_id in (110,120)
EMPLOYEE_ID | DEPARTMENT_ID | DEPARTMENT_ID | DEPARTMENT_NAME |
205 | 110 | 110 | Accounting |
206 | 110 | 110 | Accounting |
空值 | 空值 | 120 | Treasury |
部门号120没有雇员，如果这个连接条件为a.department_id =b.department_id,将不会匹配部门号120的记录，因为雇员表的没有部门为120的部门，为空值，部门表有120号部门，空值与部门120进行匹配，得到的结果仍然是空值。但是如果雇员表想知道部门表里面还有哪些部门是没有雇员的，怎么办？那么这样可以解决这个问题,雇员表department_id(+)与部门表department_id进行连接，则可以将部门表没有雇员的部门也匹配出来。这就是Oracle的右外连接.
SQL1999语法：
selecta.employee_id, a.department_id, b.department_id, b.department_name
from hr.employees a right outer join hr.departments b
on a.department_id = b.department_id
where b.department_id in (110,120)
得出一样的结果集
3.2 全外连接
oracle语法中并没有全外连接的写法，在sql1999语法中，有这个写法：
使用 fullouter join关键字来连接
(略)
4.自连接
oracle语法中的自连接，是在一张表中进行连接，雇员表中的员工与管理员的关系，可以在一张雇员表中找出来，比如员工的管理者是谁。
举例：
比如90号部门的雇员情况如下：
selectw.employee_id,
w.first_name,
w.last_name,
w.manager_id,
w.department_id
from hr.employees w
where w.department_id = 90
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | MANAGER_ID | DEPARTMENT_ID |
100 | Steven | King |
| 90 |
101 | Neena | Kochhar | 100 | 90 |
102 | Lex | De Haan | 100 | 90 |
员工101,102的的管理者为100，即Steven King.如果通过两张表的连接体现在一行上面.使用oracle的自连接可以实现这一点.
selectw.employee_id as "员工编号",
w.first_name || ' '||
w.last_name as "员工姓名",
m.employee_id as "管理者编号",
m.first_name ||' ' ||
m.last_name as "部门管理者",
m.manager_id as "部门管理者编号"
from hr.employees w, hr.employees m
where w.manager_id = m.employee_id
and w.department_id = 90
员工编号 | 员工姓名 | 管理者编号 | 部门管理者 | 部门管理者编号 |
101 | Neena Kochhar | 100 | Steven |
|
102 | Lex De Haan | 100 | Steven |
|
使用自连接，实现了一行记录即有员工，也有管理者的记录，其实就是把雇员ID为100的一行记录分别与雇员ID为101,102的两行记录进行了合并而已。
这就是oracle的自连接。
5.using子句进行表连接
sql1999语法：
其实using 子句是相对于natural join来说的，其实就是等值连接
举例：
selectdepartment_id "b-department_id",
department_id"a-department_id",
manager_id "b-manager_id",
manager_id "a-manager_id"
from hr.employees a join hr.departments b using (department_id)
where department_id = 30
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 100 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
相当于等值连接
注意的是，select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
也可以这样写：
selectdepartment_id "b-department_id",
department_id"a-department_id",
manager_id "b-manager_id",
manager_id "a-manager_id"
from hr.employees a join hr.departments b using(department_id,manager_id)
where department_id = 30
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
相等于自然连接。
注意的是，select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
6.on子句
sql1999语法：
其实on子句只不过是等值连接另外一种写法罢了。
举例说明：
selectb.department_id "b-department_id",
a.department_id"a-department_id",
b.manager_id "b-manager_id",
a.manager_id "a-manager_id"
from hr.employees a
join hr.departments b
on (a.department_id = b.department_id)
where a.department_id = 30
相当于：
selectb.department_id "b-department_id",
a.department_id"a-department_id",
b.manager_id "b-manager_id",
a.manager_id "a-manager_id"
from hr.employees a, hr.departments b
where a.department_id = 30
and b.department_id = a.department_id
得到相同的结果集：
b-department_id | a-department_id | b-manager_id | a-manager_id |
30 | 30 | 114 | 100 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
30 | 30 | 114 | 114 |
注意的是，在on子句中，select 列表中，允许有别名来限定表名，where 条件中允许有别名来限定表名。