Do I need to use UNION ALL to combine separate SELECT statements instead of using IN & OR operators in WHERE clause?

Question

Hi there,

Here is the table structure:

CREATE TABLE [dbo].[tblCustomer](
	[ID] [int] NOT NULL,
	[WorkTel] [varchar](50) NULL,
	[MobileTel] [varchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))
GO

CREATE NONCLUSTERED INDEX [ix_WorkTel] ON [dbo].[tblCustomer]
(
	[WorkTel] ASC
)
GO

CREATE NONCLUSTERED INDEX [ix_MobileTel] ON [dbo].[tblCustomer]
(
	[MobileTel] ASC
)
GO

Do I need to use UNION ALL to combine separate SELECT statements instead of using IN & OR operators in WHERE clause?   

-- 1
SELECT * FROM tblCustomer 
WHERE WorkTel = '123456' OR MobileTel = '123456'

-- 2
SELECT * FROM tblCustomer 
WHERE WorkTel = '123456' 
UNION ALL
SELECT * FROM tblCustomer 
WHERE MobileTel = '123456'

Thanks.

Answer 1

Definitely yes. Also you can use union to have distinct records only.

---

Many Thanks & Best Regards, Hua Min

Answer 2

I would be with the second approach with the index given on the table. Second method would use the indexes appropriately.

Answer 3

and here are the execution plan diagrams:

Answer 4

I would also go with the second approach..Also do you need all the columns(Yes this also impacts performance)??

---

Thanks and regards, Rishabh K

Answer 5

I think that there are some misconception here.

There is no such thing that second option is DEFINITELY better than the first one or vice versa. there is one fundamental thing will very much impact the query plan which is the statistics. Also these 2 queries WILL EVEN RETURN BACK THE DIFFERENT NUMBER OF ROWS as for the first if WorkTel = MobileTel it only return one row, but the second query will return 2 ROWS!!

You asked that:

 use UNION ALL to combine separate SELECT statements instead of using IN & OR operators in WHERE clause?  

I would say that in general, in many cases that the In & OR will generate better execution plan than use UNION ALL.

For IN, you can think it as a subset of Or, as when you use same column in where condition with OR, it is same as IN. (More precisely, need to be explained reversed, that is in is equalent to same column with or)

OR is SARGable, so index seek can be achieved. If multiple columns used as condition with OR and all the columns have their own index, then an index Union strategy might be used by the execution plan.

This is indeed exactly the execution plan your query used.

As I can't see the extimated rows from your query plan, I could say the Option 1 is much better then Option 2, as it invovled with less Key LookUp operation. the cLustered key lookup is an expensive operation as it is always operated in a random seek fashion. as you can see that the second option account for overall 50% key lookups.

The Merge Join in the first option is very efficient which as the merge join suggests that the 2 data streams are already sorted with their join Key (clustered index key). after merge joined, it performs a group (stream aggregation) to remove duplicate which reduce number of rows to be looked up.

From index point of view, if you want to improve the performance, then you might need to include the MobileTel in Index A and include WorkTel in index B to eliminate the Key Loopups. Though it will need more disk space and affet data update performance.

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!