NULL-ийн үнэн

Танилцуулга

Бүлэг 1-ээс хойш манай хүснэгтүүдэд хоёр онцгой нүд байсаар ирсэн: "Монголын нууц товчоо"-ны зохиогч болон Тэмүүлэнгийн имэйл. DB Browser тэдгээрийг саарал өнгөөр NULL гэж харуулдаг бөгөөд бид "энэ тухай дараа ярина" гэж хэд хэдэн удаа хойшлуулсан. Тэр цаг одоо ирлээ. NULL бол SQL-ийн хамгийн их төөрөгдөл үүсгэдэг ойлголт бөгөөд туршлагатай хөгжүүлэгчид ч түүнээс болж алдаа гаргасаар байдаг.

Энэ хичээлийн гол санаа нэг өгүүлбэрт багтана: NULL бол утга биш, харин утга байхгүй гэсэн тэмдэглэгээ юм. Энэ өгүүлбэр энгийн сонсогдож байгаа ч түүний үр дагавар гайхмаар. NULL-ийг =-ээр хайж олох боломжгүй, NULL нь өөртэйгөө ч тэнцүү биш, NULL-тэй хийсэн тооцоолол бүхэн NULL болдог. Бид эдгээрийг нэг бүрчлэн өөрсдийн нүдээр харж, дараа нь NULL-тэй зөв ажиллах хоёр тусгай хэрэгсэл — IS NULL ба IS NOT NULL-ийг сурна.

Хичээлийн төгсгөлд өмнөх хичээлд амласан NOT IN-ий хачин занг ч тайлбарлана. Мөн хамгийн чухал асуулт — NULL ер нь яагаад байдаг вэ, түүнгүйгээр болохгүй юу — гэдэгт шударга хариулт өгнө.

Зүйрлэл

Өргөдлийн маягтын хоосон нүдийг төсөөлөөд үзээрэй. Хэн нэгэн маягт бөглөхдөө "Утасны дугаар" гэсэн нүдийг хоосон орхижээ. Энэ хоосон нүд юу гэсэн утгатай вэ? Тэр хүн утасгүй байж болно, утастай мөртлөө бичихийг хүсээгүй байж болно, эсвэл зүгээр л мартсан байж болно. Бид мэдэхгүй. Хоосон нүд бол “0” биш, "байхгүй" гэсэн үг биш, зүгээр л мэдээлэл ирээгүй гэдгийн тэмдэг.

Одоо ажилтан танаас асууж байна: "Энэ хүний утасны дугаар 9911-2233 мөн үү?" Та "тийм" гэж хэлж чадахгүй, "үгүй" гэж ч хэлж чадахгүй — "мэдэхгүй" гэж л хэлнэ. Ажилтан дахин асууна: "Хоёр өөр маягт дээр утасны нүд хоёулаа хоосон байна. Тэр хоёр хүний дугаар ижил үү?" Энэ удаа ч "мэдэхгүй" гэдэг л цорын ганц шударга хариулт. Хоёр хоосон нүд хоёулаа хоосон байгаа нь хоёр хүний дугаар адилхан гэсэн үг огт биш.

SQL дахь NULL яг энэ хоосон нүд юм. Мөн SQL нь "тийм", "үгүй" хоёроос гадна гурав дахь хариулттай: "мэдэхгүй" (UNKNOWN). Энэ хичээлийн бүх гайхамшиг энэ гурав дахь хариултаас үүдэлтэй.

Анхны жишээ: = NULL урхи

Бидний books хүснэгт:

id | title                    | author           | year | genre       | copies
---+--------------------------+------------------+------+-------------+-------
1  | Монголын нууц товчоо     | NULL             | 1240 | Түүх        | 2
2  | Тунгалаг Тамир           | Ч.Лодойдамба     | 1962 | Роман       | 3
3  | Учиртай гурван толгой    | Д.Нацагдорж      | 1934 | Жүжиг       | 1
4  | Clean Code               | Robert C. Martin | 2008 | Програмчлал | 4
5  | Python Crash Course      | Eric Matthes     | 2015 | Програмчлал | 5
6  | The Pragmatic Programmer | Andrew Hunt      | 1999 | Програмчлал | 2

