Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What is the nullability of trantype and product_code? –  Martin Smith Jan 15 '13 at 16:38
    
All fields are non-null. –  Warren P Jan 15 '13 at 16:38

2 Answers 2

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 
share|improve this answer
    
Oh... That covers either one being null, so either the left-hand-side (T1) or right-hand--side (T1) is null, but the other side is not null, and thus, we have a row returned. Both cases (t1 row missing, t2 row missing) are covered? –  Warren P Jan 15 '13 at 16:45
1  
@WarrenP - Yes both cases are covered. If the T2 row is missing the corresponding columns will be NULL and if the T1 row is missing the T1 columns will be NULL. You could do something a bit more elaborate with just one set of columns. SELECT ISNULL(T1.trantype, T2.trantype) AS trantype, ISNULL(T1.product_code, T2.product_code) AS product_code, CASE WHEN T1.trantype IS NULL THEN 0 ELSE 1 END AS flag –  Martin Smith Jan 15 '13 at 16:49

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.