Null (SQL) - Null (SQL)

Řecký malý znak omega (ω) se používá k reprezentaci hodnoty Null v teorii databáze .

Null nebo NULL je speciální značka používaná ve Structured Query Language k označení, že datová hodnota v databázi neexistuje . SQL Null, který vytvořil tvůrce modelu relační databáze EF Codd , slouží ke splnění požadavku, aby všechny skutečné systémy pro správu relačních databází ( RDMS ) podporovaly reprezentaci „chybějících informací a neaplikovatelných informací“. Codd také představil použití malého řeckého symbolu omega (ω) k reprezentaci Null v teorii databáze . V SQL NULLse k identifikaci této značky používá vyhrazené slovo .

Nulová hodnota by neměla být zaměňována s hodnotou 0. Nulová hodnota označuje nedostatek hodnoty, což není totéž jako hodnota nula. Zvažte například otázku „Kolik knih vlastní Adam?“ Odpověď může být „nula“ ( víme , že žádný nevlastní ) nebo „null“ ( nevíme, kolik jich vlastní). V databázové tabulce by sloupec vykazující tuto odpověď začínal bez hodnoty (označeno Null) a nebyl by aktualizován hodnotou „nula“, dokud bychom nezjistili, že Adam nevlastní žádné knihy.

SQL null je stav, nikoli hodnota. Toto použití je zcela odlišné od většiny programovacích jazyků, kde nulová hodnota odkazu znamená, že neukazuje na žádný objekt .

Dějiny

EF Codd zmínil nuly jako způsob reprezentace chybějících dat v relačním modelu v dokumentu z roku 1975 ve FDT Bulletinu ACM - SIGMOD . Coddův papír, který je nejčastěji citován v souvislosti se sémantikou Null (jak byl přijat v SQL), je jeho papír z roku 1979 v ACM Transactions on Database Systems , ve kterém také představil svůj relační model/Tasmánii , ačkoli většina ostatních návrhů z poslední papír zůstal nejasný. Oddíl 2.3 jeho článku z roku 1979 podrobně popisuje sémantiku nulové propagace v aritmetických operacích a také srovnání využívající ternární (tříhodnotovou) logiku při porovnávání s nulovými hodnotami ; také podrobně popisuje zacházení s Nulls v jiných nastavených operacích (poslední problém je dodnes kontroverzní). V kruzích teorie databáze je původní návrh Codda (1975, 1979) nyní označován jako „Coddovy tabulky“. Codd později posílil svůj požadavek, aby všechny RDBMS podporovaly Null pro indikaci chybějících údajů v dvoudílném článku z roku 1985 publikovaném v časopise ComputerWorld .

1986 SQL norma v zásadě přijala Codd návrhu po implementaci prototypu v IBM System R . Ačkoli Don Chamberlin uznával nuly (vedle duplicitních řádků) jako jeden z nejkontroverznějších rysů SQL, hájil návrh Nullů v SQL s odvoláním na pragmatické argumenty, že šlo o nejméně nákladnou formu systémové podpory pro chybějící informace, čímž programátora zachránil před mnoho duplicitních kontrol na úrovni aplikace (viz problém semipredikátu ) a současně poskytuje návrháři databází možnost nepoužívat Null, pokud si to přejí; například, aby se zabránilo dobře známým anomáliím (diskutováno v sémantické části tohoto článku). Chamberlin také tvrdil, že kromě poskytování některých funkcí s chybějící hodnotou vedly praktické zkušenosti s Nulls také k dalším jazykovým funkcím, které se na Nulls spoléhají, jako jsou určité seskupovací konstrukce a vnější spojení. Nakonec tvrdil, že v praxi se Nulls také používá jako rychlý způsob, jak opravit existující schéma, když se potřebuje vyvinout nad rámec původního záměru, kódování nikoli pro chybějící, ale spíše pro neaplikovatelné informace; například databáze, která rychle potřebuje podporovat elektromobily a přitom mít sloupec mil na galon.

Codd ve své knize The Relational Model for Database Management, Version 2 , The 1990 , uvedl, že jediný Null nařízený standardem SQL byl neadekvátní a měl by být nahrazen dvěma samostatnými značkami typu Null, které by naznačovaly důvod, proč data chybí. V Coddově knize jsou tyto dva markery nulového typu označovány jako „hodnoty A“ a „hodnoty I“, což znamená „chybějící, ale použitelné“ a „chybějící, ale nepoužitelné“. Coddovo doporučení by vyžadovalo rozšíření logického systému SQL tak, aby vyhovoval logickému systému se čtyřmi hodnotami. Kvůli této dodatečné složitosti nezískala myšlenka více nul s různými definicemi široké uznání v doméně databázových praktiků. Zůstává však aktivní oblastí výzkumu, přičemž stále existuje řada publikací.

Výzvy

Null byl středem kontroverzí a zdrojem debat kvůli jeho přidružené logice se třemi hodnotami (3VL), zvláštním požadavkům na jeho použití při spojování SQL a zvláštnímu zacházení vyžadovanému agregačními funkcemi a operátory seskupování SQL. Profesor informatiky Ron van der Meyden shrnul různé problémy takto: „Nesrovnalosti ve standardu SQL znamenají, že není možné připsat žádné intuitivní logické sémantice léčbě nuly v SQL.“ Ačkoli byly k řešení těchto problémů předloženy různé návrhy, složitost alternativ zabránila jejich širokému přijetí.

Nulová propagace

Aritmetické operace

Protože Null není datová hodnota, ale značka pro chybějící hodnotu, použití matematických operátorů na Null dává neznámý výsledek, který je reprezentován Null. V následujícím příkladu vynásobením hodnoty 10 hodnotou Null získáte hodnotu Null:

10 * NULL          -- Result is NULL

To může vést k neočekávaným výsledkům. Například při pokusu o rozdělení hodnoty Null nulou mohou platformy vrátit hodnotu Null namísto vyvolání očekávané „výjimky dat - dělení nulou“. Ačkoli toto chování není definováno standardem ISO SQL, mnoho dodavatelů DBMS zachází s touto operací podobně. Například platformy Oracle, PostgreSQL, MySQL Server a Microsoft SQL Server vracejí výsledek Null pro následující:

NULL / 0

Zřetězení řetězců

Operace zřetězení řetězců , které jsou běžné v SQL, mají také za následek Null, pokud je jeden z operandů Null. Následující příklad ukazuje Null výsledek vrácený pomocí Null s ||operátorem zřetězení řetězce SQL .

'Fish ' || NULL || 'Chips'   -- Result is NULL

To neplatí pro všechny implementace databáze. V Oracle RDBMS jsou například NULL a prázdný řetězec považovány za totéž, a proto „Fish“ || NULL || „Chips“ má za následek „Fish Chips“.

Porovnání s NULL a tříhodnotovou logikou (3VL)

Protože Null není členem žádné datové domény , není považován za „hodnotu“, ale spíše za značku (nebo zástupný symbol) označující nedefinovanou hodnotu . Z tohoto důvodu nemůže srovnání s Null nikdy vyústit v True ani False, ale vždy ve třetí logický výsledek, Neznámý. Logický výsledek níže uvedeného výrazu, který porovnává hodnotu 10 s hodnotou Null, je Neznámý:

SELECT 10 = NULL       -- Results in Unknown

Některé operace na Null však mohou vrátit hodnoty, pokud chybějící hodnota není relevantní pro výsledek operace. Zvažte následující příklad:

SELECT NULL OR TRUE   -- Results in True

V tomto případě je skutečnost, že hodnota vlevo od OR je nepoznatelná, irelevantní, protože výsledek operace OR by byl True bez ohledu na hodnotu vlevo.

SQL implementuje tři logické výsledky, takže implementace SQL musí poskytovat specializovanou logiku se třemi hodnotami (3VL) . Pravidla upravující tříhodnotovou logiku SQL jsou uvedena v níže uvedených tabulkách ( p a q představují logické stavy) „Tabulky pravd, které SQL používá pro AND, OR a NEodpovídají běžnému fragmentu tříhodnotové logiky Kleene a Łukasiewicz ( které se liší svou definicí implikace, nicméně SQL žádnou takovou operaci nedefinuje).

Účinek neznámého v klauzulích WHERE

Logika se třemi hodnotami SQL se vyskytuje v Data Manipulation Language (DML) ve srovnávacích predikátech příkazů a dotazů DML. WHEREKlauzule způsobí příkazu DML jednat pouze ty řádky, pro které přívlastková vyhodnotí jako True. Řádky, u nichž přívlastková vyhodnocen jako nepravdivý nebo neznámé nejsou působeno INSERT, UPDATEnebo DELETEDML, a odstraňují SELECTdotazů. Interpretace Neznámého a Falešného jako stejného logického výsledku je běžnou chybou, se kterou se setkáváme při práci s Nulls. Následující jednoduchý příklad ukazuje tento klam:

SELECT *
FROM t
WHERE i = NULL;

Výše uvedený příklad logicky vždy vrátí nula řádků, protože srovnání sloupce i s Null vždy vrátí Neznámý, a to i pro řádky, kde i je Null. Výsledek Neznámý způsobí, že SELECTpříkaz souhrnně zahodí každý řádek. (V praxi však některé nástroje SQL načtou řádky pomocí porovnání s Null.)

Nulové specifické a 3VL specifické srovnávací predikáty

Základní operátory porovnávání SQL při porovnávání čehokoli s Null vždy vrátí Neznámý, takže standard SQL poskytuje dva speciální porovnávací predikáty specifické pro Null. Tyto IS NULLa IS NOT NULLPredikáty (které používají postfixový syntaxi) testu, zda se data, nebo není, null.

Standard SQL obsahuje volitelnou funkci F571 „Testy hodnot pravdy“, která zavádí další tři logické unární operátory (šest ve skutečnosti, pokud počítáme jejich negaci, která je součástí jejich syntaxe), rovněž pomocí postfixové notace. Mají následující pravdivostní tabulky:

Funkce F571 je ortogonální k přítomnosti booleovského datového typu v SQL (diskutováno dále v tomto článku) a navzdory syntaktickým podobnostem F571 nezavádí booleovské nebo tříhodnotové literály v jazyce. Funkce F571 byla ve skutečnosti přítomna v SQL92 , dlouho předtím, než byl booleovský datový typ zaveden do standardu v roce 1999. Funkce F571 je však implementována několika systémy; PostgreSQL je jedním z těch, kteří jej implementují.

Přidání IS UNKNOWN k ostatním operátorům tříhodnotové logiky SQL činí tříhodnotovou logiku SQL funkčně kompletní , což znamená, že její logické operátory mohou vyjadřovat (v kombinaci) jakoukoli myslitelnou logickou funkci se třemi hodnotami.

V systémech, které nepodporují funkci F571, je možné emulovat IS UNKNOWN p procházením každého argumentu, který by mohl způsobit výraz p Neznámý a otestovat tyto argumenty pomocí IS NULL nebo jiných funkcí specifických pro NULL, i když to může být více těžkopádný.

Zákon vyloučeného čtvrtého (v klauzulích WHERE)

Ve třech oceňují logiky SQL je právo vyloučeného středa , p nebo ne p , už ne vyhodnocen jako pravdivý pro všechny p . Přesněji, v logice se třemi hodnotami SQL p NEBO NE je p neznámé přesně, když p je neznámé a pravdivé jinak. Protože přímé srovnání s Null má za následek neznámou logickou hodnotu, následující dotaz

SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 );

není ekvivalentní v SQL s

SELECT * FROM stuff;

pokud sloupec x obsahuje nějaké Null; v takovém případě by druhý dotaz vrátil některé řádky, první nevrátil, a to všechny ty, ve kterých x je Null. V klasické logice se dvěma hodnotami by zákon vyloučeného středu umožňoval zjednodušení predikátu klauzule WHERE, ve skutečnosti jeho odstranění. Pokus o aplikaci zákona vyloučeného středu na 3VL SQL je ve skutečnosti falešná dichotomie . Druhý dotaz je ve skutečnosti ekvivalentní s:

SELECT * FROM stuff;
-- is (because of 3VL) equivalent to:
SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL;

Správné zjednodušení prvního příkazu v SQL tedy vyžaduje, abychom vrátili všechny řádky, ve kterých x není null.

