Here is the simplest working example of EXCEPT and INTERSECT I can come up with …
/* Except.sql */
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1 (c1 INT);
INSERT INTO #t1 VALUES (1), (2);
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;
CREATE TABLE #t2 (c2 INT);
INSERT INTO #t2 VALUES (2), (3);
SELECT * FROM #t1
EXCEPT
SELECT * FROM #t2; /* = 1 */
SELECT * FROM #t1
INTERSECT
SELECT * FROM #t2; /* = 2 */
SELECT * FROM #t2
EXCEPT
SELECT * FROM #t1; /* = 3 */
SELECT * FROM #T1
EXCEPT
SELECT * FROM #T2
UNION
(SELECT * FROM #T2
EXCEPT
SELECT * FROM #T1); /* = 1 & 3 */
I use this frequently whilst refactoring to check the outputs are identical. And sometimes when syncing a MySQL table to MSSQL.
Database Consultant 