T-Sql: Join and 'Not In' vs Exists and 'Not Exists'
Jignesh Desai writes on our UG Forum :-
Intersect you can achieve using joins also....
Select Jignesh.Topic
From Jignesh, Naveen
Where Jignesh.Topic = Naveen.Topic.
[update: this would include repetitions of the topic]
Minus query can be formed as :-
Select Topic
From Jignesh
Where Topic Not In (Select Topic From Naveen)
[update: this wouldn't work if there is a null in Naveen.Topic]
I replied with the following :-
Wouldn't the following be more appropriate ?
Intersect :-
Select Jignesh.Topic
From Jignesh
Where Exists(Select 1 From Naveen Where Jignesh.Topic=Naveen.Topic)
Minus :-
Select Topic
From Jignesh
Where Not Exists(Select 1 From Naveen Where Jignesh.Topic=Naveen.Topic)
I haven't heard from him yet, but I've always sort of wondered regarding what is the REAL difference in terms of performance etc.
I am of the [unverified] opinion that Exists and 'Not Exists' is faster but maybe you'll can educate me here. I am looking forward to my copy of Advance T-Sql which I am sure would also teach me a few things.
[update: The adv-tsql book just arrived and its already taught me that 'Not Exists' performs better than 'Not In', we can verify this using 'Show Execution Plan' in Sql Query Analyzer]
What do you think ?