All of table A but not table B

1

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.

2 
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.

3

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.

5

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