NULL praktiskā pielietošana

Iepriekšējā rakstā par NULL bija neliels teorētiskais pamatojums, kas tas tāds ir un ko ziemā ēd. Tagad ķersimies pie praktiskākām lietām, lai saprastu kā ar Null jādarbojas un kādi potenciāli zemūdens akmeņi zem Null var slēpties, kas var būt gana sāpīgi, ja tos laikus nepamana.

Salīdzināšana ar NULL

Parasta salīdzināšana (“=”, zīme vienāds) ar Null vienmēr rezultātā dod Null. Kas arī ir visnotaļ loģiski, jo Null nozīmē nezināms, nepielietojams, trūkstošs, līdz ar to mēs neko nevaram pateikt, vai dotā vērtība ir tāda pati kā Null, vai nē. Un vēl trakāk – pat divus Null salīdzinot savā starpā rezultāts arī ir Null, kas atkal ir loģiski, jo nevar taču zināt vai viens nezināmais ir tas pats, kas otrs nezināmais.
Tādējādi salīdzināšanas tabula diviem mainīgajiem p un q ir šāda:

p = q p
Patiess Aplams Null
q Patiess Patiess Aplams Null
Aplams Aplams Patiess Null
Null Null Null Null

Protams, kaut kādā veidā tomēr ir jāvar noskaidrot vai vērtība ir Null vai nē. Tas tiek darīts ar speciālu operatoru IS NULL. Tātad vienmēr, kad jumsir nepieciešams noskaidrot vai vērtība ir null, vienmēr lietojiet šo operatoru. Skatamies piemērus ar jau tradicionālajām tabulām, kuru izveides skripti ir šeit.

Piemērs 1. Parasta salīdzināšana ar Null vienmēr neatgriež nevienu ierakstu
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2 WHERE prs_tevs_id = NULL;
no rows selected

Piemērs 2. Jālieto IS NULL operators
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id IS NULL;
PRS_VARDS     PRS_UZVARDS   PRS_TEVS_ID
------------- ------------- -----------
JĀNIS         BĒRZIŅŠ
ANNA          BĒRZIŅA

Līdzīgi var rīkoties arī, lai noskaidrotu tieši pretējās vērtības, tas ir tādas kas NAV NULL, šajā gadījumā jāpievieno NOT, tātad noliegums.

Piemērs 3. IS NOT NULL jālieto, lai dabūtu visas vērtības, kas nav Null
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id IS NOT NULL;
PRS_VARDS  PRS_UZVARDS  PRS_TEVS_ID
---------- ------------ -----------
PĒTERIS    BĒRZIŅŠ      1
KĀRLIS     BĒRZIŅŠ      2

Kā redzams pēdējā piemērā personai ir norādīts tēva identifikators. Tikpat uzmanīgiem jābūt lietojot arī nevienādību. Jo arī tā salīdzinot ar Null, rezultējas par Null, un atkal ieraksti netiek atlasīti, kas pirmajā brīdī ir pavisam neintuitīvi. Tātad tas pats pirmais piemērs, tikai gribam atlasīt visus ierakstus, kam tēv aidentifikators nav vienāds ar 1. Kā jūs domājat cik rindiņas būs? Pareizi 1!!!

Piemērs 4.
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id <> 1;
PRS_VARDS   PRS_UZVARDS  PRS_TEVS_ID
----------- ------------ -----------
KĀRLIS      BĒRZIŅŠ      2

Lai gan pirmajā brīdī droši vien gribētos dabūt arī NULL vērtības, jo tās taču nav 1. Tomēr tā kā tās ir nezināmas, tad neko par tām pateikt nevaram, un tās atlasītas netiek. Lai dabūtu arī ierakstus ar Null vērtībām, ir jāpievieno papildus nosacījums:

Piemērs 5.
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id <> 1 OR prs_tevs_id IS NULL;
PRS_VARDS   PRS_UZVARDS    PRS_TEVS_ID
----------- -------------  -----------
JĀNIS       BĒRZIŅŠ
ANNA        BĒRZIŅA
KĀRLIS      BĒRZIŅŠ        2

Līdzīgi tas ir spēkā arī ar NOT IN operatoru. Protams, ka tās vērtības, kur tabulā ir NULL netiek atlasītas:

Piemērs 6.
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id NOT IN (1);
PRS_VARDS   PRS_UZVARDS   PRS_TEVS_ID
----------- ------------- -----------
KĀRLIS      BĒRZIŅŠ       2

Lai dabūtu arī tās vērtības kam tabulā ir NULL, ir jāpieraksta papildus klauza:

Piemērs 7.
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id NOT IN (1) OR prs_tevs_id IS NULL;
PRS_VARDS   PRS_UZVARDS   PRS_TEVS_ID
----------- ------------- -----------
JĀNIS       BĒRZIŅŠ
ANNA        BĒRZIŅA
KĀRLIS      BĒRZIŅŠ       2

Bet vēl trakāk ir tad, ja IN sarakstā ir NULL vērtība:

Piemērs 8.
SQL> SELECT prs_vards, prs_uzvards, prs_tevs_id FROM personas
  2  WHERE prs_tevs_id NOT IN (1, NULL);
no rows selected

Šajā gadījumā nekad neviens ieraksts netiks atrasts. Tieši šādā veidā ir maza varbūtība, ka jūs veidosiet SQL vaicājumu, biežāk tas notiek gadījumā, kad NOT IN saraksts tiek veidots ar apakšvaicājumu, šajā gadījumā ir uzmanīgi jāraugās, lai šis apakšvaicājums nekad neatgrieztu NULL vērtības.

Oracle funkcijas, kas maskē NULL

Oracle ir šādas f-jas, kas tādā vai citādā veidā kaut ko dara ar Null:

  • COALESCE – atgriež pirmo vērtību no saraksta, kas nav Null. NVL jaunākais un spēcīgākais brālis.
  • LNNVL – testē nosacījumu un pārveido tā rezultātu. Izmantojama tikai where klauzā.
  • NULLIF – salīdzina divas izteiksmes, ja tās ir vienādas, tad atgriež Null, citādi pirmo izteiksmi, loģiski ekvivalenta šādai izteiksmei CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END.
  • NVL – no divām izteiksmēm atgriež pirmo, kas nav null. Coalesce “nabaga vecākais brālis”🙂
  • NVL2 – f-jai ir 3 argumenti, ja pirmais nav null, tad atgriež otro, ja pirmais ir null, tad atgriež trešo.

Domāju, ka Nvl un Coalesce ir krietni biežāk lietojamas un noderīgākas nekā pārējās. Tātad daži piemēri.
Ja tēvs ir zināms, tad atlasam tā identifikatoru, citādi rakstam, ka tas nav zināms.

Piemērs 9. Oracle NVL funkcija
SQL> SELECT  prs_vards,
  2    prs_uzvards, nvl(to_char(prs_tevs_id), 'Nav zināms') tēvs
  3  FROM personas;
PRS_VARDS  PRS_UZVARDS    TĒVS
---------- ------------- ------------
JĀNIS      BĒRZIŅŠ       Nav zināms
PĒTERIS    BĒRZIŅŠ       1
ANNA       BĒRZIŅA       Nav zināms
KĀRLIS     BĒRZIŅŠ       2

Ja māte ir zināma, tad atlasam māti, ja māte nav zināma, tad atlasam tēvu, ja arī tēvs nav zināms, tad rakstam, ka vecāks nav zināms.

Piemērs 10. Oracle coalesce funkcija
SQL> SELECT  prs_vards, prs_uzvards,
  2    coalesce(to_char(prs_mate_id),
  3             to_char(prs_tevs_id), 'Nav zināms') vecāks
  4  FROM personas;
PRS_VARDS   PRS_UZVARDS   VECĀKS
----------- ------------ ------------
JĀNIS       BĒRZIŅŠ      Nav zināms
PĒTERIS     BĒRZIŅŠ      1
ANNA        BĒRZIŅA      Nav zināms
KĀRLIS      BĒRZIŅŠ      3

Microsoft SQL Server funkcijas, kas maskē Null

  • COALESCE – no vairākām izteiksmēm atgriež pirmo, kas nav Null.
  • ISNULL – no divām izteiksmēm atgriež pirmo, kas nav Null.
  • NULLIF – salīdzina divas izteiksmes, ja tās ir vienādas, tad atgriež Null, citādi pirmo izteiksmi.

Piemēri līdzīgi kā Oraclē, tikai ar mazliet citu datu tipu konversijas funkciju:

Piemērs 11. Microsoft SQL Server ISNULL funkcija
SELECT prs_vards, prs_uzvards,
  isnull(cast(prs_tevs_id as char), 'Nav zināms') tēvs
FROM personas;
JĀNIS   BĒRZIŅŠ Nav zināms
PĒTERIS BĒRZIŅŠ 1
ANNA    BĒRZIŅA Nav zināms
KĀRLIS  BĒRZIŅŠ 2

Piemērs 12. Microsoft SQL Server COALESCE funkcija
SELECT prs_vards, prs_uzvards,
  coalesce(cast(prs_mate_id as char),
           cast(prs_tevs_id as char), 'Nav zināms') vecāks
FROM personas;
JĀNIS   BĒRZIŅŠ Nav zināms
PĒTERIS BĒRZIŅŠ 1
ANNA    BĒRZIŅA Nav zināms
KĀRLIS  BĒRZIŅŠ 3

Null un aritmētiskās funkcijas

Lielākā daļa lietu, tiklīdz ka saskarās ar Null, tā sasmērējās. Un kļūst neuzticamas, nezināmas tas ir Null. Tas ir spēkā arī ar aritmētiskajām funkcijām. Lai gan nākošajiem piemēriem biznesa jēga nav nekāda, jo identifikatorus parasti neviens neskaita, bet tomēr tas lieliski der kā uzskates materiāls:

Piemērs 13. Tiklīdz kā kāds no saskaitāmajiem ir Null, tā viss rezultāts arī ir Null
SQL> SELECT prs_id, prs_tevs_id, prs_mate_id,
  2    prs_id + prs_tevs_id + prs_mate_id Summa
  3  FROM personas;
    PRS_ID PRS_TEVS_ID PRS_MATE_ID      SUMMA
---------- ----------- ----------- ----------
         1
         2           1
         3
         4           2           3          9

Null un agregātfunkcijas

Ir dažas lietas, kas tomēr ir morāli noturīgas un nav tā, ka tiklīdz saskarās ar Null, tā sasmērējas.  Un tādas ir agregātfunkcijas. Agregātfunkcijas parasti tiek pielietotas kopā ar Group By klauzu (sīkāk par to skat. pie Vienkārša Select teikuma). Tad nu lūk agregātfunkcijas Null vērtības vienkārši ignorē. Kā tas notiek skatīsimies uz tiem pašiem piemēriem un skaitīsim kopā ID vērtības. Tātad Id vērtības var redzēt 13. piemērā tepat augstāk. Skatamies, ko tad mums dod agregātfunkcija SUM:

Piemērs 14. Tiklīdz kā kāds no saskaitāmajiem ir Null, tā viss rezultāts arī ir Null
SQL> SELECT sum(prs_id) id_summa,
  2    sum(prs_tevs_id) tevu_id_summa,
  3    sum(prs_mate_id) mates_id_summa,
  4    sum(prs_id + prs_tevs_id + prs_mate_id) kopējā_id_summa
  5  FROM personas;
  ID_SUMMA TEVU_ID_SUMMA MATES_ID_SUMMA KOPĒJĀ_ID_SUMMA
---------- ------------- -------------- ---------------
        10             3              3               9

Kā redzams SUM ir ignorējis visas NULL vērtības un saskaitījis tikai tās, kur vērtība ir atšķirīga no Null. Nākošajā piemērā var arī redzēt, ka SUM(A) + SUM(B) ≠ SUM (A + B), jo SUM Null vērtības ignorē, bet aritmētiskās operācijas nē. Attiecīgi, operāciju “SUM” un “+” darbības nav vienādas attiecībā uz Null vērtībām.

Piemērs 15. Sum un “+” nav viens un tas pats
SQL> SELECT sum(prs_tevs_id) "SUM(A)",
  2    sum(prs_mate_id) "SUM(B)",
  3    sum(prs_tevs_id + prs_mate_id) "SUM(A + B)",
  4    sum(prs_tevs_id) + SUM(prs_mate_id) "SUM(A) + SUM(B)"
  5  FROM personas;
    SUM(A)     SUM(B) SUM(A + B) SUM(A) + SUM(B)
