patternsqlMinor
Subquery v/s inner join in sql server
Viewed 0 times
sqljoinsubqueryserverinner
Problem
I have following queries
First one using inner join
second one using sub query like
In this item table
First one using inner join
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = @doctorId AND F.favType = 'I'second one using sub query like
SELECT item_ID,item_Code,item_Name from [Pharmacy].[tblitemHdr]
WHERE item_ID IN
(SELECT itemID FROM EMR.tblFavourites
WHERE doctorID = @doctorId AND favType = 'I'
)In this item table
[Pharmacy].[tblitemHdr] Contains 15 columns and 2000 records. And [Pharmacy].[tblitemHdr] contains 5 columns and around 100 records. in this scenario which query gives me better performance?Solution
The SQL Server query analyzer is smart enough to understand that these queries are identical (given that
They should yield exactly the same execution plan, and thus they are equal in terms of performance. I would prefer second variant though...
item_ID is unique in EMR.tblFavourites for a certain doctorID/favType pair). They should yield exactly the same execution plan, and thus they are equal in terms of performance. I would prefer second variant though...
Context
StackExchange Code Review Q#19962, answer score: 3
Revisions (0)
No revisions yet.