SQL CASE ב-SQLite: WHEN, THEN, ELSE ו-IIF

CASE הוא ה-If/Else של SQL

CASE הוא הדרך להכניס לוגיקה מותנית לתוך שאילתה. הוא עובר על ענפי WHEN לפי הסדר, בוחר את הראשון שמתאים, ומחזיר את הערך שאחרי THEN. אם שום דבר לא מתאים, הוא מחזיר את הערך של ELSE, או NULL אם לא כתבתם כזה.

המילה החשובה היא ביטוי. CASE מפיק ערך, ולכן הוא יכול להופיע בכל מקום שבו מותר ערך: עמודה ב-SELECT, מפתח ב-ORDER BY, הצד השני של השוואה, ארגומנט לפונקציה.

SELECT
    'apple' AS item,
    CASE
        WHEN 5 > 3 THEN 'yes'
        ELSE 'no'
    END AS bigger;

זה כל המבנה: CASE, אחד או יותר WHEN ... THEN ..., ELSE אופציונלי, ואז END. ה-END הוא חובה: לשכוח אותו זו טעות ההקלדה הנפוצה ביותר.

דוגמה מציאותית

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

WITH orders(id, total) AS (
    VALUES
        (1, 12.00),
        (2, 89.50),
        (3, 250.00),
        (4, 5.25),
        (5, 1200.00)
)
SELECT
    id,
    total,
    CASE
        WHEN total < 10 THEN 'tiny'
        WHEN total < 100 THEN 'small'
        WHEN total < 500 THEN 'medium'
        ELSE 'large'
    END AS size_bucket
FROM orders;

הענפים נבדקים מלמעלה למטה. ההתאמה הראשונה מנצחת, אז סדרו אותם מהספציפי ביותר לכללי ביותר. ה-ELSE תופס את כל מה שלא התאים: בלעדיו, 1200.00 היה חוזר כ-NULL במקום 'large'.

מחפש מול פשוט

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

WITH users(id, status) AS (
    VALUES
        (1, 'A'),
        (2, 'B'),
        (3, 'C'),
        (4, 'A')
)
SELECT
    id,
    status,
    CASE status
        WHEN 'A' THEN 'active'
        WHEN 'B' THEN 'banned'
        WHEN 'C' THEN 'closed'
        ELSE 'unknown'
    END AS status_label
FROM users;

הביטוי שאחרי CASE מחושב פעם אחת ומושווה עם = לכל ערך של WHEN. נקי יותר כשעושים בדיקות שוויון על עמודה אחת.

מלכודת אחת: CASE פשוט משתמש ב-=, ו-NULL = NULL הוא לא אמת ב-SQL. אם status יכול להיות NULL, הענפים 'A'/'B'/'C' לא יתאימו לו, ותגיעו ל-ELSE. כדי לטפל ב-NULL במפורש, עברו לצורה המחפשת והשתמשו ב-WHEN status IS NULL THEN ....

CASE ב-ORDER BY

ORDER BY מקבל כל ביטוי, ולכן CASE עובד שם מצוין. שימושי כשאתם רוצים סדר מיון מותאם שלא תואם סדר אלפביתי או מספרי:

WITH tickets(id, priority) AS (
    VALUES
        (1, 'low'),
        (2, 'high'),
        (3, 'medium'),
        (4, 'high'),
        (5, 'low')
)
SELECT id, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END,
    id;

לפי סדר אלפביתי 'high' < 'low' < 'medium', וזה חסר תועלת למיון פניות לפי דחיפות. מיפוי של כל עדיפות למספר דרך CASE נותן לכם את הסדר שאתם באמת רוצים. ה-, id שבסוף מכריע בשוויון באופן יציב.

CASE ב-WHERE

אפשר לשים CASE בתוך WHERE, אבל ברוב המקרים אין בזה צורך: שרשור של AND/OR ברור יותר. הוא זוהר כש_התנאי עצמו_ תלוי בערך אחר:

WITH products(id, name, price, on_sale) AS (
    VALUES
        (1, 'mug', 12.00, 1),
        (2, 'shirt', 25.00, 0),
        (3, 'cap', 18.00, 1),
        (4, 'bag', 45.00, 0)
)
SELECT id, name, price
FROM products
WHERE price < CASE WHEN on_sale = 1 THEN 20 ELSE 30 END;

פריטים במבצע עוברים מתחת ל-20, פריטים רגילים מתחת ל-30. הסף עצמו מותנה. בלי CASE הייתם צריכים לכתוב (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30): אותה תוצאה, יותר רעש.

CASE בתוך פונקציות צבירה

כאן CASE מצדיק את קיומו. שלבו אותו עם SUM או COUNT כדי לחשב סכומים על תת קבוצה של שורות במעבר אחד: המקבילה ב-SQL ל"ספרו כמה מאלה מתאימים":

WITH orders(id, total, status) AS (
    VALUES
        (1, 50.00, 'paid'),
        (2, 30.00, 'pending'),
        (3, 75.00, 'paid'),
        (4, 20.00, 'cancelled'),
        (5, 100.00, 'paid')
)
SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;

ה-CASE מחזיר 1 לשורות שמתאימות ו-0 לכל השאר, כך ש-SUM הופך לספירה מותנית. אותו טריק עובד להכנסות: מחזירים total בשורות המתאימות ו-0 בכל מקום אחר. סריקה אחת של הטבלה, כמה צבירות מותנות.

IIF: קיצור הדרך לשני ענפים

לתנאי אחד עם שתי תוצאות, ל-SQLite יש IIF(cond, when_true, when_false). זה פשוט קיצור של CASE WHEN cond THEN when_true ELSE when_false END:

WITH accounts(id, balance) AS (
    VALUES
        (1, 100.00),
        (2, -25.00),
        (3, 0.00),
        (4, 1500.00)
)
SELECT
    id,
    balance,
    IIF(balance < 0, 'overdrawn', 'ok') AS state
FROM accounts;

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

מלכודות שכדאי להכיר

כמה דברים שתופסים אנשים:

• לשכוח את END. CASE פותח בלוק; END סוגר אותו. SQLite תיתן לכם שגיאת פענוח הרבה אחרי המקום של הטעות עצמה.
• בלי ELSE מקבלים NULL. אם אף ענף WHEN לא מתאים ודילגתם על ELSE, התוצאה היא NULL. לפעמים זה מה שאתם רוצים; בדרך כלל לא.
• סדר הענפים חשוב. בצורה המחפשת, ה-WHEN ה_ראשון_ שמתאים מנצח. אם תשימו WHEN total < 500 לפני WHEN total < 100, לענף השני לעולם לא תגיעו.
• ערבוב טיפוסים. כל ענף יכול להחזיר טיפוס אחר, ו-SQLite לא תתלונן, אבל קוד שמשתמש בתוצאה עלול כן. נסו לשמור שכל הענפים יחזירו טיפוסים תואמים (כולם טקסט, כולם מספרים).
• CASE פשוט ו-NULL. כמו שהוזכר: הצורה הפשוטה משתמשת ב-=, שאף פעם לא מתאים ל-NULL. השתמשו בצורה המחפשת כשיש NULL בתמונה.

הבא בתור: פונקציות מחרוזת

CASE מאפשר להתפצל לפי ערכים; הפרק הבא מתחיל ב_שינוי_ ערכים. פונקציות מחרוזת, UPPER, LOWER, SUBSTR, REPLACE ותבניות LIKE, מטפלות בעבודה היומיומית של ניקוי ועיצוב מחדש של עמודות טקסט. זה מה שמחכה בהמשך.