oracle笔记三（Sql99语法）

1. 交叉连接cross join(相当于笛卡尔积效果)
Select e.ename,d.dname
From emp e cross join dept d;

2. 自然连接 natural join
Select e.ename,d.dname
From emp e natural join dept d;

3. 使用using子句的方式指定作等值连接的列
Select e.ename,d.dname
From emp e join dept d
Using(deptno)；

4. 使用on子句的方式指定作等值连接的列
Select e.ename,d.dname
From emp e join dept d
On (e.deptno=d.deptno);

Select e.ename,d.dname,l.city
From emp e join department d on(e.deptno=d.did)
Join locations l on(d.lid=l.locid);

5. 左外连接
Select e.ename,d.dname,e,deptno
From emp e left outer join dept d
On(e.deptno=d.deptno);

6. 右外连接
Select e.ename,d.dname,d,deptno
From emp e right outer join dept d
On(e.deptno=d.deptno);

7. 全外连接
Select e.ename,d.dname,d.deptno
From emp e full outer join dept d
On(e.deptno=d.deptno);

合并行（备份表、多条插入）
Merge into emp_bak e1
Using emp e2
On(e1.empno=e2.empno)
When matched then
Update set
e1.ename=e2.ename,e1.sal=e2.sal,e1.comm=e2.comm,e1.job=e2.job,e1.hiredate=e2.hiredate
when not matched then
insert (e1.empno,e1.ename,e1.sal,e1.comm,e1.hiredate,e1.deptno,e1.job)
values(e2.empno,e2.ename,e2.sal,e2.comm,e2.hiredate,e2.deptno,e2.job)