SELECT * FROM stuff WHERE x IS NOT NULL;

Vzhledem k výše uvedenému si všimněte, že pro klauzuli WHERE jazyka SQL lze napsat tautologii podobnou zákonu vyloučeného středu. Za předpokladu, že je přítomen operátor IS UNKNOWN, platí p OR (NOT p ) OR ( p IS UNKNOWN) pro každý predikát p . Mezi logiky se tomu říká zákon vyloučeného čtvrtého .

Existuje několik výrazů SQL, ve kterých je méně zřejmé, kde dochází k falešnému dilematu, například:

SELECT 'ok' WHERE 1 NOT IN (SELECT CAST (NULL AS INTEGER))
UNION
SELECT 'ok' WHERE 1 IN (SELECT CAST (NULL AS INTEGER));

neprodukuje žádné řádky, protože se INpřevádí na iterovanou verzi rovnosti v sadě argumentů a 1 <> NULL je Neznámý, stejně jako 1 = NULL je Neznámý. (CAST v tomto příkladu je potřeba pouze v některých implementacích SQL, jako je PostgreSQL, které by jej jinak odmítly s chybou při kontrole typu. V mnoha systémech funguje v poddotazu prostý příkaz SELECT NULL.) Chybějící výše uvedený případ je samozřejmě:

SELECT 'ok' WHERE (1 IN (SELECT CAST (NULL AS INTEGER))) IS UNKNOWN;

Vliv Null a Unknown v jiných konstrukcích

Připojuje se

Připojení se vyhodnocují pomocí stejných pravidel porovnání jako u klauzulí WHERE. Proto je třeba dávat pozor při používání sloupců s možnou hodnotou null v kritériích připojení SQL. Zejména tabulka obsahující libovolné hodnoty null není rovná přirozenému samočinnému spojení, což znamená, že zatímco to platí pro jakýkoli vztah R v relační algebře , automatické připojení SQL vyloučí všechny řádky, které mají hodnotu Null kdekoli. Příklad tohoto chování je uveden v části analyzující sémantiku chybějících hodnot Null. ${\ Displaystyle R \ bowtie R = R}$

COALESCEFunkci nebo CASEvýrazy SQL lze použít k „simulaci“ nulové rovnosti v kritériích spojení IS NULLa IS NOT NULLpredikáty a lze použít také v kritériích spojení. Následující predikát testuje rovnost hodnot A a B a považuje Null za rovnocenné.

(A = B) OR (A IS NULL AND B IS NULL)

CASE výrazy

SQL poskytuje dvě varianty podmíněných výrazů . Jeden se nazývá „jednoduchý PŘÍPAD“ a funguje jako příkaz switch . Ten druhý se ve standardu nazývá „prohledávaný CASE“ a funguje jako if ... elseif .

Jednoduché CASEvýrazy používají implicitní srovnání rovnosti, která fungují podle stejných pravidel jako WHEREpravidla klauzule DML pro Null. Tak jednoduchý CASEvýraz nemůže kontrolovat existenci Null přímo. Kontrola Null v jednoduchém CASEvýrazu vždy vede k Neznámému, jako v následujícím:

SELECT CASE i WHEN NULL THEN 'Is Null'  -- This will never be returned
              WHEN    0 THEN 'Is Zero'  -- This will be returned when i = 0
              WHEN    1 THEN 'Is One'   -- This will be returned when i = 1
              END
FROM t;

Protože je výraz i = NULLvyhodnocen jako Neznámý bez ohledu na to, jakou hodnotu sloupec i obsahuje (i když obsahuje Null), řetězec 'Is Null'nebude nikdy vrácen.

Na druhou stranu „hledaný“ CASEvýraz může ve svých podmínkách používat predikáty jako IS NULLa IS NOT NULL. Následující příklad ukazuje, jak pomocí prohledávaného CASEvýrazu správně zkontrolovat hodnotu Null:

SELECT CASE WHEN i IS NULL THEN 'Null Result'  -- This will be returned when i is NULL
            WHEN     i = 0 THEN 'Zero'         -- This will be returned when i = 0
            WHEN     i = 1 THEN 'One'          -- This will be returned when i = 1
            END
FROM t;

V hledaném CASEvýrazu je řetězec 'Null Result'vrácen pro všechny řádky, ve kterých i je Null.

Oracle dialekt SQL poskytuje vestavěnou funkci, DECODEkterou lze použít místo jednoduchých výrazů CASE a považuje dvě nuly za rovnocenné.

SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t;

Nakonec všechny tyto konstrukce vrátí NULL, pokud není nalezena shoda; mají výchozí ELSE NULLklauzuli.

IF prohlášení v procedurálních rozšířeních

SQL/PSM (SQL Persistent Stored Modules) definuje procedurální rozšíření pro SQL, například IFpříkaz. Hlavní dodavatelé SQL však historicky zahrnovali vlastní proprietární procedurální rozšíření. Procedurální rozšíření pro smyčky a porovnávání fungují podle nulových srovnávacích pravidel podobných těm pro příkazy a dotazy DML. Následující fragment kódu ve standardním formátu ISO SQL ukazuje použití Null 3VL v IFpříkazu.

IF i = NULL THEN
      SELECT 'Result is True'
ELSEIF NOT(i = NULL) THEN
      SELECT 'Result is False'
ELSE
      SELECT 'Result is Unknown';

Příkaz IFprovádí akce pouze pro ta srovnání, která jsou vyhodnocena jako True. U příkazů, které jsou vyhodnoceny jako Nepravdivé nebo Neznámé, IFpředá příkaz řízení ELSEIFklauzuli a nakonec ELSEklauzuli. Výsledkem výše uvedeného kódu bude vždy zpráva, 'Result is Unknown'protože srovnání s hodnotou Null se vždy vyhodnotí jako Neznámá.

Analýza sémantiky chybějící hodnoty SQL Null

Průkopnická práce T. Imielińského a W. Lipského mladšího (1984) poskytla rámec, ve kterém lze vyhodnotit zamýšlenou sémantiku různých návrhů na implementaci sémantiky chybějící hodnoty, která se označuje jako Imieliński-Lipski Algebras . Tato část zhruba navazuje na kapitolu 19 učebnice „Alice“. Podobná prezentace se objevuje v recenzi Ron van der Meyden, §10.4.

