Refine Qurey
Hi,
I am seeking a better way to refine the following conditional query.
DROP TABLE t1;
CREATE TABLE t1 (a NUMBER);
DROP TABLE t2;
CREATE TABLE t2 (b NUMBER);
DROP TABLE t3;
CREATE TABLE t3 (c NUMBER);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (3);
COMMIT;
/* Conditional
if match found in t2 then
use t2
else
find match in t3
end if;
*/
SELECT t1.a FROM t1,t2 WHERE t1.a = t2.b
UNION
SELECT t1.a FROM t1,t3 WHERE t1.a = t3.c AND t1.a NOT IN (SELECT b FROM t2)
DROP TABLE t1;
CREATE TABLE t1 (a NUMBER);
DROP TABLE t2;
CREATE TABLE t2 (b NUMBER);
DROP TABLE t3;
CREATE TABLE t3 (c NUMBER);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (3);
COMMIT;
/* Conditional
if match found in t2 then
use t2
else
find match in t3
end if;
*/
SELECT t1.a FROM t1,t2 WHERE t1.a = t2.b
UNION
SELECT t1.a FROM t1,t3 WHERE t1.a = t3.c AND t1.a NOT IN (SELECT b FROM t2)
0