דילוג לתוכן
SQL ומסדי נתונים

COALESCE SQL ו־CASE: טיפול חכם בערכים חסרים ובתנאים בלי לשבור שאילתות

הדס קורן

פורסם 9 דקות קריאה 24 דוגמאות קוד

אם חיפשתם coalesce sql, כנראה נתקלתם באחת הבעיות הכי נפוצות בעבודה עם מסדי נתונים: עמודה שמדי פעם מכילה NULL, חישוב שמחזיר תוצאה ריקה, או דוח שבו צריך להציג ערך חלופי במקום “אין נתון”. בדיוק כאן נכנסים לתמונה COALESCE ו־CASE.

שני הכלים האלה קטנים, אבל הם משפיעים מאוד על איכות השאילתות. COALESCE עוזר לבחור את הערך הראשון שאינו NULL. CASE מאפשר לכתוב תנאים בתוך SQL, בדומה ל־if בשפות תכנות. יחד הם מאפשרים לנקות תצוגות, לבנות דוחות קריאים, להימנע מחישובים שבורים ולנסח לוגיקה עסקית פשוטה ישירות בשאילתה.

במאמר הזה נעבוד בצורה מעשית: נבנה טבלאות קטנות, נכניס נתוני דוגמה, נריץ שאילתות, נראה פלט צפוי, ונעבור גם על טעויות אמיתיות שקוראים נתקעים בהן. אם זו הפעם הראשונה שלכם עם SQL, כדאי לשמור בצד גם את מדריך צעדים ראשונים ולחזור אליו כשצריך בסיס רחב יותר.

מה צריך להכין מראש

כדי להריץ את הדוגמאות, מספיק מנוע SQL מקומי. הדוגמאות כאן נכתבו עבור SQLite, כי הוא קטן, נוח, ואפשר להריץ אותו בלי שרת.

אפשר לעבוד כך:

  • בלינוקס או macOS: להשתמש ב־sqlite3 מתוך הטרמינל.
  • ב־Windows: להשתמש ב־sqlite3.exe מתוך PowerShell.
  • בפייתון: להשתמש במודול המובנה sqlite3, בלי התקנת חבילות חיצוניות.

אם אתם כבר עובדים עם PostgreSQL, MySQL או SQL Server, רוב הרעיונות זהים. יש הבדלים קטנים בשמות פונקציות ובטיפוסים, אבל COALESCE ו־CASE עצמם קיימים כמעט בכל מסד נתונים מודרני.

להבין קודם את NULL

לפני COALESCE ו־CASE, צריך להבין מהו NULL. ב־SQL, NULL אינו אפס, אינו מחרוזת ריקה, ואינו false. הוא אומר: “אין ערך ידוע”.

המשמעות חשובה:

  • 0 הוא מספר ידוע.
  • '' היא מחרוזת ריקה ידועה.
  • NULL הוא ערך לא ידוע או חסר.

לכן השוואות רגילות לא תמיד מתנהגות כמו שמצפים. למשל, column = NULL לא בודק אם עמודה ריקה. הדרך הנכונה היא column IS NULL או column IS NOT NULL.

הדוגמה הראשונה: coalesce sql לבחירת ערך חלופי

COALESCE מקבלת רשימת ערכים ומחזירה את הראשון שאינו NULL. אם כולם NULL, היא מחזירה NULL.

ניצור טבלת לקוחות קטנה. לחלק מהלקוחות יש שם תצוגה, לחלק רק שם מלא, ולחלק אין אף אחד מהם.

sql
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    display_name TEXT,
    full_name TEXT,
    email TEXT
);

INSERT INTO customers (id, display_name, full_name, email) VALUES
(1, 'Dana', 'Dana Levi', '[email protected]'),
(2, NULL, 'Yossi Cohen', '[email protected]'),
(3, NULL, NULL, '[email protected]');

SELECT
    id,
    COALESCE(display_name, full_name, email, 'Anonymous') AS name_to_show
FROM customers
ORDER BY id;

השאילתה בוחרת שם להצגה לפי סדר עדיפויות: קודם display_name, אחר כך full_name, אחר כך email, ולבסוף הטקסט 'Anonymous'. הפלט הצפוי:

