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

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

הדס קורן

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

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

CTE, קיצור של Common Table Expression, הוא כלי פשוט יחסית אבל חזק מאוד. הוא לא מחליף טבלאות, לא דורש שינוי בסכמה, ולא מחייב יצירת אובייקטים קבועים במסד הנתונים. הוא פשוט נותן שם זמני לתוצאה של שאילתה, ואז מאפשר להשתמש בשם הזה בהמשך אותה פקודת SELECT, INSERT, UPDATE או DELETE, בהתאם למסד הנתונים שבו עובדים.

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

מה צריך להכין לפני שמתחילים

כדי להריץ את הדוגמאות, מספיק מסד נתונים שתומך ב־WITH. הדוגמאות כאן נכתבו כך שירוצו ב־SQLite, כי הוא קטן, נוח ללמידה ולא דורש שרת נפרד. אפשר להריץ אותן גם בכלים גרפיים שתומכים ב־SQLite, או דרך שורת הפקודה אם מותקן אצלכם sqlite3.

  • מערכת לינוקס או macOS: אפשר להשתמש ב־sqlite3 מהטרמינל.
  • Windows: אפשר להשתמש ב־PowerShell יחד עם התקנה של SQLite, או בכלי גרפי שמריץ SQL.
  • ידע בסיסי: כדאי להכיר SELECT, WHERE, GROUP BY ו־JOIN.

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

מה זה cte sql ולמה משתמשים ב־WITH

CTE הוא תוצאה זמנית עם שם. התחביר הכללי הוא: מתחילים עם WITH, נותנים שם ל־CTE, מגדירים בתוכו שאילתה, ואז משתמשים בשם הזה בשאילתה הראשית.

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

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

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

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

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

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount INTEGER NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (1, 101, 120),
    (2, 101, 150),
    (3, 102, 80),
    (4, 103, 310),
    (5, 104, 40);

WITH customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_orders,
    total_amount
FROM customer_totals
WHERE total_amount >= 200
ORDER BY total_amount DESC;

מה קורה כאן? בתוך customer_totals אנחנו מחשבים לכל לקוח כמה הזמנות יש לו ומה הסכום הכולל. אחר כך, בשאילתה החיצונית, אנחנו מסננים רק לקוחות שהגיעו לסכום של 200 ומעלה.

customer_id total_orders total_amount
103 1 310
101 2 270

אפשר היה לכתוב את זה עם תת־שאילתה רגילה בתוך FROM, אבל השם customer_totals מסביר לקורא מה השלב הזה עושה. כששאילתה גדלה, ההבדל הזה נהיה משמעותי מאוד.

מתי CTE עדיף על תת־שאילתה

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

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

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

דוגמה שנייה: כמה CTEs באותה שאילתה

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

sql
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price INTEGER NOT NULL
);

INSERT INTO products (product_id, product_name, category) VALUES
    (1, 'Keyboard', 'Hardware'),
    (2, 'Mouse', 'Hardware'),
    (3, 'SQL Book', 'Books'),
    (4, 'Linux Book', 'Books');

INSERT INTO sales (sale_id, product_id, quantity, unit_price) VALUES
    (1, 1, 2, 120),
    (2, 2, 3, 70),
    (3, 3, 2, 90),
    (4, 3, 1, 90),
    (5, 4, 1, 110);

WITH sales_with_category AS (
    SELECT
        p.category,
        s.quantity * s.unit_price AS line_total
    FROM sales AS s
    JOIN products AS p
        ON p.product_id = s.product_id
),
category_totals AS (
    SELECT
        category,
        SUM(line_total) AS revenue
    FROM sales_with_category
    GROUP BY category
),
grand_total AS (
    SELECT
        SUM(revenue) AS total_revenue
    FROM category_totals
)
SELECT
    c.category,
    c.revenue,
    ROUND(c.revenue * 100.0 / g.total_revenue, 1) AS revenue_percent
FROM category_totals AS c
CROSS JOIN grand_total AS g
ORDER BY c.revenue DESC;

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

category revenue revenue_percent
Hardware 450 54.2
Books 380 45.8

שימו לב לפסיקים בין הגדרות ה־CTE. כותבים WITH first AS (...), second AS (...), third AS (...), ורק אחרי ההגדרה האחרונה מגיעה השאילתה הראשית.

שימוש ב־COALESCE יחד עם CTE

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

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

דוגמה שלישית: CTE רקורסיבי ליצירת רצף תאריכים

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

sql
WITH RECURSIVE days(day_value, step_number) AS (
    SELECT
        DATE('2026-01-01') AS day_value,
        1 AS step_number

    UNION ALL

    SELECT
        DATE(day_value, '+1 day') AS day_value,
        step_number + 1 AS step_number
    FROM days
    WHERE step_number < 7
)
SELECT
    day_value
FROM days;

החלק הראשון יוצר את היום הראשון. החלק השני מפעיל את עצמו שוב ושוב, בכל פעם מוסיף יום אחד, עד שהתנאי step_number < 7 מפסיק את הריצה.

