SQLite ו-NULL: IS NULL, COALESCE ו-IFNULL

NULL פירושו "לא ידוע"

כל ערך אחר ב-SQLite מייצג משהו מוגדר: מספר, מחרוזת, blob. NULL שונה. הוא מחזיק מקום לערך שחסר או לא ידוע. הרעיון הזה לבדו מסביר כל התנהגות מוזרה של NULL בשאילתות.

צרו טבלה קטנה להתנסות:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

שתי עמודות מאפשרות NULL. לבוריס אין אימייל. לקליאו אין גיל. לדן אין אף אחד מהם. שאר העמוד עוסק בשליפת שורות כאלה בלי ליפול בפח.

= ו-<> לא עובדים עם NULL

האינסטינקט הראשון הוא לכתוב WHERE email = NULL. זה נראה הגיוני. זה לא מחזיר כלום:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

אפס שורות, למרות שלבוריס ולדן ברור שאין אימייל. הסיבה: השוואה של כל דבר ל-NULL מחזירה NULL, לא אמת ולא שקר. פסוקית ה-WHERE של SQLite משאירה רק שורות שבהן התנאי אמת, ו-NULL הוא לא אמת. לכן השורה מסוננת החוצה.

אותה מלכודת עם <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

הייתם מצפים שזה יחזיר את כולם חוץ מעדה. זה מחזיר רק את קליאו. בוריס ודן, שהאימייל שלהם NULL, נופלים, כי גם NULL <> 'ada@example.com' הוא NULL, לא אמת.

זו המלכודת הנפוצה ביותר ב-SQL. בכל פעם ששאילתה "מאבדת שורות" שלא ציפיתם לאבד, חשדו בעמודה עם NULL.

השתמשו ב-IS NULL וב-IS NOT NULL

הדרך הנכונה לבדוק NULL היא האופרטור IS. בניגוד ל-=, הוא מכיר את NULL ומחזיר אמת או שקר, אף פעם לא NULL:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

השאילתה הראשונה מחזירה את בוריס ואת דן. השנייה מחזירה את עדה ואת קליאו. IS NULL ו-IS NOT NULL הם שני האופרטורים שנבנו במיוחד כדי לשאול "האם הערך הזה חסר?". השתמשו בהם בכל מקום שבו הייתם מתפתים לכתוב = NULL או <> NULL.

אם אתם רוצים "כולם חוץ מעדה, כולל הלא ידועים", שלבו את הבדיקות במפורש:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

עכשיו בוריס, קליאו ודן מופיעים כולם.

NULL מתפשט בחישובים ובשרשור

הכלל של "לא ידוע" חל גם מעבר להשוואות. כל פעולה שנוגעת ב-NULL מחזירה NULL:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Hi ' || name AS greeting,
    'Age: ' || age AS labelled_age
FROM users;

next_year ו-doubled הם NULL אצל קליאו ודן. גם labelled_age הוא NULL אצלם: שרשור מחרוזת עם NULL נותן NULL, לא 'Age: '. אם עמודה עלולה להיות NULL ואתם צריכים ערך שימושי בסוף, אתם חייבים לטפל בזה. כאן נכנסות שתי הפונקציות הבאות.

IFNULL: ערך חלופי עם שני ארגומנטים

IFNULL(a, b) מחזירה את a, אלא אם הוא NULL, ואז היא מחזירה את b. זו הדרך הפשוטה ביותר להחליף NULL בערך ברירת מחדל:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(no email)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

בוריס ודן מקבלים (no email). קליאו ודן מקבלים 0. הנתונים המקוריים לא משתנים: IFNULL רק משכתבת את הפלט.

IFNULL תמיד מקבלת בדיוק שני ארגומנטים. אם אתם צריכים יותר חלופות, השתמשו ב-COALESCE.

COALESCE: הערך הראשון שאינו NULL מנצח

COALESCE(a, b, c, ...) עוברת על הארגומנטים שלה לפי הסדר ומחזירה את הראשון שאינו NULL. היא הכללה של IFNULL לכל מספר של חלופות:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'anonymous') AS contact
FROM users;

אצל עדה וקליאו משתמשים באימייל. אצל בוריס ודן האימייל הוא NULL, אז SQLite מנסה את הארגומנט השני: כתובת שנבנית מהשם. אם גם הוא היה NULL, היא הייתה ממשיכה ל-'anonymous'.

COALESCE היא הבחירה הניידת: כל מסד נתונים גדול של SQL תומך בה באותה צורה. IFNULL היא נוחות של SQLite ו-MySQL למקרה של שני ארגומנטים. בחרו ב-COALESCE כברירת מחדל, והשתמשו ב-IFNULL רק כשבאמת יש לכם שני ארגומנטים בלבד ואתם מעדיפים את השם הקצר.

NULL הוא לא מחרוזת ריקה

בלבול נפוץ: מתייחסים ל-NULL ול-'' כאילו הם ניתנים להחלפה. הם לא.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' היא מחרוזת אמיתית שפשוט יש בה אפס תווים. NULL הוא היעדר ערך. length('') הוא 0, ו-length(NULL) הוא בעצמו NULL. ו-NULL = NULL הוא NULL, לא 1, וזו בדיוק הסיבה ש-IS NULL קיים.

אם עמודה יכולה להכיל גם '' וגם NULL, החליטו איזה מהם פירושו "חסר" והיצמדו אליו. ערבוב ביניהם מכריח כל שאילתה לטפל בשני מקרים, ואחד מהם בטוח יישכח.

NULL ב-IN, ב-NOT IN וב-DISTINCT

עוד כמה מקומות שבהם NULL מפתיע.

IN עם רשימה שמכילה NULL יכול לתת תוצאות מפתיעות, במיוחד עם NOT IN:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

אולי תצפו לקבל את כל מי שהגיל שלו אינו 25. אתם מקבלים כלום. SQLite פורשת את NOT IN (25, NULL) בערך ל-age <> 25 AND age <> NULL, ו-age <> NULL הוא תמיד NULL, כך שהתנאי כולו אף פעם לא אמת. הפתרון הוא לסנן את ה-NULL מהרשימה (או מהעמודה) לפני ההשוואה.

DISTINCT, לעומת זאת, מתייחס לערכי NULL כשווים זה לזה לצורך הסרת כפילויות:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

מקבלים שלוש שורות: האימייל של עדה, האימייל של קליאו, ו-NULL יחיד (שאוחד מבוריס ומדן). אותו דבר נכון ל-GROUP BY ול-UNION: הם מתייחסים לכל ה-NULL כקבוצה אחת, ההפך ממה ש-= עושה. SQL לא תמיד עקבית בנושא הזה, ולכן כדאי לדעת לאיזה צד נופל כל אופרטור.

רשימת בדיקה מהירה

• בדקו ערכים חסרים עם IS NULL / IS NOT NULL. אף פעם לא עם = NULL.
• כל חישוב, שרשור או השוואה שנוגעים ב-NULL מחזירים NULL.
• השתמשו ב-COALESCE(a, b, c, ...) כדי להחליף NULL בערך חלופי. השתמשו ב-IFNULL(a, b) כקיצור לשני ארגומנטים.
• מחרוזת ריקה '' אינה זהה ל-NULL. בחרו אחד מהם שמשמעותו "חסר" בכל עמודה.
• NOT IN (..., NULL) הוא כמעט תמיד באג. הסירו קודם את ה-NULL מהרשימה.

הבא: מיון תוצאות

אחרי שאתם יודעים לסנן שורות נכון, כולל אלה עם NULL, השלב הבא הוא לסדר אותן בסדר שימושי. ORDER BY הוא העמוד הבא, ויש לו דעות משלו על המקום שבו ערכי NULL נוחתים בתוצאה ממוינת.