select * from (
select coalesce (c.id,b.id) as id , 
 state=(case when c.id IS null then '丢失' 
                when b.id  IS not null and  c.id is not null then b.state 
                when b.id  IS  null and c.id is not null  then  '新增' end )
  from Base  b
full  join Compare c
on b.id=c.id ) T
group by id,state

------解决方案--------------------

SQL code
USE tempdb
 GO
 CREATE TABLE base
     (
       id INT ,
       [state] VARCHAR(10)
     )
 CREATE TABLE compare
     (
       id INT ,
       [state] VARCHAR(10)
     )
 
 INSERT  INTO base
         SELECT  1 ,
                 '不变'
         UNION ALL
         SELECT  2 ,
                 '不变'
 
 INSERT  INTO compare
         SELECT  2 ,
                 '新增'
         UNION ALL
         SELECT  2 ,
                 '新增'
         UNION ALL
         SELECT  3 ,
                 '新增'
 
 
 SELECT DISTINCT
         CASE WHEN b.id IS NULL THEN a.id
              WHEN a.id IS NULL THEN b.id
              ELSE a.id
         END AS id ,
         CASE WHEN b.id IS NULL THEN '丢失'
              WHEN a.id IS NULL THEN '新增'
              ELSE '不变'
         END AS [state]
 FROM    base a
         FULL JOIN compare b ON a.id = b.id

------解决方案--------------------

SQL code
--借用楼上数据

 CREATE TABLE base(id INT ,[state] VARCHAR(10))
 CREATE TABLE compare(id INT ,[state] VARCHAR(10))
 INSERT  INTO base
         SELECT  1 ,'不变' UNION ALL
         SELECT  2 ,'不变'
 INSERT  INTO compare
         SELECT  2 ,'新增' UNION ALL
         SELECT  2 ,'新增' UNION ALL
         SELECT  3 ,'新增'
select  id= (case
               when b.id is null
                    and a.id is not null then  a.id
               when a.id is null
                    and b.id is not null then b.id
               else   null
             end)
      state=(case

               when c.id IS null then '丢失'

               when b.id  IS not null and  c.id is not null then b.state

               when b.id  IS  null and c.id is not null  then  '新增'
               else null
               end )

from   base a

full join  compare b on a.id = b.id