Зохиогч нь тодорхойгүй номнуудыг олъё. Өмнөх хоёр хичээлээр сурсан зүйлээрээ бол хамгийн байгалийн query нь:

sql

SELECT title
FROM books
WHERE author = NULL;

Үр дүн:

(хоосон — 0 мөр)

Энэ query юу гэж хэлж байна вэ гэвэл: “books хүснэгтээс author нь NULL-тэй тэнцүү мөрүүдийн title-ийг харуул.” Хүснэгтэд author нь NULL байгаа мөр тодорхой харагдаж байгаа мөртлөө нэг ч мөр ирсэнгүй. Алдааны мессеж ч гарсангүй — SQLite query-г амжилттай гүйцэтгэж, 0 rows returned гэж мэдээлэв. Энэ бол SQL-д шинээр суралцагч бүрийн дор хаяж нэг удаа гацдаг урхи юм.

Яагаад ийм болсныг харуулын нүдээр мөшгье. 1-р мөрийн хувьд харуул асууна: “NULL нь NULL-тэй тэнцүү юу?” Маягтын зүйрлэлээр бол "хоосон нүд нь өөр нэг хоосон нүдтэй ижил үү" гэсэн асуулт — хариулт нь "мэдэхгүй". WHERE зөвхөн "тийм" гэсэн хариултад мөрийг нэвтрүүлдэг тул "мэдэхгүй" гарсан мөр хасагдлаа. 2-6-р мөрүүдийн хувьд асуулт нь "Ч.Лодойдамба нь NULL-тэй тэнцүү юу" гэх мэт байх бөгөөд энэ нь ч мөн "мэдэхгүй" — учир нь мэдэгдэхгүй утгатай юуг ч харьцуулахад үр дүн мэдэгдэхгүй. Үр дүнд нь зургаан мөр бүгд хасагдлаа. = NULL гэсэн нөхцөл ямар ч хүснэгт дээр, ямар ч өгөгдөлтэй үргэлж хоосон үр дүн өгдөг.

IS NULL ба IS NOT NULL

NULL-ийг олохын тулд SQL тусгай хэрэгсэл өгдөг. Query:

sql

SELECT title
FROM books
WHERE author IS NULL;

Үр дүн:

title
--------------------
Монголын нууц товчоо

Энэ query юу гэж хэлж байна вэ гэвэл: “books хүснэгтээс author нь хоосон (утгагүй) мөрүүдийн title-ийг харуул.” IS NULL нь харьцуулалт биш, харин асуулт юм: "энэ нүд хоосон уу?" Энэ асуултад үргэлж тодорхой хариулт бий — нүд хоосон эсвэл хоосон биш, "мэдэхгүй" гэх хувилбар байхгүй. Тиймээс 1-р мөрийн хувьд "тийм" гарч нэвтэрсэн, бусад таван мөрийн хувьд "үгүй" гарч хасагдсан.

= болон IS хоёрын ялгааг маягтын зүйрлэлээр дахин хэлье. author = NULL нь "энэ хүний утасны дугаар нь хоосон нүдтэй ижил үү" гэсэн утгагүй асуулт. author IS NULL нь "энэ хүний утасны нүд хоосон уу" гэсэн хариулж болох асуулт. Хоёулаа ижил санааг илэрхийлэх гэж байгаа мэт харагддаг ч SQL-ийн нүдээр огт өөр асуултууд юм.

Эсрэг хэрэгсэл нь IS NOT NULL. members хүснэгт:

id | name     | city        | joined_year | email
---+----------+-------------+-------------+------------------
1  | Бат      | Улаанбаатар | 2023        | bat@example.com
2  | Сараа    | Дархан      | 2024        | saraa@example.com
3  | Тэмүүлэн | Улаанбаатар | 2022        | NULL
4  | Номин    | Эрдэнэт     | 2024        | nomin@example.com
5  | Ану      | Улаанбаатар | 2025        | anu@example.com

