SQLSACT (12/19/2012)

---

Hi All

Some DDL

--Table 1

CREATE TABLE [dbo].[Indexing2](

[Col1] [int] IDENTITY(1,1) NOT NULL,

[Col2] [int] NULL,

[Col3] [int] NULL,

[Col4] [int] NULL

)

--Indexes

CREATE UNIQUE CLUSTERED INDEX [CX] ON [dbo].[Indexing2] ([Col1])

CREATE NONCLUSTERED INDEX [NCX] ON [dbo].[Indexing2] ([Col4])

--Table2

CREATE TABLE [dbo].[Indexing](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Col2] [int] NULL

)

--Indexes

CREATE UNIQUE CLUSTERED INDEX [CX] ON [dbo].[Indexing] ([ID])

CREATE NONCLUSTERED INDEX [NCIX] ON [dbo].[Indexing] ([Col2])

And consider the following selects

select Col4 from Indexing2

inner join Indexing

on Indexing2.Col4 = Indexing.Col2

where Col4 between '200' and '250'

select Col4 from Indexing2

where exists (select * from Indexing where Indexing.Col2 = Indexing2.Col4)

and Col4 between '200' and '250'

Essentially, the 2 queries are the same. Why does the 1st query return duplicates and the 2nd query doesn't?

Also, the the execution plans are identical besides the 2nd query having a stream aggregate. Why is this being added?

Thanks