oracle 联接查询

[longtenggdf]

现在有这样一个案例：
表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

请问该用什么联接查询来实现

[mg_chen]

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 后两个

[q806294478]

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  

[horizonlyhw]

with t1 as(
select '111' a,'222' b,'1' c from dual
union all
select '111' a,'333' b,'1' c from dual
),
t2 as(
select '111' a,'222' b,'2' d from dual
union all
select '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

[小德]

左连接和右连接的并集

WITH t1 AS(
SELECT 111 ca,222 cb,1 cc FROM dual
UNION ALL 
SELECT 111,333,1 FROM dual
),t2 AS (
SELECT 111 ca,222 cb,2 cd FROM dual
UNION 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.cb
UNION 
SELECT t2.ca,t2.cb,t1.cc,t2.cd FROM t1 right join t2 ON t1.ca=t2.ca AND t1.cb=t2.cb

[小德]

精彩
[Quote=引用 2 楼 的回复:]
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……
[/Quote]

[longtenggdf]

[Quote=引用 1 楼 的回复:]

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……
[/Quote]
学习了，谢谢