SELECT X AS 'Intersecting'
JOIN b ON b.y = a.x;
Except the ordering. INTERSECT returns the values in numeric order; JOIN returns the values in the order in which they were found in the left table (A).
I didn't see anything in the BOL documentation on this. Anyone shed some light on this? Thanks.
Neither version is guaranteed to return the values in any particular order. The results you are seeing are determined purely by the plan that the optimiser chooses - this could change with different volumes of data, or between different versions of SQLServer, etc.
The only way to guarantee a particular sequence is to specify an Order By clause.