chriscoates (6/26/2012)

---

Nice question, thanks.

Prior to answering I would have done something like

SELECT DISTINCT x AS 'Intersect Chars with BIGINT'
FROM #A
INNER JOIN #B on #b.M = #a.x

A quick execution plan shows that the INTERSECT is ever so marginally more efficient too (in this case anyway), primarily because the Nested Loop step is a Left Semi Join, where the INNER JOIN has a slightly higher nested loop step cost.

Every day's a school day!

chriscoates (6/27/2012)

---

Perhaps we are at cross purposes Hugo, I meant tht the result set is the same, regardless of uniqueness. Of course if the values are not unique then there's a man-to-many relationship that I'm sure would make my query cost soar to determine the DISTINCT results, but the output would surely be the same?

Am I missing something else obvious here?