I am trying to improve this query? Is there a better way to write the query and get the same results. I hate the fact that I am querying a table 3 times.
Select statement
WITH t1 AS
(SELECT 101 AS ID
, 1 AS a
, NULL AS b
, 5 AS c
FROM DUAL
UNION ALL
SELECT 102
, NULL
, 2
, 5
FROM DUAL
UNION ALL
SELECT 101
, 3
, 5
, 7
FROM DUAL
UNION ALL
SELECT 101
, 1
, NULL
, NULL
FROM DUAL)
, t2 AS
(SELECT 101 AS ID
, 1 AS x
FROM DUAL
UNION ALL
SELECT 101
, 5
FROM DUAL
UNION ALL
SELECT 102
, 5
FROM DUAL
UNION ALL
SELECT 102
, 2
FROM DUAL)
SELECT *
FROM t1
WHERE t1.ID = 101
AND ( t1.a IS NULL
OR t1.a IN (SELECT t2.x
FROM t2
WHERE t2.ID = t1.ID))
AND ( t1.b IS NULL
OR t1.b IN (SELECT t2.x
FROM t2
WHERE t2.ID = t1.ID))
AND ( t1.c IS NULL
OR t1.c IN (SELECT t2.x
FROM t2
WHERE t2.ID = t1.ID))
Output:
ID A B C
---------- ---------- ---------- ----------
101 1 5
101 1
-- Thanks