union ,inner join 与(left,right)outer join 的区别

[cp430]

请问各位大侠union ,inner join 与(left,right)outer join 的区别
最好能举例说明一下~

[l2g32003]

join 操作在合并连接之前对记录进行排序

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
SQL> select a.ename,b.dname from emp a, dept b
2 where a.deptno=b.deptno;

ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ADAMS RESEARCH
FORD RESEARCH
SCOTT RESEARCH
JONES RESEARCH
ALLEN SALES
BLAKE SALES
MARTIN SALES
JAMES SALES
TURNER SALES
WARD SALES

已选择14行。
========================================== right outer join

SQL> select a.ename,b.dname from emp a right outer join dept b
2 on a.deptno=b.deptno;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS

已选择15行。
============================================= left outer join

SQL> select a.ename,b.dname from emp a left outer join dept b
2 on a.deptno=b.deptno;

ENAME DNAME
---------- --------------
MILLER ACCOUNTING
KING ACCOUNTING
CLARK ACCOUNTING
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
SMITH RESEARCH
JAMES SALES
TURNER SALES
BLAKE SALES
MARTIN SALES
WARD SALES
ALLEN SALES

已选择14行。
=============================================== full outer join

SQL> select a.ename,b.dname from emp a full outer join dept b
2 on a.deptno=b.deptno;

ENAME DNAME
---------- --------------
MILLER ACCOUNTING
KING ACCOUNTING
CLARK ACCOUNTING
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
SMITH RESEARCH
JAMES SALES
TURNER SALES
BLAKE SALES
MARTIN SALES
WARD SALES
ALLEN SALES
LG
OPERATIONS

已选择16行。
==================================================== (+)
SQL> select a.ename,b.dname from emp a ,dept b
2 where a.deptno(+)=b.deptno;

ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ADAMS RESEARCH
FORD RESEARCH
SCOTT RESEARCH
JONES RESEARCH
ALLEN SALES
BLAKE SALES
MARTIN SALES
JAMES SALES
TURNER SALES
WARD SALES
OPERATIONS

已选择15行。
======================================================================

利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.
例如,下面这条命令执行起来很慢

select a.empno from emp a where a.empno not in

(select empno from emp1 where job='SALE');

　　倘若利用外部联接,改写命令如下:

select a.empno from emp a ,emp1 b where a.empno=b.empno(+)

and b.empno is null and b.job='SALE';

可以发现,运行速度明显提高.

[LGQDUCKY]

都是数据连接
SELECT * FROM A
union ALL
SELECT * FROM B

SELECT * FROM A INNER JOIN B ON A.ID=B.ID WHERE A.ID='XX'

[bzszp]

SQL> select * from tb;

ADDR NAME OTH
---------- ---------- ----------
3 asd kjl
2 aaa kjl
1 kjl
内连接
SQL> select * from tb a,tb b where a.addr=b.addr;

ADDR NAME OTH ADDR NAME OTH
---------- ---------- ---------- ---------- ---------- ----------
1 kjl 1 kjl
2 aaa kjl 2 aaa kjl
3 asd kjl 3 asd kjl
union:不包含重复纪录
SQL> select * from tb
2 union
3 select * from tb;

ADDR NAME OTH
---------- ---------- ----------
1 kjl
2 aaa kjl
3 asd kjl

外连接
SQL> select * from a1;

BBB
----------
101
102
103
104
105

SQL> select * from a2;

BBB CCC
---------- --------------------
101
102
105

SQL> select * from a1,a2 where a1.bbb(+)=a2.bbb;

BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
105 105

SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);

BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
103
104
105 105

SQL>

[cp430]

这回懂了，谢谢各位！接分吧！

[niuniu777]

注意数据记录数！