Comments on Schnieds Blog: Creating a SQL Exclusion Table Using LEFT JOIN

Great Post! Im 90% what I was trying to do. Basica...

2010-08-12T11:13:04.284-07:00

Great Post! Im 90% what I was trying to do. Basically I have
a master [AddressBook] table that
5 people use. So I have a [Users] table. I also have a master [Exclusion] table. The [AddressBook] table has no reference to an individual user, all users would see all address.
If any user desires not to see an
individual name, I want to put that in the [Exclusion] table.

I believe this is called a Set Difference algorithm.

I've seen several approaches, but your approach, the JOIN + IS NULL on the LEFT JOINed table, is my preference. I suspect your approach would be faster than the NOT EXISTS sub-query approach as it doesn't require a per row subquery and NOT EXISTS can be very slow on large tables, though I haven't run any performance tests myself.

On a related note, you can also get the set difference of two list with Linq using Except().

Provided I'm reading it right, there's an ...

2010-01-13T09:16:07.488-08:00

Provided I'm reading it right, there's an easier way: NOT EXISTS. Performance-wise it should perform like that of the join, rather than that of NOT IN.

select * from a where NOT EXISTS (select * from b where a.key = b.key)