Ve výběrech a projekcích: slabé zastoupení

Konstrukty představující chybějící informace, jako jsou Coddovy tabulky, jsou ve skutečnosti určeny k tomu, aby představovaly sadu relací, jednu pro každou možnou instanci jejich parametrů; v případě Coddových tabulek to znamená náhradu Nullů nějakou konkrétní hodnotou. Například,

Konstrukt (například Coddova tabulka) je údajně silným reprezentačním systémem (chybějících informací), pokud lze jakoukoli odpověď na dotaz provedený na konstrukci specifikovat, aby se získala odpověď na jakýkoli odpovídající dotaz na vztahy, které představuje, což jsou považovány za modely konstrukce. Přesněji, pokud q je vzorec dotazu v relační algebře („čistých“ vztahů) a pokud q je jeho zvednutí do konstruktu určeného k reprezentaci chybějících informací, silná reprezentace má tu vlastnost, že pro jakýkoli dotaz q a (tabulka) konstrukt T , q zvedne všechny odpovědi na konstrukt, tj .:

${\ displaystyle \ mathop {\ mathrm {Models}} ({\ bar {q}} (T)) = \ {q (R) \, | R \ in \ mathop {\ mathrm {Models}} (T) \ }}$

(Výše uvedené musí platit pro dotazy, které berou jako argumenty libovolný počet tabulek, ale pro tuto diskusi stačí omezení na jednu tabulku.) Coddovy tabulky zjevně nemají tuto silnou vlastnost, pokud jsou výběry a projekce považovány za součást jazyka dotazu. Například všechny odpovědi na

SELECT * FROM Emp WHERE Age = 22;

by měla zahrnovat možnost, že může existovat relace jako EmpH22. Coddovy tabulky však nemohou představovat disjunkci „výsledek s 0 nebo 1 řádky“. Zařízení, většinou teoretického zájmu, nazývané podmíněná tabulka (nebo c-tabulka) však může představovat takovou odpověď:

kde sloupec podmínky je interpretován jako řádek neexistuje, pokud je podmínka nepravdivá. Ukazuje se, že protože vzorce ve sloupci podmínek c-tabulky mohou být libovolné výrokové logické vzorce, má algoritmus pro problém, zda c-tabulka představuje nějaký konkrétní vztah, co-NP-úplnou složitost, a proto je malý praktická hodnota.

Slabší pojem reprezentace je proto žádoucí. Imielinski a Lipski zavedli pojem slabé reprezentace , který v zásadě umožňuje ( zrušeným ) dotazům přes konstrukt vrátit reprezentaci pouze pro jistou informaci, tj. Pokud je platná pro všechny „ možné světové “ instance (modely) konstruktu. Konkrétně je konstrukt slabým reprezentačním systémem, pokud

${\ Displaystyle \ bigcap \ mathop {\ mathrm {Models}} ({\ bar {q}} (T)) = \ bigcap \ {q (R) \, | R \ in \ mathop {\ mathrm {Models}} (T) \}}$

Pravá strana výše uvedené rovnice je jistá informace, tj. Informace, kterou lze určitě extrahovat z databáze bez ohledu na to, jaké hodnoty se používají k nahrazení hodnot Null v databázi. V příkladu, který jsme zvažovali výše, je snadné vidět, že průsečík všech možných modelů (tj . Jistých informací) výběru dotazu je ve skutečnosti prázdný, protože například (nezvedaný) dotaz nevrací žádné řádky pro vztah EmpH37. Obecněji řečeno, Imielinski a Lipski ukázali, že Coddovy tabulky jsou slabým reprezentačním systémem, pokud je dotazovací jazyk omezen na projekce, výběry (a přejmenování sloupců). Jakmile však do dotazovacího jazyka přidáme buď spojení, nebo odbory, dojde i ke ztrátě této slabé vlastnosti, jak dokazuje následující část. WHERE Age = 22

Pokud jsou brány v úvahu spojení nebo odbory: ani slabé zastoupení

Zvažte následující dotaz na stejnou tabulku Codd Emp z předchozí části:

SELECT Name FROM Emp WHERE Age = 22
UNION
SELECT Name FROM Emp WHERE Age <> 22;

Ať už si pro NULLvěk Harriet vyberete jakoukoli konkrétní hodnotu , výše uvedený dotaz vrátí úplný sloupec jmen jakéhokoli modelu Emp , ale když je (zvednutý) dotaz spuštěn na samotném Emp , Harriet bude vždy chybět, tj. Máme :

Když jsou tedy do dotazovacího jazyka přidány odbory, Coddovy tabulky nejsou ani slabým reprezentačním systémem chybějících informací, což znamená, že dotazy nad nimi ani nehlásí všechny jisté informace. Zde je důležité poznamenat, že sémantika UNION on Nulls, která jsou probrána v pozdější části, v tomto dotazu ani nepřišla do hry. Stačila „zapomnětlivá“ povaha těchto dvou dílčích dotazů, aby bylo zajištěno, že při spuštění výše uvedeného dotazu na Coddově tabulce Emp zůstanou některé jisté informace nehlášeny.

U přirozených spojení je příklad potřebný k prokázání, že určité informace mohou být u některých dotazů nehlášeny, o něco složitější. Zvažte tabulku

a dotaz

SELECT F1, F3 FROM
  (SELECT F1, F2 FROM J) AS F12
  NATURAL JOIN
  (SELECT F2, F3 FROM J) AS F23;

Intuice, co se stane výše, je, že Coddovy tabulky představující projekce v poddotazech ztrácejí přehled o tom, že nuly ve sloupcích F12.F2 a F23.F2 jsou ve skutečnosti kopiemi originálů v tabulce J. Toto pozorování naznačuje, že relativně jednoduché vylepšení Coddových tabulek (které v tomto případě funguje správně) by bylo použití Skolemových konstant (což znamená Skolemovy funkce, které jsou také konstantními funkcemi ), řekněme ω ₁₂ a ω ₂₂ místo jediného symbolu NULL. Takový přístup, nazývaný v-tabulky nebo naivní tabulky, je výpočetně méně nákladný než c-tabulky diskutované výše. Stále to však není úplné řešení pro neúplné informace v tom smyslu, že v-tabulky jsou pouze slabou reprezentací pro dotazy, které při výběru nepoužívají žádné negace (a také nepoužívají žádný nastavený rozdíl). První příklad zvažovaný v této části používá klauzuli negativního výběru , takže je také příkladem, kde by dotazy v-tables nehlásily jisté informace. WHERE Age <> 22

