Just to make sure I understood the Intersect/Except operators, I tried a variation of the examples from BOL. I put debug(s) around it all just to run various tests without having to re-generate the TEMP tables all the time:

DECLARE @DEBUG INT;

SET @DEBUG = 1;

IF (@DEBUG = 0)

BEGIN

CREATE TABLE #TableA (col1 int);

CREATE TABLE #TableB (col1 int);

CREATE TABLE #TableC (col1 int);

INSERT INTO #TableA

SELECT NULL UNION

SELECT 1 UNION

SELECT 2 UNION

SELECT 2 UNION

SELECT 2 UNION

SELECT 2 UNION

SELECT 3 UNION

SELECT 4 UNION

SELECT 4;

INSERT INTO #TableA VALUES (NULL);

INSERT INTO #TableA VALUES (NULL);

INSERT INTO #TableB

SELECT NULL UNION

SELECT 1 UNION

SELECT 3 UNION

SELECT 4 UNION

SELECT 4;

INSERT INTO #Tablec

SELECT 2 UNION

SELECT 2 UNION

SELECT 2 UNION

SELECT 4 UNION

SELECT 4;

SELECT * from #TableA;

SELECT * FROM #TableB;

SELECT * FROM #TableC;

END;

IF (@Debug = 1)

BEGIN

SELECT col1

FROM #TableA

EXCEPT

SELECT col1 FROM #TableB;

SELECT A.Col1

FROM #TableA A

LEFT OUTER JOIN #TableB B

ON A.Col1 = B.Col1

WHERE B.Col1 IS NULL;

SELECT *

FROM #TableA

INTERSECT

SELECT * FROM #TableB;

SELECT A.Col1

FROM #TableA A

INNER JOIN #TableB B

ON A.Col1 = B.Col1

END

IF (@DEBUG = 10)

DROP TABLE #TableA, #TableB, #TableC;

All of the results here are as expected. But that still leaves the questions as to why the first tests had differences..... 🙁