🗊Презентация Формування багатотабличного SQL-запиту

Заняття 6. Формування багатотабличного SQL-запиту

Зв’язані (корельовані) підзапити Основною ознакою зв’язаного (корельованого) підзапиту є те, що він не може бути виконаним самостійно, без зв’язку з основним запитом. Формально це реалізується тим, що підзапит посилається на таблицю, яка вказується в основній частині запиту.

Виконання запиту з корельованим підзапитом Спочатку береться увесь перший запис з таблиці T1. Цей запис називається поточним. Значення стовпців для цього запису є доступними і можуть використовуватись у підзапиті. Після цього виконується підзапит, який повертає список значень стовпця B таблиці T2 у тих записах, в яких значення стовпця C рівне значенню стовпця C з таблиці T1. Припускаємо, що підзапит повертає єдине значення (оскільки в операторі WHERE основного запиту операція =). Тепер виконується оператор WHERE основного запиту. Якщо значення стовпця B поточного запису таблиці T1 рівне значенню, яке вибрав підзапит, то цей запис виділяється зовнішнім запитом і поміщається в результатну таблицю. Якщо умова оператора WHERE основного запиту не виконується, то вибраний запис ігнорується. Після цього відбувається перехід на наступний запис таблиці T1. Аналогічно все виконується для кожного запису таблиці T1.

Зв’язані (корельовані) підзапити Приклад 1. Знайти інформацію про усіх замовників, що здійснювали замовлення 3 жовтня: SELECT * FROM Customers WHERE '2009-10-03' IN (SELECT odate FROM Orders WHERE Customers.cnum = Orders.cnum);

Використання предиката EXISTS Приклад 3. (Перевірка на непорожній результат). Отримати відомості про замовників, які зробили хоча б одну покупку. SELECT * FROM Customers WHERE EXISTS (SELECT DISTINCT onum FROM Orders WHERE Customers.cnum = Orders.cnum);

Внутрішнє з’єднання (INNER JOIN) NATURAL JOIN – природне з'єднання. JOIN…ON – з’єднання за умовою. JOIN…USING – з’єднання за іменами стовпців.

Природне з’єднання (NATURAL JOIN) Перевіряються на рівність усі одноіменні стовпці таблиці (з’єднання за рівністю). Синтаксис: SELECT Таблиця1.*, Таблиця2.* FROM Таблиця1 NATURAL JOIN Таблиця2;

Умовне з’єднання (JOIN…ON), з’єднання за іменами стовпців (JOIN…USING) JOIN…ON використовується, коли потрібно з’єднати за іншими логічними умовами, не обов’язково за рівністю. JOIN…USING подібне на природне з’єднання. Відмінність полягає в тому, що можна вказати, які саме стовпці повинні перевірятись.

Внутрішнє з’єднання (INNER JOIN) При внутрішньому з'єднанні рядки таблиць, що не співпадають виключаються. Приклад 4. Вивести імена продавців і замовників з однакових міст. SELECT Sellers.sname, Sellers.city, Customers.cname FROM Sellers, Customers WHERE Sellers.city = Customers.city AND Sellers.snum = Customers.snum; SELECT s.sname, s.city, c.cname FROM Sellers s INNER JOIN Customers c ON s.snum = c.snum WHERE s.city = c.city;

З’єднання таблиці з собою Приклад 6. Знайти усі пари замовників, які мають однаковий рейтинг. Рішення 1 (з дублюванням): SELECT c1.cname, c2.cname, c1.rating FROM Customers c1 JOIN Customers c2 ON c1.rating = c2.rating;

Перехресне з’єднання (CROSS JOIN) Відповідає операції розширеного декартового добутку, тобто операції з’єднання двох таблиць, при якому кожний запис першої таблиці з’єднується з кожним записом другої таблиці. У деяких СУБД не використовується (В MySQL використовується). SELECT <список стовпців> FROM Таблиця1, Таблиця2; Або SELECT <список стовпців> FROM Таблиця1 CROSS JOIN Таблиця2;

Зовнішні з’єднання LEFT OUTER JOIN – ліве з’єднання. RIGHT OUTER JOIN – праве з’єднання. FULL JOIN – повне з’єднання. UNION JOIN – об’єднане з’єднання.

Відмінність внутрішнього і зовнішнього з’єднання При внутрішньому з’єднанні відкидаються усі записи, для яких немає відповідних записів одночасно в обох таблицях. При зовнішньому з’єднанні такі невідповідні записи повинні залишатись.

Ліве з’єднання (LEFT OUTER JOIN) При лівому зовнішньому з’єднанні невідповідні записи, які є в лівій таблиці (від оператора JOIN), зберігаються в результатній таблиці, а ті, які є в правій таблиці – відкидаються.

