محتویات سایت
        برچسب های محبوب 








 
   تفاضل دو مجموعه در SQL Server
  تفاضل یک عمل پر استفاده در بحث Querying است. هنگامی که سطرهایی را از جدولی انتخاب می کنید که در جدول دیگر وجود ندارد در حقیقت این عمل را انجام داده اید.
   SQL Server
   ۴۲۴۸۰
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۸۸/۱۲/۶
نسخه قابل چاپ نسخه قابل چاپ

بحث Querying یکی از جذاب ترین مباحث پایگاه داده ها و مخصوصا MS SQL Server RDBMS است. از نظر علمی یک Query را از دو شیوه ی متفاوت می توانیم خلق کنیم. جبر و گزاره. دو فصل از کتاب معروف آقای C.J. Date با عنوان های Relational Algebra و Relational Calculus به این موضوع پرداخته شده است.

بطور مثال فرض کنید می خواهیم مشخصات تمام تولید کنندگانی را بدست آوریم که هر دو قطعه ی X و Y را تولید کرده اند. اگر با دید منطق گزاره ای به صورت مساله نگاه کنیم می توانیم با ترکیب دو عملگر EXISTS و AND این کار را انجام بدیم. ولی اگر با دیدگاه جبری به مساله نگاه کنیم ابتدا با یک Query تولید کنندگان محصول X و با Query دیگر تولید کنندگان محصول Y را بدست آورده و نتیجه ی اشتراک این دو مجموعه تولید کنندگانی خواهند بود که هر دو محصول را تولید کرده اند.

(این مساله در واقع همان مساله ی تقسیم رابطه ای است اما با مقسوم علیه های محدود و مشخص)

 

SELECT *
  FROM Suppliers S
 WHERE EXISTS
       (SELECT *
          FROM SuppliersProducts SP
         WHERE S.S# = SB.S#
           AND SP.P# = 'X')
   AND EXISTS
       (SELECT *
          FROM SuppliersProducts SP
         WHERE S.S# = SB.S#
           AND SP.P# = 'Y'); 

SELECT *
  FROM Suppliers S
       INNER JOIN (SELECT S#
                     FROM SuppliersProducts
                    WHERE P# = 'X'
                   INTERSECT
                   SELECT S#
                     FROM SuppliersProducts
                    WHERE P# = 'Y') AS D(S#)
           ON S.S# = D.S#;

 

عملگرهای جبری را از سه دیدگاه پایگاه داده ها، زبان SQL و نرم افزار تجاری SQL Server می توانیم مورد بررسی قرار دهیم. خوشبختانه SQL Server به سمت استانداردها پیش می رود یعنی تمامی Syntax های استاندارد موجود در زبان SQL را پشتیبانی می کند و در کنار آن یکسری عملگرها و توابع غیر استاندارد را نیز مخصوص خود طراحی کرده است. بطور مثال تابع CAST جز استاندارد زبان SQL است در صورتی که SQL Server جدا از این، از تابع دیگری به نام Convert نیز سود می برد.

آقای دکتر Codd هشت عملگر جبری را معرفی کرده است. ضرب دکارتی (Cartesian Product)، اتصال (Join)، تصویر (Projection)، انتخاب (Selection)، اشتراک (Intersect)، اجتماع (Union)، تفاضل (Difference) و تقسیم رابطه ای (Relational Division). از این هشت عملگر سه عملگر مجموعه ای (Set Operations) اشتراک، اجتماع و تفاضل همانگونه تعریف می شوند که در مجموعه ی اعداد و ریاضیات وجود دارند.

زبان SQL سه عملگر EXCEPT و INTERSECT و UNION را به ترتیب برای تفاضل، اشتراک و اجتماع مجموعه ها طراحی کرده است. در SQL Server این عملگر ها با همین نام استفاده می شوند. البته عملگرهای مجموعه ای INTERSECT و EXCEPT در SQL Server 2005 معرفی شده اند پس استفاده از آنها در نسخه ی 2000 مقدور نیست.


تفاضل دو مجموعه

A - B = A EXCEPT B = {X | X element of A and X not element of B}

Query_1
EXCEPT
Query_2
ORDER BY

عمل تفاضل بین دو مجموعه صورت می گیرد. زبان SQL بر اساس تئوری مجموعه ها (Set Theory) عمل می کند. در مجموعه ترتیب و تکرار اعضا معنایی ندارد و مقادیر تکراری حذف شده و فقط یکبار در نظر گرفته می شوند. پس اگر از عملگر EXCEPT برای تفاضل استفاده کنیم دو Query اول و دوم به عنوان دو مجموعه در نظر گرفته می شوند. و همانطور که اشاره شد قبل از عمل تفاضل مقادیر تکراری بطور خودکار حذف می شوند و شما اجازه ندارین که از ماده ی ORDER BY در Query اول استفاده کنید.

از طرفی مقادیر NULL با مقادیر دیگر مقایسه می شوند. بطور مثال اگر query_1 دارای یک ستون بوده و شامل 5 سطر با مقدار null باشد و query_2 نیز دارای یک ستون و یک سطر با مقدار null باشد نتیجه ی تفاصل کوئری دوم از اول مجموعه ی تهی خواهد بود. چون ابتدا مقادیر تکراری حذف شده سپس دو مقدار null با یکدیگر برابر بوده سپس عضو null از مجموعه ی اول حذف می شود.

مقادیر null را نمی توانیم با عملگر تساوی = مقایسه کنیم. در ادامه یک تکنیک معرفی می کنم که با استفاده از آن می توانیم تمام مقادیر از جمله مقادیر null را با یکدیگر مقایسه کنیم.

انواع روش های پیاده سازی تفاضل:

      1. EXCEPT ( قابل استفاده در نسخه ی 2005 و بالاتر)
      2. UNION ALL
      3. NOT IN
      4. NOT EXISTS
      5. LEFT OUTER JOIN
      6. OUTER APPLY (قابل استفاده در نسخه ی 2005 و بالاتر)

فرض کنید می خواهیم سطرهای جدول Customers را از سطرهای Employees در بانک Northwind  تفریق کنیم. البته با ستون های مشترک (کشور، منطقه و شهر)
روش اول:
در زمان استفاده از عملگر EXCEPT بایستی توجه داشته باشید که تعداد فیلد ها در قسمت target (لیست ستونها) در هر دو کوئری یکسان باشد

-- EXCEPT
 SELECT Country, Region, City
   FROM Employees
 EXCEPT
 SELECT Country, Region, City
   FROM Customers;


روش دوم:
بعد از حذف داده های تکراری (با DISTINCT) و ساخت یک ستون عبارتی با مقدار ثابت سطرهای دو کوئری را با یکدیگر اجتماع می کنیم، سپس نتیجه ی حاصل را بر اساس سه ستون مورد نظر گروه بندی می کنیم و بعد از گروه بندی سطرهایی را فیلتر می کنیم که تعداد سطر موجود در هر گروه برابر با 1 بوده و مقدار ستون ثابت K نیز برابر با E باشد.

-- UNION ALL
 SELECT Country, Region, City
   FROM (SELECT DISTINCT Country, Region, City, K='E'
           FROM Employees
          UNION ALL
         SELECT DISTINCT Country, Region, City, K='C'
           FROM Customers) D
  GROUP BY Country, Region, City
 HAVING COUNT(*) = 1
    AND MAX(K) = 'E';

روش سوم:
همانطور که اشاره شد روش ما بایستی قادر باشد که با یک دستور تمام مقادیر از جمله null ها را با یکدیگر مقایسه کند برای این منظور از تابع COALESCE استفاده می شود. این تابع اولین عبارتی که غیر از null باشد را بر می گرداند. البته به جای آن از تابع ISNULL که ویژه ی SQL Server است نیز می توان استفاده کرد. اگر مقدار برابر با null بود سپس تابع مقدار 1 را بر میگرداند. 1 مقداری است که ممکن نیست نام یک شهر یا کشور باشد. پس هیچ وقت تداخلی ایجاد نمی کند. تمام روش های باقیمانده برای تکامل نیاز به این روش مقایسه دارند که برای خوانایی کد ها از آنها صرف نظر کردم.
اگر قرار بود تنها یک ستون مقایسه شود نیازی نبود که از کوئری تودرتوی وابسته (Correlated Subquery) استفاده کنیم. ولی در اینجا چون نیاز داریم سه ستون را با یکدیگر مقایسه کنیم دو ستون را داخل کوئری مقایسه کرده و یک ستون را خارج از کوئری.

-- NOT IN
 SELECT DISTINCT Country, Region, City
   FROM Employees E
  WHERE COALESCE(E.Country, '1') NOT IN
        (SELECT COALESCE(C.Country, '1')
           FROM Customers C
          WHERE COALESCE(E.Region, '1') = COALESCE(C.Region, '1')
            AND COALESCE(E.City, '1') = COALESCE(C.City, '1'));

روش چهارم:
با کمک EXISTS Predicate و Correlated Subquery اینکار را پیاده سازی می کنیم.

-- NOT EXISTS
 SELECT DISTINCT Country, Region, City
   FROM Employees E
  WHERE NOT EXISTS
        (SELECT *
           FROM Customers C
          WHERE E.Country = C.Country
            AND E.Region = C.Region
            AND E.City = C.City);

روش پنجم:
عملگر های مجموعه ای را می توانیم با انواع Join پیاده سازی کنیم. بطور مثال FULL OUTER JOIN برابر با UNION و LEFT OUTER JOIN برابر با EXCEPT و INNER JOIN برابر با INTERSECT است. البته روی کاغذ چون همانطور که اشاره شد این روش های جایگزین هیچگاه از نظر منطقی برابر با روش اصلی نیستند.

-- LEFT JOIN
 SELECT DISTINCT E.Country, E.Region, E.City
   FROM Employees E
        LEFT OUTER JOIN
        Customers C
            ON E.Country = C.Country
               AND E.Region = C.Region
               AND E.City = C.City
  WHERE C.Country IS NULL
    AND C.Region IS NULL
    AND C.City IS NULL
    AND E.Country IS NOT NULL

روش ششم:
عملگر غیر استاندارد جدولی APPLY می تواند یک جایگزین زیبا و شایسته ای برا عمل تفاضل باشد. اگر می خواهید مورد تشویق شرکت ماکروسافت قرار گیرید محصولات آن را تبلیغ کنید!

 

-- APPLY
 SELECT DISTINCT E.Country, E.Region, E.City
   FROM Employees E
        OUTER APPLY (SELECT 1
                       FROM Customers C
                      WHERE E.Country = C.Country
                        AND E.Region = C.Region
                        AND E.City = C.City) D(i)
  WHERE D.i IS NULL