The reason your results are not corresponding to what you would have expected them to be, is that you're not understanding the operations correctly.

EXCEPT and INTERSECT compare the *entire* data set.

EXCEPT returns all the rows which exist in Table 1 but do NOT exist in table 2 - for *ALL* columns.

Take the following information as example data for what I'm going to describe below:

SELECT * FROM #Transactions WHERE tranID = 100

TranIdTranDateTranCodeAmount

1001995-01-01 01:00:00.000tax1.00

-----------------

SELECT * FROM #VendorExport WHERE tranID = 100

TranIdTranDateTranCodeAmount

1001995-01-05 04:00:00.000room3.5587

When doing the EXCEPT query, the row would be returned. This is because although they have the same TranID, they are different in their TranDate, TranCode, and Amount fields.

When doing the LEFT OUTER JOIN query, the row would not be returned. This is because the LEFT OUTER JOIN query is looking to match on TranID alone, and since both tables share the TranID in question, the "WHERE v.TranID IS NULL" part of the query would cause it to be excluded.

When doing the INNER JOIN query, the row would be returned. This is because the INNER JOIN is joining only when the TranID matches. The reason why you are seeing the "same" information as the records from Query 1, is because you are looking only at the columns from #Transactions. If you were to show the columns from #VendorExport, then you would see the different results brought back by the JOIN.

Finally, when doing the INTERSECT query, the row would not be returned. The reason why the INNER JOIN query returns 1099 rows while the INTERSECT query returns 0 rows, is because of the way INTERSECT (and EXCEPT, for that matter) works. INTERSECT returns the opposite result set of EXCEPT. It returns for you all the rows which are *exactly* the same between the two tables (while EXCEPT returns the rows which are *not* exactly the same).