הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף אחד לא יודע איך באמת מבטאים את השם שלה 🙂

נתחיל בקווי הדמיון בין הפונקציות האלה:

מה?
לשתי הפונקציות תפקיד דומה – להחזיר את האיבר הראשון שאיננו NULL מתוך רשימה של איברים. אם כל האיברים ברשימה הם NULL, גם התוצאה תהיה NULL.

איפה?
לשתי הפונקציות אפשר לקרוא גם מ-SQL וגם מ-PL/SQL.

לגבי ההבדלים בין הפונקציות – יש שני הבדלים עיקריים ומשמעותיים:

כמה?
ההבדל הראשון הוא בכמות האיברים שהפונקציות מקבלות.

NVL תומכת בשני איברים בדיוק. הביטוי

NVL(a,b)

יחזיר את הערך של a אם הוא שונה מ-NULL, ואת b אם a שווה ל-NULL.
הפונקציה COALESCE תומכת בשני איברים או יותר.
נסתכל על הביטוי

COALESCE(X1, X2, X3,..., Xn)

אם X1 שונה מ-NULL הערך שלו יחזור
אחרת, אם X2 שונה מ-NULL הערך שלו יחזור
אחרת, אם X3 שונה מ-NULL הערך שלו יחזור
וכך הלאה
אז אם יוצא לכם לכתוב לפעמים משהו כמו:

NVL(a,NVL(b,c))

או לחילופין:

CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END

אתם יכולים להחליף את זה ב:

COALESCE(a,b,c)

מתי?
ההבדל השני פחות בולט לעין, אבל מאוד משמעותי במקרים מסויימים.
במשך שנים רבות הנחתי שאם אני כותב

NVL(expression1,expression2)

אז expression2 יחושב אך ורק אם expression1 הוא NULL. זה פשוט נראה הגיוני, לא? אבל לא כך הדבר. ב-NVL שני הביטויים מחושבים תמיד. ב-COALESCE, לעומת זאת, הביטויים מחושבים רק לפי הצורך (וכך גם ב-DECODE וב-CASE, דרך אגב).

נראה דוגמה בה התנהגות זו של NVL בעייתית:
בשאילתה הבאה רוצים להחזיר את הערך של העמודה DESCRIPTION אם הוא קיים. אם העמודה ריקה, רוצים להחזיר את התוצאה של פונקציה כלשהי – get_description – שמקבלת כקלט את העמודות A_ID ו-B_ID.

SELECT NVL(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;

לא חשוב מה בדיוק עושה הפונקציה get_description. מה שבטוח זה שכל קריאה לפונקציה כאשר העמודה DESCRIPTION מכילה ערך מיותרת, אבל זה בדיוק מה שקורה כאן, והפגיעה בביצועים יכולה להיות משמעותית.
אבל אם נשתמש ב-COALESCE במקום ב-NVL, נשיג את אותה תוצאה מבחינה פונקציונלית, והקריאות המיותרות לא יתבצעו. get_description תיקרא רק עבור הרשומות בהן העמודה DESCRIPTION ריקה.

SELECT COALESCE(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;

יתרון אחד שיש ל-NVL על פני COALESCE הוא כמות התווים שצריך להקליד.
אז אם יש לנו בדיוק שני ביטויים, ושניהם כבר מחושבים, אפשר להמשיך להשתמש ב-NVL. בשאר המקרים, כדאי לעבור ל-COALESCE.

בהצלחה,
אורן נקדימון

The following two tabs change content below.

• Bio
• Latest Posts

אורן נקדימון

אורן נקדימון (oren@db-oriented.com) הוא יועץ עצמאי, מומחה למסד הנתונים של אורקל, בעל למעלה מ-20 שנות נסיון עשיר ונרחב עם מערכות מבוססות אורקל – כאיש פיתוח, DBA, מנהל ומרצה. אורן צבר במרוצת השנים ידע רב על אורקל, והוא שמח לחלוק אותו עם אחרים. אורן מאמין בשיתוף פעולה בין אנשי הפיתוח וה-DBAs, ובעבודתו מנסה לגשר על הפערים המסורתיים בין שני העולמות האלה. לפרטים נוספים: www.db-oriented.com.

Latest posts by אורן נקדימון (see all)

• הפונקציה COALESCE - 09/06/2014