The following is an example shows a SQL statement with an IN List expression. The SQL retrieves records from EMPLOYEE table that EMP_DEPT should match any value in a list of values.
WHERE EMP_DEPT IN (‘AAD’,‘COM’,‘AAA’)
The query plan shows three Hash Match with EMPLOYEE’s indexes. For indexes EMPLOYEE_PK and emps_salary_inx are processing with EstimateRows up to 3000000, it seems too expensive since this condition EMP_DEPT IN (‘AAD’,’COM’,’AAA’) should rapidly trim down the return records. Let me rewrite the IN list into multiple UNION conditions in the following:
from EMPLOYEE E1
where exists ( select ‘x’
where E1.EMP_DEPT = ‘AAD’
where E1.EMP_DEPT = ‘COM’
where E1.EMP_DEPT = ‘AAA’)
and EMP_SALARY < 10000000
This rewrite can force the IN list operation to be processed first before the condition EMP_SALARY < 10000000 takes place. Here the following is the query plan after rewrite, SQL server now can utilize Merge Join of 3 Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of employee”. The speed now is 0.191 seconds and is much faster than the original SQL.
This kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 12 times faster than the original SQL.Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited