Jason-299789 (10/11/2012)
GilaMonster (10/11/2012)
IN is not an expensive operation. For matching rows, it's cheaper than join.Moving a filter from a join to the where when you have an outer join changes the logic of the query and likely the results.
Interesting about the IN, I've always found them to be more expensive than Joins and so avoided them.
The difference isn't usually noticable, but it's there.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Also they are not semantically equivalent. Join can result in duplicate rows, IN can not.
Mentally noted about the Outer Join and filter moving, does it have the same effect on an Inner Join.
With an inner join the filter has the same effect and performance whether in the join or the where.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability