قارن الصفوف مع الاستعلامات الفرعية المرتبطة

مكتمل

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

فهم تنفيذ الاستعلام الفرعي المترابط

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

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

فكر في كيفية اختلاف هذين الاستعلامين:

-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);

-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p1.ProductCategoryID  -- References outer query
);

في المثال غير المترابط، يحسب الاستعلام الفرعي متوسط سعر واحد عبر جميع المنتجات. يتم حساب هذه القيمة مرة واحدة، ثم يقارن سعر كل منتج مع ذلك الرقم الثابت.

في المثال المرتبط، يشير p1.ProductCategoryID الاستعلام الفرعي إلى الاستعلام الخارجي. هذا يخلق تبعية: لكل صف من الصف، يحسب الاستعلام الفرعي متوسط السعر لتلك الفئة المحددة. يتم مقارنة المنتج في فئة "الدراجات" بمتوسط الدراجات، بينما يقارن المنتج في فئة "الإكسسوارات" بمتوسط الإكسسوارات.

‏‫ملاحظة‬

غالبا ما يحول محسن الاستعلامات الفرعية المترابطة إلى انضمامات مكافئة داخليا. ومع ذلك، فإن فهم السلوك المنطقي المرتبط يساعدك على كتابة الاستعلامات الصحيحة، حتى عندما يختلف التنفيذ الفيزيائي.

تصفية مع الاستعلامات الفرعية المرتبطة

تتيح الاستعلامات الفرعية المترابطة في الجملة WHERE شروط تصفية خاصة بالصف، وهي شروط مستحيلة مع المقارنات الثابتة. بدلا من المقارنة مع قيمة ثابتة واحدة، يتم تقييم كل صف بناء على قيمة محسوبة ديناميكيا بناء على خصائص ذلك الصف.

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

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;

يمكنك تطبيق نفس النمط لتحديد العملاء الذين يختلف سلوكهم عن أساسهم الشخصي.

الاستفسار التالي يجد العملاء الذين قدموا طلبا واحدا على الأقل يتجاوز متوسط قيمة طلبهم، مما يساعد في تحديد أنماط الشراء غير العادية أو المعاملات ذات القيمة العالية:

SELECT DISTINCT
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
    SELECT AVG(soh2.TotalDue)
    FROM SalesLT.SalesOrderHeader AS soh2
    WHERE soh2.CustomerID = c.CustomerID
);

الاستخدام EXISTS مع الاستعلامات الفرعية المرتبطة

يقوم المؤثر EXISTS مع استعلام فرعي مرتبط باختبار ما إذا كانت هناك صفوف مطابقة في جدول ذي صلة، ويعيد نتيجة بسيطة صحيحة أو خاطئة. هذا النمط فعال للغاية لأن محرك قاعدة البيانات يمكنه التوقف عن البحث بمجرد العثور على أول صف مطابق. على عكس الاستعلامات الفرعية التي تعيد بيانات فعلية، EXISTS تحتاج فقط إلى تأكيد وجود أو غياب.

استخدمها EXISTS عندما تحتاج للإجابة على أسئلة مثل "أي العملاء قدموا طلبات؟" أو "أي المنتجات لم تباع من قبل؟" عادة ما يستخدم SELECT 1 الاستعلام الفرعي لأن القيم الفعلية لا تهم:

-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

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

توضح الأمثلة التالية كيفية العثور على منتجات ذات طلبات وفئات ذات كميات عالية حيث يحقق كل منتج حد سعري:

-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderDetail AS sod
    WHERE sod.ProductID = p.ProductID
      AND sod.OrderQty > 10
);

-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.Product AS p
    WHERE p.ProductCategoryID = pc.ProductCategoryID
      AND p.ListPrice <= 100
);

نصيحة

EXISTS عادة ما يتفوق IN في الاستعلامات الفرعية، خاصة عند التحقق من وجود الجداول الكبيرة. يمكن للمحسن التوقف بعد العثور على أول تطابق مع EXISTS، بينما IN قد يحتاج إلى استرجاع جميع القيم المتطابقة.

احسب القيم التي تحتوي على استعلامات فرعية مترابطة في SELECT

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

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

-- Show each product with its category's average price
SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS CategoryAvgPrice,
    p.ListPrice - (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS DifferenceFromAvg
FROM SalesLT.Product AS p;

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

-- Show each customer with their order count
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    (
        SELECT COUNT(*)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS OrderCount,
    (
        SELECT MAX(soh.OrderDate)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS LastOrderDate
FROM SalesLT.Customer AS c;

‏‫ملاحظة‬

يجب أن تعطي الاستعلامات الفرعية المترابطة في الجملة SELECT قيمة واحدة بالضبط. إذا كان بإمكان الاستعلام الفرعي إرجاع عدة صفوف، قم بتلفها في دالة تجميع مثل MAX()، MIN()، أو SUM().

ابحث عن أعلى N لكل مجموعة تحتوي على استعلامات فرعية مترابطة

واحدة من أكثر التطبيقات العملية للاستعلامات الفرعية المرتبطة هي إيجاد أعلى N عنصر ضمن كل مجموعة. يجيب هذا النمط على أسئلة مثل "ما هي أغلى ثلاثة منتجات في كل فئة؟" أو "من هم أفضل خمسة مندوبي مبيعات في كل منطقة؟"

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

الاستفسار التالي يبحث عن أغلى ثلاثة منتجات لكل فئة من خلال اختيار المنتجات التي تظهر هويتها ضمن أفضل 3 في فئتها:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
    SELECT TOP 3 p2.ProductID
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
    ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;

طريقة بديلة تعد عدد العناصر التي ترتب أعلى من الصف الحالي. إذا كان عدد العناصر الأقل من N لها قيمة أعلى، يكون الصف الحالي في أعلى N. هذه التقنية تتعامل مع الروابط بشكل مختلف ويمكن أن تكون مفيدة عندما تحتاج إلى جميع العناصر التي تربط في المركز N:

-- Find products that are in the top 3 by price within their category
SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
    SELECT COUNT(*)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
      AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;

قارن الصفوف المتتالية

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

يجد الاستعلام الفرعي صفا مرتبطا عن طريق تصفية الصفوف التي تأتي قبل (أو بعد) الصف الحالي في التسلسل المنطقي، ثم يأمر النتائج بالحصول على الصف المجاور مباشرة:

-- Show each order with the previous order's total
SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    (
        SELECT TOP 1 soh2.TotalDue
        FROM SalesLT.SalesOrderHeader AS soh2
        WHERE soh2.CustomerID = soh.CustomerID
          AND soh2.OrderDate < soh.OrderDate
        ORDER BY soh2.OrderDate DESC
    ) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;

نصيحة

في المقارنات المتتالية بين الصفوف، تكون دوال النوافذ مثل LAG() و LEAD() عادة أكثر كفاءة وقابلية للقراءة من الاستعلامات الفرعية المرتبطة بذلك. استخدم الاستعلامات الفرعية المرتبطة عندما تحتاج إلى شروط أكثر تعقيدا من دعم دوال النوافذ.

اختر بين الاستعلامات الفرعية المترابطة والبدائل

الاستعلامات الفرعية المرتبطة ليست دائما أفضل نهج. الجدول التالي يساعدك في اختيار التقنية المناسبة:

استخدم هذا الأسلوب	عندما تحتاج...
الاستعلامات الفرعية المرتبطة	قارن كل صف بقيمة محسوبة ديناميكيا بناء على خصائص ذلك الصف، أو اختبر وجود مع EXISTS/NOT EXISTS، أو استرجاع قيمة ذات صلة واحدة بالضبط لكل صف باستخدام منطق اختيار معقد.
الانضمام	استرجاع الأعمدة من جداول متعددة، أو عندما تكون العلاقات بسيطة دون حسابات لكل صف.
دوال النافذة	احسب الإجماليات الجارية، الترتيبات، أو الوصول إلى الصفوف السابقة/التالية باستخدام LAG()/LEAD(). أكثر كفاءة من الاستعلامات الفرعية المرتبطة لهذه الأنماط.
CTEs	الرجوع إلى نفس النتيجة المحسوبة عدة مرات، أو قسم المنطق المعقد إلى خطوات مسماة وقابلة للقراءة.

الاعتبارات الخاصة بالأداء

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

اتبع هذه الإرشادات لتحسين أداء الاستعلام الفرعي المترابط:

• إنشاء فهارس على أعمدة الارتباط: تأكد من أن الأعمدة المشار إليها في جملة الاستعلام WHERE الفرعي التي ترتبط بالاستعلام الخارجي مفهرسة. على سبيل المثال، إذا كان الاستعلام الفرعي الخاص بك يقوم بتصفية على ProductCategoryID، فإن فهرس على ذلك العمود يسمح لقاعدة البيانات بالعثور بسرعة على صفوف مطابقة بدلا من مسح الجدول بالكامل لكل صف خارجي.

• أضف أعمدة إضافية في الفهارس: إذا كان استعلامك الفرعي يقوم أيضا بتصفية أو تجميع على أعمدة أخرى، فكر في فهرس مركب. يدعم مؤشر على (ProductCategoryID, ListPrice) كل من البحث عن الارتباط والتصفية أو التجميع المعتمد على السعر في مؤشر واحد فقط.

• تقييم الأساليب البديلة: يمكن إعادة كتابة العديد من الاستعلامات الفرعية المرتبطة كروابط أو دوال نافذة بأداء أفضل. إذا كنت تجد القيمة القصوى لكل مجموعة، فإن دالة النافذة غالبا ROW_NUMBER() ما تتفوق على استعلام فرعي مرتبط يختار MAX() لكل صف.

• راجع خطط التنفيذ: استخدم SET STATISTICS IO ON وافحص خطة التنفيذ الفعلية لفهم كيفية معالجة المحسن للاستعلام الفرعي المرتبط بك. قد يقوم المحسن بتحويلها إلى وصلة داخليا، أو قد ينفذها صفا بصف كما هو مكتوب.

• الاختبار باستخدام أحجام بيانات واقعية: يمكن أن تصبح الاستعلامات الفرعية المرتبطة التي تؤدي أداء جيدا على مجموعات بيانات اختبار صغيرة بطيئة مع جداول بحجم الإنتاج. دائما قم بمقارنة البيانات الممثلة قبل النشر في الإنتاج.

مهم

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

لمزيد من المعلومات حول الاستعلامات الفرعية، انظر الاستعلامات الفرعية (Transact-SQL) و EXISTS (Transact-SQL).