MySQL وتصميم قواعد البيانات

UNION و INTERSECT وعمليات المجموعات

13 دقيقة الدرس 9 من 40

UNION و INTERSECT وعمليات المجموعات

تسمح لك عمليات المجموعات بدمج ومقارنة ومعالجة مجموعات النتائج من استعلامات متعددة. تعامل هذه العمليات نتائج الاستعلام كمجموعات رياضية، مما يمكّن من قدرات قوية لتحليل البيانات وإعداد التقارير.

فهم عمليات المجموعات

تجمع عمليات المجموعات الصفوف من عبارتي SELECT أو أكثر بناءً على مبادئ نظرية المجموعات:

UNION: يدمج النتائج، يزيل التكرارات
UNION ALL: يدمج النتائج، يحتفظ بالتكرارات (أسرع)
INTERSECT: يُرجع الصفوف المشتركة فقط (MySQL 8.0.31+)
EXCEPT/MINUS: يُرجع الصفوف في المجموعة الأولى وليس في الثانية

مهم: يجب أن يكون لجميع الاستعلامات في عملية المجموعة نفس عدد الأعمدة مع أنواع بيانات متوافقة. تأتي أسماء الأعمدة من الاستعلام الأول.

UNION - دمج مجموعات النتائج

يدمج UNION النتائج من استعلامات متعددة ويزيل الصفوف المكررة:

-- دمج العملاء النشطين والمؤرشفين
SELECT customer_id, customer_name, email, 'active' AS status
FROM customers
WHERE is_active = 1

UNION

SELECT customer_id, customer_name, email, 'archived' AS status
FROM archived_customers;

-- المنتجات من مصادر متعددة
SELECT product_name, price, 'warehouse_a' AS source
FROM warehouse_a_products
WHERE stock > 0

UNION

SELECT product_name, price, 'warehouse_b' AS source
FROM warehouse_b_products
WHERE stock > 0

ORDER BY product_name;

نصيحة: يمكن استخدام ORDER BY و LIMIT فقط في نهاية استعلام UNION بأكمله، وليس على عبارات SELECT الفردية. استخدم الأقواس مع ORDER BY/LIMIT للاستعلامات الفردية إذا لزم الأمر.

UNION ALL - الاحتفاظ بالتكرارات

UNION ALL أسرع لأنه لا يزيل التكرارات. استخدمه عندما تعلم أنه لا توجد تكرارات أو عندما تكون التكرارات مرغوبة:

-- جميع المعاملات من أنظمة دفع متعددة
SELECT
    transaction_id,
    customer_id,
    amount,
    transaction_date,
    'stripe' AS payment_system
FROM stripe_transactions
WHERE transaction_date >= '2024-01-01'

UNION ALL

SELECT
    transaction_id,
    customer_id,
    amount,
    transaction_date,
    'paypal' AS payment_system
FROM paypal_transactions
WHERE transaction_date >= '2024-01-01'

ORDER BY transaction_date DESC;

ملاحظة الأداء: UNION ALL أسرع بكثير من UNION لأنه يتخطى خطوة إزالة التكرارات. استخدم UNION ALL كلما أمكن ذلك.

استعلامات UNION المعقدة

-- تقرير مبيعات شامل من قنوات متعددة
SELECT
    order_date,
    'online' AS channel,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM online_orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY order_date

UNION ALL

SELECT
    sale_date AS order_date,
    'retail' AS channel,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM retail_sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY sale_date

UNION ALL

SELECT
    order_date,
    'wholesale' AS channel,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM wholesale_orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY order_date

ORDER BY order_date, channel;

INTERSECT - البحث عن الصفوف المشتركة

يُرجع INTERSECT فقط الصفوف التي تظهر في كلتا مجموعتي النتائج. يدعم MySQL 8.0.31+ INTERSECT أصلياً:

-- العملاء الذين قدموا طلبات في كل من 2023 و 2024 (MySQL 8.0.31+)
SELECT customer_id, customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE YEAR(order_date) = 2023

INTERSECT

SELECT customer_id, customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE YEAR(order_date) = 2024;

محاكاة INTERSECT (قبل 8.0.31)

لإصدارات MySQL الأقدم، استخدم INNER JOIN أو EXISTS لمحاكاة INTERSECT:

-- الطريقة 1: استخدام INNER JOIN
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id AND YEAR(o1.order_date) = 2023
JOIN orders o2 ON c.customer_id = o2.customer_id AND YEAR(o2.order_date) = 2024;

-- الطريقة 2: استخدام IN مع الاستعلامات الفرعية
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2023
)
AND customer_id IN (
    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024
);

EXCEPT - البحث عن الاختلافات

يُرجع EXCEPT (أو MINUS في Oracle) الصفوف من الاستعلام الأول التي لا تظهر في الثاني:

-- العملاء الذين طلبوا في 2023 ولكن ليس في 2024 (MySQL 8.0.31+)
SELECT customer_id, customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE YEAR(order_date) = 2023

EXCEPT

SELECT customer_id, customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE YEAR(order_date) = 2024;

محاكاة EXCEPT

لإصدارات MySQL الأقدم، استخدم LEFT JOIN مع فحص NULL أو NOT IN:

-- الطريقة 1: استخدام LEFT JOIN
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id AND YEAR(o1.order_date) = 2023
LEFT JOIN orders o2 ON c.customer_id = o2.customer_id AND YEAR(o2.order_date) = 2024
WHERE o2.order_id IS NULL;

-- الطريقة 2: استخدام NOT IN
SELECT DISTINCT customer_id, customer_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE YEAR(order_date) = 2023
  AND customer_id NOT IN (
      SELECT customer_id
      FROM orders
      WHERE YEAR(order_date) = 2024
  );

-- الطريقة 3: استخدام NOT EXISTS (الأكثر كفاءة)
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o1 ON c.customer_id = o1.customer_id
WHERE YEAR(o1.order_date) = 2023
  AND NOT EXISTS (
      SELECT 1
      FROM orders o2
      WHERE o2.customer_id = c.customer_id
        AND YEAR(o2.order_date) = 2024
  );

أفضل ممارسة: NOT EXISTS عموماً أكثر كفاءة من NOT IN لمجموعات البيانات الكبيرة، خاصة عندما قد يحتوي الاستعلام الفرعي على قيم NULL.

ترتيب العمليات

عند الجمع بين عمليات مجموعة متعددة، افهم أسبقية المشغل:

-- INTERSECT له أسبقية أعلى من UNION/EXCEPT
-- هذا الاستعلام: (A UNION B) INTERSECT C
(SELECT product_id FROM featured_products
 UNION
 SELECT product_id FROM trending_products)

INTERSECT

SELECT product_id FROM in_stock_products;

-- استخدم الأقواس للوضوح والتحكم
(SELECT product_id FROM featured_products
 INTERSECT
 SELECT product_id FROM in_stock_products)

UNION

(SELECT product_id FROM trending_products
 INTERSECT
 SELECT product_id FROM in_stock_products);

حالات الاستخدام الواقعية

-- تقرير تقسيم العملاء
SELECT
    'high_value' AS segment,
    customer_id,
    customer_name,
    total_spent
FROM (
    SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
    HAVING total_spent > 10000
) AS high_value_customers

UNION ALL

SELECT
    'medium_value' AS segment,
    customer_id,
    customer_name,
    total_spent
FROM (
    SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
    HAVING total_spent BETWEEN 1000 AND 10000
) AS medium_value_customers

UNION ALL

SELECT
    'low_value' AS segment,
    customer_id,
    customer_name,
    total_spent
FROM (
    SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
    HAVING total_spent < 1000
) AS low_value_customers

ORDER BY total_spent DESC;

-- توحيد المخزون متعدد المصادر
SELECT
    product_name,
    SUM(quantity) AS total_quantity,
    GROUP_CONCAT(DISTINCT location) AS locations
FROM (
    SELECT product_name, quantity, 'Warehouse A' AS location
    FROM warehouse_a_inventory

    UNION ALL

    SELECT product_name, quantity, 'Warehouse B' AS location
    FROM warehouse_b_inventory

    UNION ALL

    SELECT product_name, quantity, 'Store Network' AS location
    FROM store_inventory
) AS all_inventory
GROUP BY product_name
HAVING total_quantity > 0
ORDER BY total_quantity DESC;

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

نصائح التحسين:

استخدم UNION ALL بدلاً من UNION عندما لا تهم التكرارات (أسرع بكثير)
أضف جمل WHERE لتصفية البيانات قبل عمليات المجموعة
استخدم الفهارس على الأعمدة المستخدمة في شروط WHERE و JOIN
فكر في إنشاء جداول مؤقتة للعمليات المعقدة متعددة الخطوات
يمكن تطبيق LIMIT على النتيجة النهائية لتقليل نقل البيانات
تأكد من تطابق أنواع الأعمدة لتجنب التحويلات الضمنية

-- UNION فعال مع التصفية
SELECT product_id, product_name, price
FROM products
WHERE category_id = 5 AND is_active = 1

UNION ALL

SELECT product_id, product_name, price
FROM archived_products
WHERE category_id = 5 AND archived_date > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

ORDER BY price DESC
LIMIT 100;

تمرين عملي:

التحدي: اكتب استعلامات لهذه السيناريوهات:

البحث عن المنتجات المتاحة في جميع المستودعات الثلاثة (محاكاة INTERSECT)
إنشاء قائمة جهات اتصال موحدة من العملاء والموردين والموظفين
البحث عن المستخدمين الذين سجلوا ولكن لم يقوموا بالشراء أبداً (محاكاة EXCEPT)

الحلول:

-- 1. المنتجات في جميع المستودعات
SELECT product_id, product_name
FROM products p
WHERE EXISTS (SELECT 1 FROM warehouse_a WHERE product_id = p.product_id)
  AND EXISTS (SELECT 1 FROM warehouse_b WHERE product_id = p.product_id)
  AND EXISTS (SELECT 1 FROM warehouse_c WHERE product_id = p.product_id);

-- 2. قائمة جهات الاتصال الموحدة
SELECT name, email, phone, 'customer' AS type FROM customers
UNION
SELECT company_name, contact_email, contact_phone, 'supplier' FROM suppliers
UNION
SELECT CONCAT(first_name, ' ', last_name), email, phone, 'employee' FROM employees
ORDER BY name;

-- 3. المستخدمون الذين لم يشتروا أبداً
SELECT user_id, username, email
FROM users
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.user_id
);

الملخص

في هذا الدرس، أتقنت:

UNION و UNION ALL لدمج مجموعات النتائج
INTERSECT للبحث عن الصفوف المشتركة (MySQL 8.0.31+)
EXCEPT للبحث عن الاختلافات بين المجموعات
محاكاة INTERSECT و EXCEPT في إصدارات MySQL الأقدم
ترتيب العمليات واستخدام الأقواس للتحكم
التطبيقات الواقعية وتحسين الأداء

التالي: في الدرس التالي، سنستكشف دوال النافذة والتحليلات للتحليل المتقدم للبيانات وإعداد التقارير!