text
1|Dana
2|Yossi Cohen
3|[email protected]

שימו לב: אצל הלקוח השלישי אין שם תצוגה ואין שם מלא, ולכן COALESCE ממשיכה לערך הבא — כתובת האימייל. הערך 'Anonymous' לא נבחר כי האימייל אינו NULL.

למה לא פשוט להשתמש ב־IF?

ב־SQL סטנדרטי אין תמיד IF בתוך שאילתת SELECT. יש מנועים שמספקים פונקציות מיוחדות, אבל אם רוצים קוד קריא ונייד יותר, בדרך כלל עדיף להשתמש ב־CASE.

CASE מחזיר ערך לפי תנאי. יש לו שתי צורות נפוצות:

צורה מתי להשתמש דוגמה רעיונית
CASE WHEN כשבודקים תנאים שונים אם סכום גדול מ־100, סמן כגבוה
CASE column WHEN כשמשווים ערך אחד לרשימת אפשרויות אם סטטוס הוא paid, הצג שולם

דוגמה מלאה עם CASE: תיוג הזמנות לפי סכום וסטטוס

ניצור טבלת הזמנות ונחזיר עמודה מחושבת שמסבירה את מצב ההזמנה בצורה קריאה.

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount INTEGER,
    status TEXT
);

INSERT INTO orders (id, customer_id, amount, status) VALUES
(1, 1, 50, 'paid'),
(2, 1, 220, 'paid'),
(3, 2, NULL, 'pending'),
(4, 3, 0, 'cancelled');

SELECT
    id,
    amount,
    status,
    CASE
        WHEN status = 'cancelled' THEN 'Cancelled order'
        WHEN amount IS NULL THEN 'Missing amount'
        WHEN amount >= 200 THEN 'Large paid order'
        WHEN amount > 0 THEN 'Regular order'
        ELSE 'No charge'
    END AS order_label
FROM orders
ORDER BY id;

הפלט הצפוי:

text
1|50|paid|Regular order
2|220|paid|Large paid order
3||pending|Missing amount
4|0|cancelled|Cancelled order

הסדר בתוך CASE חשוב מאוד. השאילתה בודקת את התנאים מלמעלה למטה ועוצרת בתנאי הראשון שמתקיים. לכן הזמנה שבוטלה תקבל Cancelled order גם אם הסכום שלה הוא אפס.

שילוב COALESCE ו־CASE באותה שאילתה

בפועל, הרבה שאילתות טובות משלבות את שניהם. COALESCE מטפל בערכים חסרים; CASE מטפל בהחלטות.

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

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    base_price INTEGER,
    sale_price INTEGER,
    stock INTEGER
);

INSERT INTO products (id, name, base_price, sale_price, stock) VALUES
(1, 'Keyboard', 120, NULL, 8),
(2, 'Mouse', 80, 65, 0),
(3, 'Cable', NULL, NULL, 15);

SELECT
    id,
    name,
    COALESCE(sale_price, base_price, 0) AS effective_price,
    CASE
        WHEN stock IS NULL THEN 'Unknown stock'
        WHEN stock = 0 THEN 'Out of stock'
        WHEN stock < 10 THEN 'Low stock'
        ELSE 'In stock'
    END AS stock_label
FROM products
ORDER BY id;

הפלט הצפוי:

text
1|Keyboard|120|Low stock
2|Mouse|65|Out of stock
3|Cable|0|In stock

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

מתי COALESCE מתאים, ומתי CASE מתאים יותר

קל להתבלבל בין שני הכלים, אבל החלוקה די פשוטה.

  • השתמשו ב־COALESCE כשאתם רוצים “הערך הראשון שקיים”.
  • השתמשו ב־CASE כשאתם רוצים החלטה לפי תנאי.
  • שלבו ביניהם כשצריך גם להשלים ערכים חסרים וגם לסווג אותם.

לדוגמה, אם יש עמודות mobile_phone, home_phone, ו־office_phone, הבחירה במספר הראשון הזמין היא שימוש קלאסי ב־COALESCE. לעומת זאת, אם רוצים לסמן לקוח כ־active, inactive או new לפי תאריך רכישה, זה מתאים יותר ל־CASE.

