Kepriye NULL tumindak ing SQL?

Question

Accepted Answer

**`NULL`** ngewakili **nilai sing ilang utawa ora ketok** — ora nol, ora string kosong, nanging *ora ana* nilai. NULL nduweni tumindak khusus, kadhangkala nggumunake, ing perbandingan, agregat, lan logika — nggawe NULL minangka sumber umum saka bug. ## NULL yaiku "ora ketok" — perbandingan karo NULL ```sql -- ❌ you CANNOT compare to NULL with = or <> (the result is "unknown," never true) WHERE email = NULL -- returns NOTHING (never true!) WHERE email <> NULL -- also returns nothing -- ✅ use IS NULL / IS NOT NULL WHERE email IS NULL WHERE email IS NOT NULL ``` Amarga NULL ateges "ora ketok," perbandingan *karo* NULL ngasilake "ora ketok" (ora true), mula `= NULL` ora tau cocok. Sampeyan **kudu** nggunakake `IS NULL`/`IS NOT NULL` — miskomprehensyon paling umum babagan NULL. ## NULL ing logika telung-nilai ```text SQL uses THREE-valued logic: TRUE, FALSE, and UNKNOWN (NULL). NULL = NULL → UNKNOWN (not true!) NULL AND true → UNKNOWN NULL OR true → TRUE NOT NULL → UNKNOWN → Conditions evaluating to UNKNOWN are treated as not-matching by WHERE. ``` ## NULL ing agregat lan aritmetika ```sql -- aggregates IGNORE NULLs (except COUNT(*)) SELECT AVG(salary) FROM employees; -- averages only non-NULL salaries SELECT COUNT(salary) FROM employees; -- counts only non-NULL (vs COUNT(*) = all rows) -- arithmetic with NULL → NULL SELECT price + NULL; -- NULL (any operation with NULL is NULL) ``` Agregat nglirwakke NULL, lan operasi aritmetika sing nunut NULL ngasilake NULL. ## Nangani NULL: COALESCE lan NULLIF ```sql -- COALESCE — return the first non-NULL value (provide a default) SELECT COALESCE(phone, 'No phone') FROM users; -- 'No phone' if phone is NULL SELECT COALESCE(nickname, name, 'Unknown') FROM users; -- first non-NULL -- NULLIF — return NULL if two values are equal SELECT NULLIF(value, 0); -- NULL if value is 0 (e.g. avoid divide-by-zero) ``` `COALESCE` nyediakake fallback kanggo NULL (umum banget kanggo standar), lan `NULLIF` ngowahi nilai dadi NULL kanthi kondisional. ## Kepriye wigati Nampa tumindak NULL penting kanggo nulis SQL sing bener, amarga semantik khusus NULL yaiku **sumber asring saka bug halus** — lan NULL babar blas ana ing data nyata (kolom opsional, nilai ilang, join sing ora pas). Poin paling kritis lan asring kesalahan yaiku **NULL teges "ora ketok," mula sampeyan ora bisa mbandingan karo `=`** (bug terkenal endi `WHERE col = NULL` diam-diam ora nggawe apa-apa); sampeyan kudu nggunakake `IS NULL`/`IS NOT NULL`. Nampa **logika telung-nilai SQL** (TRUE/FALSE/UNKNOWN, endi perbandingan karo NULL ngasilake UNKNOWN lan dianggep ora cocok) njlasake rasa ruwet lan penting kanggo nulis syarat kanthi bener. Nampa yen **agregat nglirwakke NULL** (nduwek pengaruh marang AVG, COUNT, lsp. — kadhangkala ngejutake), yen **aritmetika karo NULL ngasilake NULL**, lan carane **nangani NULL** karo `COALESCE` (nyediakake standar/fallback — kebutuhan asring) lan `NULLIF` yaiku kapraktisan, pengetahuan sing asring dipraktikake. Amarga NULL ana ing saindo-indo ing basis data nyata (kolom apa-apa sing bisa null, JOIN KIWA apa-apa), lan amarga tumindak NULL sing ora intuitif NULL (utamane masalah perbandingan lan logika telung-nilai) nyebabake bug nyata, hard-to-debug, nangerteni carane NULL tumindak — perbandingan, logika, agregat, lan nangani — yaiku pengetahuan kudu-ngerti, pusat kanggo nulis SQL sing bener lan topik interview asring sing njlasake pangertian aspek basis data sing asring disalahake.