How can I do a differential query (delta plus/minus) telling me what rows are in view A that are not in view B and vice versa?

Question

I have two views which use radically different means to accomplish what I hope is the same end view. One uses complex query expressions and walks entire giant tables and is highly expensive, and the other uses a new boolean bit flag which has been added to the database for precisely the purpose of accelerating this view.

I can write this kind of union, and then (in my program code) use this to determine that a row in view A is not in the results of view B, but I'm hoping I can do this entirely in SQL:

Here's the union I came up with:

select trantype,product_code, 0 as type from vwVIEW1 where key = 'DEMO' 
union
select trantype,product_code, 1 as type  from vwVIEW2 where key = 'DEMO'
order by product_code, trantype

My old view is fictionally called vwVIEW1 above, and it walks giant database tables to determine its output, whereas vwVIEW2 does a very quick index-lookup on two fields to achieve the same results (select a,b,c from tblUnderlyingTable where tblUnderlyingTable.HeyRickyCanIBeInYourShow=1).

Typical results coming from the above union would in the healthy case, return a pair of rows for each trantype+product_code, plus my invented "type" flag:

trantype product_code  type
-------- ------------  ----
0        MATCHY        0
0        MATCHY        1
0        NONMATCHY     0

Out of the above set of data, I would want to only see the row NONMATCHY, because it is in vwVIEW1 but not in vwVIEW2. All fields in the union above are non-null. No nulls are ever expected in trantype, or product_code.

Answer 1

You can use a FULL OUTER JOIN for this

WITH T1
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW1
         WHERE  KEY = 'DEMO'),
     T2
     AS (SELECT trantype,
                product_code
         FROM   vwVIEW2
         WHERE  KEY = 'DEMO')
SELECT *
FROM   T1
       FULL OUTER JOIN T2
         ON T1.trantype = T2.trantype
            AND T1.product_code = T2.product_code
WHERE  T1.trantype IS NULL
        OR T2.trantype IS NULL 

Answer 2

Here is another approach which presents the data more inline with your sample result:

;WITH a AS 
(
  SELECT trantype, product_code, [type] = 0 
  FROM dbo.vwVIEW1 WHERE [key] = 'DEMO'
), 
b AS 
(
  SELECT trantype, product_code, [type] = 1 
  FROM dbo.vwVIEW2 WHERE [key] = 'DEMO'
)
SELECT trantype, product_code, [type] FROM a 
  EXCEPT SELECT trantype, product_code, [type] = 0 FROM b
UNION ALL
SELECT trantype, product_code, [type] FROM b
  EXCEPT SELECT trantype, product_code, [type] = 1 FROM a;