EXCEPT en UNION

Gepubliceerd op 17/7/2013

Een tester bij ons op het project maakt voor het testen van ons datawarehouse vaak gebruik van het commando EXCEPT. Het EXCEPT statement zet je tussen twee queries in zoals bijvoorbeeld:

SELECT *
FROM Table1
EXCEPT
SELECT
*
FROM Table2

Je krijgt dan als resultaat alles wat in de eerste set zit, behalve die rijen die exact overeenkomen met een rij uit de tweede set. De testers gebruiken dit om de inhoud van een tabel die gevuld is door de (nieuwe) ETL te vergelijken met een door hun zelf gemaakte 'voorspel query'.

 

Stel bijvoorbeeld dat je uit een tabel alle rijen moet weggooien met status 'X'. Om te testen of er niet te veel rijen verwijderd zijn, wordt dit statement gebruikt:

SELECT *
FROM OudeTabel
WHERE Status <> 'X'
EXCEPT
SELECT
*
FROM NieuweTabel

Dit statement zal 0 rijen opleveren tenzij er te veel rijen verwijderd zijn. Alle rijen met een status anders dan 'X' moeten namelijk in "NieuweTabel" aanwezig zijn.

 

Om te testen of álle rijen die verwijderd moesten worden echt verwijderd zijn, gebruik je het statement:

SELECT *
FROM NieuweTabel
EXCEPT
SELECT
*
FROM OudeTabel
WHERE Status <> 'X'

Als dit statement wél rijen oplevert dan heb je bewezen dat blijkbaar één of meer rijen met status 'X' niet verwijderd zijn of dat er nieuwe rijen aan NieuweTabel toegevoegd zijn.

 

Een handige methode, maar de 'voorspel query' kan best complex worden. En daardoor kwamen we erachter dat de combinatie EXCEPT met UNION in een SQL statement verrassende resultaten kan opleveren.

 

Stel we hebben deze drie tabellen:

SELECT * FROM OudeTabel
SELECT * FROM NieuweTabel
SELECT * FROM NieuweTabel2

De inhoud van de eerste tabel is exact gelijk aan de combinatie van de tweede en derde tabel. Je zou dus verwachten dat:

SELECT * FROM OudeTabel
EXCEPT
SELECT
* FROM NieuweTabel
UNION
SELECT
* FROM NieuweTabel2

geen rijen oplevert. Maar het resultaat is:

Toen wij deze situatie (natuurlijk in een veel complexer scenario) tegenkwamen was onze eerste conclusie dat er toch verschillen tussen de data sets moesten zijn. Bijvoorbeeld een extra spatie achter één van de character velden (een veel voorkomend en op het oog onzichtbaar probleem). Maar na lang zoeken moesten we toch concluderen dat de data sets écht identiek waren.

 

Wat blijkt? De EXCEPT wordt vóór de UNION gedaan!

 

Dus eerst wordt dit statement uitgevoerd:

SELECT * FROM OudeTabel
EXCEPT
SELECT
* FROM NieuweTabel

Wat als resultaat geeft:

Vervolgens wordt dit resultaat samengevoegd (UNION) met de gegevens uit NieuweTabel2 (dit is dezelfde dataset!). Omdat een UNION impliciet ook een DISTINCT van de rijen geeft wordt het eindresultaat 3 rijen. En daardoor lijkt het of de rijen uit de EXCEPT komen.

 

De oplossing? Haakjes!

SELECT * FROM OudeTabel
EXCEPT
(SELECT * FROM NieuweTabel
UNION
SELECT
* FROM NieuweTabel2
)

Terug naar Artikelen

Stuur een e-mail