1

To give a simple example, let's say I have two tables

  T1                      T2
=======                 =======
  Val                     Val
=======                 =======
   1                       1
   2                       3
   4                       4
   5                       5
                           6

The result set I want is

2
3
6

because those are the numbers contained in one but not the other. I know it can be done with something like

WITH cte1 (Val) AS (
    SELECT Val
    FROM T1
    LEFT JOIN T2
    ON T1.Val = T2.Val
    WHERE Val IS NOT NULL
),
cte2 (Val) AS (
    SELECT Val
    FROM T2
    LEFT JOIN T1
    ON T1.Val = T2.Val
    WHERE Val IS NOT NULL
)
SELECT *
FROM cte1
UNION WITH cte2

but is there a more compact and/or efficient way to do this?

user7127000
  • 3,143
  • 6
  • 24
  • 41

2 Answers2

6

a Full outer join should work, with an or clause.

SELECT isnull(t1.Val,t2.Val) [Val]
    FROM T1
    FULL OUTER JOIN T2
    ON T1.Val = T2.Val
WHERE T1.Val is null OR T2.Val is null
Daniel E.
  • 2,029
  • 3
  • 22
  • 28
  • This solution seems better in terms of performance, since most optimizers will sort the tables behind the scenes, before correlating them. No need for indexes on this solution. Upvoting it. – The Impaler Jun 01 '18 at 17:10
  • 1
    Elegant solution. I would replace ISNULL with COALESCE in case if the OP needs to apply it on several columns and not only two. – iSR5 Jun 01 '18 at 18:04
2

I would do this as :

select t1.* 
from t1
where not exists (select 1 from t2 where t2.val = t1.val)
union all
select t2.* 
from t2
where not exists (select 1 from t1 where t2.val = t1.val);
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52