C, PHP, VB, .NET

В теорията на релационните бази от данни се използват често основните операции върху множества. Досега не сме ги разглеждали в теоретичен план, а ги използвахме чисто интуитивно. Сега отново няма да се задълбочаваме във формализъм, а ще погледнем нещата от практична гледна точка, като естествено ще поставим основния акцент върху реализацията на операциите в MySQL.

1. Обединение на множества:

Ако имаме множествата A = {1, 2, 3} и B = {2, 3, 4}, то тяхното обединение е множеството {1, 2, 3, 4}. За реализация на обединение в MySQL, темата е разгледана с примери в статията за UNION и UNION ALL и по-точно с конкретна реализация в статията FULL JOIN в MySQL.

2. Сечение на множества

Ако имаме множествата A = {1, 2, 3} и B = {2, 3, 4}, то тяхното сечение е A ∩ B = {2, 3}. От гледна точка на релационните бази от данни множествата са релации (таблици). Сечението на две релации ще бъде релация, която съдържа общите записи на тези две релации (колоните на таблиците трябва да съвпадат по брой и тип). Например (приемаме, че А и B имат по две колони):

(SELECT col1, col2  FROM A)
INTERSECT
(SELECT col1, col2 FROM B);

MySQL НЕ поддържа директно оператор INTERSECT. Той обаче много лесно може да бъде моделиран по алтернативни начини. Най-простото и най-правилно решение за горния пример е да обединим условието в WHERE. Заявката ще стане следната:

SELECT DISTINCT col1, col2 FROM A
WHERE (col1, col2) IN ( SELECT col1, col2 FROM B);

Друго решение би било да използваме JOIN с DISTINCT:

SELECT DISTINCT A.col1, A.col2
FROM A JOIN B ON A.col1 = B.col1 AND A.col2 = B.col2;

Както виждате използваме оператор DISTINCT, за да премахнем повторенията – такива ще има ако в A и B има повторения на комбинациите от col1 и col2. Операторът, който разрешава повторенията е INTERSECT ALL:

(SELECT col1, col2  FROM A)
INTERSECT ALL
(SELECT col1, col2 FROM B);

В MySQL той се представя просто като пропуснем DISTINCT при решението с вложен SELECT:

SELECT col1, col2 FROM A
WHERE (col1, col2) IN ( SELECT col1, col2 FROM B);

Нека разгледаме един пример имаме две SQL заявки – едната изкарва списък с id на служителите на фирма, които са над 1500 лева на месец, а втората изкарва списък с id на служителите, които са от град София.

SELECT id
FROM employees
WHERE salary > 1500;

SELECT id
FROM employees
WHERE city="Sofia";

Сечението на тези две множества (на теория) би трябвало да е следното:

(SELECT id FROM employees
WHERE salary > 1500)
INTERSECT
(SELECT id FROM employees
WHERE city="Sofia");

В такава заявка няма да има повторения ако id е уникално – тоест няма значение дали е INTERSECT или INTERSECT ALL. Вероятно сте се досетили, че тук най-простото и очевидно решение е друго (извън шаблона, който показахме по-горе):

SELECT id
FROM employees
WHERE salary>1500 AND city="Sofia";

В редица случаи обаче нещата няма да стават толкова просто. Ако данните не се взимат от една таблица, а от различни, този метод няма да е приложим. Нека имаме например таблица за доставчици и таблица за поръчки. Искаме да видим списък с идентификационните номера само на доставчиците, които имат направени поръчки, т.е. искаме да премахнем доставчиците, които все още нямат никакви поръчки. Търсената заявка е:

(SELECT id FROM suppliers)
INTERSECT
(SELECT supplier_id FROM orders);

В MySQL (вероятно вече се досещате) това може да се реализира с вложен SELECT:

SELECT id
FROM suppliers
WHERE id IN( SELECT supplier_id FROM orders );

Тук въпреки, че реализираме INTERSECT, а не INTERSECT ALL, можем да не слагаме DISTINCT в SELECT заявката защото приемаме, че suppliers.id е UNIQUE.

Друго решение би било да използваме JOIN с DISTINCT:

SELECT DISTINCT suppliers.id
FROM suppliers JOIN orders ON suppliers.id = orders.supplier_id;

Обърнете се към статията Join срещу вложен Select за повече информация и сравнение междудвата метода. При всички положения, както се вижда от примерите, липсата на оператор INTERSECT в MySQL не е сериозно неудобство, защото той може да бъде напълно покрит чрез JOIN или условия в WHERE клаузите на заявките.

3. Разлика на множества

Ако имаме множествата A = {1, 2, 3} и B = {2, 3, 4}, то тяхната разлика A \ B = {1}. Аналогично разликата B \ A = {4}.

От гледна точка на релационните бази от данни бихме казали следното – разликата между две релации са записите в първата релация, които не участват във втората релация.

(SELECT col1, col2 FROM A)
ЕXCEPT
(SELECT col1, col2 FROM B);

Оператор EXCEPT също премахва повторенията и има допълнително оператор EXCEPT ALL, по подобие на всички други оператори от теория на множествата в бази от данни.

В MySQL няма оператор ЕXCEPT (който от своя страна в някои DBMS това е реализиран като оператор MINUS). Както и в предишния случай това може да бъде преодоляно лесно. За EXCEPT това ще бъде:

SELECT DISTINCT col1, col2 FROM A
WHERE (col1, col2) NOT IN ( SELECT col1, col2 FROM B );

или с LEFT JOIN:

SELECT DISTINCT A.col1, A.col2
FROM A LEFT JOIN B ON A.col1 = B.col1 AND A.col2 = B.col2
WHERE B.col1 IS NULL AND B.col2 IS NULL;

А за EXCEPT ALL ще бъде:

SELECT col1, col2 FROM A
WHERE (col1, col2) NOT IN ( SELECT col1, col2 FROM B );

или

SELECT A.col1, A.col2
FROM A LEFT JOIN B ON A.col1 = B.col1 AND A.col2 = B.col2
WHERE B.col1 IS NULL AND B.col2 IS NULL;

Нека от последния пример да пожелаем да изкараме списък с тези доставчици, които все още нямат никакви поръчки в системата. Единият начин отново е с вложен SELECT (отново приемаме, че suppliers.id е UNIQUE и в случая реализираме оператор EXCEPT).

SELECT id FROM suppliers
WHERE id NOT IN ( SELECT supplier_id FROM orders);

Другият начин отново е с JOIN, но този път външен, като допълнително имаме и условие WHERE:

SELECT suppliers.id
FROM suppliers LEFT JOIN orders ON suppliers.id = orders.supplier_id
WHERE orders.supplier_id IS NULL;

Забележете, че моделираме EXCEPT, но без DISTINCT в JOIN заявката, защото приемаме, че suppliers.id е UNIQUE.

4. Симетрична разлика на множества

Ако имаме множествата A = {1, 2, 3} и B = {2, 3, 4}, то тяхната симетрична разлика е разликата на тяхното обединение с тяхното сечение (A ∪ B) \ (A ∩ B) = {1, 4}. Тъй като вече имаме и операциите обединение и сечение, можем (в теоретичен план) да ги свържем точно по този начин:

((SELECT col1, col2 FROM A) UNION (SELECT col1, col2 FROM B))
ЕXCEPT
((SELECT col1, col2 FROM A) INTERSECT (SELECT col1, col2 FROM B));

или с по-ефективното преобразувание:

((SELECT col1, col2 FROM A) ЕXCEPT (SELECT col1, col2 FROM B))
UNION ALL
((SELECT col1, col2 FROM B) ЕXCEPT (SELECT col1, col2 FROM A));

Е, това вече може да се „преведе“ на езика на MySQL по абсолютно същият принцип, заменяйки тези оператори с техните еквивалентни. Примерът със supliers и orders не е практичен за демонстрация, защото orders.supplier_id е същинско подмножество на suppliers.id (сечението на втората заявка с първата ще е празно множество).

5. Декартово произведение на множества

Това е „комбинацията всяко с всяко“. Реализира се с оператор JOIN без условие ON.

Обновено 06.05.2018 г.: В MariaDB 10.3 вече ще има оператори INTERSECT и EXCEPT.