Webmaster Forum / ASP / Database Access / October 2008

True but EXISTS and IN can often be expressed as a JOIN or correlated
subquery.  I get identical execution plans with all of the methods below:

CREATE TABLE dbo.MyTable(
MyTableKey int NOT NULL
 CONSTRAINT PK_MyTable PRIMARY KEY,
Fields int NOT NULL,
FKeyField int NOT NULL
);

CREATE TABLE dbo.OtherTable(
KeyField int NOT NULL
 CONSTRAINT PK_OtherTable PRIMARY KEY,
OtherValue varchar(20) NOT NULL
);

CREATE INDEX index1 ON dbo.OtherTable(
OtherValue, KeyField);

ALTER TABLE dbo.MyTable
ADD CONSTRAINT FK_MyTable_OtherTable
FOREIGN KEY (FKeyField)
REFERENCES dbo.OtherTable(KeyField);

SELECT Fields
FROM dbo.MyTable
WHERE FKeyField IN(
SELECT OtherTable.KeyField
FROM dbo.OtherTable
WHERE OtherValue = 'FilterValue'
  );

SELECT Fields
FROM dbo.MyTable
WHERE EXISTS(
SELECT *
FROM dbo.OtherTable
WHERE OtherTable.KeyField = MyTable.FKeyField AND
 OtherTable.OtherValue = 'FilterValue'
  );

SELECT Fields
FROM dbo.MyTable
JOIN dbo.OtherTable ON
OtherTable.KeyField = MyTable.FKeyField
AND OtherTable.OtherValue = 'FilterValue';

SELECT Fields
FROM dbo.MyTable
WHERE FKeyField =
(
SELECT KeyField
FROM dbo.OtherTable
WHERE
 OtherTable.OtherValue = 'FilterValue'
 AND OtherTable.KeyField = MyTable.FKeyField
  );

Do you mean compared to EXISTS, or compared to = ?  You can also use EXISTS
regardless of the number of results returned by the subquery.

For the OP, I prefer EXISTS for a couple of reasons.  One is the reason that
Uri pointed out; if your subquery can return NULLs, then IN/NOT IN will not
necessarily produce correct results.  The other is that EXISTS has the
possibility of performing better; it can stop "looking" once it has found
its first result, and you can also set it to return a constant (e.g. SELECT
1) instead of a column... This makes it easier to spot as a
non-data-returning element in your query.

A

I'd love to see a case where IN performs better.  It sounds to me like your
advisor is either guessing, or making facts up, or speaking to one very
specific case... If they are looking at estimated execution plan, for
example, remind them that they should be checking statistics i/o and actual
execution plan.  There may be some cases where this is true but in general
you should always test and compare instead of building some imaginary
"always use x" rule.  The only rule is, it always depends.

One more thought on the IN vs EXISTS question.  IN only works with
single-column keys.  EXISTS works with single or multiple column keys.
My tendency is to want to use a consistent style, and EXISTS lets me
use the same approach in both cases.

Roy Harvey
Beacon Falls, CT