Мэдэгдэл имэйлээр илгээхийн тулд имэйлтэй гишүүдийг олъё. Query:

sql

SELECT name, email
FROM members
WHERE email IS NOT NULL;

Үр дүн:

name  | email
------+------------------
Бат   | bat@example.com
Сараа | saraa@example.com
Номин | nomin@example.com
Ану   | anu@example.com

Энэ query юу гэж хэлж байна вэ гэвэл: “members хүснэгтээс email нь хоосон биш мөрүүдийн name болон email-ийг харуул.” Тэмүүлэнгийн имэйл хоосон тул хасагдсан, бусад дөрвөн гишүүн имэйлтэй тул үлдсэн. Энэ төрлийн query нь бодит програмд маш их хэрэглэгддэг: "утасны дугаартай хэрэглэгчдэд SMS илгээ", "зурагтай бүтээгдэхүүнүүдийг нүүр хуудсанд харуул" гэх мэт.

Олон жишээ

Жишээ 1: <> дээр NULL-ийн чимээгүй хасалт

= NULL-ийн урхи тодорхой харагддаг, учир нь хоосон үр дүн гарч ирэхэд та ямар нэг юм буруу болсныг мэдэрдэг. Үүнээс ч нууцлаг хэлбэр бий. Andrew Hunt-аас бусад зохиогчийн номнуудыг олъё. Query:

sql

SELECT title, author
FROM books
WHERE author <> 'Andrew Hunt';

Үр дүн:

title                 | author
----------------------+-----------------
Тунгалаг Тамир        | Ч.Лодойдамба
Учиртай гурван толгой | Д.Нацагдорж
Clean Code            | Robert C. Martin
Python Crash Course   | Eric Matthes

Энэ query юу гэж хэлж байна вэ гэвэл: “books хүснэгтээс author нь ‘Andrew Hunt’ биш мөрүүдийг харуул.” Дөрвөн ном ирсэн. Гэвч бодоод үзвэл "Монголын нууц товчоо"-г Andrew Hunt бичээгүй нь лавтай — тэгвэл яагаад үр дүнд алга вэ? Учир нь харуул “NULL нь ‘Andrew Hunt’-аас ялгаатай юу” гэж асуухад хариулт нь "мэдэхгүй" гарсан. Зохиогч нь тодорхойгүй ном Andrew Hunt-ынх байж ч болох юм — өгөгдлийн сан дүгнэлт хийх эрхгүй. Үр дүн нь зургаагаас нэгийг хасахад таван мөр биш, дөрвөн мөр болсон бөгөөд дутуу мөрийг хэн ч анзаарахгүй байж магадгүй.

Хэрэв зохиогч нь тодорхойгүй номнууд ч үр дүнд орох ёстой бол үүнийг ил тод хэлэх хэрэгтэй:

sql

SELECT title, author
FROM books
WHERE author <> 'Andrew Hunt' OR author IS NULL;

Үр дүн:

title                 | author
----------------------+-----------------
Монголын нууц товчоо  | NULL
Тунгалаг Тамир        | Ч.Лодойдамба
Учиртай гурван толгой | Д.Нацагдорж
Clean Code            | Robert C. Martin
Python Crash Course   | Eric Matthes

Энэ query юу гэж хэлж байна вэ гэвэл: “author нь ‘Andrew Hunt’ биш, эсвэл author нь хоосон мөрүүдийг харуул.” Одоо таван мөр ирлээ. Эндээс гарах дадал: NULL агуулж болох баганыг <>, <, > зэрэг тэмдгээр шүүхдээ "хоосон мөрүүд энэ үр дүнд орох ёстой юу" гэж үргэлж өөрөөсөө асуу. Хариулт нь "тийм" бол OR ... IS NULL нэм.

Жишээ 2: NULL-тэй тооцоолол

Бүлэг 1-д бид FROM-гүй SELECT-ээр тооцоолол хийж байсан. NULL оролцсон тооцоолол юу буцаахыг харъя. Query:

sql

SELECT NULL + 5, NULL = NULL, 'Ном: ' || NULL;

Үр дүн:

NULL + 5 | NULL = NULL | 'Ном: ' || NULL
---------+-------------+----------------
NULL     | NULL        | NULL

Энэ query юу гэж хэлж байна вэ гэвэл: "хоосон утга дээр 5 нэмсний үр дүн, хоосон утга хоосон утгатай тэнцүү эсэх, болон 'Ном: ‘ гэсэн текстэд хоосон утга залгасан үр дүнг харуул." Гурван багана гурвуулаа NULL болсон. || бол SQLite-д хоёр текстийг залгадаг тэмдэг (Python-ы текстийн +-тэй ижил үүрэгтэй). Эхний баганын логик: мэдэгдэхгүй тоон дээр 5 нэмбэл үр дүн нь ч мэдэгдэхгүй. Хоёр дахь багана энэ хичээлийн гол санааг нэг нүдэнд илэрхийлж байна: NULL нь өөртэйгөө ч тэнцүү биш — хариулт нь "тийм" ч биш, "үгүй" ч биш, харин NULL ("мэдэхгүй"). Гурав дахь багана нь текстэд ч мөн адил дүрэм үйлчилдгийг харуулж байна.

Энэ зан чанарыг “NULL халдварладаг” гэж ихэвчлэн хэлдэг: ямар нэг илэрхийлэлд NULL орвол түүний үр дүн бараг үргэлж NULL болдог. Бодит хүснэгтэд энэ нь чухал үр дагавартай. Жишээ нь SELECT title, copies * 2 FROM books; гэвэл copies нь NULL байх мөрийн хувьд тооцоолсон багана ч NULL болно. Манай хүснэгтэд copies-д NULL байхгүй тул энэ асуудал гарахгүй, харин бодит өгөгдөлд тайлангийн тоонууд яагаад хоосон гарч ирснийг ойлгоход энэ дүрэм түлхүүр болно. Бүлэг 8-д тоо нэгтгэх функцуудыг судлахдаа NULL-тэй дахин уулзана.

Жишээ 3: NOT IN ба NULL — хамгийн нууцлаг урхи

Өмнөх хичээлд NOT IN-ий нэг хачин занг дараа үзнэ гэж хэлсэн. Одоо түүнийг харъя. Энэ урхи хоёр хэлбэртэй. Эхний хэлбэр нь Жишээ 1-тэй ижил шалтгаантай: WHERE author NOT IN ('Andrew Hunt') гэж бичвэл "Монголын нууц товчоо" мөн адил хасагдана, учир нь NULL жагсаалтад байгаа эсэхийг мэдэх аргагүй.

Хоёр дахь хэлбэр нь илүү гайхмаар. Жагсаалт дотор өөрөө NULL байвал юу болох вэ? Query:

sql

SELECT title, genre
FROM books
WHERE genre NOT IN ('Роман', NULL);

Үр дүн:

(хоосон — 0 мөр)

Энэ query юу гэж хэлж байна вэ гэвэл: “books хүснэгтээс genre нь 'Роман'-аас ч, хоосон утгаас ч ялгаатай мөрүүдийг харуул.” Түүх, жүжиг, програмчлалын номнууд 'Роман' биш нь ойлгомжтой, тэгвэл яагаад нэг ч мөр ирээгүй вэ? NOT IN нь "энэ утга жагсаалтын элемент бүрээс ялгаатай юу" гэж асуудаг. "Монголын нууц товчоо"-ны хувьд: 'Түүх' нь 'Роман'-аас ялгаатай — тийм; 'Түүх' нь NULL-ээс ялгаатай — мэдэхгүй. "Тийм бөгөөд мэдэхгүй" гэсэн хослолын эцсийн хариулт нь "мэдэхгүй" тул мөр хасагдсан. Бусад мөр бүрт яг ижил зүйл тохиолдсон. Өөрөөр хэлбэл жагсаалтад ганц NULL орвол NOT IN хэзээ ч, ямар ч мөрийг нэвтрүүлэхгүй.

