CODEWITHSUNDEEP
sql
Prashath Kesari
Prashath Kesari

Reputation: 11

When to use OR and when to use UNION in sql while using nested Select quires

I was trying to use nested Select query Using OR:

select * from TRANSACTION_LOG
where GLOBAL_ID IN (select UNIQUE GLOBAL_ID from ORDER_REF where ORDER_ID in ('O01625','O01628','O01640'))
OR (GLOBAL_ID ='MC11f5f14');

The other way was using UNION ALL:

select * from TRANSACTION
where GLOBAL_ID IN (select UNIQUE GLOBAL_ID from ORDER_REF where ORDER_ID in ('O01625','O01628','O01640'))
UNION ALL
select * from TRANSACTION
where GLOBAL_ID ='MC11f5f14';

The first query is taking too much time for result, while the second one was quick, Not sure why and how it works!

Upvotes: 1

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

First, the standard SQL construct is select distinct, not select unique. Second, neither is needed with in.

So, the first query should be written as:

select tl.*
from TRANSACTION_LOG tl
where tl.GLOBAL_ID in (select o.GLOBAL_ID
                       from ORDER_REF o
                       where o.ORDER_ID in ('O01625', 'O01628', 'O01640')
                      ) or
      tl.GLOBAL_ID = 'MC11f5f14';

The equivalent query with union all is:

select tl.*
from TRANSACTION_LOG tl
where tl.GLOBAL_ID = 'MC11f5f14'
union all
select tl.*
from TRANSACTION_LOG tl
where tl.GLOBAL_ID in (select o.GLOBAL_ID
                       from ORDER_REF o
                       where o.ORDER_ID in ('O01625', 'O01628', 'O01640')
                      ) and
      tl.GLOBAL_ID <> 'MC11f5f14';
 -----^

Note the additional clause. This allows the query to use union all rather than union. union incurs a performance penalty because it removes duplicates.

Which is better? That can depend on the database. Some databases have sophisticated optimizers. Some don't. Some databases prefer exists to in for optimization.

In general, the second form allows more choices for optimization. However, if you don't have appropriate indexes on the tables, then all bets are off.

Upvotes: 1

Related Questions