Semi Join in SQL server

It’s an interesting fact that we never tend to use semi joins in SQL server, and instead we tend to use the more easy (or rather lazy) way of using an ‘IN’, which obviously takes huge amount of time (again depends !). I was writing a query to search from multiple tables (having millions of records) and don’t have much option of change, and it was taking lot of time (again depends!) to fetch the result set. That’s when I remembered about semi-join, replaced all lazy ‘IN’s with semi joins and I got alteast 40% performance improvement.

Now how to use semi joins

Semi Join

DELETE @AvalableOrgIdList FROM @AvalableOrgIdList AVL

                                    WHERE EXISTS (SELECT  OrgId FROM @TmpSrchResult RSLT WHERE RSLT.OrgId = AVL.OrgId)

Anti Semi join

DELETE @AvalableOrgIdList FROM @AvalableOrgIdList AVL

                                    WHERE NOT EXISTS (SELECT  OrgId FROM @TmpSrchResult RSLT WHERE RSLT.OrgId = AVL.OrgId)

Want more explanation and more in depth into SQL joins etc , check this blog.

http://blogs.msdn.com/craigfr/Default.aspx

-Rujith