Query using "IN" isn''t working
-
Has anyone else ran into problems using a query like this?
Select * from enrollment where LearnerID in (Select LearnerID from UserLessons)
I've been using this type of query for a while to determine who has lessons assigned to them but I've found on two occassions this no longer works. I replaced it with
Select * From enrollment, userlessons where enrollment.learnerid = userlessons.learnerid
and I got the desired result. Any ideas why the "In" type query wouldn't work?
thanks for any help.
******
I'm using query analyzer and I should get results. There are no nulls in the Learner ID field. I should have at least one record that I know of return but when I run the query there are no results.
-
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?
-
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 :
-
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)
Julian Kuiters
juliankuiters.id.au -
Don't use IN, use the JOIN
IN is not SARGable, it is a sequence of ORs,
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000463.htm
-
DITO
. -
According to the table at the end of that article IN is sargable. NOT IN is not sargable however.
Julian Kuiters
juliankuiters.id.au -
Nice point... but I still like to see it written as a join, makes the query read better.
-
Maybe it is...but probably not as efficient as a join as the IN list count goes up...
-
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.
Julian Kuiters
juliankuiters.id.au
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply