PRB: Subquery with IN Clause Runs Very Slowly

ID Number: Q77780

1.11 4.20

OS/2

Summary:

Problem ID: PRSQL9110015

SYMPTOMS

Under SQL Server version 1.11, a subquery with an IN clause may run

more slowly than in SQL Server 1.1a.

CAUSE

SQL Server 1.11 has been enhanced to comply with the ANSI 89 SQL

specification for subqueries with an IN or ANY clause. This

specification states a subquery with IN or ANY must not return

duplicates. The enhancement may result in some performance

degradation of queries using IN or ANY.

WORKAROUND

It is possible in many cases to express a subquery as an equivalent

JOIN.

STATUS

This is the expected behavior of SQL Server versions 1.11 and

later.

Additional reference words. 4.20