טיפול ב-NULL: IS NULL ו-COALESCE

במערך הנתונים employees, אנדריאה קוסטה מיוחדת: יש לו department_id = NULL. כבר התחננת נגד NULL (עם LEFT JOIN), אבל הגיע הזמן לקחת את זה ברצינות: NULLs ב-SQL להתנהג שונה מכל ערך אחר, והתעלמות מהם היא אחד מהערכים הסיבות העיקריות לבאגים בקוד הייצור.

NULL אינו אפס, לא מחרוזת ריקה

NULL פירושו ערך לא ידוע/חסר. זה לא 0, לא '', לא false. זה "אני לא יודע". מכאן נובע מאפיין מפתיע: NULL אינו שווה לכלום, אפילו לא לעצמו.

-- All these expressions return NULL (not true!):
SELECT NULL = NULL;       -- NULL
SELECT NULL = 0;          -- NULL
SELECT NULL <> 'qualcosa'; -- NULL

תוצאה קונקרטית: WHERE column = NULL אף פעם לא עובד. אתה חייב להשתמש האופרטורים הייעודיים IS NULL / IS NOT NULL:

-- Employees without a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NULL;

-- Employees WITH a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NOT NULL;

COALESCE - החזרה NULL

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

-- Show the department_id, or 0 if missing:
SELECT first_name, last_name, COALESCE(department_id, 0) AS dept
FROM   employees;

בשילוב עם LEFT JOIN הוא הופך חזק להפליא עבור דוחות:

SELECT e.first_name,
       e.last_name,
       COALESCE(d.name, '(nessun dipartimento)') AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

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

NULL ומצטברים

פונקציות מצטברות התעלם NULLs (למעט COUNT(*)):

• COUNT(department_id) סופר רק את השורות עם ערך;
• ממוצעים של AVG(salary) תוך התעלמות מ-NULLs - זה לא מתייחס אליהם כ-0;
• SUM(...) מסכם רק את הערכים שאינם NULL.

זה כמעט תמיד מה שאתה רוצה. אבל טוב לדעת: אם כן מחפש את "המספר הכולל של העובדים" השתמש ב-COUNT(*), לא COUNT(department_id).

נסה את זה

סקירת התרגיל