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.