---------- ---------- ---------- ---------------
         3          3          5               6

 Interesanti darbojas arī Count funkcija. Nerakstīts standarta veids ierakstu saskaitīšanai tabula ir rakstīt count(*). Daži cilvēki ir gājuši tālāk un domā, ka vieninieki ir lābāki vai strādā ātrāk nekā zvaigznītes un raksta count(1). Vismaz Oracle DBVS tam ar ātrdarbību nav nekādas saistības un absolūti nekāda pamata, ja neskaita iespējamas estētiskas izjūtas. Bet – tas ko vajag atcerēties ir, ka iekavās aiz count var rakstīt patvaļīgu izteiksmi un skaitītas tiek tikai tās vērtības, kur izteiksmes rezultāts nav Null. Skaitam identifikatorus:

Piemērs 16. Count skaita izteiksmes, kuru rezulāts nav Null
SQL> SELECT count(prs_id) id_skaits,
  2    count(prs_tevs_id) tevu_id_skaits,
  3    count(prs_mate_id) mates_id_skaits,
  4    count(prs_id + prs_tevs_id + prs_mate_id) kopējā_id_skaits
  5  FROM personas;
 ID_SKAITS TEVU_ID_SKAITS MATES_ID_SKAITS KOPĒJĀ_ID_SKAITS
---------- -------------- --------------- ----------------
         4              2               1                1

Tagad jautājums uz iekrišanu – kā Jūs domājat, kāda būs funkcijas avg (no average – vidējais) vērtība šiem pašiem identifikatoriem, piemēram tēvam? Redzam, ka tabulā ir pavisam 4 ieraksti, no kuriem 2 ir aizpildīti ar vērtībām 1 un 2. Tātad vidējā vērtība būs (1+2) /2 = 1,5 vai (1+2) / 4 = 0,75 ? Redzot iepriekšminēto count darbību, tas jau uz kaut ko vedina domāt, tātad pareizā atbilde ir 1,5. Arī avg ignorē Null vērtības un varam no nākošā piemēra redzēt, ka:
avg(izteiksme) = sum(izteiksme) / count(izteiksme).

Piemērs 17. Avg skaita izteiksmes, kuru rezulāts nav Null
SQL> SELECT sum(prs_id) id_summa,
  2    count(prs_id) id_skaits,
  3    avg(prs_id) id_vidējā_vērt,
  4    sum(prs_tevs_id) tevu_id_summa,
  5    count(prs_tevs_id) tevu_id_skaits,
  6    avg(prs_tevs_id) tevu_id_vidējā_vērt
  7  FROM personas;
ID_SUMMA ID_SKAITS ID_VIDĒJĀ_VĒRT TEVU_ID_SUMMA
-------- --------- -------------- -------------
      10         4            2,5             3 
TEVU_ID_SKAITS TEVU_ID_VIDĒJĀ_VĒRT
-------------- -------------------
             2                 1,5

NULL vērtības un pārējās funkcijas

Lielākā daļa citu funkciju tiklīdz kā ievadā jebkurā argumentā saņem Null, tā rezultātā arī ir Null. Piemēram funkcija apakšvirkne, kas pirmajā argumentā saņem apstrādājamo virkni, otrā – no kura simbola sākt, trešajā – cik garu virkni ņemt.

Piemērs 18. Substr jebkurā argumentā saņemot Null, rezultātā atgriež Null
SQL> SELECT substr(null, 1, 1),
  2         substr('a', null, 1),
  3         substr('a', 1, null)
  4  FROM dual;
S S S
- - -

 

NOT NULL ierobežojums tabulām

Lai nodrošinātos pret negribētām, nejauši vai kļūdas dēļ ievadītām NULL vērtībām, tajā vieta, kur bija jānorāda kāda noteikta vērtība ir jālieto NOT NULL ierobežojums tabulas kolonām. Tas viszemākajā datu līmenī nodrošina to, ka Null vērtības šai kolonā nebūs, neatkarīgi no tā vai to ievada caur kādu aplikāciju, pa taisno tabulā izmantojot SQL Insert teikumus, vai jebkā citādi. Tā ir drošība un kopā ar citiem ierobežojumu veidiem viens mazs ķieģelītis datu bāzes kvalitātes jomā, ko nevar nodrošināt nekā citādi. Tātad veidojot tabulu pie kolonas vienkārši ir jānorāda, ka ši kolona nedrīkst būt Null.

