写了一个例子,你可以参考一下:SQL> select * from a; C1 C2 ------- -------------------- 100 Steven 101 Neena 102 Lex 103 Alexander 104 Bruce 105 David6 rows selectedSQL> select * from b; C1 C2 ------- -------------------- 103 Alexander 104 Bruce 105 David 106 Valli 107 Diana 108 Nancy 109 Daniel7 rows selectedSQL> SQL> SELECT * 2 FROM a 3 WHERE c1 > 101 4 UNION 5 SELECT * 6 FROM b 7 WHERE c1 > 101 AND NOT EXISTS (SELECT 1 8 FROM a 9 WHERE a.c1 > 101 AND rownum <=1); C1 C2 ------- -------------------- 102 Lex 103 Alexander 104 Bruce 105 DavidSQL> SQL> SELECT * 2 FROM a 3 WHERE c1 > 106 4 UNION 5 SELECT * 6 FROM b 7 WHERE c1 > 106 AND NOT EXISTS (SELECT 1 8 FROM a 9 WHERE a.c1 > 106 AND rownum <=1); C1 C2 ------- -------------------- 107 Diana 108 Nancy 109 DanielSQL> SQL> SELECT * 2 FROM a 3 WHERE c1 = 101 4 UNION 5 SELECT * 6 FROM b 7 WHERE c1 =101 AND NOT EXISTS (SELECT 1 8 FROM a 9 WHERE a.c1 = 101 AND rownum <=1); C1 C2 ------- -------------------- 101 NeenaSQL> SQL> SELECT * 2 FROM a 3 WHERE c1 = 104 4 UNION 5 SELECT * 6 FROM b 7 WHERE c1 =104 AND NOT EXISTS (SELECT 1 8 FROM a 9 WHERE a.c1 = 104 AND rownum <=1); C1 C2 ------- -------------------- 104 BruceSQL> SQL> SELECT * 2 FROM a 3 WHERE c1 = 108 4 UNION 5 SELECT * 6 FROM b 7 WHERE c1 =108 AND NOT EXISTS (SELECT 1 8 FROM a 9 WHERE a.c1 = 108 AND rownum <=1); C1 C2 ------- -------------------- 108 NancySQL>
select * from a where ...unionselect * from b where ... and (select count(*) from a where ...)=0
select decode(aCount.cnt,0,(select * from B),(select from A)) from (select count(*) cnt from A ) aCount
select a.c1,a.c2 from a union select b.c1,b.c2 from b where (b.c1,b.c2 ) not in(select a.c1,a.c2 from a)
------- --------------------
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
105 David6 rows selectedSQL> select * from b; C1 C2
------- --------------------
103 Alexander
104 Bruce
105 David
106 Valli
107 Diana
108 Nancy
109 Daniel7 rows selectedSQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 > 101
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 > 101 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 > 101 AND rownum <=1); C1 C2
------- --------------------
102 Lex
103 Alexander
104 Bruce
105 DavidSQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 > 106
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 > 106 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 > 106 AND rownum <=1); C1 C2
------- --------------------
107 Diana
108 Nancy
109 DanielSQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 101
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =101 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 101 AND rownum <=1); C1 C2
------- --------------------
101 NeenaSQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 104
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =104 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 104 AND rownum <=1); C1 C2
------- --------------------
104 BruceSQL>
SQL> SELECT *
2 FROM a
3 WHERE c1 = 108
4 UNION
5 SELECT *
6 FROM b
7 WHERE c1 =108 AND NOT EXISTS (SELECT 1
8 FROM a
9 WHERE a.c1 = 108 AND rownum <=1); C1 C2
------- --------------------
108 NancySQL>
where ...unionselect * from b
where ...
and (select count(*) from a
where ...)=0
union
select b.c1,b.c2 from b where (b.c1,b.c2 ) not in(select a.c1,a.c2 from a)