day_value
2026-01-01
2026-01-02
2026-01-03
2026-01-04
2026-01-05
2026-01-06
2026-01-07

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

דוגמה רביעית: להריץ CTE מתוך פייתון בצורה בטוחה

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

python
import sqlite3

min_total = 200

with sqlite3.connect(":memory:") as conn:
    conn.execute("""
        CREATE TABLE orders (
            order_id INTEGER PRIMARY KEY,
            customer_id INTEGER NOT NULL,
            amount INTEGER NOT NULL
        )
    """)

    conn.executemany(
        "INSERT INTO orders (order_id, customer_id, amount) VALUES (?, ?, ?)",
        [
            (1, 101, 120),
            (2, 101, 150),
            (3, 102, 80),
            (4, 103, 310),
            (5, 104, 40),
        ],
    )

    sql = """
        WITH customer_totals AS (
            SELECT
                customer_id,
                SUM(amount) AS total_amount
            FROM orders
            GROUP BY customer_id
        )
        SELECT
            customer_id,
            total_amount
        FROM customer_totals
        WHERE total_amount >= ?
        ORDER BY total_amount DESC
    """

    for row in conn.execute(sql, (min_total,)):
        print(row)

הפלט הצפוי:

text
(103, 310)
(101, 270)

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

איך לדבג שאילתת WITH שלב אחר שלב

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

  1. הריצו את ה־CTE הראשון לבד: העתיקו את ה־SELECT שבתוכו ובדקו את התוצאה.
  2. הוסיפו את ה־CTE הבא: ודאו שמספר השורות והעמודות הגיוני.
  3. בדקו שמות עמודות: במיוחד אחרי SUM, COUNT או חישובים עם כינויים.
  4. הזיזו את הסינון: לפעמים WHERE צריך להיות לפני הסיכום, ולפעמים HAVING אחרי הסיכום.
  5. הוסיפו ORDER BY רק בסוף: אלא אם יש סיבה מדויקת אחרת, הסדר הסופי צריך להיות בשאילתה החיצונית.

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

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

שימוש ב־CTE מחוץ לשאילתה שבה הוא הוגדר

CTE חי רק למשך פקודה אחת. אם תנסו להריץ SELECT נוסף שמשתמש בשם שלו, ב־SQLite תקבלו בדרך כלל:

output
Error: in prepare, no such table: customer_totals

הפתרון: להשתמש ב־CTE באותה פקודה בלבד. אם צריך להשתמש בתוצאה בכמה פקודות נפרדות, שקלו ליצור טבלה זמנית עם CREATE TEMP TABLE, או להריץ שוב את ה־CTE בכל שאילתה שבה הוא נדרש.

שכחתם פסיק בין שני CTEs

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

output
Error: in prepare, near "category_totals": syntax error

הפתרון: בדקו שהמבנה הוא WITH first AS (...), second AS (...). אין פסיק אחרי ה־CTE האחרון, כי אחריו מגיעה השאילתה הראשית.

ציפייה שסדר פנימי יישמר

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

רקורסיה בלי תנאי עצירה

ב־CTE רקורסיבי, תנאי העצירה הוא לא קישוט. אם שכחתם אותו, השאילתה יכולה לרוץ עד שמנוע המסד יעצור אותה או עד שתיאלצו להפסיק ידנית. במסדי נתונים מסוימים תראו הודעת חריגה על מגבלת רקורסיה; ב־SQL Server, למשל, ההודעה הנפוצה היא:

text
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

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

כללי אצבע לכתיבת CTE קריא

  • תנו שמות שמסבירים כוונה: active_customers ברור יותר מ־cte1.
  • אל תעמיסו יותר מדי על CTE אחד: אם הוא מסנן, מצרף, מסכם ומדרג יחד, אולי כדאי לפצל.
  • שמרו על כינויים עקביים: למשל total_amount, order_count, revenue.
  • בדקו ביצועים כשיש הרבה נתונים: קריאות חשובה, אבל שאילתה איטית מדי דורשת בדיקה של אינדקסים ותוכנית ביצוע.
  • אל תהפכו כל שאילתה פשוטה ל־CTE: לפעמים SELECT קצר הוא הפתרון הכי קריא.

סיכום: שדרוג קטן שמשנה את תחזוקת ה־SQL

CTE הוא לא קסם ולא תחליף להבנה של JOIN, סינון וסיכום. הוא כן דרך נקייה יותר לכתוב שאילתות מורכבות, במיוחד כשיש כמה שלבי עיבוד שצריך להסביר לעצמכם ולקוראים אחרים של הקוד.

התחילו מדוגמה קטנה: קחו שאילתה קיימת עם תת־שאילתה אחת, הפכו אותה ל־WITH, ותנו לשלב שם משמעותי. אם השאילתה נהיית קלה יותר לקריאה, הרווחתם. אם לא, אין חובה להשתמש ב־CTE בכל מקום. כמו הרבה כלים טובים ב־SQL, הערך שלו מגיע כשמשתמשים בו במידה הנכונה ובמקום שבו הוא באמת מפשט את המחשבה.

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