Many to many merge join on a unique lookup
What is the reason the merge join is Many to many, even though the exists clause will generate a true false against a unique index.
CREATE UNIQUE CLUSTERED INDEX CntyCd ON #CntyCd(CntyCd) CREATE UNIQUE NONCLUSTERED INDEX Edition ON #CntyCd(CntyCd,Edition) ALTER TABLE [tCommon].[UnvPclID] ADD CONSTRAINT [tCommon_UnvPclID_PKC] PRIMARY KEY CLUSTERED ([CntyCD], [Edition], [PclId], [PclSeqNbr]) WITH (SORT_IN_TEMPDB = ON, FILLFACTOR = 95) ON [Common] GO CREATE NONCLUSTERED INDEX [IDX_UnvPclID] ON [tCommon].[UnvPclID] ([UnvPclID]) INCLUDE ([CntyCD], [Edition], [PclId], [PclSeqNbr]) WITH (SORT_IN_TEMPDB = ON, FILLFACTOR = 95) ON [Common] GO .... FROM ( SELECT up.CntyCd , up.Edition , up.PclId , up.PclSeqNbr , up.UnvPclID FROM [tCommon].[UnvPclId] up WHERE EXISTS (SELECT 1 FROM #CntyCd c WHERE up.CntyCd = c.CntyCd AND up.Edition = c.Edition) ) up ...
A one-to-many merge join requires the 'one' side of the join input to be the upper input. The join hints in your query (e.g. LEFT MERGE JOIN) add an implied FORCE ORDER hint, forcing the order of inputs for all joins. Although the EXISTS semi-join is converted to an inner join by the optimizer, you have removed its freedom to reorder inputs and joins, so you end up with a merge join where the top input is the many side of the join. Hence, you get a many-to-many join. This query is a mess of hints – you really need to understand all the implications of hinting before writing such a thing.