Zkontrolujte omezení a cizí klíče

Primární místo, ve kterém se tříhodnotová logika SQL protíná s jazykem SQL Data Definition Language (DDL), je ve formě kontrolních omezení . Omezení kontroly umístěné na sloupec funguje podle trochu jiné sady pravidel, než jsou pravidla pro WHEREklauzuli DML . Zatímco WHEREklauzule DML musí být pro řádek vyhodnocena jako True, kontrolní omezení nesmí být vyhodnoceno jako False. (Z logického hlediska jsou určené hodnoty True a Unknown.) To znamená, že omezení kontroly bude úspěšné, pokud je výsledek kontroly True nebo Unknown. Následující příklad tabulky s omezením kontroly zakáže vkládání jakýchkoli celočíselných hodnot do sloupce i , ale umožní vložení hodnoty Null, protože výsledek kontroly bude vždy vyhodnocen jako Neznámý pro hodnoty Null.

CREATE TABLE t (
     i INTEGER,
     CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) );

Kvůli změně označených hodnot vzhledem k klauzuli WHERE je z logického hlediska zákon vyloučeného středu tautologií omezení CHECK , což znamená, že význam je vždy úspěšný. Kromě toho za předpokladu, že hodnoty Null mají být interpretovány jako existující, ale neznámé hodnoty, některé patologické KONTROLY, jako je ta výše, umožňují vložení hodnot Null, které nikdy nemohly být nahrazeny žádnou hodnotou, která není null. CHECK (p OR NOT p)

Aby bylo možné omezit sloupec tak, aby odmítl hodnoty Null, NOT NULLlze použít omezení, jak ukazuje následující příklad. NOT NULLOmezení je sémanticky ekvivalentní kontrolního omezení s IS NOT NULLpredikátu.

CREATE TABLE t ( i INTEGER NOT NULL );

Ve výchozím nastavení jsou omezení omezení vůči cizím klíčům úspěšná, pokud jsou některá pole v těchto klíčích Null. Například tabulka

CREATE TABLE Books
( title VARCHAR(100),
  author_last VARCHAR(20),
  author_first VARCHAR(20),
FOREIGN KEY (author_last, author_first)
  REFERENCES Authors(last_name, first_name));

by umožnilo vkládání řádků, kde author_last nebo author_first jsou NULLbez ohledu na to, jak je definována tabulka Autoři nebo co obsahuje. Přesněji, null v kterémkoli z těchto polí by umožnil jakoukoli hodnotu v druhém, i když to není v tabulce Autoři. Pokud by například Autoři obsahovali pouze ('Doe', 'John'), pak ('Smith', NULL)by splňovalo omezení cizího klíče. SQL-92 přidal dvě další možnosti pro zúžení shod v takových případech. Pokud MATCH PARTIALje přidán po REFERENCESdeklaraci, pak jakákoli nenulová hodnota musí odpovídat cizímu klíči, např. ('Doe', NULL)Bude se stále shodovat, ale ('Smith', NULL)nebude. Nakonec, pokud MATCH FULLje přidán, pak ('Smith', NULL)by také neodpovídalo omezení, ale (NULL, NULL)stále by odpovídalo.

Vnější se připojí

Příklad dotazu na vnější spojení SQL s prázdnými zástupnými symboly v sadě výsledků. Null markery jsou reprezentovány slovem místo dat ve výsledcích. Výsledky pocházejí z Microsoft SQL Server , jak ukazuje SQL Server Management Studio.NULL

Vnější spojení SQL , včetně levého vnějšího spojení, pravého vnějšího spojení a úplného vnějšího spojení, automaticky vytvoří Null jako zástupné symboly pro chybějící hodnoty v souvisejících tabulkách. Například u levých vnějších spojů se místo řádků chybějících v tabulce na pravé straně LEFT OUTER JOINoperátoru vytvoří hodnoty Null . Následující jednoduchý příklad používá dvě tabulky k demonstraci výroby zástupných symbolů Null v levém vnějším spojení.

První tabulka ( Employee ) obsahuje čísla ID zaměstnance a jména, zatímco druhá tabulka ( PhoneNumber ) obsahuje související identifikační čísla zaměstnanec a telefonní čísla , jak je uvedeno níže.

Následující ukázkový dotaz SQL provede v těchto dvou tabulkách levé vnější spojení.

SELECT e.ID, e.LastName, e.FirstName, pn.Number
FROM Employee e
LEFT OUTER JOIN PhoneNumber pn
ON e.ID = pn.ID;

Výsledná sada generovaná tímto dotazem ukazuje, jak SQL používá Null jako zástupný symbol pro hodnoty chybějící v pravé tabulce ( PhoneNumber ), jak je uvedeno níže.

Agregační funkce

SQL definuje agregační funkce pro zjednodušení agregačních výpočtů dat na straně serveru. Kromě COUNT(*)funkce všechny agregační funkce provádějí krok eliminace hodnoty Null, takže hodnoty Null nejsou zahrnuty do konečného výsledku výpočtu.

Vyloučení Null není ekvivalentní nahrazení Null nulou. Například v následující tabulce AVG(i)(průměr hodnot i) poskytne jiný výsledek než AVG(j):

Zde AVG(i)je 200 (průměr 150, 200 a 250), zatímco AVG(j)150 (průměr 150, 200, 250 a 0). Dobře známým vedlejším účinkem tohoto je, že v SQL AVG(z)je ekvivalentní s not SUM(z)/COUNT(*)but SUM(z)/COUNT(z).

Výstup agregační funkce může být také Null. Zde je příklad:

SELECT COUNT(*), MIN(e.Wage), MAX(e.Wage)
FROM Employee e
WHERE e.LastName LIKE '%Jones%';

