Essentially, the 2 queries are the same.

...
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

anthony.green (12/19/2012)

---

Sample data would be good as well

Using the following sample data

insert into Indexing values (199),(200),(210),(220),(230),(240),(250),(251)
insert into Indexing2 (Col4) values (199),(200),(210),(220),(230),(240),(250),(251)

I get no duplicates and now stream aggregate, the only difference is a left semi join on the exists vs a inner join.

Also post your execution plans in a SQLPLAN format so we can take a look.

anthony.green (12/19/2012)

---

Sample data would be good as well

Using the following sample data

insert into Indexing values (199),(200),(210),(220),(230),(240),(250),(251)
insert into Indexing2 (Col4) values (199),(200),(210),(220),(230),(240),(250),(251)

I get no duplicates and now stream aggregate, the only difference is a left semi join on the exists vs a inner join.

Also post your execution plans in a SQLPLAN format so we can take a look.

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

SQLSACT (12/19/2012)

Why does the 1st query return duplicates and the 2nd query doesn't?

... the 2nd query having a stream aggregate. Why is this being added?

Thanks