Null (SQL)
Null (null) is een speciale markering die wordt gebruikt in de gestructureerde querytaal ( SQL ) om aan te geven dat een waarde niet bestaat in een database. Geïntroduceerd door de maker van het relationele databasemodel EF Codd , zijn functie is om de vereiste op te lossen dat echte relationele databasebeheersystemen (DBMS) "onbekende" informatie kunnen vertegenwoordigen. "of "niet van toepassing". Evenzo introduceerde Codd ook het gebruik van de Griekse letter omega (ω) in kleine letters om de Null in databasetheorie weer te geven. NULL is ook een gereserveerd woord in de SQL-taal om de speciale Null-markering te identificeren.
Null is een bron van controverse en een bron van discussie geweest vanwege de associatie met ternaire logica (in het Engels: Three-Valued Logic , afgekort 3VL), de beperkingen op het gebruik ervan in SQL en de moeilijkheid om het in SQL te verwerken. Hoewel speciale functies en predikaten dienen om effectief met Nulls om te gaan, is de concurrentie van mening dat het oplossen van dit soort problemen onnodige complexiteit en tegenstrijdigheden binnen het relationele model van databases toevoegt.
Geschiedenis
Null is geïntroduceerd door EF Codd als een methode om ontbrekende gegevens in het relationele model weer te geven. Codd versterkte later zijn oproep voor alle RDBMS' om de null-functie op te nemen om ontbrekende gegevens aan te geven in een dubbel artikel gepubliceerd in het tijdschrift ComputerWorld. Codd introduceerde ook ternaire logica, bestaande uit de waarheidsgetrouwheid van de waarden True (true), False (false) en Unknown (Unknown), die nauw verwant zijn aan het concept van Null. De echte onbekende waarde wordt gegenereerd wanneer Null wordt vergeleken met gegevens of met een andere Null.
Codd verklaarde in zijn boek "The Relational Model for Database Management gepubliceerd in 1990, versie 2" dat een enkel Null-commando via de SQL-standaard ontoereikend was en vervangen zou moeten worden door twee afzonderlijke Null-vlaggen om de reden aan te geven waarom de gegevens ontbreken. Deze twee typen nulmarkeringen worden gewoonlijk "A-waarden" en "I-waarden" genoemd, die respectievelijk "ontbrekende maar toepasselijke gegevens" en "ontbrekende maar niet van toepassing zijnde gegevens" vertegenwoordigen. De aanbeveling van Codd had een uitbreiding van het logische systeem van SQL moeten vereisen om plaats te bieden aan het quaternaire logische systeem. Vanwege de extra complexiteit heeft het idee van meerdere nulwaarden geen algemene erkenning gekregen.
Ternaire logica (driewaardige logica -3VL-)
Omdat het niet tot een datadomein behoort, wordt Null niet correct als een "waarde" beschouwd, maar is het in feite een markering die de afwezigheid van een waarde aangeeft. Hierdoor retourneren Null-vergelijkingen nooit waar of onwaar, maar als een ternair logisch resultaat onbekend. Het logische resultaat van de volgende expressie, die de waarde 10 vergelijkt met Null, is Onbekend:
10 = NULL -- Resultaten in Onbekend (Onbekend)
Bepaalde Null-bewerkingen kunnen echter waarden retourneren als de Null-waarde niet relevant is voor het resultaat van de bewerking. Dit is het geval in het volgende voorbeeld, aangezien de OR-instructie wordt geëvalueerd in de vorm van een korte beurt:
TRUE OF NULL -- Resultaten in True
In dit geval is het feit dat de waarde rechts van OR onbekend is, niet relevant, omdat het resultaat van de bewerking True zou moeten zijn, ongeacht de waarde aan de rechterkant. SQL dwingt ternaire logische resultaten af, dus SQL-toepassingen moeten gespecialiseerde ternaire logische waarden bieden. De regels voor ternaire logische waarden in SQL worden weergegeven in de volgende tabellen (p en q vertegenwoordigen logische toestanden):
|
|
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Basis operationele vergelijkingen in SQL resulteren in Unknown dat iets met Null vergelijkt, dus de SQL-standaard bepaalt speciale verzamelde predikaatberekeningen voor de twee Null-specifieke functies.
De test IS NULL en IS NOT NULL bepaalt of de gegevens al dan niet Null zijn.
Gegevenstypen
Null is niet bedoeld als een geheel getal, teken of ander specifiek gegevenstype. Hierdoor is het soms wenselijk om Null expliciet naar een specifiek datatype te casten. Als overbelaste functies bijvoorbeeld worden ondersteund door het DBMS (Database Management System), kan SQL mogelijk niet de juiste functie bepalen zonder de gegevenstypen van alle parameters te kennen, inclusief die waarvoor Null heeft gehandeld.
Taal voor gegevensmanipulatie
ternaire logische SQL-waarden zijn te vinden in de Data Manipulation Language (DML) in predikaatvergelijkingen van DML-bevestigingen en FAQ's. De WHERE-instructie zorgt ervoor dat de DML-instructie alleen werkt op die rijen waarvoor de predikaatberekening True oplevert. Rijen waarvoor deze berekeningen False of Unknown opleveren, worden niet afgeleid van DML-instructies zoals INSERT, UPDATE of DELETE en worden verwijderd uit SELECT-vragen. Het interpreteren van Onbekend en Onwaar als hetzelfde logische resultaat is een veelgemaakte fout bij het omgaan met nulls. Het volgende voorbeeld laat dit zien:
KIES *
VANUIT t
WAAR i = NULL ;
Het bovenstaande voorbeeld retourneert altijd logisch nullen voor de rijen omdat de vergelijking van kolom i met Null altijd een onbekende waarde retourneert, zelfs voor die rijen waar i Null is. Het resultaat Unknown zorgt ervoor dat de SELECT-instructie elke rij weggooit (in de praktijk zullen SQL-tools de rijen echter ophalen met behulp van Null-vergelijking).
CASE-uitdrukkingen
SQL CASE-expressies werken volgens dezelfde regels als de DMS WHERE voor Null. Omdat het kan worden geëvalueerd als een reeks gelijkheidsvergelijkingsvoorwaarden, kan een eenvoudige CASE-uitdrukking niet rechtstreeks op Null testen. Een controle op het bestaan van Null in een eenvoudige CASE-expressie retourneert altijd Unknown, als volgt:
SELECT CASE i WHEN NULL THEN 'Is Null' -- Dit wordt nooit geretourneerd.
WHEN 0 THEN 'Is nul' -- Dit wordt geretourneerd wanneer i = 0
WHEN 1 THEN 'Is één' -- Dit wordt geretourneerd wanneer i = 1
END
FROM t ;
Aangezien de uitdrukking i = NULL resulteert in Onbekend, ongeacht welke waarde kolom i bevat (zelfs als deze Null bevat), zal het resultaat ' IS NULL ' nooit worden geretourneerd. Een gezochte CASE-expressie retourneert ook de eerste waarde waarvoor het resultaat van de predikaatvergelijking True oplevert, inclusief predikaatvergelijkingen die de "IS NULL" AND "IS NOT NULL" gebruiken. Het volgende voorbeeld laat zien hoe u een gezochte CASE-expressie gebruikt om te controleren op Null:
SELECTEER GEVAL WANNEER i NULL IS DAN 'Is Null' -- Dit wordt geretourneerd wanneer i NULL is WHEN i = 0 THEN 'Nul' -- Dit levert i = 0 op WHEN i = 1 THEN 'One' -- Dit wordt geretourneerd i = 1 EINDE VAN t ;
In de gezochte CASE-expressie wordt ' Result Null ' geretourneerd voor alle rijen waarin i Null is.
Herziening van beperkingen
De primaire plaats waar ternaire SQL-logica de SQL Data Definition Language (DDL) kruist, is in de vorm van beperkingscontrole. Een recensie of beperkingscontrole die op een kolom wordt geplaatst, werkt onder een iets andere set regels dan die voor de DML WHERE-clausule. Terwijl een DML WHERE-component voor een rij moet worden geëvalueerd als True, mag een controle niet worden geëvalueerd als False. Dit betekent dat een controle slaagt als het resultaat van de controle Waar of Onbekend is. Het volgende voorbeeld toont een tabel met een controle die verbiedt dat een geheel getal in kolom i wordt ingevoegd, maar waarbij Null kan worden ingevoegd, aangezien het resultaat van de controle altijd zal resulteren in Onbekend voor Null.
MAAK TABEL t (
i INTEGER ,
CONSTRAINT ck_i CONTROLEER ( i < 0 AND i = 0 AND i > 0 ) );
Om ervoor te zorgen dat een kolom Nulls verwerpt, kan de beperking NOT NULL worden toegepast, zoals weergegeven in het onderstaande voorbeeld. De NOT NULL-voorwaarde is semantisch gelijk aan een beperkingscontrole met een IS NOT NULL-predikaat.
MAAK TABEL t ( i INTEGER NIET NULL );
Procedure-uitbreidingen
SQL/PSM (SQL Stored Persistent Modules) definieert procedurele uitbreidingen van SQL, zoals de IF-instructie. De belangrijkste releases van SQL bevatten echter historisch gezien hun eigen procedurele uitbreidingen. Procedurele uitbreidingen voor looping en vergelijking werken onder nulvergelijkingsregels, vergelijkbaar met DML-instructies en -query's. Het volgende codefragment, in standaard ISO SQL-formaat, demonstreert het gebruik van de Null 3VL in een IF-instructie.
IF i = NULL SELECTEER DAN
'Resultaat is waar' ELSEIF NIET ( i = NULL ) SELECTEER DAN 'Resultaat is niet waar' ELSE SELECTEER 'Resultaat is onbekend' ;
De IF-instructie voert alleen acties uit voor die vergelijkingen die als True worden geëvalueerd. Voor instructies die worden geëvalueerd als False of Unknown, geeft de IF-instructie de besturing door aan de ELSEIF-clausule en ten slotte aan de ELSE-clausule. Het resultaat van deze code is altijd het bericht 'Resultaat is onbekend', aangezien nulvergelijkingen altijd op Onbekend uitkomen.
JOIN-instructies
De JOIN-instructie in SQL staat de combinatie van records uit twee of meer tabellen binnen dezelfde relationele database toe. Er zijn drie soorten JOIN's in SQL: inner, outer en cross. Outer Joins, inclusief linkertabel, rechtertabel en volledige join-instructies, produceren automatisch Nulls als tijdelijke aanduidingen voor ontbrekende waarden in gerelateerde tabellen. Voor buitenste joins van de linkertabel worden bijvoorbeeld nulls geproduceerd in plaats van de ontbrekende tabelrijen die aan de rechterkant van de JOIN-operator van de linkertabel worden weergegeven. In het volgende voorbeeld worden twee tabellen gebruikt om de productie van de nulmarkering in de linker outer join te demonstreren. De eerste tabel (Werknemer) bevat de werknemers-ID-nummers en namen, terwijl de tweede tabel (Telefoonnummer) telefoonnummers bevat die verband houden met de werknemers-ID, zoals hieronder weergegeven.
|
|
De volgende voorbeeld-SQL voert een outer-join van de linkertabel uit tussen de twee bovenstaande tabellen.
KIES e . ID , e . Achternaam , e . Naam , pr . Nummer
VAN Medewerker e
LEFT OUTER JOIN Telefoonnummer pn ON e ._ _ _ id = pn . identiteitskaart ;
De uitvoer die in deze vragenreeks wordt gegenereerd, laat zien hoe SQL Null gebruikt als tijdelijke aanduiding voor ontbrekende waarden aan de rechterkant van de tabel (telefoonnummers), zoals hieronder weergegeven:
| ID kaart | Achternaam | Naam | Nummer |
|---|---|---|---|
| 1 | Links | Robert | 555-2323 |
| twee | ambrosius | albert | NUL |
| 3 | Alfonso | Katja | 555-9876 |
| 4 | Barbadillo | Willem | NUL |
Inner Joins en Cross Joins zijn ook beschikbaar in de SQL-standaard en genereren geen Null-vlaggen voor ontbrekende waarden in gerelateerde tabellen.
Voorzichtigheid is geboden bij het gebruik van nullable-kolommen in SQL-joincriteria. Omdat een Null niet gelijk is aan een andere Null, worden nulls die in één kolom in een tabel worden gevonden, niet samengevoegd met Nulls in gerelateerde kolommen in een andere tabel die de standaardoperatoren voor gelijkheidsvergelijking gebruikt. De SQL COALESCE-functie of CASE-expressies kunnen worden gebruikt om Null-gelijkheid in join-criteria te "simuleren", en de predikaten 'IS NULL' en 'IS NOT NULL' kunnen ook in join-criteria worden gebruikt.
Het volgende predikaat toetst de gelijkheid van de waarden A en B en behandelt Null als gelijk. De operator IFNULL is vereist omdat A = B een Null-waarde retourneert als ten minste een van de waarden (A of B) Null is en NULL OF FALSE zelf Null is.
IFNULL ( A = B , FALSE ) OF ( A IS NULL EN B IS NULL )
Wiskundige en karakteraaneenschakeling
Aangezien Null geen gegevenswaarde is, maar een markering voor een onbekende waarde, resulteert het gebruik van wiskundige operatoren op Null in een onbekende waarde, die wordt weergegeven door Null. In het volgende voorbeeld resulteert vermenigvuldiging van 10 met Null in Null:
10 * NULL -- Resultaat is NULL
Dit kan tot onverwachte resultaten leiden. Wanneer bijvoorbeeld wordt geprobeerd om Null door nul te delen, kunnen platforms Null retourneren in plaats van een verwachte "gegevensuitzondering - deling door nul" te genereren. Hoewel dit gedrag niet wordt gedefinieerd door de SQL ISO-standaard, behandelen veel commerciële DBMS-producten deze bewerking op een vergelijkbare manier. Oracle, PostgreSQL, MySQL Server en Microsoft SQL Server-platforms retourneren bijvoorbeeld Null voor de bewerking:
NULL / 0
De reeks geketende bewerkingen, die gebruikelijk zijn in SQL, retourneren ook Null wanneer een van de operanden Null is. Het volgende voorbeeld toont het Null-resultaat dat wordt geretourneerd door Null te gebruiken met de SQL-operator op een aaneengeschakelde tekenreeks.
'Omelet' || NULL || ' Aardappels ' - Resultaat is NULL
Dit geldt niet voor alle database-implementaties. In Oracle RDBMS worden bijvoorbeeld NULL en lege string als hetzelfde beschouwd en daarom resulteert ' Tortilla ' de NULL 'Potato' in 'Tortilla Potato'.
Toegevoegde functies
SQL definieert geaggregeerde functies om geaggregeerde berekeningen van servergegevens te vereenvoudigen. Vrijwel alle geaggregeerde functies voeren een Null-eliminatiestap uit, zodat Null-waarden niet worden meegenomen in het uiteindelijke resultaat van de berekening. Deze impliciete Null-verwijdering kan echter de resultaten van aggregatiefuncties beïnvloeden. De voorbeeldtabel hieronder leidt verschillende resultaten af die naar elke kolom worden geretourneerd wanneer de SQL-aggregatiefunctie AVG (gemiddeld) wordt toegepast:
| Yo | j |
|---|---|
| 150 | 150 |
| 200 | 200 |
| 350 | 350 |
| NUL | 0 |
De AVG SQL-aggregatiefunctie retourneert 233 wanneer toegepast op kolom i, maar retourneert 175 wanneer toegepast op kolom j. De verwijderingsstap van de Null-aggregatiefunctie verklaart het verschil in deze resultaten. De enige aggregatiefunctie die de Null niet impliciet verwijdert, is de COUNT-functie.
Groepering
Aangezien SQL:2003 alle Null-markeringen definieert als ongelijk aan elkaar, hadden ze een speciale definitie nodig om Nulls te groeperen om bepaalde bewerkingen uit te voeren. SQL definieert "elke twee waarden die gelijk zijn aan elkaar, of elk paar Nulls", als "niet onderscheiden". Met deze definitie van niet onderscheiden kan SQL nulls groeperen en sorteren wanneer de GROUP BY-component (en andere trefwoorden die de groepering uitvoeren) wordt gebruikt.
Andere SQL-bewerkingen, -clausules en trefwoorden gebruiken "niet onderscheiden" in hun behandeling van nulls. Deze omvatten het volgende:
- De classificatieclausule PARTITION BY en vensterfuncties zoals ROW_NUMBER.
- UNION, INTERSECT en de EXCEPT-operator, die NULL's hetzelfde behandelen voor rijgewijze vergelijking/verwijderingsbepalingen.
- Het DISTINCT-sleutelwoord wordt gebruikt in SELECT-vragen.
- Unieke beperkingen op kolommen waarin de Null-functie kan worden gevonden, waardoor slechts één enkele Null-waarde in de kolom kan worden opgeslagen.
De SQL-standaard definieert niet expliciet de standaard bestelklasse voor Nulls. In plaats daarvan kunnen op conforme systemen Nulls voor of na alle gegevenswaarden worden gesorteerd door respectievelijk de NULLS FIRST- of NULLS LAST-clausules van de ORDER BY-lijst te gebruiken. Niet alle DBMS-leveranciers implementeren deze functionaliteit echter. Leveranciers die deze functionaliteit niet implementeren, kunnen een andere behandeling voor Null-classificatie in het DBMS specificeren.
Null-afhandelingsfuncties
SQL definieert twee functies om Null expliciet te verwerken: COALESCE en NULLIF. Beide functies zijn afkortingen voor gezochte CASE-expressies.
COALESCE
De functie COALESCE accepteert een lijst met parameters en retourneert de eerste niet-null-waarde in de lijst:
COALESCE ( waarde1 , waarde2 , waarde3 , ...)
COALESCEis gedefinieerd als de volgende SQL-expressie CASE:
GEVAL WANNEER waarde1 NIET NULL IS DAN waarde1 WANNEER waarde2 NIET NULL IS DAN waarde2 WANNEER waarde3 NIET NULL IS DAN waarde3 ... EINDE
Sommige SQL DBMS'en implementeren vergelijkbare leverancierspecifieke functies voor COALESCE. Sommige systemen implementeren een ISNULL-functie of andere soortgelijke functies die functioneel vergelijkbaar zijn met COALESCE.
NULLIF
De NULLIF-functie accepteert twee parameters. Als de eerste parameter gelijk is aan de tweede, retourneert NULLIF Null. Anders wordt de waarde van de eerste parameter geretourneerd.
NULLIF ( waarde1 , waarde2 )
Daarom is NULLIF een afkorting voor de volgende CASE-expressie:
CASE WHEN waarde1 = waarde2 THEN NULL ELSE waarde1 END
Zie ook
Externe links
- Oracle NULL's Gearchiveerd 12-04-2013 op de Wayback Machine .
- Transact-SQL Null-referentie
- Behandeling van NULL-waarden in SQL-statements ( gebroken link beschikbaar op Internet Archive ; zie geschiedenis , eerste en laatste versie ).
