الاستعلامات الفرعية المترابطة (Correlated Subqueries)

الاستعلام الفرعي المترابط (Correlated Subquery) هو استعلام فرعي يعتمد على الاستعلام الخارجي، حيث يُنفذ مرة واحدة لكل صف من الاستعلام الرئيسي. هذا يختلف عن الاستعلامات الفرعية العادية التي تُنفذ مرة واحدة فقط.

يُسمى "مترابط" لأنه يستخدم قيم من الصف الحالي في الاستعلام الخارجي لإجراء الحسابات أو المقارنات.

الفرق الأساسي:

النوع	عدد مرات التنفيذ	الاعتماد
Subquery عادي	مرة واحدة فقط	مستقل تماماً
Correlated Subquery	مرة لكل صف	يعتمد على الصف الحالي

تحذير الأداء: نظراً لأن الاستعلام الفرعي المترابط يُنفذ لكل صف، فقد يكون بطيئاً على الجداول الكبيرة. استخدمه بحذر وتأكد من وجود الفهارس المناسبة.

بيانات الأمثلة

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT,
    hire_date DATE
);

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    emp_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    product VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

-- إدراج بيانات الموظفين
INSERT INTO employees VALUES
(1, 'أحمد محمد', 'تطوير', 15000, NULL, '2018-01-15'),
(2, 'فاطمة علي', 'تطوير', 12000, 1, '2019-03-20'),
(3, 'خالد حسن', 'تسويق', 9000, NULL, '2020-06-10'),
(4, 'سارة أحمد', 'تسويق', 11000, 3, '2019-08-05'),
(5, 'محمد عبدالله', 'مبيعات', 8000, NULL, '2021-01-12'),
(6, 'نورا خالد', 'مبيعات', 8500, 5, '2020-11-20'),
(7, 'علي حسين', 'تطوير', 13000, 1, '2018-05-10'),
(8, 'ليلى سعيد', 'موارد بشرية', 10000, NULL, '2019-09-15');

-- إدراج بيانات المبيعات
INSERT INTO sales VALUES
(1, 5, '2024-01-15', 5000, 'لابتوب'),
(2, 5, '2024-01-20', 7500, 'هاتف'),
(3, 6, '2024-01-18', 6000, 'لابتوب'),
(4, 5, '2024-02-10', 8000, 'تابلت'),
(5, 6, '2024-02-15', 5500, 'هاتف'),
(6, 5, '2024-03-05', 9000, 'لابتوب');

كيف يعمل الاستعلام المترابط؟

مثال توضيحي:

-- الموظفين الذين رواتبهم أعلى من متوسط قسمهم
SELECT emp_name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department  -- الارتباط هنا!
);

خطوات التنفيذ:

الصف 1: يأخذ قسم الموظف الأول (e1.department)
تنفيذ Subquery: يحسب متوسط الرواتب لهذا القسم
المقارنة: يقارن راتب الموظف بالمتوسط
الصف 2: يكرر العملية للموظف الثاني
وهكذا... لكل صف في الجدول

تصور التنفيذ:

-- لكل صف في employees (e1):
--   الصف 1: أحمد محمد، تطوير، 15000
--     → تنفيذ: SELECT AVG(salary) FROM employees WHERE department = 'تطوير'
--     → النتيجة: 13333.33
--     → المقارنة: 15000 > 13333.33 ✓ (يُضاف للنتيجة)
--
--   الصف 2: فاطمة علي، تطوير، 12000
--     → تنفيذ: SELECT AVG(salary) FROM employees WHERE department = 'تطوير'
--     → النتيجة: 13333.33
--     → المقارنة: 12000 > 13333.33 ✗ (لا يُضاف)
--
-- ... وهكذا لكل صف

أمثلة أساسية

مثال 1: الموظفين فوق متوسط قسمهم

SELECT 
    emp_name,
    department,
    salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department = e1.department) AS avg_dept_salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
)
ORDER BY department, salary DESC;

النتيجة:

emp_name	department	salary	avg_dept_salary
أحمد محمد	تطوير	15000.00	13333.33
علي حسين	تطوير	13000.00	13333.33
سارة أحمد	تسويق	11000.00	10000.00

مثال 2: عدد الزملاء في نفس القسم

SELECT 
    emp_name,
    department,
    (SELECT COUNT(*) - 1  -- ناقص 1 لاستثناء الموظف نفسه
     FROM employees e2
     WHERE e2.department = e1.department) AS عدد_الزملاء
FROM employees e1
ORDER BY عدد_الزملاء DESC, emp_name;

مثال 3: الموظفين الأقدم في قسمهم

SELECT emp_name, department, hire_date
FROM employees e1
WHERE hire_date = (
    SELECT MIN(hire_date)
    FROM employees e2
    WHERE e2.department = e1.department
)
ORDER BY department;

مثال 4: إجمالي المبيعات لكل موظف

SELECT 
    emp_name,
    department,
    (SELECT COALESCE(SUM(amount), 0)
     FROM sales s
     WHERE s.emp_id = e.emp_id) AS total_sales,
    (SELECT COUNT(*)
     FROM sales s
     WHERE s.emp_id = e.emp_id) AS sale_count
FROM employees e
ORDER BY total_sales DESC;

EXISTS مع الاستعلامات المترابطة

EXISTS يُستخدم بشكل شائع مع الاستعلامات المترابطة لأنه فعّال جداً.

مثال 1: الموظفين الذين لديهم مبيعات

SELECT emp_name, department
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.emp_id = e.emp_id
)
ORDER BY emp_name;

مثال 2: الموظفين الذين ليس لديهم مبيعات

SELECT emp_name, department
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.emp_id = e.emp_id
)
ORDER BY department, emp_name;

مثال 3: الموظفين الذين لديهم مبيعات في آخر 30 يوم

SELECT emp_name, department
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.emp_id = e.emp_id
    AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);

مثال 4: الأقسام التي جميع موظفيها رواتبهم > 8000

SELECT DISTINCT department
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.department = e1.department
    AND e2.salary <= 8000
);

أمثلة متقدمة

مثال 1: ترتيب الموظفين داخل قسمهم

SELECT 
    emp_name,
    department,
    salary,
    (SELECT COUNT(*) + 1
     FROM employees e2
     WHERE e2.department = e1.department
     AND e2.salary > e1.salary) AS rank_in_dept
FROM employees e1
ORDER BY department, rank_in_dept;

مثال 2: الفرق عن أعلى راتب في القسم

SELECT 
    emp_name,
    department,
    salary,
    (SELECT MAX(salary)
     FROM employees e2
     WHERE e2.department = e1.department) AS max_dept_salary,
    (SELECT MAX(salary)
     FROM employees e2
     WHERE e2.department = e1.department) - salary AS difference
FROM employees e1
ORDER BY department, salary DESC;

مثال 3: المبيعات التراكمية (Running Total)

SELECT 
    s1.sale_id,
    s1.sale_date,
    s1.amount,
    (SELECT SUM(s2.amount)
     FROM sales s2
     WHERE s2.sale_date <= s1.sale_date
     AND s2.emp_id = s1.emp_id) AS running_total
FROM sales s1
ORDER BY s1.emp_id, s1.sale_date;

مثال 4: مقارنة مع الفترة السابقة

SELECT 
    s1.sale_id,
    s1.sale_date,
    s1.amount AS current_sale,
    (SELECT s2.amount
     FROM sales s2
     WHERE s2.emp_id = s1.emp_id
     AND s2.sale_date < s1.sale_date
     ORDER BY s2.sale_date DESC
     LIMIT 1) AS previous_sale,
    s1.amount - (
        SELECT COALESCE(s2.amount, 0)
        FROM sales s2
        WHERE s2.emp_id = s1.emp_id
        AND s2.sale_date < s1.sale_date
        ORDER BY s2.sale_date DESC
        LIMIT 1
    ) AS difference
FROM sales s1
ORDER BY s1.emp_id, s1.sale_date;

استخدام Correlated Subqueries مع UPDATE و DELETE

مثال 1: تحديث الرواتب بناءً على متوسط القسم

-- زيادة 10% للموظفين تحت متوسط قسمهم
UPDATE employees e1
SET salary = salary * 1.10
WHERE salary < (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

ملاحظة: في MySQL، قد تحتاج لاستخدام جدول مؤقت لتجنب خطأ "can't update table while selecting from it".

مثال 2: حذف الموظفين بدون مبيعات

DELETE FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.emp_id = e.emp_id
)
AND department = 'مبيعات';

مثال 3: تحديث بناءً على عدد المبيعات

-- إضافة علاوة للموظفين الذين لديهم أكثر من 3 مبيعات
UPDATE employees e
SET salary = salary * 1.15
WHERE (
    SELECT COUNT(*)
    FROM sales s
    WHERE s.emp_id = e.emp_id
) > 3;

اعتبارات الأداء

مشكلة N+1

إذا كان لديك 1000 صف في الجدول الرئيسي، سيُنفذ الاستعلام الفرعي 1000 مرة! هذا يُسمى مشكلة N+1.

1. استخدم الفهارس

تأكد من وجود فهارس على الأعمدة المستخدمة في:

شرط الربط (WHERE e2.department = e1.department)
أعمدة التجميع (AVG, SUM, COUNT)
أعمدة الترتيب (ORDER BY)

-- إنشاء فهارس لتحسين الأداء
CREATE INDEX idx_emp_dept ON employees(department);
CREATE INDEX idx_sales_emp ON sales(emp_id);
CREATE INDEX idx_sales_date ON sales(sale_date);

2. فكر في استخدام JOIN بدلاً منه

في كثير من الحالات، يمكن استبدال Correlated Subquery بـ JOIN:

-- ❌ بطيء: Correlated Subquery
SELECT 
    emp_name,
    salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department = e1.department) AS avg_sal
FROM employees e1;

-- ✅ أسرع: JOIN
SELECT 
    e.emp_name,
    e.salary,
    dept_avg.avg_sal
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) dept_avg ON e.department = dept_avg.department;

3. استخدم EXPLAIN لفحص خطة التنفيذ

EXPLAIN SELECT emp_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

4. حدد الأعمدة المطلوبة فقط

-- ❌ سيء
SELECT *,
    (SELECT COUNT(*) FROM sales WHERE emp_id = e.emp_id)
FROM employees e;

-- ✅ جيد
SELECT emp_name, department,
    (SELECT COUNT(*) FROM sales WHERE emp_id = e.emp_id) AS sale_count
FROM employees e;

متى تستخدم Correlated Subqueries؟

استخدمها عندما:

تحتاج مقارنة كل صف بمجموعته (مثل: متوسط القسم)
تستخدم EXISTS/NOT EXISTS للتحقق من الوجود
تحتاج حسابات تعتمد على الصف الحالي
الجدول صغير نسبياً (< 10,000 صف)
لديك فهارس مناسبة

تجنبها عندما:

الجدول كبير جداً (> 100,000 صف)
يمكن تحقيق نفس النتيجة بـ JOIN بسيط
لا توجد فهارس على أعمدة الربط
الاستعلام الفرعي معقد ويستغرق وقتاً
تحتاج أداء عالي جداً

أخطاء شائعة

1. نسيان الربط بين الاستعلامين

-- ❌ خطأ: لا يوجد ربط!
SELECT emp_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    -- نسينا: WHERE e2.department = e1.department
);
-- هذا سيقارن بمتوسط الشركة كلها، ليس القسم!

-- ✅ صحيح
SELECT emp_name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

2. استخدام نفس الـ Alias

-- ❌ خطأ: نفس الـ alias (e)
SELECT emp_name
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e  -- مشكلة!
    WHERE department = e.department
);

-- ✅ صحيح: aliases مختلفة
SELECT emp_name
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

3. عدم التحقق من NULL

-- ❌ قد يسبب مشاكل مع NULL
SELECT emp_name,
    (SELECT SUM(amount) FROM sales WHERE emp_id = e.emp_id)
FROM employees e;
-- قد يُرجع NULL للموظفين بدون مبيعات

-- ✅ أفضل: استخدم COALESCE
SELECT emp_name,
    (SELECT COALESCE(SUM(amount), 0) 
     FROM sales WHERE emp_id = e.emp_id) AS total_sales
FROM employees e;

أفضل الممارسات

1. استخدم aliases واضحة ومختلفة

-- ✅ جيد: e1 للخارجي، e2 للداخلي
SELECT e1.emp_name
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

2. أضف تعليقات توضيحية

SELECT 
    emp_name,
    salary,
    -- حساب متوسط راتب القسم لكل موظف
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department = e1.department) AS avg_dept_salary
FROM employees e1
-- فقط الموظفين فوق متوسط قسمهم
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

3. قارن الأداء مع البدائل

قبل استخدام Correlated Subquery في الإنتاج:

جرّب نفس الاستعلام بـ JOIN
استخدم EXPLAIN لمقارنة خطط التنفيذ
اختبر على بيانات حقيقية بحجم مشابه للإنتاج
قِس وقت التنفيذ الفعلي

4. تأكد من وجود الفهارس

-- قبل استخدام correlated subquery، أنشئ الفهارس
CREATE INDEX idx_dept ON employees(department);
CREATE INDEX idx_emp_sales ON sales(emp_id);

-- ثم استخدم الاستعلام
SELECT emp_name, 
    (SELECT COUNT(*) FROM sales WHERE emp_id = e.emp_id)
FROM employees e;

ملخص الدرس

في هذا الدرس، تعلمنا عن الاستعلامات الفرعية المترابطة:

Correlated Subquery يُنفذ مرة لكل صف من الاستعلام الخارجي
يعتمد على قيم من الصف الحالي في الاستعلام الرئيسي
مفيد للمقارنات داخل المجموعات (مثل: متوسط القسم)
يعمل بشكل ممتاز مع EXISTS/NOT EXISTS
قد يكون بطيئاً على الجداول الكبيرة (مشكلة N+1)
الفهارس ضرورية لتحسين الأداء
في كثير من الحالات، JOIN أسرع
استخدم EXPLAIN لفحص الأداء

دليل القرار السريع:

السيناريو	الحل الأفضل
التحقق من وجود/عدم وجود	EXISTS/NOT EXISTS (correlated)
مقارنة مع متوسط المجموعة	JOIN مع derived table
حسابات بسيطة لكل صف	Correlated subquery (إذا كان الجدول صغير)
جداول كبيرة (> 100k صف)	تجنب correlated، استخدم JOIN
تحديثات/حذف مشروط	Correlated subquery مناسب

نصيحة ذهبية: الاستعلامات الفرعية المترابطة قوية لكنها قد تكون بطيئة. استخدمها بحكمة، وتأكد دائماً من وجود الفهارس المناسبة، وقارن أداءها مع البدائل قبل الاستخدام في الإنتاج.

تهانينا! 🎉 لقد أكملت سلسلة دروس الاستعلامات الفرعية. الآن أنت تعرف:

✅ أساسيات Subqueries
✅ Subqueries في WHERE (IN, EXISTS, ANY, ALL)
✅ Subqueries في FROM (Derived Tables)
✅ Correlated Subqueries

في الدروس القادمة، سننتقل إلى موضوعات متقدمة أخرى في SQL!

DevArabi

دورة تعلم SQL

الاستعلامات الفرعية المترابطة (Correlated Subqueries)