June 19, 2009 at 4:58 am
I saw "Left Anti Semi Join" in an execution plan. Looking it up on BOL2008, I found it under the heading of "Left Anti Semi Join Showplan Operator". It says that "Left Anti Semi Join is a logical operator" yet I can't use it in my code.
According to the execution plan, the query engine replaced
select * from tbl_A
WHERE
[FKCol] NOT IN ( SELECT DISTINCT
[PKCol]
FROM
[dbo].[tbl_B]
WHERE
[dbo].[tbl_B].[AnotherColumn] = 'D' )
with "Nest Loops (Left Anti Semi Join) Cost: 4%" This example was from code inside a stored procedure. Attached is code to replicate, and an execution plan.
I was hoping to be able to replace "X not in (select distinct" with "Left anti semi join tbl_B on tbl_A.FKCol = tbl_B.PKCol" or something similar. Am I missing something, or can't I do it?
June 19, 2009 at 5:23 am
An anti join is not called explicitly with 'Anti Join' syntax. it is how you use it. Specifically, if you want all records in one table where not in another you can use
select * from tbl_A a
left join tbl_B b
on a.FKCol = b.PKCol
and b.[AnotherColumn] = 'D'
where b.PKCol is NULL
It is the where clause that makes it an anto join. Notice the "b.[AnotherColumn] = 'D'" is in the join condition. This is because if you put it in the where clause, it turns the join into an Inner Join type.
HTH
Dave J
June 22, 2009 at 3:43 am
Thanks Dave. Great surname BTW... 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply