Запити з операціями з’єднання Таблиць

Операції з’єднання двох Таблиць завжди вказуються у реченні FROM, іншими словами, джерела для формування виборок із бази даних вказуються у обов’язковому реченні FROM.

У реченні FROM вказується імена Таблиць чи запитів з яких обираються дані. Розглянемо речення FROM детальніше.

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

SELECT <списокПолів>

FROM <выражение> [IN внешняяБазаДаних]

Значення елементів синтаксису вказані у вигляді наступної таблиці:

Елемент	Значення
<списокПолів>	Імена одного чи кількох полів, з яких відбираються дані, разом з псевдонімами, статистичними функціями SQL, предикатами відбору (ALL, DISTINCT, DISTINCTROW або TOP) та іншими елементами інструкції SELECT.
<выражение>	Вираз, який визначає одну або кілька Таблиць, з яких вибирають дані. Такий вираз може бути ім’ям окремої таблиці, або запиту чи результатом операції з’єднання: INNER JOIN, LEFT JOIN, або RIGHT JOIN.
внешняяБазаДннных	Повне ім’я зовнішньої бази даних, що має таблиці, вказані у аргументі < выражение>.

Як зазначали раніше, речення FROM повинно бути завжди у інструкції SELECТ, порядок слідування імен Таблиць в аргументі < выражение> - не є суттєвим. Структуру результуючої таблиці формує речення:

SELECT <списокПолів >;

У елементі < списокПолів> перераховуються поля Таблиць (джерел даних) або виразів над полями і речення у загальному записі має вигляд:

SELECT [предикат] { * | Таблиця.*| [Таблиця.] поле1 [ AS псевдонім1] [Таблиця.] поле1 [ AS псевдонім 1], [Таблиця.] поле2 [AS псевдонім2] [, …] ] }

(5.2.1)

Предикат управляє відображенням даних у вихідній структурі і може приймати одне із вказаних нижче значень:

ALL	Відбір усіх записів згідно з умовами;
DISTINCT	Відбір записів без повторень значень поля;
DISTINCTROW	Відбір записів без повторень у повному складі полів;
TOP n	Відбір перших n записів із усіх записів, що відповідають умовам. Використовується для підготовки рейтингових Таблиць.

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

Розглянемо загальний вигляд речення FROM:

FROM <таблиця1>
[INNER]
│ {{LEFT │RIGHT│FULL }[OUTER]} JOIN < таблиця2> [ON < предикат >]	(5.2.2)

Зв’язок Таблиць описується за допомогою оператора JOIN. Зв’язок Таблиць (з’єднання) може мати наступні різновиди:

Внутрішнє (симетричне) з’єднання	INNER;
Зовнішнє з’єднання - OUTER, яке має два типи - LEFT JOIN та RIGHT JOIN
LEFT JOIN	зовнішнє об’єднання зліва
RIGHT JOIN	зовнішнє об’єднання праворуч.

Службові слова INNER та OUTER можна пропускати у реченні FROM, оскільки зовнішнє об’єднання однозначно визначається типом (ліве, праве з’єднання).

Предикат у синтаксисі речення FROM визначає умови об’єднання рядків із різних Таблиць. При цьому INNER JOIN означає, що в результуючу таблицю попадуть лише ті з’єднання рядків двох Таблиць, для яких значення предиката дорівнює TRUE.

Операції з’єднання рекомендують використовувати для підвищення швидкодії вибору даних із бази даних

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

$ Визначити для одержання якої інформації призначено наступний запит:

SELECT Контролер.[Код контролера], Контролер.Пр, [Довідник несправностей].Назва_несправності

FROM Контролер LEFT JOIN [Довідник несправностей] ON Контролер.[Код контролера] = [Довідник несправностей].Контролер

WHERE ((([Довідник несправностей].Назва_несправності) Is Null));

· Для контролю яких ситуацій може використовуватись вказаний запит?

· Що потрібно змінити у даному запиті, щоб результуюча Таблиця мала наступний вигляд:

Код контролера	Контролер
	Романідзе С.

Приклад 2. Розглянемо задачу підрахунку вартості проведених тестувань для кожного виду несправностей, виявлених під час тестувань. При цьому врахувати:

1. До подачі даних у результуючій таблиці можуть бути різні вимоги, які визначаютьсяформою вихідного документа.

2. Форма документа 1. Якщо під час тестувань несправність не була виявлена, то потрібно задати розрахункові поля Вартість та Кількість рівними 0. Результуючу таблицю упорядкувати за спаданням Вартості тестувань.

3. Форма документа 2 Оплата праці за тестування, у яких не було виявлено несправностей певного типу оплачується за іншими ставками і в даній задачі не розглядається. У результуючу таблицю включити лише дані про виявлені несправності.

4. Форма документа 3. У результуючу таблицю включити перші 4 найбільш дорогі (з точки зору оплати тестування) несправності, що були виявлені під час тестування.

Вирішення прикладу 2. Вказана задача має не один варіант вирішення. Розглянемо два варіанти із можливих:

1-й варіант	Використання допоміжного запиту
2-й варіант	Використання об’єднання Таблиць

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

· розробити допоміжний запит,

· використати допоміжний запит для розрахунку вартості тестування відповідно до форми вихідногодокумента.

a) Спочатку створимо допоміжний запит. Нагадаємо, що у базі даних “Тестування авто” (див. Додаток А) не зберігається кількість проведених тестувань, тому у допоміжному запиті знайдемо число виявлених несправностей кожного виду. Цей запит розглядався у підрозділі 5.1.6, але для зручності читання надамо відповідну SQL-інструкцію:

SELECT DISTINCTROW [Результати тестування].[Код_несправності#], Count(*) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#]

ORDER BY Count(*) DESC;

b) Далі розробимо запити для звітності про вартість проведених тестувань залежно від форми вихідного документа.

Форма1 вихідного документа. (Включення у результуючу таблицю усіх несправностей). SQL-інструкція може бути записана наступним чином:

SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Код_несправності#], IIf([Довідник несправностей]![Код_несправності#]=[Допоміжний запит]![Код_несправності#],[Допоміжний запит]![Кількість],0) AS Кількість, [Довідник несправностей].[Ціна тестування], IIf([Довідник несправностей]![Код_несправності#]=[Допоміжний запит]![Код_несправності#],[Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування],0) AS Вартість

