Remove duplicate sub-query

Question

I have a complex SQL query that can be simplified to the below:

Select ColA,ColB,ColC,ColD
From MyTable
Where (ColA In (Select ItemID From Items Where ItemName like '%xxx%') 
    or ColB In (Select ItemID From Items Where ItemName like '%xxx%'))

As you can see, the sub-query appears twice. Is the compiler intelligent enough to detect this and gets the result of the sub-query only once? Or does the sub-query run twice?

FYI, table Items has about 20,000 rows and MyTable has about 200,000 rows.

Is there another way to re-write this SQL statement so that the sub-query appears/runs only once?

Update: The Where clause in the main query is dynamic and added only when needed (i.e. only when a user searches for 'xxx'). Hence changes to the main select statement or re-structuring of the query are not possible.

Answer 1

UPDATE Your request not to change the query, just the WHERE

You can pack the CTE directly in the place where it is called (untested):

Select ColA,ColB,ColC,ColD
From MyTable
Where EXISTS (SELECT 1 FROM (Select i.ItemID 
                             From Items AS i 
                             Where iItemName like '%xxx%') AS itm 
              WHERE itm.ItemID=MyTable.ColA OR itm.ItemID=MyTable.ColB) 

previous

I think this should be the same...

WITH MyCTE AS
(
    Select ItemID From Items Where ItemName like '%xxx%'
)
Select ColA,ColB,ColC,ColD
From MyTable
Where EXISTS (SELECT 1 FROM MyCTE WHERE ItemID=ColA OR ItemID=ColB) 

A substring LIKE search is - for sure - not performant.

If you can reduce your "Items" to just a few rows with your LIKE filter, you must test which is fastest.