English 中文 العربية 日本語 Português

PostgreSQL: الاستعلامات الفرعية و CTEs في PostgreSQL

آخر تحديث: 2026-08-26

1. ما ستتعلمه

2. القصة

Alice هي مطورة نظام موارد بشرية في شركة SaaS. الشركة لديها أكثر من 500 شخص، منظمين في شجرة: الرئيس التنفيذي في القمة، تحته نواب الرئيس، تحتهم المدراء، تحتهم المديرون، تحتهم الموظفون.

مدير المنتج يسأل: بدءاً من أي عقدة، اذكر تلك العقدة وجميع أحفادها (عبر مستويات متعددة)، معروضة بإزاحة لتوضيح التسلسل الهرمي.

الاستعلام العادي يمكنه فقط▶�لنزول مستوى واحداً. تتعلم Alice WITH RECURSIVE CTEs التكرارية وتحل الشجرة الفرعية بالكامل في عبارة SQL واحدة.

3. المفهوم

(1) تصنيف الاستعلامات الفرعية

النوع يعيد يمكن أن يظهر في مثال
استعلام فرعي عددي صف واحد، قيمة واحدة SELECT, WHERE, HAVING (SELECT MAX(salary) ...)
استعلام فرعي عمودي عمود واحد، صفوف متعددة WHERE + IN/ANY/ALL WHERE id IN (SELECT ...)
استعلام فرعي صفي صف واحد، أعمدة متعددة WHERE WHERE (a,b) = (SELECT x,y ...)
استعلام فرعي جدولي صفوف متعددة، أعمدة متعددة FROM FROM (SELECT ...) AS t

(1) ▶ مثال

SQL 
SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
WHERE department_id = 5;
TEXT 📖 للعرض فقط 
 name    | salary | company_avg | diff
---------+--------+-------------+-------
 Alice   |  95000 |    72000.00 | 23000
 Bob     |  88000 |    72000.00 | 16000

(2) ▶ مثال

SQL 
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)

(3) ▶ مثال

SQL 
SELECT order_id, amount
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE region = 'NA'
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(4) ▶ مثال

SQL 
SELECT name, department_id, salary
FROM employees
WHERE (department_id, salary) = (
  SELECT department_id, MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING department_id = employees.department_id
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTS / NOT EXISTS

EXISTS تتحقق مما إذا كان الاستعلام الفرعي يعيد أي صفوف؛ لا تهتم بالقيم الفعلية، فقط بـ "الوجود".

(5) ▶ مثال

SQL 
SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)
الطريقة الصيغة شرط التوقف صديقة لـ NULL
IN WHERE id IN (SELECT ...) تمسح الكل انتبه لـ NULLs
EXISTS WHERE EXISTS (SELECT 1 ...) تتوقف عند أول صف NULL ليس لها تأثير
JOIN JOIN ... جميع التطابقات يعتمد على نوع JOIN

(6) ▶ مثال

SQL 
SELECT u.user_id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT EXISTS أكثر أماناً من NOT IN: عندما تحتوي نتيجة الاستعلام الفرعي NULL، NOT IN تعيد نتيجة فارغة للاستعلام بالكامل.

(7) ▶ مثال

SQL 
SELECT name FROM customers
WHERE region NOT IN ('NA', 'EU', NULL);
TEXT 📖 للعرض فقط 
(0 rows)

لأن x NOT IN (a, b, NULL) مكافئة لـ x <> a AND x <> b AND x <> NULL، و x <> NULL هي UNKNOWN، التعبير بالكامل FALSE.

(3) ANY / ALL

(8) ▶ مثال

SQL 
SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 3
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

مكافئة لـ > MIN(نتيجة الاستعلام الفرعي).

(9) ▶ مثال

SQL 
SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 3
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

مكافئة لـ > MAX(نتيجة الاستعلام الفرعي).

المعامل المعنى المكاف��
> ANY (...) أكبر من أي واحد > MIN(...)
> ALL (...) أكبر من الكل > MAX(...)
= ANY (...) يساوي أي واحد IN (...)

(10) ▶ مثال

SQL 
SELECT
  department_id,
  avg_salary,
  count
FROM (
  SELECT
    department_id,
    AVG(salary) AS avg_salary,
    COUNT(*)    AS count
  FROM employees
  GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;

Output:

TEXT 📖 للعرض فقط 
 count 
-------
     5
(1 row)

4. نقاط رئيسية

(1) CTE (عبارة WITH)

CTE (تعبير جدول مشترك) تستخدم WITH لتعريف مجموعة نتائج مؤقتة مسماة يمكن الإشارة إليها عدة مرات.

(11) ▶ مثال

SQL 
WITH regional_sales AS (
  SELECT
    region,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region
  FROM regional_sales
  WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales)
)
SELECT
  o.order_id,
  o.amount,
  o.region
FROM orders o
WHERE o.region IN (SELECT region FROM top_regions)
ORDER BY o.amount DESC;

Output:

TEXT 📖 للعرض فقط 
  result  
----------
   42.50
(1 row)
الطريقة قابلية القراءة قابلة لإعادة الاستخدام المحسن يضمن مجسدة
استعلام فرعي متداخل ضعيفة لا نعم —
CTE جيدة نعم PG 12+ تقرر تلقائياً يمكن فرض MATERIALIZED
جدول مؤقت مقبولة نعم لا تكتب على القرص

(12) ▶ مثال

SQL 
WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc
UNION ALL
SELECT * FROM expensive_calc;

Output:

TEXT 📖 للعرض فقط 
 count 
-------
     5
(1 row)

MATERIALIZED تفرض الحساب مرة واحدة وتخزنه مؤقتاً، مثالية لـ CTEs المشار إليها عدة مرات بحساب ثقيل.

(13) ▶ مثال

SQL 
WITH simple_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE region = 'NA'
)
SELECT * FROM simple_filter WHERE amount > 50000;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT MATERIALIZED تسمح للمحسن بتضمين التوسيع، مناسبة لسيناريوهات دفع المسندات البسيطة.

(2) CTE تكراري (WITH RECURSIVE)

CTE التكراري هو الطريقة القياسية في SQL لبيانات الشجرة والرسوم البيانية.

هيكل الصيغة:

SQL 
WITH RECURSIVE cte_name AS (
  base_query        -- ا��مرساة: بذرة غير تكرارية
  UNION ALL
  recursive_query   -- تشير إلى cte_name نفسها
)
SELECT * FROM cte_name;

(14) ▶ مثال

SQL 
WITH RECURSIVE subordinates AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS level,
    name::text AS path
  FROM employees
  WHERE manager_id IS NULL
    AND name = 'David'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    s.level + 1,
    s.path || ' > ' || e.name
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT
  level,
  REPEAT('  ', level - 1) || name AS org_chart,
  path
FROM subordinates
ORDER BY path;
TEXT 📖 للعرض فقط 
 level |       org_chart        |              path
-------+------------------------+--------------------------------
     1 | David                  | David
     2 |   Alice                | David > Alice
     3 |     Bob                | David > Alice > Bob
     3 |     Charlie            | David > Alice > Charlie
     2 |   Eve                  | David > Eve
     3 |     Frank              | David > Eve > Frank

(15) ▶ مثال

SQL 
WITH RECURSIVE subordinates AS (
  SELECT
    employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
  WHERE s.level < 5
)
SELECT * FROM subordinates;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

WHERE s.level < 5 يحدد التكرار بـ 5 مستويات كحد أقصى.

(16) ▶ مثال

SQL 
WITH RECURSIVE date_series AS (
  SELECT '2025-01-01'::date AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt < '2025-12-31'
)
SELECT dt FROM date_series;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. تطبيق عملي

(17) ▶ مثال

SQL 
SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(18) ▶ مثال

SQL 
WITH customer_orders AS (
  SELECT
    customer_id,
    MAX(created_at)                           AS last_order_date,
    COUNT(*)                                  AS frequency,
    SUM(amount)                               AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  frequency,
  monetary,
  NTILE(4) OVER (ORDER BY monetary DESC) AS m_quartile
FROM customer_orders;

Output:

TEXT 📖 للعرض فقط 
 count 
-------
     5
(1 row)

(19) ▶ مثال

SQL 
WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT
  depth,
  REPEAT('──', depth) || name AS tree_view
FROM category_tree
ORDER BY depth, name;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(20) ▶ مثال

SQL 
SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM regions r
  WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.region = r.region_code
  )
);

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(21) ▶ مثال

SQL 
WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn <= 3;

Output:

TEXT 📖 للعرض فقط 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. مثال شامل

استعلام Alice للمخطط التنظيمي — من أي مدير، اذكر جميع المرؤوسين مع إزاحة المستوى والمسار وعدد الفريق:

SQL 
WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1          AS level,
    name::text AS path,
    ARRAY[employee_id] AS subtree_ids
  FROM employees
  WHERE name = 'Alice'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name,
    o.subtree_ids || e.employee_id
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.employee_id
)
SELECT
  o.level,
  REPEAT('    ', o.level - 1) || o.name AS org_chart,
  o.path,
  ARRAY_LENGTH(o.subtree_ids, 1)       AS team_size
FROM org_tree o
ORDER BY o.path;
TEXT 📖 للعرض فقط 
 level |         org_chart          |              path               | team_size
-------+----------------------------+---------------------------------+-----------
     1 | Alice                      | Alice                           |         1
     2 |     Bob                    | Alice > Bob                     |         2
     3 |         Diana              | Alice > Bob > Diana             |         3
     3 |         Eve                | Alice > Bob > Eve               |         4
     2 |     Charlie                | Alice > Charlie                 |         5
     3 |         Frank              | Alice > Charlie > Frank         |         6

7. تدفق تنفيذ CTE التكراري

100% 
flowchart TD
    A["استعلام المرساة<br/>(بذرة غير تكرارية)"] --> B["جدول العمل T₀"]
    B --> C["استعلام تكراري<br/>(JOIN مع T₀)"]
    C --> D{"صفوف جديدة<br/>منتجة؟"}
    D -->|نعم| E["جدول العمل T₁"]
    E --> F["إلحاق بالنتيجة"]
    F --> C
    D -->|لا| G["النتيجة النهائية<br/>(جميع التكرارات UNION ALL)"]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style G fill:#c8e6c9
الخطوة العملية الوصف
1 تنفيذ استعلام المرساة بذرة غير تكرارية، تولد الصفوف الأولية
2 وضع في جدول العمل T₀ = نتيجة المرساة
3 استعلام تكراري JOIN جدول العمل مع الجدول الأصلي
4 التحقق من الصفوف الجديدة استمر إذا وجدت صفوف جديدة؛ توقف إذا لم توجد
5 دمج النتائج UNION ALL جميع نتائج التكرارات

❓ أسئلة شائعة

س هل هناك فرق في الأداء بين CTE والاستعلام الفرعي؟
ج PostgreSQL 12+ تقرر تلقائياً ما إذا كانت ستضمن CTE. CTEs البسيطة تضمن عادة؛ المعقدة قد تجسد. استخدم MATERIALIZED / NOT MATERIALIZED للتحكم يدوياً.
س هل يمكن لـ CTE تكراري أن يدور للأبد؟
ج محتمل. إذا كانت البيانات تحتوي دورة (مثلاً A→B→A)، لن يتوقف التكرار. احمِ ضده بإضافة حد مستوى، أو تتبع المسار لتجنب إعادة زيارة العقد.
س لماذا تعيد NOT IN فارغاً عندما تصطدم بـ NULL؟
ج x NOT IN (a, NULL) مكافئة لـ x<>a AND x<>NULL، و x<>NULL هي UNKNOWN؛ في سلسلة AND، UNKNOWN تجعل الصف بالكامل FALSE. NOT EXISTS أكثر أماناً بدلاً من ذلك.
س أيهما أسرع، EXISTS أم IN؟
ج يعتمد على البيانات والفهارس. عادة IN أسرع عندما تكون مجموعة نتائج الاستعلام الفرعي صغيرة، و EXISTS أسرع عندما يكون الجدول الخارجي صغيراً. محسن PostgreSQL يعيد الكتابة تلقائياً، لذا الأداء متقارب في معظم الحالات.
س هل يمكن لـ CTE تكراري معالجة هياكل الرسوم البيانية؟
ج نعم، لكنه يحتاج منطق إضافي لمنع الدورات. تتبع العقد المزارة (بـ ARRAY أو سلسلة المسار) في الجزء التكراري لتجنب إعادة الزيارة.
س هل يمكن لـ CTE الإشارة إلى CTE معرف سابقاً؟
ج نعم. في WITH a AS (...), b AS (SELECT ... FROM a)، b يمكنها الإشارة إلى a، بالإشارة للأسفل حسب ترتيب التعريف.

📖 ملخص

📝 تمارين

  1. ⭐ استخدم استعلاماً فرعياً عددياً لإيجاد الموظفين الذين رواتبهم أعلى من متوسط الشركة.
  2. ⭐ استخدم NOT EXISTS لإيجاد العملاء الذين لم يقدموا أي طلب.
  3. ⭐⭐ أعد كتابة الاستعلام الفرعي المتداخل التالي باستخدام CTE: إيجاد الطلبات التي مبلغها أعلى من متوسط مبلغ طلبات ذلك العميل.
  4. ⭐⭐ استخدم WITH RECURSIVE لاستعلام مرؤوسي مدير محدد حتى 3 مستويات من جدول employees، مع إزاحة المستوى.
  5. ⭐⭐⭐ باستخدام CTE تكراري مع التجميع: بدءاً من الرئيس التنفيذي، عد التقارير المباشرة لكل مدير وعدد أفراد الشجرة الفرعية بالكامل، كل ذلك في عبارة SQL واحدة.
Web-Tutorial.com

فريق Web-Tutorial التقني

منصة دروس برمجية يديرها عدة مطورين. كل درس يتم كتابته ومراجعته بواسطة مطورين متخصصين في المجال. نعمل على ضمان دقة وموثوقية المحتوى — إذا لاحظت أي مشكلة، فيرجى إخبارنا.

100%