What is the best way to select data in only one of two tables?

Question

If I have two tables, such as:

CREATE TABLE #table1 (id INT, name VARCHAR(10))
INSERT INTO #table1 VALUES (1,'John')
INSERT INTO #table1 VALUES (2,'Alan')
INSERT INTO #table1 VALUES (3,'Dave')
INSERT INTO #table1 VALUES (4,'Fred')
CREATE TABLE #table2 (id INT, name VARCHAR(10))
INSERT INTO #table2 VALUES (1,'John')
INSERT INTO #table2 VALUES (3,'Dave')
INSERT INTO #table2 VALUES (5,'Steve')

And I want to see all the rows that appear in only one of the tables, what would be the best way to do this?

All I can think of is to do:

SELECT * from #table1 except SELECT * FROM #table2
UNION
SELECT * from #table2 except SELECT * FROM #table1

Or something like strings:

SELECT id,MAX(name) as name  FROM
(
SELECT *,1 as count from #table1 UNION ALL
SELECT *,1 as count from #table2
) data 
group by id
HAVING SUM(count) =1

To bring back Alan, Fred and Steve in this case.

But they seem awkward - is there a better way to get closer to this?

Answer 1

select coalesce(t1.id, t2.id)     id,
       coalesce(t1.name, t2.name) name
from   #table1 t1
       full outer join #table2 t2
         on t1.id = t2.id
where  t1.id is null
        or t2.id is null  

. , (, ) NULL . NULL.

COALESCE , NULL.

, , . , , name JOIN. , name. ( JOIN) ​​.

, CREATE INSERT, , - .

Answer 2

EXCEPT INTERSECT:

-- All rows
SELECT * FROM #table1 
UNION
SELECT * FROM #table2
EXCEPT -- except
(
  -- those in both tables
  SELECT * FROM #table1 
  INTERSECT
  SELECT * FROM #table2
)

, , EXCEPT UNION...

Answer 3

select id, name
from
 (select *, count(*) over(partition by checksum(*)) as cc
  from (select *
        from #table1
        union all
        select *
        from #table2
       ) as T
 ) as T
where cc = 1