Need Query without Union and minus.

[Rama S]

Hi,

We need a query which retrieves option_code, for a plan from OPTION
table which are unique in (market, "ALL").
I have an example below
PLAN MARKET OPTION_CODE RATE
PLAN1 TEXAS 1000 1
PLAN1 TEXAS 2000 2
PLAN1 ALL 1000 3
PLAN1 ALL 3000 4

When we ran the SQL, for PLAN1, we need to get
OPTION_CODE RATE
1000 1
2000 2
3000 4

Currently we are using union and minus to retrieve unique options in
both markets.
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'TEXAS'
UNION
(
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'ALL'
MINUS
select option_code, rate from OPTION
where plan = 'PLAN1'
and market= 'TEXAS'
)

Is there any way to do without using union and minus. We tried to use
NOT EXISTS, but it is slowing the query...

Thanks in advance for the help......

[Tim Cuthbertson]

Re: Need Query without Union and minus.

union and minus are the best (most efficient) way to do it. You shouldn't be
trying to avoid them.

Tim