Jeg har egentlig aldrig tænkt meget over NULL tidligere, og har egentlig også haft det sådan, at hvis der ikke var data så foretrak jeg NULL frem for en ’deafult’ værdi. Men jeg må indrømme, at jo mere jeg arbejder med data desto mere frustrerende kan NULL være. Jeg vil her komme med et par eksempler på hvad NULL betyder for data og hvorfor jeg mener, at vi altid skal tage stilling til NULL og håndtere det!
Når vi er nødt til at markere, at vi i en række i en kolonne ikke har noget data, indsætter vi NULL, men hvad er NULL egentligt? Og hvilke konsekvenser kan det have at der er NULL værdier i et datasæt?
Inden jeg går helt i gang så tag stilling til følgende:
Jeg har 2 tabeller S og P med en række i hver:
DROP TABLE IF EXISTS S;
CREATE TABLE S (
SNO VARCHAR(5),
CITY VARCHAR(20)
);
GO
DROP TABLE IF EXISTS P;
CREATE TABLE P (
PNO VARCHAR(5),
CITY VARCHAR(20)
);
GO
INSERT INTO S VALUES('S1','London');
INSERT INTO P VALUES ('P1',NULL);
GO;Det giver os dette data:
Jeg kører dette Query, som er skrevet efter ”gammel” syntax. (så er det lidt lettere at forklare):
SELECT DISTINCT S.SNO, P.PNO
FROM P, S
WHERE S.City <> P.City
OR P.City <> 'Paris'
GO;Hvor mange rækker returneres?
Absolut ingenting, eller mere præcist; en tom række. Lad mig prøve at gå det igennem ved at kigge på where prædikatet:
WHERE S.City <> P.City
OR P.City <> 'Paris'S.City <> P.City -> London <> NULL .. det er jo I princippet rigtigt, at de ikke er ens
Eller p.City <> ’Paris’ -> Null <> ’Paris’ .. hvilket som sådan også er rigtigt… hvorfor kommer der så ikke noget retur?
NULL kan man tænke på som en markering af ingenting. For NULL er vitterligt ingenting. Og det er netop det der er udfordringen med NULL.
Hvis vi kigger på den relationelle model (Som SQL Serveren jo bekendt er bygget på) så har den 2 value logic, hvilket vil sige at når vi f.eks. sammenligner noget, eller kører en operation, så er det enten sandt eller falsk, men da vi også har muligheden for at noget kan være ingenting, altså NULL, så får vi 3 value logic (sandt, falsk, ukendt).
Når vi laver en sammenligning som den ovenfor, spørger vi altså om London er forskellig fra ”ukendt” og det kan hverken være sandt eller falsk. Og netop derfor bliver spørgsmålet i ovenstående where som følger:
Er London forskellig fra ”ukendt” eller er Paris forskellig fra ”ukendt” og det kan der ikke svares på derfor det tomme resultatsæt.
Udfordringer med NULL
USE Demo
CREATE TABLE X (
a INT NULL
);
INSERT INTO X VALUES(1),(1),(1),(1),(NULL),(NULL);SELECT SUM(a) AS sum_a,
AVG(a) AS avg_a,
COUNT(a) AS count_a,
COUNT(*) AS count_star
FROM X;
Sum_a = 4 hvilket må være rigtigt, da der er 4 rækker med 1 og 2 rækker med NULL
Avg_a = 1 det er forkert, da der er 6 rækker så regnestykket må være 4/6 = 0.66. Årsagen til dette er, at i AVG der undlades rækker med NULL. For at få det rigtige resultat, kan vi lave enten et tjek på NULL eller bruge COALESCE:
SELECT AVG(COALESCE(a, 0.0)) AS avg_a,
AVG(ISNULL(a, 0.0)) AS avg_a
FROM X;Og nu er resultatet rigtigt:
Count_a og count_star giver 2 forskellige resultater. Årsagen hertil er at Count(a) ikke tæller NULL med hvilket Count(*) gør, da det her er rækkerne, der tælles. Så Count(a) er forkert for selvom 2 af rækkerne indeholder NULL i kolonnen, så er det kolonner i rækken og disse bør tælles med.
Igen er løsningen den samme som før:
SELECT COUNT(COALESCE(a, 0.0)) AS count_a,
COUNT(ISNULL(a, 0.0)) AS count_a
FROM X;
Så husk i aggregate funktioner undlades NULL kolonner, det er derfor altid en god ide at håndtere NULL værdier ved f.eks. at benytte en af de 2 muligheder ovenfor, så er du sikker på ikke at få nogle uønskede resultater.
Her er et eksempel med nogle tekstkolonner. Jeg opretter 2 tabeller Z og Y Jeg vil gerne indsætte de rækker som ikke findes fra Y til Z:
DROP TABLE IF EXISTS Z
CREATE TABLE Z(
names VARCHAR(16) NULL,
city VARCHAR(16) NULL,
state VARCHAR(2) NULL
);
DROP TABLE IF EXISTS Y
CREATE TABLE Y(
names VARCHAR(16) NULL,
city VARCHAR(16) NULL,
state VARCHAR(2) NULL
);
INSERT INTO Z VALUES('Superman', NULL,'XX'),('Anders', 'Andeby','BB' ),('Guld-tue',NULL,'AA');
INSERT INTO Y VALUES('Batman','Hulen', 'DD'), ('Superman', NULL, 'XX'),('Guld-tue',NULL, 'AA');Data ser sådan her ud:
I nederste tabel er det altså kun Batman jeg gerne vil indsætte i den øverste tabel, så med denne forespørgsel forventes der at der kommer 1 ny række i Z:
INSERT INTO Z (names, city, state)
SELECT *
FROM Y
WHERE NOT EXISTS(
SELECT *
FROM Z
WHERE y.names = Z.names
AND Y.city = Z.city
AND Y.state = Z.state
);
Igen er det NULL, der er problemet. For selvom Superman og Guld-tue er ens i begge tabeller, så bliver sammenligningen på city ”Ukendt”, hvilket giver udtryk i at SQL Serveren ikke antager at rækken findes i tabellen. NULL = NULL -> Ukendt = Ukendt -> False.
Der er en måde at håndtere det på. Hvilket i øvrigt altid er et godt råd; håndtér NULL enten i data eller f.eks. som denne forespørgsel:
INSERT INTO Z (names, city, state)
SELECT *
FROM Y
WHERE NOT EXISTS(
SELECT *
FROM Z
WHERE y.names = Z.names
AND ISNULL(Y.city, 'N/A') = ISNULL(Z.city,'N/A')
AND Y.state = Z.state
);Nu kommer det forventede resultat med kun en række indsat i tabellen. (nb: dog af performance grunde er det ikke særlig godt at bruge funktioner i where prædikater).
En anden mulighed er, at vi kan ændre måden hvordan SQL Serveren sammenligner NULL ved at sætte ANSI_NULL til OFF så sammenlignes NULL = NULL -> true
SELECT CASE WHEN NULL = NULL THEN 'true'
WHEN NULL <> NULL THEN 'false'
ELSE 'unknown'
END AS null_comparison;Hvilket default giver Unknown. Tilføjer vi SET ANSI_NULLS ON så bliver det true:
Generelt omkring NULL
Sørg altid for at tage stilling til hvad NULL betyder. Er det fordi der mangler data?, er det fordi der er sket en fejl?, er det fordi vi har taget stilling, men der er ikke data? F.eks. hvis vi har en tabel, hvor vi kan indsætte fornavn, mellemnavn og efternavn, så er NULL et valid input hvis personen nu ikke har et mellemnavn, hvor imod NULL i efternavn ikke er valid, da det formentlig vil være en fejl, at vi ikke har fået data ind. Og hvordan kender vi forskel på de 2?
Når det handler om dimension og fact tabeller i et Data Warehouse er det enormt vigtigt at tage stilling til og håndtere NULL værdier, af de samme grunde som beskrevet ovenfor.
Jeg sad for nyligt og ville rydde op i NULL værdier i et datasæt – simpel overskrive NULL med en default værdi – på flere tabeller. Når jeg skal gøre noget mere end én gang, så ser jeg altid om jeg kan lave et script som gør det for mig. Igen med hjælp fra systemtabellerne i SQL Serveren har jeg lavet et script, der ud fra et tabel navn slår op og tager de kolonner, som har NULL tilladt, bygger en forespørgsel dynamisk, som ser om der er NULL i den kolonne og hvis der er, opdaterer værdien baseret på hvilken datatype det er.
(Scriptet kommer i bunden af denne artikel)
På tabellen Z fra før ser data sådan her ud:
Når scriptet er kørt sådan her:
Jeg medgiver, at på denne lille tabel giver det ikke så meget mening, men når vi snakker tabeller med mange kolonner, så er det nu meget handy 😊
I denne artikel har jeg meget fokuseret på ulemperne ved NULL. Men NULL er også brugbar, f.eks hvis vi har sendt en masse spørgeskemaer ud hvor vores kunder kan bedømme vores arbejde, og dermed har en række pr. e-mail vi har sendt til, i en tabel. Her giver det god mening at bruge NULL på bedømmelsen; for de rækker hvor kunden har valgt ikke at gøre noget ved vores spørgeskema. For så tæller deres manglende besvarelse ikke med i vores gennemsnitsberegning. Men uanset hvad, så er min holdning at NULL skal håndteres i data på den ene eller anden måde. Er du i tvivl om hvad der er den bedste løsning eller hvilke muligheder der er, så tag endelig fat i mig eller en af mine kollegaer i Unit IT på tlf.: 88 333 333.