שימוש בפייתון עם SQLite, COALESCE ו־CASE

לפעמים השאילתה לא רצה ידנית אלא מתוך קוד. הדוגמה הבאה משתמשת ב־Python 3 ובספרייה המובנית sqlite3. אם אתם מתחילים עם השפה, אפשר להיעזר גם במדריך פייתון באתר.

python
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("""
CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    assigned_to TEXT,
    priority INTEGER
)
""")

cursor.executemany(
    "INSERT INTO tickets (id, title, assigned_to, priority) VALUES (?, ?, ?, ?)",
    [
        (1, "Login bug", "Dana", 3),
        (2, "Add report", None, 2),
        (3, "Fix typo", None, None),
    ],
)

cursor.execute("""
SELECT
    id,
    title,
    COALESCE(assigned_to, 'Unassigned') AS owner,
    CASE
        WHEN priority IS NULL THEN 'No priority'
        WHEN priority >= 3 THEN 'High'
        ELSE 'Normal'
    END AS priority_label
FROM tickets
ORDER BY id
""")

for row in cursor.fetchall():
    print(row)

connection.close()

הפלט הצפוי:

text
(1, 'Login bug', 'Dana', 'High')
(2, 'Add report', 'Unassigned', 'Normal')
(3, 'Fix typo', 'Unassigned', 'No priority')

הקוד יוצר מסד נתונים בזיכרון, מכניס שלוש קריאות שירות, ואז משתמש ב־COALESCE כדי להציג בעלים חלופי וב־CASE כדי לתרגם עדיפות לתווית. שימו לב לשימוש ב־? בתוך executemany. זו הדרך הבטוחה להעביר ערכים לשאילתה במקום לחבר מחרוזות SQL ידנית.

שלב אחר שלב: איך להכניס את זה לשאילתה קיימת

כשיש לכם שאילתה קיימת עם ערכים חסרים, אל תתחילו בשכתוב גדול. עבדו בשלבים:

  1. מצאו איזו עמודה מחזירה NULL ומפריעה לתוצאה.
  2. החליטו אם מדובר בערך חלופי פשוט או בלוגיקה תנאית.
  3. אם זה ערך חלופי, הוסיפו COALESCE(column, fallback).
  4. אם זו החלטה, הוסיפו CASE WHEN ... THEN ... ELSE ... END.
  5. הריצו את השאילתה על כמה שורות ידועות ובדקו ידנית שהתוצאה הגיונית.
  6. רק אחרי שזה עובד, שלבו את הביטוי בדוח, בתצוגה או בקוד האפליקציה.

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

טעויות נפוצות והודעות שגיאה אמיתיות

שימוש ב־COALESCE בלי מספיק ארגומנטים

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

sql
SELECT COALESCE();

ב־SQLite דרך פייתון תראו בדרך כלל:

text
sqlite3.OperationalError: wrong number of arguments to function COALESCE()

התיקון הוא להעביר לפחות ערך אחד וערך חלופי ברור:

sql
SELECT COALESCE(NULL, 'fallback');

הפלט הצפוי:

text
fallback

שכחת END בתוך CASE

CASE חייב להסתיים ב־END. אם שוכחים אותו, מתקבלת שגיאת תחביר.

sql
SELECT
    CASE
        WHEN 1 = 1 THEN 'yes';

ב־SQLite ההודעה תהיה בסגנון:

text
Parse error: near ";": syntax error

התיקון:

sql
SELECT
    CASE
        WHEN 1 = 1 THEN 'yes'
        ELSE 'no'
    END AS answer;

הפלט הצפוי:

text
yes

כתיבת מחרוזת בלי גרשיים

אם כותבים ערך טקסטואלי בלי גרשיים, SQL מפרש אותו כשם עמודה.

sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    nickname TEXT
);

INSERT INTO users (id, nickname) VALUES (1, NULL);

SELECT COALESCE(nickname, Guest) AS name_to_show
FROM users;

ב־SQLite תראו:

text
Parse error: no such column: Guest

התיקון הוא להוסיף גרשיים סביב המחרוזת:

sql
SELECT COALESCE(nickname, 'Guest') AS name_to_show
FROM users;

הפלט הצפוי:

text
Guest