Tento dotaz vždy zobrazí přesně jeden řádek, počítá počet zaměstnanců, jejichž příjmení obsahuje „Jones“, a udává minimální a maximální mzdu nalezenou pro tyto zaměstnance. Co se však stane, pokud žádný ze zaměstnanců nesplňuje daná kritéria? Výpočet minimální nebo maximální hodnoty prázdné sady není možný, takže tyto výsledky musí být NULL, což znamená, že neexistuje žádná odpověď. Toto není neznámá hodnota, je to Null představující nepřítomnost hodnoty. Výsledkem by bylo:

Když jsou dvě hodnoty null stejné: seskupování, třídění a některé nastavené operace

Protože SQL: 2003 definuje všechny značky Null jako navzájem nerovné, byla při provádění určitých operací vyžadována speciální definice, aby bylo možné seskupit hodnoty Null dohromady. SQL definuje „jakékoli dvě hodnoty, které jsou si navzájem rovné, nebo libovolné dvě hodnoty Null“ jako „neliší se“. Tato definice nelišící umožňuje SQL seskupovat a třídit Nulls při použití GROUP BYklauzule (a dalších klíčových slov, která provádějí seskupování).

Jiné operace, klauzule a klíčová slova SQL používají při zpracování hodnot Null „neliší se“. Mezi ně patří následující:

• PARTITION BY klauzule hodnocení a okenních funkcí jako ROW_NUMBER
• UNION, INTERSECTA EXCEPToperátor, který zacházet NULL za stejné pro účely srovnání řádků / eliminace
• DISTINCTklíčové slovo používané v SELECTdotazech

Princip, že hodnoty Null nejsou navzájem rovnocenné (ale výsledkem je spíše Neznámý), je ve specifikaci SQL UNIONoperátoru, který navzájem identifikuje hodnoty null, účinně porušen . V důsledku toho mohou některé nastavené operace v SQL, jako je sjednocení nebo rozdíl, vytvářet výsledky, které nepředstavují jisté informace, na rozdíl od operací zahrnujících explicitní srovnání s NULL (např. Ty v WHEREklauzuli diskutované výše). V Coddově návrhu z roku 1979 (který byl v podstatě přijat SQL92) je tato sémantická nekonzistence racionalizována argumentem, že k odstranění duplikátů v nastavených operacích dochází „na nižší úrovni podrobností než testování rovnosti při hodnocení operací načítání“.

Standard SQL nedefinuje výslovně výchozí pořadí řazení pro hodnoty Null. Místo toho lze v konformních systémech Null seřadit před nebo za všemi hodnotami dat pomocí klauzul NULLS FIRSTnebo seznamu. Ne všichni dodavatelé DBMS však tuto funkci implementují. Dodavatelé, kteří tuto funkci neimplementují, mohou v DBMS určit různá zpracování pro nulové řazení. NULLS LASTORDER BY

Vliv na operaci indexu

Některé produkty SQL neindexují klíče obsahující hodnoty NULL. Například verze PostgreSQL před 8.3 ne, s dokumentací pro index B-stromu , která uvádí, že

B-stromy mohou zpracovávat dotazy týkající se rovnosti a rozsahu u dat, která lze řadit do nějakého řazení. Plánovač dotazů PostgreSQL zejména zváží použití indexu B-stromu, kdykoli je indexovaný sloupec zapojen do srovnání pomocí jednoho z těchto operátorů: <≤ = ≥>

Konstrukty ekvivalentní kombinacím těchto operátorů, jako MEZI a IN, lze také implementovat pomocí vyhledávání indexu B-stromu. (Všimněte si však, že IS NULL není ekvivalentní = a není indexovatelný.)

V případech, kdy index vynucuje jedinečnost, jsou hodnoty NULL z indexu vyloučeny a jedinečnost není mezi NULL vynucena. Opět cituji z dokumentace PostgreSQL :

Když je index deklarován jako jedinečný, nebude povoleno více řádků tabulky se stejnými indexovanými hodnotami. Nuly nejsou považovány za rovnocenné. Unikátní index více sloupců odmítne pouze případy, kdy jsou všechny indexované sloupce stejné ve dvou řádcích.

To je konzistentní s chováním definovaným pro SQL: 2003 skalárního Null srovnání.

Další metoda indexování hodnot Null zahrnuje jejich zpracování jako odlišných v souladu s chováním definovaným SQL: 2003. Dokumentace k serveru Microsoft SQL Server například uvádí následující:

Pro účely indexování se hodnoty NULL porovnávají jako rovnocenné. Pokud jsou klíče NULL ve více než jednom řádku, nelze tedy vytvořit jedinečný index nebo UNIQUE omezení. Při výběru sloupců pro jedinečný index nebo jedinečné omezení vyberte sloupce, které jsou definovány jako NOT NULL.

Obě tyto strategie indexování jsou v souladu s chováním Nulls definovaným SQL: 2003. Protože metodiky indexování nejsou explicitně definovány standardem SQL: 2003, strategie indexování pro hodnoty Null jsou ponechány zcela na prodejcích, aby je navrhli a implementovali.

Funkce zpracování nuly

SQL definuje dvě funkce pro explicitní zpracování hodnot Null: NULLIFa COALESCE. Obě funkce jsou zkratkami pro hledané CASEvýrazy .

NULLIF

NULLIFFunkce přijímá dva parametry. Pokud se první parametr rovná druhému parametru, NULLIFvrátí hodnotu Null. Jinak je vrácena hodnota prvního parametru.

NULLIF(value1, value2)

Tak NULLIFje zkratka pro následující CASEvýraz:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

COALESCE

COALESCEFunkce přijímá seznam parametrů, vrací první nenulovou hodnotu ze seznamu:

COALESCE(value1, value2, value3, ...)

COALESCEje definován jako zkratka pro následující CASEvýraz SQL :

CASE WHEN value1 IS NOT NULL THEN value1
     WHEN value2 IS NOT NULL THEN value2
     WHEN value3 IS NOT NULL THEN value3
     ...
     END

