How would I go about getting everything from table A and everything from table B that is not in table A? I'm trying to prevent duplicate information coming from table B when combining the two tables.
SELECT Col1, Col2, Col3 FROM TableA
UNION
SELECT Col1, Col2, Col3 FROM TableB
There is one caveat though. If there were two (or more) rows with the same values of Col1, Col2, Col3 in TableA this query would return only one of those. In other words, it de-duplicates the combination of all the rows in TableA and TableB taken together.
If you want to know which table it came from? then perhaps
SELECT 'A' AS Source, Col1, Col2, Col3 FROM TableA AS A
UNION ALL
SELECT 'B' AS Source, Col1, Col2, Col3 FROM TableB AS B
WHERE NOT EXISTS
(
SELECT *
FROM TableA AS A
WHERE A.KeyColumn1 = B.KeyColumn1
AND A.KeyColumn2 = B.KeyColumn2
...
)
I expect you could also do a FULL OUTER JOIN but I tend to find that the syntax of that get's in my way and I wind up reverting to EXISTS / NOT EXISTS instead.
Like James said, his code will easily do the trick with the caveat that any duplicates in TableA will also be removed.
If you really do need ALL rows from TableA (even the duplicates), then this will do the trick.
--===== Create some test data.
-- This is NOT a part of the solution.
SELECT *
INTO #TableA
FROM (
SELECT 1,2 UNION ALL
SELECT 3,4 UNION ALL
SELECT 1,2 UNION ALL --Dupe in A (display the dupe)
SELECT 5,6 UNION ALL
SELECT 7,8
) d (Col1,Col2)
;
SELECT *
INTO #TableB
FROM (
SELECT 1,2 UNION ALL --Dupe in A (Don't display the dupe)
SELECT 3,4 UNION ALL --Dupe in A (Don't display the dupe)
SELECT 6,5 UNION ALL
SELECT 8,7
) d (Col1,Col2)
;
--===== Different type of solution.
-- Also equates NULLs but not demonstrated here.
SELECT * FROM #TableA
UNION ALL
SELECT * FROM
(
SELECT * FROM #Tableb
EXCEPT
SELECT * FROM #TableA
) d
;
1 Like