Полное, левое и правое внешнее соединение таблиц
Часто пользователям информационных систем необходимы данные, содержащиеся в кортежах, для которых не выполняются условия соединения • Для получения такой информации применяются различные модификации операции внешнего соединения. • Пример. Созданы две базы данных: • БД 1 npi содержит информацию о преподавателях, работающих в ЮРГТУ (НПИ); • БД 2 nimi хранит данные о преподавателях Новочеркасской государственной мелиоративной академии (НГМА).
Сформировать отчет, в котором попарно выдаются фамилии преподавателей ЮРГТУ (НПИ) и НГМА, которые зарегистрированы в одном и том же городе. • Очевидно, что отчет, использующий традиционную операцию внутреннего соединения, будет представлен следующим образом
• Внешнее соединение – операция, в результате выполнения которой в таблицу передаются кортежи, которые не были соединены при реализации операции внутреннего соединения. Различают: левое внешнее соединение, правое внешнее соединение и полное внешнее соединение
Полное внешнее соединение включает выполнение следующих трех этапов. • Этап 1. В результирующей таблице формируются кортежи, удовлетворяющие правилам для внутреннего соединения. • Этап 2. В результирующей таблице слева указываются кортежи первой таблицы, которые на первом этапе не были соединены с кортежами второго отношения. Причинами несоединения являются: • неопределенность значений атрибутов первой таблицы, по которым производится соединение; • отсутствие кортежей во второй таблице, удовлетворяющих условию соединения. В частности, для эквисоединения – отсутствие во второй таблице значений атрибута, совпадающих со значением атрибутасоединения из первой таблицы. • Справа включенные в результирующую таблицу атрибуты дополняются неопределенными значениями NULL. • Этап 3. В результирующей таблице справа указываются кортежи второй таблицы, которые на первом этапе не были соединены с кортежами первой
представлены результаты выполнения операций внутреннего, левого, правого и полного внешних соединений • Внутреннее соединение – результат этапа 1, левое внешнее соединение – объединение результатов этапа 1 и этапа 2; правое внешнее соединение – объединение результатов этапа 1 и этапа 3, полное внешнее соединение – объединение этапов 1, 2 и 3.
В стандарте SQL 2 предложен простой способ задания различных видов соединения таблиц Предложение FROM имеет следующий вид: для внутреннего соединения таблиц FROM <таблица 1> INNER JOIN <таблица 2> ON <условие>; для полного внешнего соединения FROM <таблица 1> FULL OUTER JOIN <таблица 2> ON <условие>; для левого внешнего соединения FROM <таблица 1> LEFT OUTER JOIN <таблица 2> ON <условие>; для правого внешнего соединения FROM <таблица 1> RIGHT OUTER JOIN <таблица 2> ON <условие>, • где <таблица 1> и <таблица 2> - это исходные таблицы; <условие> - предназначено для задания любых критериев сравнения строк двух соединяемых таблиц. • • •
сформулировать отчет о распределении товаров по поставщикам: SELECT t. prod_id, t. prod_naim, s. prois_id, s. prois_name FROM proiswod s INNER JOIN product t ON s. prois_id = t. prois_id ORDER BY s. prois_name ASC; формирование отчета о товарах, поставленных производителями из Южного Федерального округа или производителями, которые не зарегистрированы в двух столицах России, представляется следующим образом: SELECT t. prod_id, t. prod_naim, s. city, s. region FROM proiswod s INNER JOIN product t ON s. prois_id = t. prois_id WHERE s. region = ‘ЮФО’ OR s. city NOT IN (‘Москва’, ‘Санкт-Петербург’) ORDER BY t. prod_id;
Запрос, предназначенный для формирования отчета, в котором для каждого производителя указывается количество поставляемых им товаров: • • • SELECT s. prois_id, COUNT (t. prod_id) AS COLPR FROM proiswod s INNER JOIN product t ON s. prois_id = t. prois_id GROUP BY s. prois_id ORDER BY s. prois_id; • Запрос, позволяющий выдать перечень товаров, доход от продажи которых превышает сумму предоплаты не меньше, чем в 10 раз, представляется следующим образом: SELECT t. prod_id, t. prod_naim, c. advance, t. price*t. sales AS DCH FROM product t INNER JOIN oplata c ON t. price*sales>c. advance*10 AND t. prod_id=c. prod_id ORDER BY t. price*sales DESC; • •
пример использования левого внешнего соединения • • • выдача данных о количестве товаров, поставленных каждым производителями, в том числе и выдачи информации о производителях, которые не поставили ни одного типа товара: SELECT s. prois_id, COUNT (t. prod_id) AS COLPR FROM proiswod s LEFT OUTER JOIN product t ON s. prois_id = t. prois_id GROUP BY s. prois_id; В качестве примера использования полного внешнего соединения приводится запрос, предназначенный для формирования отчета о всех производителях и основных складах продукции, независимо от совпадения города, в котором зарегистрирован производитель, и города, в котором размещен основной склад: • • • SELECT s. prois_name, t. prod_naim FROM proiswod s LEFT OUTER JOIN product t ON s. city = t. city ORDER BY s. prois_name ASC, t. prod_naim ASC; • •
Вложенные запросы в языке SQL • Вложенные запросы имеют базовую структуру
Основные требования к подзапросу: • подзапрос может применяться в следующих предложениях главного запроса SELECT, WHERE , HAVING и FROM; • подзапрос обязательно заключается в круглые скобки; • подзапрос не может содержать предложение сортировки данных ORDER BY. В главном запросе предложение ORDER BY должно быть указано последним; • подзапрос должен быть указан справа от оператора сравнения ; • подзапрос может использовать столбцы таблиц, которые приводятся в предложении FROM самого подзапроса либо другого подзапроса; • столбцы таблицы, которая указана только в подзапросе, не могут быть включены в результирующий отчет, то есть не могут содержаться в предложении SELECT главного запроса. • Подзапросы делятся на однострочные, многострочные и многостолбцовые.
Однострочный подзапрос в SELECT-списке содержит один столбец и результатом его выполнения является одно значение. • Из отношения sotr_5 необходимо получить фамилии сотрудников, у которых оклад больше, чем у сотрудника с кодом 101:
. Из отношения sotr_5 получить фамилии сотрудников, у которых нагрузка больше, чем у сотрудника с кодом 200, и которые были приняты на работу до образования кафедры АСУ: SELECT fio, unag, datap FROM sotr_5 WHERE unag > (SELECT unag FROM sotr_5 WHERE cod = 200) AND datap < (SELECT god FROM kafedra_1 WHERE naim_kaf = ‘АСУ’); • Из отношения sotr_5 получить наименование подразделения, сотрудникам которых назначен минимальный средний оклад: • • • SELECT pod, AVG (oklad) FROM sotr_5 GROUP BY pod HAVING AVG (oklad) = (SELECT MIN (AVG (oklad)) FROM sotr_5 GROUP BY pod);
Многостолбцовые вложенные запросы содержат более одного атрибута в SELECT-списке подзапроса. • Такое же количество атрибутов должно быть указано в предложении WHERE главного запроса. Обязательным является попарное совпадение типа и размера данных для атрибутов предложения WHERE главного запроса и SELECT-списка подзапроса. Рассматриваемые запросы имеют следующий синтаксис: • SELECT <атрибут 1>, <атрибут 2> • FROM <таблица 1> • WHERE (<атрибут 3>, <атрибут 4>) IN • SELECT <атрибут 5>, <атрибут 6> • FROM <таблица 2> • WHERE (<условие>);
• Созданы отношения kafedra_1 и sotr_6. Отношение sotr_6 в сравнении с отношением sotr_5 дополнительно содержит атрибут должность dol. Найти кафедры, у которых нагрузка заведующего кафедрой совпадает с учебной нагрузкой хотя бы одного доцента этой же кафедры: • SELECT naim_kaf, fio_sav, nag_sav, god • FROM kafedra_1 • WHERE (naim_kaf, nag_sav) IN • (SELECT pod, unag • FROM sotr_6 • WHERE dol = ‘ДОЦЕНТ’);
Скачать презентацию Полное левое и правое внешнее соединение таблиц
Язык_SQL_4.pptx