Приклад 7. Переглянути всіх продавців разом із їх операціями купівлі-продажу, які вони обслуговували 3 жовтня 2009 року. Приклад 7. Переглянути всіх продавців разом із їх операціями купівлі-продажу, які вони обслуговували 3 жовтня 2009 року. SELECT s.sname, o.onum FROM Sellers s LEFT JOIN Orders o ON o.odate = '2009-10-03' AND s.snum = o.snum;

Альтернативний варіант LEFT JOIN: Альтернативний варіант LEFT JOIN: SELECT s.sname, o.onum FROM Sellers s, Orders o WHERE o.odate = '2009-10-03' AND s.snum = o.snum UNION SELECT s.sname, NULL FROM Sellers s WHERE s.sname NOT IN (SELECT s.sname FROM Sellers s, Orders o WHERE o.odate = '2009-10-03' AND s.snum = o.snum);

Праве з’єднання (RIGHT OUTER JOIN) При правому зовнішньому з’єднанні невідповідні записи, які є у правій таблиці, зберігаються в результатній таблиці, а ті, які є в лівій таблиці – відкидаються.

Приклад 8. Переглянути всіх клієнтів разом з продавцями, які їх обслуговують, а також відобразити продавців які не мають клієнтів. Приклад 8. Переглянути всіх клієнтів разом з продавцями, які їх обслуговують, а також відобразити продавців які не мають клієнтів. SELECT c.cnum, s.sname FROM customers c RIGHT JOIN sellers s ON c.snum = s.snum;

Повне з’єднання (FULL JOIN) Повне з'єднання є комбінацією лівого і правого з'єднань. Воно показує всі рядки з обох таблиць: за наявності збігів – з'єднані, в іншому випадку – з NULL-значеннями в стовпцях з іншої таблиці. В MySQL немає оператора FULL JOIN! Для використання FULL JOIN в MySQL потрібно об'єднати два однакові запити із LEFT JOIN та RIGHT JOIN за допомогою оператора UNION.

Об’єднане з’єднання (UNION JOIN) При об’єднаному з’єднанні створюється віртуальна таблиця, яка містить усі стовпці двох вихідних таблиць. При цьому стовпці з лівої вихідної таблиці містять усі свої записи, а в тих же записах в стовпцях з правої таблиці містяться значення NULL. Аналогічно, стовпці з правої таблиці містять усі свої записи, а ці ж записи з лівої таблиці містять NULL. Загальна кількість записів, які містяться в результатній таблиці рівна сумі кількості записів, які є в обох вихідних таблицях. Як правило, результат об’єднаного з’єднання розглядається в якості проміжного при виконанні більш складного запиту. В MySQL немає оператора UNION JOIN!

Теоретико-множинні операції в SQL UNION – об’єднання наборів записів. INTERSECT – перетин наборів записів. EXEPT – віднімання наборів записів.

Об’єднання наборів записів (UNION) Запит1 UNION Запит2; До набору рядків, які виводяться одним запитом додаються рядки, які виводяться другим запитом. Об’єднуються рядки двох чи більше таблиць з подібними структурами в одну таблицю. При об’єднанні в результатній таблиці залишаються лише різні рядки. Щоб зберегти в результатній таблиці усі рядки, необхідно написати UNION ALL. Коли використовується об’єднання більш ніж двох запитів, можна використовувати дужки для визначення порядку запитів.

Правила використання оператора UNION При об’єднанні рядків двох таблиць їх стовпці повинні бути сумісними. Це означає, що кожний запит повинен вказувати однакову кількість стовпців і в однаковому порядку. Типи полів повинні бути теж сумісні. Однак, імена відповідних стовпців та їх розміри можуть бути різними. Щоб об’єднати рядки таблиць з несумісними (по типу даних) стовпцями, необхідно застосувати функцію перетворення типу даних CAST().

Упорядкування об’єднання наборів записів Використовується фраза: ORDER BY <ім'я стовпця> Можна упорядковувати об’єднання за декількома полями, одне всередині іншого, і вказати ASC або DESC для кожного.

Перетин наборів записів (INTERSECT) Запит1 INTERSECT Запит2; Перетин наборів рядків повертає таблицю, рядки якої містяться одночасно у двох наборах. При перетині в результатній таблиці залишаються лише різні записи. Щоб зберегти в ній усі записи, необхідно написати INTERSECT ALL. В MySQL немає оператора INTERSECT!

Віднімання наборів записів (EXEPT) Запит1 EXEPT Запит2; Віднімання наборів рядків повертає таблицю, рядки якої містяться в одному наборі за виключенням тих, які містяться в другому наборі. В MySQL немає оператора EXEPT!

Завдання 1. Написати багатотабличні запити для власної бази даних Написати 2 запити із корельованими підзапитами. Перевірити 1 пару таблиць на непорожні значення зовнішніх ключів (оператори LEFT JOIN або RIGHT JOIN). Написати 1 запит із аналогом INTERSECT або EXEPT. Написати декілька запитів для внутрішнього з'єднання таблиць (мінімум 4).