בדיקה שגויה של NULL עם סימן שוויון

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

sql
SELECT *
FROM users
WHERE nickname = NULL;

התיקון:

sql
SELECT *
FROM users
WHERE nickname IS NULL;

ב־SQL, בדיקת NULL נעשית עם IS NULL ולא עם = NULL.

השפעה על מיון, סינון וקיבוץ

COALESCE ו־CASE לא חייבים להופיע רק ב־SELECT. אפשר להשתמש בהם גם ב־ORDER BY, ולעיתים גם ב־GROUP BY.

לדוגמה, אם רוצים למיין משימות כך שמשימות בלי עדיפות ירדו לסוף, אפשר ליצור ערך חלופי גבוה:

sql
DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    priority INTEGER
);

INSERT INTO tasks (id, title, priority) VALUES
(1, 'Write tests', 2),
(2, 'Clean logs', NULL),
(3, 'Fix crash', 1);

SELECT id, title, priority
FROM tasks
ORDER BY COALESCE(priority, 999), id;

הפלט הצפוי:

text
3|Fix crash|1
1|Write tests|2
2|Clean logs|

כאן NULL מקבל לצורך המיון את הערך 999, ולכן הוא מופיע אחרון. זה לא משנה את הנתון בטבלה, אלא רק את אופן המיון בתוצאה.

ביצועים וקריאות: מה כדאי לזכור

ברוב השאילתות הקטנות, השימוש ב־COALESCE ו־CASE לא יהיה צוואר בקבוק. אבל יש כמה כללים שכדאי לשמור:

  • אל תסתירו בעיות נתונים בלי לחשוב. אם מחיר חסר הוא תקלה, אולי לא נכון להחליף אותו אוטומטית באפס.
  • תנו שמות ברורים לעמודות מחושבות. עדיף effective_price על פני price2.
  • שמרו את התנאים קצרים. אם CASE נהיה ארוך מדי, אולי כדאי להעביר חלק מהלוגיקה לטבלה נוספת.
  • בדקו השפעה על אינדקסים. ביטוי כמו COALESCE(column, 0) בתוך WHERE עשוי למנוע שימוש יעיל באינדקס בחלק מהמנועים.

אם צריך לסנן הרבה שורות לפי עמודה, לפעמים עדיף לכתוב תנאי מפורש כמו column IS NULL OR column = 0 במקום לעטוף את העמודה בפונקציה. זה תלוי במסד הנתונים, בגודל הטבלה ובאינדקסים.

שאלות שקוראים באמת שואלים

האם COALESCE משנה את הנתונים בטבלה?

לא. COALESCE משנה רק את התוצאה המוצגת בשאילתה. אם העמודה מכילה NULL, היא תישאר כך עד שתבצעו UPDATE מפורש.

האם ELSE חובה בתוך CASE?

לא תמיד, אבל כמעט תמיד כדאי לכתוב אותו. אם אין ELSE ואף תנאי לא מתקיים, התוצאה תהיה NULL. לפעמים זה רצוי, אבל במקרים רבים זה יוצר בלבול בדוחות.

האם אפשר לשים CASE בתוך COALESCE?

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

האם COALESCE עדיף על IFNULL?

ב־SQLite קיימת גם IFNULL, אבל COALESCE גמיש יותר כי הוא מקבל יותר משני ערכים, והוא מוכר במגוון רחב יותר של מסדי נתונים. כשאפשר, עדיף להתרגל אליו.

סיכום מעשי

COALESCE ו־CASE הם שני כלים בסיסיים שכדאי להכיר מוקדם בלימוד SQL. הראשון עונה על השאלה “איזה ערך להציג אם הערך חסר?”, והשני עונה על השאלה “מה להחזיר כאשר תנאי מסוים מתקיים?”.

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

עוד בSQL ומסדי נתונים

SQL ומסדי נתונים

CTE SQL: שאילתות WITH שקל לקרוא, לבדוק ולתחזק

אם שאילתת SQL מתחילה להיראות כמו סולם של תתי־שאילתות מקוננות, הגיע הזמן להכיר ברצינות את cte sql. בעזרת WITH אפשר לפרק שאילתה ארוכה לשלבים עם שמות…

9 דק׳ קריאה