Різниця множин

Аналогічний тип запитів, не менш корисний для аналізу кореляції між двома наборами даних, називається запитом на різниця множин Він шукає відмінності між двома наборами даних, грунтуючись на умові обєднання У термінах реляційної алгебри він видаляє дільник з ділене, залишаючи різницю Цей тип запитів є зворотним внутрішньому обєднання Неформально його називають запитом пошуку невідповідних рядків

Запити на різницю множин ідеально підходять для пошуку недоречних або невідповідних даних (наприклад, рядків, які присутні в одному наборі даних і відсутні в іншому) (рис 911)

Puc 911 Запит на різницю множин шукає дані, що знаходяться за межами перетину двох наборів

У стандарті ANSI SQL запити на різницю реалізовані за допомогою ключового слова except, яке SQL Server не підтримує

Запит на різницю множин аналогічний злиттю різниці за винятком того, що останній є операцією, заснованої на рядках таблиць з ідентичним визначенням стовпців, в той час як перший має справу тільки зі стовпцями, які беруть участь у умови обєднання У цьому сенсі запит на різницю множин є злиттям різниці тільки тих стовпців, які беруть участь в обєднанні

Використовуючи наш приклад з таблицями One і Two, ми можемо створити запит, який шукає всі рядки таблиці One, що не мають відповідностей в таблиці Two, видаляючи безліч Два (дільник) з безлічі Один (ділене)

Зовнішнє обєднання вже містить рядки, що знаходяться поза перетину, так що нам залишається тільки створити запит обєднання різниці, використовує зовнішнє обєднання з обмеженням IS NULL, що накладається на первинний ключ другого набору даних

USE Tempdb

SELECT Thingl, Thing2 FROM dboOne

LEFT OUTER JOIN dboTwo ON OneOnePK = TwoOnePK WHERE TwoTwoPK IS NULL

Різниця в першому наборі даних залишиться такою:

Thingl           Thing2

Old Thing     NULL

Blue Thing   NULL

Перенести теорію на сценарій з реального життя ви зможете за допомогою навчальної бази даних OBXKites У наступному прикладі запит на різницю множин знаходить всіх клієнтів, ще не розмістили замовлення У даному прикладі таблиця Contact є діленим, а запит на різницю множин видаляє з неї запису тих клієнтів, у яких є відповідності в таблиці [Order] Пропозиція WHERE обмежує результуючий набір даних тільки тими рядками, яким не знайдені відповідності в таблиці [Order]:

USE OBXKites

SELECT LastName, FirstName FROM dboContact

LEFT OUTER JOIN dbo[Order]

ON ContactContactID = [Order]ContactID WHERE OrderID IS NULL

В результаті отримаємо різницю між таблицями Contact і [Order], тобто список клієнтів, які ще не розмістили замовлення:

LastName            FirstName

Andrews Ed

Boston Dave

Earl   Betty

Hanks   Nickolas

Harrison              Charlie

Запит різниці множин також можна написати, використовуючи подзапрос Умова WHERE NOT IN видаляє рядки результату підзапиту (дільника) із зовнішнього запиту (діленого) наступним чином:

SELECT LastName, FirstName FROM dboContact WHERE ContactID NOT IN (SELECT ContactID FROM dbo[Order])

ORDER BY LastName, FirstName

Будь-яка з форм цього запиту (ліве зовнішнє обєднання або подзапрос NOT IN) добре впораються з завданням, маючи дуже подібні плани виконання (рис 912)

Рис 912 Запит на різниця множин, що використовує подзапрос, оптимізується практично в той же план виконання, що і рішення, що використовує ліве зовнішнє обєднання

Я часто використовую модифіковану версію цієї методики для видалення некоректних даних, що зявилися в ході перетворень З логічної точки зору запит на різницю повних множин є прямою протилежністю внутрішньому обєднання Він знаходить всі рядки поза перетину в будь-якому з наборів даних, комбінуючи повне зовнішнє обєднання з обмеженням WHERE, відсіваються тільки порожні значення в будь-якому з первинних ключів:

SELECT Thingl, Thing2 FROM One

FULL OUTER JOIN Two

ON OneOnePK = TwoOnePK WHERE TwoTwoPK IS NULL OR OneOnePK IS NULL

Результатом стануть всі рядки, що не мають відповідності в таблиці на іншому кінці обєднання:

Thingl

Thing2

NULL         Plane

NULL         Cycle

Blue Thing NULL

Old Thing   NULL

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*