Oracle之join
oracle多表联接
create table t_group_test(
cust_id number(12),
prod_id number(12),
prod_value number(12)
)
create table t_prod_info(
prod_id number(12),
prod_info varchar2(12));
SQL> select * from t_group_test;
CUST_ID PROD_ID PROD_VALUE
------------- ------------- -------------
101 2001 12
100 2001 12
100 2002 13
102 2002 13
103 2001 12
104 2003 14
6 rows selected
SQL> select * from t_prod_info ;
PROD_ID PROD_INFO
------------- ------------
2001 INFO_A
2003 INFO_B
2004 INFO_C
2005 INFO_D
2006 INFO_E
2007 INFO_F
6 rows selected
1. natural join 指明两表进行自然连接，并且连接是基于两表中所有同名字段
SQL> select cust_id,prod_id,prod_info
2 from t_group_test a
3 natural join t_prod_info b;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
101 2001 INFO_A
100 2001 INFO_A
103 2001 INFO_A
104 2003 INFO_B
当t_prod_info表增加一个冗余列并且列值均为空时
alter table t_prod_info add(cust_id number(12));
SQL> select cust_id,prod_id,prod_info
2 from t_group_test a
3 natural join t_prod_info b;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
SQL>
事实上，此时基于两个表中所有共同字段进行关联查询，由于冗余列值全为空值，无符合条件的查询结果。
SQL> alter table t_prod_info drop column cust_id ;
2.inner join 指明两表进行内连接，并且连接是基于指定条件，只取两表均符合条件的结果集
SQL> select cust_id,a.prod_id,prod_info
2 from t_group_test a
3 inner join t_prod_info b
4 on (a.prod_id=b.prod_id) ;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
101 2001 INFO_A
100 2001 INFO_A
103 2001 INFO_A
104 2003 INFO_B
3.full outer join 外部全连接
SQL> select cust_id,b.prod_id,prod_info
2 from t_group_test a
3 full outer join t_prod_info b
4 on (a.prod_id=b.prod_id) ;
CUST_ID PROD_ID PROD_INFO
---------- ------------- ------------
103 2001 INFO_A
100 2001 INFO_A
101 2001 INFO_A
104 2003 INFO_B
102
100
2006 INFO_E
2004 INFO_C
2005 INFO_D
2007 INFO_F
10 rows selected
4.left outer join 左外连接
SQL> select cust_id,b.prod_id,prod_info
2 from t_group_test a
3 left outer join t_prod_info b
4 on (a.prod_id=b.prod_id)
5 order by cust_id,b.prod_id,prod_info;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
100 2001 INFO_A
100
101 2001 INFO_A
102
103 2001 INFO_A
104 2003 INFO_B
6 rows selected
oracle特有之左外连接
SQL> select cust_id,b.prod_id,prod_info
2 from t_group_test a, t_prod_info b
3 where a.prod_id=b.prod_id(+)
4 order by cust_id,b.prod_id,prod_info ;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
100 2001 INFO_A
100
101 2001 INFO_A
102
103 2001 INFO_A
104 2003 INFO_B
6 rows selected
5.right outer join
SQL> select cust_id,b.prod_id,prod_info
2 from t_group_test a
3 right outer join t_prod_info b
4 on (a.prod_id=b.prod_id)
5 order by cust_id,b.prod_id,prod_info;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
100 2001 INFO_A
101 2001 INFO_A
103 2001 INFO_A
104 2003 INFO_B
2004 INFO_C
2005 INFO_D
2006 INFO_E
2007 INFO_F
8 rows selected
oracle特有连接语法之右外连接
SQL> select cust_id,b.prod_id,prod_info
2 from t_group_test a, t_prod_info b
3 where a.prod_id(+)=b.prod_id
4 order by cust_id,b.prod_id,prod_info ;
CUST_ID PROD_ID PROD_INFO
------------- ------------- ------------
100 2001 INFO_A
101 2001 INFO_A
103 2001 INFO_A
104 2003 INFO_B
2004 INFO_C
2005 INFO_D
2006 INFO_E
2007 INFO_F
8 rows selected
