Query using "IN" isn''t working

Question

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

How do you mean it doesn't work? And the new query would not always return the same result as the one using IN, are you aware of that?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

If there's a null in the in list, it'll cause the in to fail. The correct syntaxe for this type of query is this :

Select E.col1, E.col2 from dbo.Enrollment E inner join UserLessons U on E.Leanerid = U.Learnerid

Check out this good sql tutorial :

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

alternatively you can remove the nulls from your subquery, and speed up the whole query with a distinct

Select * from enrollment where LearnerID in (Select DISTINCT LearnerID from UserLessons WHERE LearnerID IS NOT NULL)

gbn SSCertifiable Points: 6429 More actions · Answer 4

Don't use IN, use the JOIN

IN is not SARGable, it is a sequence of ORs,

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

SSC Guru

Points: 294069

August 31, 2005 at 6:49 am

DITO .

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 6

According to the table at the end of that article IN is sargable. NOT IN is not sargable however.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Nice point... but I still like to see it written as a join, makes the query read better.

gbn SSCertifiable Points: 6429 More actions · Answer 8

Maybe it is...but probably not as efficient as a join as the IN list count goes up...

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 9

Performance would definately depend on your table sizes and and the number of matching rows.

If a high percentage of your enrollments had userlessons then a JOIN would probably be as/more efficient.

If however you had millions of enrollments and a handful of userlessons then a SELECT IN would most likely be fastest.

As always, testing is the only way to find out.