Can I replace Union all with any kind on joins?-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

SELECT A.* 
FROM A 
JOIN B ON (A.COL1 = B.COL1 OR A.COL2 = B.COL4) AND B.COL3 IS NULL;

CodePudding user response：

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:

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.
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)