FROM [Довідник несправностей] LEFT JOIN Допоміжний запит ON [Довідник несправностей].[Код_несправності#] = Допоміжний запит.[Код_несправності#]

ORDER BY IIf([Довідник несправностей]![Код_несправності#]=[Допоміжний запит]![Код_несправності#],[Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування],0) DESC;

Пояснення:

1. У допоміжний запит, побудований на групуванні даних по проведеним тестування, фактично не можуть бути включені дані про несправності, що не виявлялись у процесі тестувння авто. Але Форма 1 вимагає включення відомостей про усі несправності, які можуть бути виявлені, а можуть і не бути протестовані. Тобто із таблиці Несправності маємо вибирати усі записи. Із сказаного слідує, що Таблиця, яка динамічно будується Допоміжним запитом є зовнішньою для таблиці Несправності. Відповідність записів Таблиць встановлюється так, що об’єднуються записи таблиці Несправності лишез тими записами таблиці Допоміжний запит, в яких зв’язані поля Код несправності співпадають. Якщо у процесі обробки даних таблиці Несправності відібрано код несправності, яка не тестувалась, (зв’язані поля не можуть співпасти) то у нас відсутні дані у таблиці Допоміжний запит і ми немає можливості доповнити дані про поточну несправність відповідними даними з таблиці Допоміжний запит. По замовчуванню такі поля залишаються порожнім (NULL). Cказане свідчить про те, що при розробці Форми1, ми маємо організувати зовнішнє об’єднання зліва, а саме так:

2. Щоб замість значення NULL в результуючій таблиці записати у полі Кількість значення 0, використовуємо вбудовану функцію виконання дії за умовою (функція IIf):

IIf([Довідник несправностей]![Код_несправності#]=[Допоміжний запит]![Код_несправності#],[Допоміжний запит]![Кількість],0) AS Кількість

3. Щоб розрахувати вартість тестування несправностей, у реченні SELECT включаємо вбудовану функцію

IIf([Довідник несправностей]![Код_несправності#]=[Допоміжний запит]![Код_несправності#],[Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування],0) AS Вартість

4. Щоб результуюча Таблиця була відсортована за спаданням вартості тестування необхідно вказати умову упорядкування як:

Результуюча Таблиця вихідного документа за Формою 1. буде мати вигляд:

Назва_несправності	Код_несправності#	Кількість	Ціна тестування	Вартість
Збільшення витрат масла
Припинення іскроутворення
Порушення загорання
Перебої роботи свічей
Порушення сумішестворювання
Збільшення витрат палива
Збільшення/зменшення тиску масла
Стукіт при роботі двигуна
Збільшення витрат охолоджувальної рідини
Переохолодження двигуна

Форма 2 вихідного документа. Результуючу таблицю відповідно до форми віхідного документа 2 можна побудувати наступним чином, використовуючі можливості мови SQL:

SELECT [Довідник несправностей].[Код_несправності#], [Довідник несправностей].[Ціна тестування], [Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування] AS Вартість

FROM [Довідник несправностей] RIGHT JOIN Допоміжний запит ON [Довідник несправностей].[Код_несправності#] = Допоміжний запит.[Код_несправності#];

Пояснення:

¨ При розробці Форми 2, об’єднуються записи таблиці Допоміжний запит і лише ті записи із Довідника несправностей, у яких зв’язані поля співпадають, отже записи із Довідника несправностей, що стосуються несправностей, які не зустрічались під час тестування авто, навіть не розглядаються, а пропускаються при формуванні результуючої таблиці. У даному випадку речення FROM має вигляд:

2. Для проведення розрахунків будуємо вираз у реченні SELECT:

[Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування] AS Вартість

Результуюча Таблиця за формою 2 має вигляд:

Код_несправності#	Ціна тестування	Вартість

Форма3 вихідного документа. Результуючу таблицю відповідно до форми віхідного документа 3 можна побудувати наступним чином, використовуючі можливості мови SQL:

SELECT TOP 4

[Довідник несправностей].[Код_несправності#], [Довідник несправностей].[Ціна тестування], [Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування] AS Вартість

FROM [Довідник несправностей] RIGHT JOIN Допоміжний запит ON [Довідник несправностей].[Код_несправності#] = Допоміжний запит.[Код_несправності#]

ORDER BY [Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування] DESC;

Пояснення:

Фактично даний запит побудовано шляхом модифікації попереднього запиту:

¨ Згідно з (5.2.1.) щоб із усіх запитів, які виводяться із бази даних у результуючу таблицю, відібрати перші чотири записи, потрібно після ключового слова SELECT ввести предикат TOP 4;

¨ Якщо потрібно відібрати чотири несправності, тестування яких є найбільш дорогими, то необхідно відсортувати записи за вартістю:

ORDER BY [Допоміжний запит]![Кількість]*[Довідник несправностей]![Ціна тестування] DESC;

¨ За допомогою предиката.

SELECT TOP 4

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

Код_несправності#	Ціна	Вартість

Варіант 2.

Форма 1. Вказаний варіант 2 для Форми 1 вихідногодокумента пропонує побудувати результуючу таблицю так, щоб у випадку, коли серед протестованих авто не було жодного з поточною несправністю, то вказана несправність має бути включена у результуючу таблицю з 0-ми значеннями полів Кількість та Вартість.

Результуючу таблицю відповідно до варіанта 2 розробки вихідного документа можна побудувати наступним чином, з врахуванняи можливостей мови SQL:

SELECT [Довідник несправностей].Назва_несправності, Count([Результати тестування].[Код_несправності#]) AS Кількість, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#], Count([Результати тестування]![Код_несправності#])*([Довідник несправностей]![Ціна тестування]) AS [Вартість тестування]

FROM [Довідник несправностей] LEFT JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

GROUP BY [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#]

ORDER BY [Довідник несправностей].[Код_несправності#];

Результуюча Таблиця має вигляд:

Таблиця 5.1.

Вартості тестувань

Назва_несправності	Кількість	Довідник несправностей.Код_несправності#	Контролер	Ціна тестування	Код несправності#	Вартість тестування
Стукіт при роботі двигуна			Петринчук
Припинення іскроутворення			Сидоренко
Збільшення витрат масла			Іванчук
Перебої роботи свічей			Іванчук
Порушення загорання			Ветров
Збільшення витрат палива			Иванов
Порушення сумішестворювання			Іванчук
Переохолодження двигуна			Перова
Збільшення витрат охолоджувальної рідини			Сидоренко
Збільшення/зменшення тиску масла			Иванов

Пояснення:

¨ Дані у записи одержаної таблиці обираються із двох Таблиць – Довідник несправностей та Результати тестування. Ці таблиці входять у структуру бази даних Тест, мають різне число записів (10 та 22 відповідно), упорядковані за значеннями поля Код_несправності#.

¨ Поле Довідник несправностей. Код_несправності# має 10 унікальних значень. Поле Результати тестування. Код_несправності# має неунікальні значення (деякі із значень можуть бути відсутніми, а деякі значення повторюються у кількох різних записах) Потрібно встановити закон відповідності вибору записів із вказаних Таблиць, так щоб можна було сформувати записи результуючої таблиці так, щоб вони вміщували достовірну інформацію.

У SQL-інструкції правило відповідності записів для вказаного прикладу записується наступним чином:

У результуючу таблицю включені два поля, у яких вказано значення поля [Довідник несправностей].[Код_несправності#] та відповаідне значення поля [Результати тестування].[Код_несправності#] Як бачимо, записи із лівої таблиці вибираються усі.

Якщо поточнезначення поля [Довідник несправностей].[Код_несправності#] відсутнє у зв’язаній таблиці, то у результуючій таблиці вказується порожнє значення, яке відповідає невизначеному значенню предиката

[Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

У той же час поле Кількість приймає значення 0, у разі відсутності потрібних даних у зв’язаній таблиці. Це пояснюється тим, що поле Кількість розраховується за агрегованою функцією Count, яка має тип даних - Числовой, ціле число. Аналогічно вибираються дані для розрахункового поля Вартість.