Některé SQL DBMS implementují funkce specifické pro dodavatele podobné COALESCE. Některé systémy (např. Transact-SQL ) implementují ISNULLfunkci nebo jiné podobné funkce, které jsou funkčně podobné COALESCE. (Viz Isfunkce pro více informací o ISfunkcích v Transact-SQL.)

NVL

Funkce Oracle NVLpřijímá dva parametry. Vrátí první parametr, který není NULL, nebo NULL, pokud jsou všechny parametry NULL.

COALESCEVýraz může být převeden na ekvivalentní NVLvýrazu tak:

COALESCE ( val1, ... , val{n} )

se mění v:

NVL( val1 , NVL( val2 , NVL( val3 , … , NVL ( val{n-1} , val{n} ) … )))

Případ použití této funkce je nahradit ve výrazu NULL hodnotou, jako je NVL(SALARY, 0)ta, která říká: „pokud SALARYje NULL, nahraďte ji hodnotou 0“.

Existuje však jedna pozoruhodná výjimka. Ve většině implementací COALESCEvyhodnocuje její parametry, dokud nedosáhne první hodnoty, která nemá hodnotu NULL, zatímco NVLvyhodnotí všechny její parametry. To je důležité z několika důvodů. Parametrem za prvním parametrem, který není NULL, může být funkce, která může být buď výpočetně nákladná, neplatná, nebo může způsobit neočekávané vedlejší efekty.

Datové psaní Null a Unknown

NULL Doslovný je bez typu v SQL, což znamená, že není určen jako celé číslo, znak, nebo jakýkoli jiný specifický typ dat . Z tohoto důvodu je někdy povinné (nebo žádoucí) explicitně převést hodnoty Null na konkrétní datový typ. Pokud například RDBMS podporuje přetížené funkce, SQL nemusí být schopen automaticky přeložit správnou funkci, aniž by znal datové typy všech parametrů, včetně těch, pro které je předána hodnota Null.

Převod z NULLdoslovného na Null konkrétního typu je možný pomocí CASTzavedeného v SQL-92 . Například:

CAST (NULL AS INTEGER)

představuje chybějící hodnotu typu INTEGER.

Skutečné psaní Neznámého (odlišného od NULL samotného) se mezi implementacemi SQL liší. Například následující

SELECT 'ok' WHERE (NULL <> 1) IS NULL;

analyzuje a úspěšně spouští v některých prostředích (např. SQLite nebo PostgreSQL ), které sjednocují logickou hodnotu NULL s Neznámou, ale v jiných se nepodaří analyzovat (např. v SQL Server Compact ). MySQL se v tomto ohledu chová podobně jako PostgreSQL (s drobnou výjimkou, že MySQL považuje TRUE a FALSE za nelišící se od běžných celých čísel 1 a 0). PostgreSQL navíc implementuje IS UNKNOWNpredikát, který lze použít k testování, zda je logický výsledek se třemi hodnotami Neznámý, ačkoli se jedná pouze o syntaktický cukr.

Datový typ BOOLEAN

Norma ISO SQL: 1999 zavedla do SQL datový typ BOOLEAN, ale stále je to jen volitelná funkce, která není jádrem, kódovaná T031.

Pokud je omezen NOT NULLomezením, SQL BOOLEAN funguje jako booleovský typ z jiných jazyků. Bez ohledu na to však datový typ BOOLEAN, navzdory svému názvu, může obsahovat pravdivostní hodnoty TRUE, FALSE a UNKNOWN, z nichž všechny jsou podle standardu definovány jako booleovské literály. Norma také tvrdí, že „NULL a UNKNOWN“ mohou být použity zaměnitelně a znamenat přesně totéž ”.

Booleovský typ byl předmětem kritiky, zejména kvůli nařízenému chování doslovného NEZNÁMÉHO, které se kvůli identifikaci s NULL nikdy nevyrovná.

Jak již bylo uvedeno výše, v implementaci SQL PostgreSQL SQL používá Null reprezentaci všech NEZNÁMÝCH výsledků, včetně NEZNÁMÉHO BOOLEANU. PostgreSQL neimplementuje UNKNOWN doslovný (ačkoli implementuje operátor IS UNKNOWN, což je ortogonální funkce.) Většina ostatních hlavních prodejců od roku 2012 nepodporuje booleovský typ (definovaný v T031). Procedurální část PL Oracle /SQL podporuje BOOLEAN, nicméně proměnné; lze jim také přiřadit NULL a hodnota je považována za stejnou jako NEZNÁMÝ.

Kontroverze

Obyčejné chyby

Nepochopení toho, jak Null funguje, je příčinou velkého počtu chyb v kódu SQL, a to jak ve standardních příkazech SQL ISO, tak ve specifických dialektech SQL podporovaných systémy správy databází v reálném světě. Tyto chyby jsou obvykle výsledkem záměny mezi hodnotou Null a 0 (nula) nebo prázdným řetězcem (hodnota řetězce s nulovou délkou, která je v SQL reprezentována ''). Null je však standardem SQL definována jako odlišná od prázdného řetězce i od číselné hodnoty 0. Zatímco Null označuje nepřítomnost jakékoli hodnoty, prázdný řetězec a číselná nula představují skutečné hodnoty.

Klasickou chybou je pokus použít operátor equals =v kombinaci s klíčovým slovem NULLk vyhledání řádků s hodnotou Null. Podle standardu SQL se jedná o neplatnou syntaxi a povede k chybové zprávě nebo výjimce. Většina implementací ale akceptuje syntaxi a vyhodnocuje takové výrazy UNKNOWN. Důsledkem je, že nebyly nalezeny žádné řádky - bez ohledu na to, zda řádky s hodnotou Null existují nebo ne. Navrhovaný způsob načítání řádků s Nulls je použití predikátu IS NULLmísto = NULL.

SELECT *
FROM sometable
WHERE num = NULL;  -- Should be "WHERE num IS NULL"

V podobném, ale jemnějším příkladu může WHEREklauzule nebo podmíněné prohlášení porovnat hodnotu sloupce s konstantou. Často se nesprávně předpokládá, že chybějící hodnota by byla „menší než“ nebo „nerovná se“ konstantě, pokud toto pole obsahuje Null, ale ve skutečnosti takové výrazy vrací Neznámý. Níže je uveden příklad:

SELECT *
FROM sometable
WHERE num <> 1;  -- Rows where num is NULL will not be returned,
                 -- contrary to many users' expectations.

Tyto zmatky vznikají, protože zákon identity je v logice SQL omezen. Při porovnávání rovnosti pomocí NULLliterálu nebo UNKNOWNpravdivostní hodnoty se SQL vždy vrátí UNKNOWNjako výsledek výrazu. Toto je relace částečné ekvivalence a dělá z SQL příklad nereflexivní logiky .

Podobně Nulls jsou často zaměňovány s prázdnými řetězci. Zvažte LENGTHfunkci, která vrací počet znaků v řetězci. Když je do této funkce předán Null, funkce vrátí Null. To může vést k neočekávaným výsledkům, pokud uživatelé dobře neznají logiku se třemi hodnotami. Níže je uveden příklad:

SELECT *
FROM sometable
WHERE LENGTH(string) < 20; -- Rows where string is NULL will not be returned.

To je komplikováno skutečností, že v některých programech databázového rozhraní (nebo dokonce databázových implementacích, jako je Oracle) je NULL hlášeno jako prázdný řetězec a prázdné řetězce mohou být nesprávně uloženy jako NULL.

Kritika

ISO SQL implementace Null je předmětem kritiky, debaty a volání po změně. V relačním modelu pro správu databází: verze 2 Codd navrhl, že implementace SQL Null byla chybná a měla by být nahrazena dvěma odlišnými značkami typu Null. Markery, že navrhované stáli za „Chybí však vstoupila v platnost“ a „Chybějící ale nepoužitelná“ , známá jako A-hodnot a I-hodnot , resp. Coddovo doporučení, pokud by bylo přijato, by vyžadovalo implementaci logiky se čtyřmi hodnotami v SQL. Jiní navrhli do Coddova doporučení přidat další značky typu Null, aby bylo uvedeno ještě více důvodů, proč může chybět hodnota dat, což zvyšuje složitost logického systému SQL. V různých časech byly také předloženy návrhy na implementaci více uživatelem definovaných značek Null v SQL. Vzhledem ke složitosti systémů zpracování logiky a logiky vyžadujících podporu více značek nuly žádný z těchto návrhů nezískal široké přijetí.

Chris Date a Hugh Darwen , autoři The Third Manifesto , navrhli, že implementace SQL Null je ve své podstatě chybná a měla by být zcela odstraněna, což ukazuje na nesrovnalosti a nedostatky v implementaci zpracování SQL Null (zejména v agregačních funkcích) jako důkaz, že celý koncept Null je vadný a měl by být odstraněn z relačního modelu. Jiní, jako autor Fabian Pascal , uvedli přesvědčení, že „jak by měl výpočet funkce zacházet s chybějícími hodnotami, se neřídí vztahovým modelem“.

Předpoklad uzavřeného světa

Dalším bodem konfliktu týkajícího se Nulls je, že porušují model převzetí uzavřeného světa relačních databází tím, že do něj zavádějí předpoklad otevřeného světa . Předpoklad uzavřeného světa, jak se týká databází, uvádí, že „vše, co databáze uvádí, ať už výslovně nebo implicitně, je pravda; vše ostatní je nepravda“. Tento pohled předpokládá, že znalosti světa uložené v databázi jsou úplné. Nulové hodnoty však fungují za předpokladu otevřeného světa, ve kterém jsou některé položky uložené v databázi považovány za neznámé, takže uložené informace o světě v databázi jsou neúplné.

Viz také

• SQL
• NULL v: Wikibook SQL
• Výukový program D
• Ternární logika
• Jazyk manipulace s daty
• Coddova 12 pravidel
• Zkontrolujte omezení
• Relační model/Tasmánie
• Systém správy relační databáze
• Připojte se (SQL)
• Třetí manifest

Reference

Další čtení

• EF Codd. Pochopení vztahů (splátka č. 7). Bulletin FDT of ACM-SIGMOD, 7 (3-4): 23–28, 1975.
• Codd, EF (1979). „Rozšíření relačního modelu databáze tak, aby zachytil větší význam“. Transakce ACM na databázových systémech . 4 (4): 397–434. CiteSeerX  10.1.1.508.5701 . doi : 10.1145/320107.320109 . Zvláště §2.3.
• Datum, CJ (2000). Relační model databáze: Retrospektivní přehled a analýza: Historický účet a hodnocení příspěvku EF Codda do oblasti databázové technologie . Addison Wesley Longman . ISBN 978-0-201-61294-3.
• Klein, Hans-Joachim (1994). "Jak upravit dotazy SQL, aby byly zaručeny jisté odpovědi" . Záznam ACM SIGMOD . 23 (3): 14–20. doi : 10.1145/187436.187445 .
• Claude Rubinson, Nulls, tříhodnotová logika a nejednoznačnost v SQL: Kritika kritizace data , záznam SIGMOD, prosinec 2007 (díl 36, č. 4)
• John Grant, Nulové hodnoty v SQL . Záznam SIGMOD, září 2008 (sv. 37, č. 3)
• Waraporn, Narongrit a Kriengkrai Porkaew. „ Nulová sémantika pro poddotazy a atomové predikáty “. IAENG International Journal of Computer Science 35.3 (2008): 305-313.
• Bernhard Thalheim, Klaus-Dieter Schewe (2011). „NULL‚ Hodnota ‘algebry a logika“ . Hranice v umělé inteligenci a aplikacích . 225 (Informační modelování a znalostní báze XXII). doi : 10,3233/978-1-60750-690-4-354 .Správa CS1: používá parametr autorů ( odkaz )
• Enrico Franconi a Sergio Tessaris, O logice nulových hodnot SQL , sborník ze 6. mezinárodního workshopu Alberta Mendelzona o základech správy dat, Ouro Preto, Brazílie, 27. – 30. Června 2012. s. 114–128

externí odkazy

• Oracle NULL
• Třetí manifest
• Důsledky NULL v sekvenování dat
• Zpráva o chybě Java o tom, že jdbc nerozlišuje prázdný a prázdný řetězec, který Sun zavřel jako „není chyba“