Та NOT IN-ий жагсаалтад NULL-ийг гараар бичихгүй байх магадлалтай. Гэвч Бүлэг 9-д жагсаалтыг өөр нэг query-ээс автоматаар авах (subquery) аргыг сурна, тэр үед жагсаалтад NULL чимээгүйхэн орж ирж, бүхэл тайлан хоосон гарч болно. Тэр үед энэ жишээг санаарай.

NULL яагаад байдаг вэ

Энэ бүх төвөгтэй байдлыг харсны дараа “NULL-ийн оронд хоосон текст эсвэл 0 бичвэл болохгүй юу” гэсэн асуулт төрөх нь зүйн хэрэг. Хариулт нь: тэдгээр нь өөр өөр утгатай. Бодит амьдралд "мэдэхгүй" гэдэг нь "хоосон" эсвэл "тэг"-ээс ялгаатай мэдээлэл бөгөөд өгөгдлийн сан энэ ялгааг хадгалах ёстой.

Номын хувийн тоо (copies)-гоор жишээ авъя. Хэрэв нэг номын copies нь 0 бол "бид тоолсон, энэ номын нэг ч хувь одоо номын санд байхгүй" гэсэн тодорхой мэдээлэл. Хэрэв copies нь NULL бол "энэ номыг хараахан тоолоогүй" гэсэн огт өөр мэдээлэл. Хэрэв бид NULL-ийн оронд 0 бичдэг байсан бол номын санч "энэ номыг дахин захиалах хэрэгтэй" гэж буруу дүгнэлт хийх байсан. Үүний нэгэн адил имэйлд хоосон текст (‘’) нь "энэ хүн имэйлгүй гэдгийг бид мэднэ" гэсэн утга агуулж болох бол NULL нь "бид асуугаагүй" гэсэн утгатай.

"Монголын нууц товчоо"-ны жишээ энэ санааг хамгийн сайн харуулдаг. Энэ бүтээлийн зохиогч хэн болох нь түүхчдийн дунд өнөөг хүртэл тодорхой хариултгүй асуулт юм. Зохиогчийн нүдэнд хоосон текст бичвэл "зохиогчгүй" гэсэн худал мэдээлэл болно, харин NULL нь "мэдэгдэхгүй" гэсэн үнэнийг яг таг хадгална. NULL-ийн бүх хачин зан — =-ээр олдохгүй, өөртэйгөө тэнцүү биш, тооцоололд халдварладаг — бүгд энэ үнэнч байдлын үр дагавар юм: өгөгдлийн сан мэдэхгүй зүйлийнхээ талаар таамаг дэвшүүлэхээс татгалздаг.

Бүлэг 4-т хүснэгт үүсгэж сурахдаа нэг багана NULL хүлээн авах эсэхийг өөрөө шийддэг болно. Манай books хүснэгтэд title багана NOT NULL гэсэн дүрэмтэй байгааг Бүлэг 1-ийн скриптээс санаж байж магадгүй — номын нэр мэдэгдэхгүй ном бүртгэх утгагүй тул тэр баганад NULL-ийг хориглосон. Харин зохиогч мэдэгдэхгүй ном байж болох тул author баганад зөвшөөрсөн. Ийм шийдвэр бүр нь хүснэгт зохион байгуулалтын нэг хэсэг юм.

Туршаад үзээрэй

Хэрэв сонирхож байвал members хүснэгтээс WHERE email = NULL болон WHERE email IS NULL хоёрыг ээлжлэн ажиллуулж, мессежийн талбар дахь мөрийн тоог харьцуулаарай. Мөн SELECT NULL IS NULL, NULL = NULL; гэж ажиллуулбал IS болон = хоёр нэг асуултад хэрхэн өөр хариулдгийг нэг мөрөнд харж болно (IS NULL үнэн бол SQLite 1, худал бол 0 гэж харуулдаг). Эдгээр нь зөвхөн санал бөгөөд дараагийн хичээлүүд тэдгээрийг хийсэн гэж тооцохгүй.

Түгээмэл алдаанууд

