1

I have the below query for negative testing, But I want to replace the union all if possible.

select A.*
from A
join B
on A.COL1=B.COL1
where B.COL3 is null

union all

select A.*
from A
join B
on A.COL2=B.COL4
where B.COL5 is null;

Need to get data from both SQL without using union all

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Tanmaya
  • 15
  • 3

3 Answers3

2

You could combine the two queries into a single join and collapse the where condition into it:

select A.* 
from   A 
join   B on (A.COL1 = B.COL1 and B.COL3 is null) or 
            (A.COL2 = B.COL4 and B.COL5 is null)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • -Thank you Sir, But what if the where conditions are different. – Tanmaya Jan 06 '23 at 17:59
  • @Tanmaya you could colapse it to the `join` clause and have two different branches of the join - `(a.col1 = b.col2 AND some_condition) OR (a.col2 = b.col4 AND some_other_condition)`. – Mureinik Jan 06 '23 at 18:04
  • Note that the second select has B.COL5 is null. – jarlh Jan 06 '23 at 18:08
  • 2
    @jarlh The question was edited after I posted the answer (the original had `b.col3 is null` in both queries). I edited my answer accordingly. – Mureinik Jan 06 '23 at 18:10
1

Since you're only after data from Table A you don't need the join to table B at all and can re-write this as an Exists...

SELECT A.*
FROM A
WHERE EXISTS (SELECT 1 
              FROM B 
              WHERE A.COL1=B.COL1 and B.COL3 is null)
   OR EXISTS (SELECT 1 
              FROM B 
              WHERE A.COL2=B.COL4 and B.COL5 is null)

But this has likely has two issues:

  1. I'm pretty sure if you look at the execution plan for both; you'll find the union all is more efficient because it operates at a set level instead of a row level ad the OR needed in this is slower.
  2. This will return 1 record from A instead of 2 from that of a union all. had it been a union; this should/would return the same results and avoid the union. But simply put you want the same data from A twice (or more depending on cardinality of joins)
xQbert
  • 34,733
  • 2
  • 41
  • 62
0
SELECT A.* 
FROM A 
JOIN B ON (A.COL1 = B.COL1 OR A.COL2 = B.COL4) AND B.COL3 IS NULL;
Anuj Kumar
  • 1,092
  • 1
  • 12
  • 26