分类： Windows平台
2014-09-24 22:36:36
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语法中的natural jion连接中，与oracle语法中的等值连接是有区别的.
先举例说明：
natural jion连接的条件是：两表连接中的列名要有相同的、相同的列名的值要相同的、相同的列名的数据类型要相同的。
sql-1:
select a.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:
等值连接：
select a.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:
select employee_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中，进行的是NATURAL JOIN连接.
sql-2与sql-3的结果集的区别在哪里？
sql-2的等值连接中有雇员114的记录，而sql-3的NATURAL JOIN连接中没有雇员114的记录，为什么？
natural jion连接的条件是：
(1) 两表连接的列名要有相同的
(2) 两表连接的列名的数据类型要相同的
(3) 两表连接的列名的值要相同的，原因就在这里。
原因就是雇员表中部门30的雇员114的manager_id的值是100，而部门表中30号部门中没有manager_id=100的记录，所以，不符合条件(3)，故而netural join连接中没有匹配雇员为为114的记录了，然而，等值连接则没有这样的条件，故而能匹配雇员为114的记录。
这就是NATURAL JOIN与等值连接的区别。
其实NATURAL JOIN自然连接是两表中的相同的列的自然连接，换句话说，就是不需要人工干预，比方说，A表与B表有3列名相同，那么oracle NATURAL JOIN自然连接就连接相同的三列，自然连接的这相同的三列的列值是AND的关系，三列的条件必须同时满足才匹配，如果其中一列值不匹配，那么该列的列值就被抛弃。如果进行人工干预，比方说，我只需要使用其中一列作为连接列，那么这就是不是自然连接了，可以是USING子句，oracle等价连接来完成。
再举例一个：
看一个需要人工干预的等值连接：
select b.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匹配不匹配.
自然连接则不同，下面举例：
select department_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可以等价如下语句：
select b.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张表以上）连接也是一样的。
select department_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语法：
select a.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 右外连接
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 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语法：
select a.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语法中，有这个写法：
使用 full outer join关键字来连接
(略)
4.自连接
oracle语法中的自连接，是在一张表中进行连接，雇员表中的员工与管理员的关系，可以在一张雇员表中找出来，比如员工的管理者是谁。
举例：
比如90号部门的雇员情况如下：
select w.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的自连接可以实现这一点.
select w.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来说的，其实就是等值连接
举例：
select department_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 条件中也不允许有别名来限定表名。
也可以这样写：
select department_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子句只不过是等值连接另外一种写法罢了。
举例说明：
select b.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
相当于：
select b.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 条件中允许有别名来限定表名。