= NULL эсвэл <> NULL бичих. Хамгийн түгээмэл алдаа нь Анхны жишээнд харсан WHERE author = NULL юм. Эсрэг хувилбар болох WHERE author <> NULL ч мөн адил үргэлж хоосон үр дүн өгнө. Энэ үед алдааны мессеж гарахгүй, зөвхөн мессежийн талбарт 0 rows returned гэж бичигдэнэ. Хэрэв query тань NULL-тэй харьцаж байгаад хоосон үр дүн өгвөл =-ийг IS-ээр, <>-ийг IS NOT-оор сольсон эсэхээ шалгаарай.

‘NULL’ гэсэн текстийг NULL гэж андуурах. WHERE author IS 'NULL' эсвэл WHERE author = 'NULL' гэж хашилттайгаар бичвэл SQLite дөрвөн үсэгтэй “NULL” гэсэн текстийг хайна — энэ нь хоосон нүд огт биш. Үүний эсрэг тохиолдол ч бий: хэн нэгэн өгөгдөл оруулахдаа хоосон нүдийн оронд санамсаргүй ‘NULL’ гэсэн текст бичсэн байж болно. DB Browser-ийн Browse Data табд жинхэнэ NULL нь саарал өнгөтэй, налуу үсгээр, харин ‘NULL’ гэсэн текст ердийн хар үсгээр харагддаг. Хэрэв IS NULL-ээр олдох ёстой мөр олдохгүй байвал нүдний өнгийг шалгаарай.

NULL ба хоосон текстийг андуурах. Өгөгдөлд заримдаа NULL-ийн оронд хоосон текст (‘’) хадгалагдсан байдаг. WHERE email IS NULL нь хоосон текстийг олохгүй, WHERE email = '' нь NULL-ийг олохгүй. Хэрэв хоёуланг нь "имэйлгүй" гэж үзэх ёстой бол WHERE email IS NULL OR email = '' гэж хоёуланг нь ил тод бичих хэрэгтэй. Ийм холимог өгөгдлөөс сэргийлэх нь Бүлэг 4-ийн constraint-уудын нэг зорилго юм.

<> болон NOT IN дээр NULL мөрүүдийг алдах. Жишээ 1 болон 3-т харсанчлан NULL агуулж болох баганыг "биш" гэсэн нөхцлөөр шүүх үед хоосон мөрүүд чимээгүй хасагддаг. Энэ нь query буруу ажилласан биш, харин таны асуултад NULL мөрийн тухай заавар байхгүй байсантай холбоотой. Тайлангийн мөрийн тоо хүлээснээс бага гарвал NULL мөрүүдээ эхлээд шалгаарай.

Товч дүгнэлт

Энэ хичээлд та NULL бол утга биш, харин утга байхгүй гэсэн тэмдэглэгээ гэдгийг ойлголоо. SQL "тийм", "үгүй" хоёроос гадна "мэдэхгүй" гэсэн гурав дахь хариулттай бөгөөд NULL-тэй хийсэн харьцуулалт бүр энэ хариултыг өгдөг. Тиймээс = NULL хэзээ ч юу ч олдоггүй, NULL нь өөртэйгөө ч тэнцүү биш, тооцоололд орсон NULL үр дүнг бүхэлд нь NULL болгодог. NULL-ийг олохдоо IS NULL, эсрэгийг нь олохдоо IS NOT NULL ашиглана. <> болон NOT IN нь NULL мөрүүдийг чимээгүй хасдаг, харин NOT IN-ий жагсаалтад NULL орвол бүх мөр хасагдана. NULL нь "мэдэхгүй" гэсэн үнэнийг хадгалах зорилготой бөгөөд 0 эсвэл хоосон текстээс ялгаатай мэдээлэл юм.

Дараагийн хичээлд бид Бүлэг 2-ыг үр дүнг хэлбэржүүлэх гурван хэрэгслээр дуусгана. ORDER BY нь мөрүүдийг нэг эсвэл хэд хэдэн баганаар эрэмбэлэх, LIMIT нь эхний хэдэн мөрийг авах, DISTINCT нь давтагдсан мөрүүдийг нэгтгэх боломж олгоно. Бүлэг 1-д анзаарсан "Улаанбаатар" гурван удаа давтагдах асуудал тэнд шийдэгдэнэ.