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!
Note that they are only equivalent when the columns #a.x and #b.M are unique. If there is a primary key, unique constraint, or unique index involved, you can be sure that they are unique; otherwise you can't, so you can't just replace one with the other.