Alan.B (4/22/2015)
I did not have time to put together a 1,000,000 row test but this guy:
SELECTCol1 = ISNULL(a.name,b.name),
CASE ISNULL(a.name,'') WHEN '' THEN 'Table B' ELSE 'Table A' END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name
WHERE a.name IS NULL OR b.name IS NULL;
Is as or more simple than this guy:
SELECT name,=MAX(
)
FROM
(
SELECT name,
='TableA'
FROM #tableA
UNION ALL
SELECT name, 'TableB'
FROM #tableB
) a
GROUP BY name
HAVING COUNT(*) = 1;
NULLs make the difference. Consider
DECLARE @tableA TABLE (id int, name varchar(20));
DECLARE @tableB TABLE (number int, name varchar(20));
INSERT @tableA VALUES
(101,'Dante'),
(102,'Henry'),
(103,'Harold'),
(104,'Arnold'),
(111, NULL);
INSERT @tableB VALUES
(102,'Dante'),
(107,'Gilbert'),
(109,'Harold'),
(110,'Arnold'),
(106,'Susan'),
(112,'Marian'),
(999, NULL);
It's up to OP to decide which of two guys is correct (or may be just change DDL ), but certainly results are different.