Check if a result set has specific values

Question

I want to know what is the best way to check if a subquery has a set of values. I came up with the following query but I don't think its efficient.

SELECT * FROM SomeTable 
WHERE 
(
    2 IN (SELECT OptionId FROM Table2 WHERE Table2_Id = 1) AND
    3 IN (SELECT OptionId FROM Table2 WHERE Table2_Id = 1)
)

I appreciate if anyone can provide me with a better solution.

Thanks

Answer 1

I would use EXISTS subqueries:

SELECT * FROM SomeTable 
WHERE EXISTS (SELECT * FROM Table2 WHERE Table2_Id = 1 AND OptionId = 2) 
AND   EXISTS (SELECT * FROM Table2 WHERE Table2_Id = 1 AND OptionId = 3)

If you have one of the following indexes (Table2_Id,OptionId) or (OptionId,Table2_Id) then you shouldn't have worries about performance.

Edit 1: After reading above comments I think you should JOIN somehow SomeTable with Table2 using a common column thus:

SELECT * FROM SomeTable x
WHERE EXISTS (SELECT * FROM Table2 y WHERE y.Table2_Id = 1 AND y.OptionId = 2 AND x.CommonColumn = y.CommonColumn) 
AND   EXISTS (SELECT * FROM Table2 y WHERE y.Table2_Id = 1 AND y.OptionId = 3 AND x.CommonColumn = y.CommonColumn)

If this doesn't solve your problem then you should add more infos.