SQL return all id's from 1 table and only ones that don't match from another

Question

I have 2 tables: #tmptable1 and #tmptable2. These tables have an ID to uniquely identify each record.

I want to return all records from table 1, but I only want to return the records from table 2 that aren't in table 1

I want this done in 1 query.

Thanks!

Answer 1

SELECT * FROM '#tmptable1' 
UNION ALL
SELECT * FROM  '#tmptable2' WHERE 
    ID NOT IN (SELECT ID FROM #tmptable1 WHERE ID IS NOT NULL)

Answer 2

I don't quite understand what is it that you want to get in the results, but UNION selects distinct values, so you won't have duplicate values (values from #tmptable2 that already exist in #tmptable1).

SELECT * FROM #tmptable1
UNION
SELECT * FROM #tmptable2

Anyways, these records are records from #tmptable2 that aren't in #tmptable1.

SELECT * FROM #tmptable2
EXCEPT
SELECT * FROM #tmptable1

Answer 3

If all the other fields and data of the two tables is identical you could do this:

SELECT * FROM #tmptable1
UNION
SELECT * FROM #tmptable2

UNION without the ALL modifier removes duplicates.

Answer 4

This is what UNION does

SELECT * FROM '#tmptable1' 
UNION 
SELECT * FROM  '#tmptable2'

Answer 5

SELECT ID FROM #tmptable1
UNION
SELECT ID FROM #tmptable2

The UNION operator will automatically remove duplicates.