SET NOCOUNT ON
DECLARE	@TableA TABLE
(
	A_Int int
)

INSERT INTO @TableA
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT NULL

DECLARE	@TableB TABLE
(
	B_Int int
)

INSERT INTO @TableB
SELECT 1 UNION ALL
SELECT 5  UNION ALL
SELECT NULL

SELECT	B_Int AS [Exists]
FROM	@TableB
WHERE	EXISTS
	(
		SELECT	*
		FROM	@TableA
		WHERE	A_Int = B_Int
	)

SELECT	B_Int AS [In]
FROM	@TableB
WHERE	B_Int IN
	(
		SELECT	A_Int
		FROM	@TableA
	)

SELECT	B_Int AS [Not Exists]
FROM	@TableB
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	@TableA
		WHERE	A_Int = B_Int
	)

SELECT	B_Int AS [Not In]	-- Different result to NOT EXISTS
FROM	@TableB
WHERE	B_Int NOT IN
	(
		SELECT	A_Int
		FROM	@TableA
	)
SET NOCOUNT OFF

  |--Nested Loops(Left Semi Join, WHERE:([A_Int]=[B_Int]))
       |--Table Scan(OBJECT:(@TableB))
       |--Table Scan(OBJECT:(@TableA))

  |--Nested Loops(Left Semi Join, WHERE:([B_Int]=[A_Int]))
       |--Table Scan(OBJECT:(@TableB))
       |--Table Scan(OBJECT:(@TableA))

  |--Nested Loops(Left Anti Semi Join, WHERE:([A_Int]=[B_Int]))
       |--Table Scan(OBJECT:(@TableB))
       |--Table Scan(OBJECT:(@TableA))

  |--Nested Loops(Left Anti Semi Join, WHERE:([B_Int] IS NULL OR [A_Int] IS NULL OR [B_Int]=[A_Int]))
       |--Table Scan(OBJECT:(@TableB))
       |--Table Scan(OBJECT:(@TableA))

quote:

---

Originally posted by webfred

(can't say this time what exactly I mean with "many records", sorry).

---

1) select id from table1 t1 where id in(select id from table2 t2 where t1.id=t2.id)
and 
2)select id from table1 t1 where exists(select id from table2 t2 where t1.id=t2.id)

quote:

---

Originally posted by Idera

Ok.
Let me give an example.How will this two queries perform??

---

quote:

---

According to my senior the optimiser is smart enough to understand that both the queries are same.

---

-- Original query
SELECT	s.*
FROM	dbo.theSample AS s
WHERE	(
		s.KeyA IN (SELECT v.theKey FROM dbo.theVerification AS v)
		AND s.KeyB NOT IN (SELECT v.theKey FROM dbo.theVerification AS v)
	)
        OR (
		s.KeyB IN (SELECT v.theKey FROM dbo.theVerification AS v)
		AND s.KeyA NOT IN (SELECT v.theKey FROM dbo.theVerification AS v)
	)

-- Peso
SELECT	s.*
FROM	dbo.theSample AS s
WHERE	(
		s.KeyA IN (SELECT v.theKey FROM dbo.theVerification AS v)
		AND s.KeyB NOT IN (SELECT v.theKey FROM dbo.theVerification AS v WHERE v.theKey <> s.KeyB)
	)
	OR (
		s.KeyB IN (SELECT v.theKey FROM dbo.theVerification AS v)
		AND s.KeyA NOT IN (SELECT v.theKey FROM dbo.theVerification AS v WHERE v.theKey <> s.KeyA)
	)


Metrics from SQL Profiler

            CPU  Duration    Reads
Original  21481      1400  4012657
Peso          0         1       79


Metrics from STATISTICS IO (theSample has 1,000,000 records, and theVerification has 10 records)

-- Original query (17 rows affected)
Table 'theSample'.       Scan count  1, logical reads    11 522.
Table 'theVerification'. Scan count  0, logical reads 4 000 010.

-- Peso           (17 rows affected)
Table 'theSample'.       Scan count 20, logical reads        75.
Table 'theVerification'. Scan count  2, logical reads         4.

quote:

---

Originally posted by GilaMonster

quote:

---

Originally posted by Idera

Ok.
Let me give an example.How will this two queries perform??

---

Why don't you run them and find out? Look at the exec plan, look at the query statistics, see what differences there are, if any.

Or look at the two blog posts I linked to right above where I did those tests, then take my code and run it yourself

quote:

---

According to my senior the optimiser is smart enough to understand that both the queries are same.

---

If your senior is teaching you to write IN like that, please ask him to read the blog posts I wrote as well.
The IN should not have a WHERE clause, it'd not a correlated subquery. Only the EXISTS needs a where clause correlating it to the outer query.

--
Gail Shaw
SQL Server MVP

---

#tbl1 

   logical reads 169
   physical reads 0
   Scan count 1

#tbl2 

   logical reads 85
   physical reads 0
   Scan count 1

CPU Time 109ms


Exists
50000 row(s) affected

#tbl1 

   logical reads 169
   physical reads 0
   Scan count 1

#tbl2 

   logical reads 85
   physical reads 0
   Scan count 1

CPU Time 203ms

create table #tbl1 (id int identity)
insert  #tbl1 default values
while SCOPE_IDENTITY()<100000
insert  #tbl1 default values


create table #tbl2(id int identity)
insert  #tbl2 default values
while SCOPE_IDENTITY()<50000
insert  #tbl2 default values


select id from #tbl1 t1 where id in(select id from #tbl2 t2 where t1.id=t2.id)
select id from #tbl1 t1 where exists(select id from #tbl2 t2 where t1.id=t2.id)


drop table #tbl1,#tbl2

quote:

---

Originally posted by Peso

Adding a predicate can make a huge difference in a query using IN, especially a NOT IN query.

---

quote:

---

Originally posted by IderaI tested it on my workstation.To my surpise "IN" is performing significantly little better than "Exist" ,also both the queries are returning the same records.

---

select id from #tbl1 t1 where id in(select id from #tbl2 t2 )

quote:

---

Originally posted by GilaMonster

Could it be to do with the NULLability of the column? NOT IN performs terribly when the column is nullable, regardless of whether or not there are nulls in.

--
Gail Shaw
SQL Server MVP

---

CREATE TABLE dbo.theSample
( KeyA int NOT NULL, KeyB int NOT NULL, slask nvarchar(100) NOT NULL  )
GO
CREATE TABLE dbo.theVerification (theKey INT PRIMARY KEY NOT NULL)

quote:

---

Originally posted by Peso

quote:

---

Originally posted by GilaMonster

Could it be to do with the NULLability of the column? NOT IN performs terribly when the column is nullable, regardless of whether or not there are nulls in.

---

Good point, didn't think of that.
So I checked the repro script and here it is.

CREATE TABLE dbo.theSample
( KeyA int NOT NULL, KeyB int NOT NULL, slask nvarchar(100) NOT NULL  )
GO
CREATE TABLE dbo.theVerification (theKey INT PRIMARY KEY NOT NULL)

---