外连接的一个例子

条件：

Table: t_a Table: t_b Table: t_c

ID	GENDER
1	F
2	M

ID	NAME
1	Oracle
2	IBM
3	SAP
4	Nokia

ID	DESCRIPTION
2	Home
3	School

结果：

ID	GENDER	NAME	DESCRIPTION
1	F	Oracle
2	M	IBM	Home
3		SAP	School

[@more@]

条件：

Table: t_a Table: t_b Table: t_c

ID	GENDER
1	F
2	M

ID	NAME
1	Oracle
2	IBM
3	SAP
4	Nokia

ID	DESCRIPTION
2	Home
3	School

结果：

ID	GENDER	NAME	DESCRIPTION
1	F	Oracle
2	M	IBM	Home
3		SAP	School

SQL> create table t_a(id int,gender varchar2(1));

Table created.

SQL> create table t_b(id int,name varchar2(20));

Table created.

SQL> create table t_c(id int, description varchar2(10));

Table created.

SQL> insert into t_a values(1,'F');

1 row created.

SQL> insert into t_a values(2,'M');

1 row created.

SQL> insert into t_b values(1,'Oracle');

1 row created.

SQL> insert into t_b values(2,'IBM');

1 row created.

SQL> insert into t_b values(3,'SAP');

1 row created.

SQL> insert into t_b values(4,'Nokia');

1 row created.

SQL> insert into t_c values(2,'Home');

1 row created.

SQL> insert into t_c values(3,'School');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_a;

ID G

---------- -

1 F

2 M

SQL> select * from t_b;

ID NAME

---------- --------------------

1 Oracle

2 IBM

3 SAP

4 Nokia

SQL> select * from t_c;

ID DESCRIPTIO

---------- ----------

2 Home

3 School

SQL> select b.id,b.name,a.gender,c.description

2 from t_a a,t_b b,t_c c

3 where 1 = 1

4 and (a.id = b.id or c.id = b.id)

5 and b.id=c.id(+)

6 and b.id = a.id(+);

ID NAME G DESCRIPTIO

---------- -------------------- - ----------

1 Oracle F

2 IBM M Home

3 SAP School

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7988176/viewspace-981080/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7988176/viewspace-981080/