LutzM (11/12/2013)
@Eric:From my point of view, every approach will lead to the same execution plan including the table/index scan (including the MERGE approach).
A rather dirty approach would be a separate column storing the Id of the lookup table and have a filtered index for empty columns. However, this would not only violate any normalization rule, it would also add the additional effort to cascade any change of the lookup table (e.g. DELETE or UPDATE).
But this could be managed by foreign key references and cascade of update/delete (CASCADE for update and SET NULL for delete).
It depends on the given scenario whether I would go down the "denormalized path" or not. But it's an option to consider. At least from my point of view.
LEFT JOIN vs WHERE NOT IN () vs EXCEPT can potentially yield different execution plans. You totally don't know until you unit test each variation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho