Лекція 4. Семестр 2. Обробка пов’язаних таблиць
Обробка пов’язаних таблиць
Оператор об’єднання UNION
Оператор UNION може бути використаний, якщо потрібно так би мовити "склеїти" декілька таблиць в ондну. Коли це може бути корисним? Розглянемо такий приклад: у нас є магазин який складається з декількох відділів, а саме: відділ спиртних напоїв, м’ясний відділ та кондитерський відділ. Якщо ж, ми бажаємо "заманити" нових покупців, або "здихатися" від продуктів у яких через місяць вийде термін придатності. Для таких цілей зазвичай організовують якції "червона ціна", а саме нам потрібно встановити знижки в деякому розмірі на певні групи товарів. А так як відділів у нас три, тому і таблиць з цінами теж три припустимо що вони мають такий вигляд:
- spirt_product.id = int,
spirt_product.name = VarChar,
spirt_product.price = float,
spirt_product.outdate = date;
- meat_product.id = int,
meat_product.name = VarChar,
meat_product.price = float,
meat_product.outdate = date;
- sveets_product.id = int,
sweets_product.name = VarChar,
sweets_product.price = float,
sweets_product.outdate = date;
Тобто, якщо нам потрібно зв’язати три таблиці в одну так щоб туди попали всі значення де до настання outdate (дати коли термін споживання закінчиться) два місяці.
Запит матиме вигляд:
SELECT spirt_product.name, spirt_product.price*0,8
FROM spirt_product
WHERE (TO_DAYS(spirt_product.outdate) - TO_DAYS(NOW)) < 60
UNION
SELECT meat_product.name, meat_product.price*0,8
FROM meat_product
WHERE (TO_DAYS(meat_product.outdate) - TO_DAYS(NOW)) < 60
UNION
SELECT sweets_product.name, sweets_product.price*0,8
FROM sweets_product
WHERE (TO_DAYS(sweets_product.outdate - TO_DAYS(NOW)) < 60;
Як результат ми отримаємо таблицю із двох стовпців імені продукту та його ціни із врахуванням знижки 20%.
Примітка:
Оператор UNION може об’єднувати таблиці тільки тоді коли типи данних які виводяться у стовпцях співпадають у всіх таблицях, я ще потрібно звернути увагу, що крапка з комою ставиться в самому кінці запросу.
Якщо ж наприклад магазину потрібно швидко "назбирати" коштів, тоді доцільніше робити "знижки" на дорогі товари, а саме фільтр потрібно застосувати по ціні. Наприклад у нас товар потрапить до "святкового кталогу" якщо його ціна перевищує деякий умовний бар’єр.
Тоді запит матиме вигляд:
SELECT spirt_product.name, spirt_product.price*0,8
FROM spirt_product
WHERE spirt_product.price > 150
UNION
SELECT meat_product.name, meat_product.price*0,8
FROM meat_product
WHERE meat_product.price > 80
UNION
SELECT sweets_product.name, sweets_product.price*0,8
FROM sweets_product
WHERE sweets_product.price > 50;
Отриманий запит поверне дві колонки ім’я продукту та його ціну зі знижкою 20%, якщо ціна за пляшку спиртного більша 150 грн., ціна за кілограм м’ясного виробу більша за 80 грн./кг., та солодощів ціна яких більша за 50 грн./кг. Данний метод знижок ще має один позитивний момент: якщо пляшка спиртного коштує 151 грн., то з урахуванням знижок буде 120,8 грн., тому клієнти які хотіли купити пляшку за 110 грн., будуть брати вакційний товар.
Примітка оператор UNION може використовуватися і для добавлення рядка в результат запиту, наприклад, якщо ми забажаємовивести додати рядок "|Всього |xxx|" до виводу запиту товарів із таблиці sweets_, де ххх – загальна кількість гривень які отримаються пістя продажу усього товару, то запит матиме вигляд:
SELECT * FROM sweets_product
UNION
SELECT 'Всього:', SUM(sweets_product.price);
Оператор JOIN
З’єднання таблиць за допомогою оператора JOIN може бути двох типів. Перший це повне безумовне з’єднання таблиць тобто ми отримаємо як результат одну таблицю із всіма стовпцями вхідних таблиць.
Приклад:
SELECT * FROM table1 JOIN table2;
Результат буде еквівалентним такому запиту:
SELECT * FROM table1, table2;
Зазвичай оператор JOIN можливо замінити простими умовами, а введений в мову запитів він лише тому, що при великій кількості данних об’єднання по умові буде виконуватись значно повільніше, ніж об’єднання оператором JOIN.
Якщо ж користуватися оператором об’єднання JOIN інтересними є такі комбінації з’єднань, їх можна зообразити схематично. Будемо використовувати два таблиці, опишемо їх структуру:
TableA.id = int, TableB.id = int,
TableA.name = VarChar; TableB.name = VarChar;
id name id name
-- ---- -- ----
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja
Спеціально використаємо однакові данні в таблицях, тоді можливі об’єднання таблиць схематично можна зобразити так:
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
3 Ninja 4 Ninja
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
Таким чином за допомогою оператора JOIN з умовами ми можемо змоделювати роботу з данними таблиці, як з математичними множинами.