2

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.

Improve this question
2
  • What is the nullability of trantype and product_code? Commented Jan 15, 2013 at 16:38
  • All fields are non-null. Commented Jan 15, 2013 at 16:38

2 Answers 2

Reset to default
4

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
Improve this answer
2
  • 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? Commented Jan 15, 2013 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 Commented Jan 15, 2013 at 16:49
3

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;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.