现在有这样一个案例：
表1
字段A 字段B 字段C
111 222 1
111 333 1
表2
字段A 字段B 字段D
111 222 2
111 444 2
用字段A 和字段B作为联接条件 ，希望得到结果集：
字段A 字段B 字段C 字段D
111 222 1 2
111 333 1 0或空
111 444 0或空 2
请问该用什么联接查询来实现
------解决方案--------------------
select * from tab1 ,tab2 where tab1.a=tab2.a and tab1.a=tab2.a
union
select * from tab1 ,tab2 where tab1.a=tab2.a(+) and tab1.a=tab2.a(+)
union
select * from tab1 ,tab2 where tab1.a(+)=tab2.a and tab1.a(+)=tab2.a
这样保证结果是对的
简化点可以只union 后两个
------解决方案--------------------
- SQL code
CREATE TABLE T1(A VARCHAR2(10),B VARCHAR2(10),C VARCHAR2(2));CREATE TABLE T2(A VARCHAR2(10),B VARCHAR2(10),D VARCHAR2(2));INSERT INTO T1 VALUES(111,222,1);INSERT INTO T1 VALUES(111,333,1);INSERT INTO T2 VALUES(111,222,2);INSERT INTO T2 VALUES(111,444,2);SELECT * FROM T1;SELECT * FROM T2;SELECT NVL(T1.A,T2.A) A, NVL(T1.B,T2.B) B, C, D FROM T1 FULL JOIN T2 ON T1.A=T2.A AND T1.B=T2.B;结果为：A B C D ---------- ---------- -- -- 111 222 1 2 111 333 1 111 444 2
------解决方案--------------------
- SQL code
with t1 as(select '111' a,'222' b,'1' c from dualunion allselect '111' a,'333' b,'1' c from dual),t2 as(select '111' a,'222' b,'2' d from dualunion allselect '111' a,'444' b,'2' d from dual)select t1.a, t1.b, t1.c,t2.d from t1, t2 where t1.a = t2.a and t1.b = t2.b union select t1.a, t1.b, t1.c,t2.d from t1, t2 where t1.a = t2.a(+) and t1.b = t2.b(+) union select t2.a, t2.b, t1.c,t2.d from t1, t2 where t1.a(+) = t2.a and t1.b(+) = t2.b
------解决方案--------------------
左连接和右连接的并集
- SQL code
WITH t1 AS(SELECT 111 ca,222 cb,1 cc FROM dualUNION ALL SELECT 111,333,1 FROM dual),t2 AS (SELECT 111 ca,222 cb,2 cd FROM dualUNION ALL SELECT 111,444,2 FROM dual)SELECT t1.ca,t1.cb,t1.cc,t2.cd FROM t1 left join t2 ON t1.ca=t2.ca AND t1.cb=t2.cbUNION SELECT t2.ca,t2.cb,t1.cc,t2.cd FROM t1 right join t2 ON t1.ca=t2.ca AND t1.cb=t2.cb
------解决方案--------------------
精彩