Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I have following queries

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?

share|improve this question
    
Have you checked the execution plans for the two queries? –  Cheran Shunmugavel Dec 28 '12 at 1:36
add comment

2 Answers

up vote 3 down vote accepted

The SQL Server query analyzer is smart enough to understand that these queries are identical (given that 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...

share|improve this answer
add comment

I'm not familiar with Microsoft SQL server (I assume that this is actual SQL server mentioned here). Actually I'm pretty sure that IN will not produce multiplication of rows if there is two entries for single F.itemID in contrast with INNER JOIN.

I suspect that with absence of unique index on F.itemID performance may differ too. Probably IN will prefer to ordered unique set of relations while FROM will consider statistics of both tables more equally than IN.

P.S. I prefer JOIN because of giving hint for optimizer while dropping off additional constraint. And it looks a bit more readable as for me (less amount of SELECT).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.