create table test_1(n1 number,n2 number,n3 number)

insert into test_1 values(1,2,3);

insert into test_1 values(11,22,33);

insert into test_1 values(55,66,77);


Case-1 :


SELECT N1,N2,N3
FROM TEST_1
WHERE N2=2
UNION ALL
SELECT N1,N2,N3
FROM TEST_1
WHERE N3=3

Output is : This is desired output(so we are good)

1,2,3
1,2,3


Case 2 :
SELECT N1,N2,N3
FROM TEST_1
WHERE N2=22
UNION ALL
SELECT N1,N2,N3
FROM TEST_1
WHERE N3=44


Output : (Here is the problem: If we don't have matching records for anyone union all query we don't want any records)


Output is coming : 11,22,33


Out put : No Records (We want this output.)

SQL> select n1,n2,n3
  2  from (
  3  select n1, n2, n3, count(*) over() cnt
  4  from (
  5  SELECT N1,N2,N3
  6  FROM TEST_1
  7  WHERE N2=&n2
  8  UNION ALL
  9  SELECT N1,N2,N3
 10  FROM TEST_1
 11  WHERE N3=&n3
 12  )
 13  )
 14  where cnt = 2
 15  /
Enter value for n2: 2
Enter value for n3: 3

        N1         N2         N3
---------- ---------- ----------
         1          2          3
         1          2          3

2 rows selected.

SQL> /
Enter value for n2: 22
Enter value for n3: 44

no rows selected

SQL> insert into test_1 select * from test_1;

3 rows created.

SQL> select n1,n2,n3
  2  from (
  3  select n1,n2,n3, count(distinct fl) over () cnt
  4  from (
  5  SELECT N1,N2,N3, 1 fl
  6  FROM TEST_1
  7  WHERE N2=&n2
  8  UNION ALL
  9  SELECT N1,N2,N3, 2 fl
 10  FROM TEST_1
 11  WHERE N3=&n3
 12  )
 13  )
 14  where cnt = 2   --<-- Nb UNION ALL in the subquery
 15  /
Enter value for n2: 2
Enter value for n3: 3
        N1         N2         N3
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3

4 rows selected.

SQL> /
Enter value for n2: 22
Enter value for n3: 44

no rows selected