Piemērs 19. Tabulas izveide laukam norādot NOT NULL ierobežojumu
SQL> CREATE TABLE test0 (
   2   id integer NOT NULL);

Var norādīt noklusēto vērtību ar DEFAULT atslēgas vārdu un ieliekt tajā vērtību izmantojot to pašu atslēgas vārdu:

Piemērs 20. Tabulas izveide laukam norādot NOT NULL ierobežojumu un noklusēto vērtību
SQL> CREATE TABLE test1 (
  2    id integer DEFAULT 1 NOT NULL);
Table created.
SQL> INSERT INTO test1 VALUES (default);
1 row created.
SQL> SELECT * FROM test1;
ID
----------
  1

Var šo nosacījumu pielikt jau eksistējošai tabulai.

Piemērs 21. Tabulas izveide bez NOT NULL ierobežojuma uz kolonu un pēc tam NOT NULL ierobežojuma uzlikšana
SQL> CREATE TABLE test2 (
  2    id integer);
Table created.

Oraclē tas būtu šādi:

SQL> ALTER TABLE test2 MODIFY id NOT NULL;
Table altered.

Microsoft SQL Server šādi:

ALTER TABLE test2 ALTER COLUMN id integer NOT NULL;
Command(s) completed successfully.

Protams, ja jums tabulās jau ir dati, tad visiem ierakstiem attiecīgajā kolonā ir jābūt vērtībām, kas nav Null, citādi būs kļūdas paziņojums. Tas nozīmē, ka pirms šāda ierobežojuma uzlikšanas ir vai nu trūkstošās vērtības jāizpilda pēc kaut kāda biznesam zināma algoritma, vai arī visur jāliek kādas noklusētās vērtības.

Tālākā lasāmviela

4 Responses to NULL praktiskā pielietošana

  1. Ieva says:

    vairākus gadus strādājam ar MS SQL serveri, pavisam nejauši atradām, ka null=null var būt arī true.

    piemērs ->

    SET ANSI_NULLS OFF
    GO
    if null=null
    select ‘true’
    else
    select ‘false’

    šis ir piemērs no sērijas “izlasiet, bet tā nekad nedariet”

  2. Gints Plivna says:

    Tev taisnība Ieva!
    Bet kā jau Tu teici, ar šo ir jābūt ļoti uzmanīgam, jo arīdzan dokumentācijā par šo SET ANSI_NULLS ON/OFF ir teikts, ka “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”. Pie tam kā tur rakstīts, tad tas šad tad strādā, šad tad nē, piemēram, ja salīdzinašanā viens ir mainīgais vai literālis, tad strādā:
    SET ansi_nulls off
    select prs_id from personas
    where prs_tevs_id = null
    prs_id
    1
    3
    Bet, ja abas ir kolonas, tad nestrādā (kā redzam null vērtības netika atlasītas):
    SET ansi_nulls off
    select prs_id from personas
    where prs_tevs_id = prs_tevs_id
    prs_id
    2
    4
    Izrādās, ka SQL Serverī ir vēl vismaz viens cits līdzīgs uzstādījums, no kura būtu jāizvairās🙂, bet pašlaik ir:
    SET CONCAT_NULL_YIELDS_NULL
    SET CONCAT_NULL_YIELDS_NULL on
    select ‘a’ + null
    NULL

    SET CONCAT_NULL_YIELDS_NULL off
    select ‘a’ + null
    a

    Piemēram, Oraclē, gan konkatenēšanas operators Null vērtības ignorē:
    SQL> select ‘ab’ || null || ‘cd’ from dual;

    ‘AB’
    —-
    abcd

  3. Edgars Čupits says:

    Sveiks Gints,
    Manuprāt vēl ir vērts pieminēt, ka Oracle ir viens interesants izņēmums. Oracle (droši vien vēsturisku iemeslu dēļ) uzskata tukšu virkni (”) par null un sanāk, ka tukšas virknes garums ir null, nevis 0. Tas neatbilst ANSI standartam, bet tas tā ir un pašlaik, diez vai Oracle var šādu uzvedību izmainīt🙂

  4. Gints Plivna says:

    Tas bija pieminēts (vismaz tas, ka tukša virkne Oraclē ir tas pats, kas NULL) iepriekšējā teorētiskajā rakstā par NULL🙂

Komentēt

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: