|
|
Глава 10. Реляционные операторы В этой главе...
SQL — это язык запросов, используемый в реляционных базах данных. Почти во всех примерах предыдущих глав рассматривались простые базы данных с одной таблицей. Теперь настало время показать, в чем же состоит реляционность реляционной базы. Вообще говоря, эти базы называются "реляционными" потому, что состоят из множества связанных друг с другом таблиц (а "связанные друг с другом" — это по-английски "related"). Так как данные, хранящиеся в реляционной базе, распределены по множеству таблиц, то запрос обычно извлекает данные из более чем одной таблицы. В SQL:2OO3 имеются операторы, которые объединяют данные из множества исходных таблиц в одну. Это операторы UNION, INTERSECTION и EXCEPT, а также семейство операторов объединения JOIN. Причем каждый из них объединяет данные своим особым способом. UNION Оператор UNION (объединение) — это реализация в языке SQL оператора объединения из реляционной алгебры. Оператор UNION дает возможность получать информацию из нескольких таблиц, имеющих одинаковую структуру. Одинаковая структура означает следующее.
При соблюдении этих критериев таблицы являются совместимыми для объединения. В результате объединения двух таблиц возвращаются все строки, имеющиеся в каждой из них. Не возвращаются только повторяющиеся строки. Скажем, вы создаете базу данных по бейсбольной статистике (см. главу 9). Она состоит из двух таблиц, совместимых для объединения, которые называются AMERICAN (Американская лига) и NATIONAL (Национальная лига). В каждой из них имеются по три столбца, и типы у всех соответствующих столбцов совпадают. На самом деле даже имена у таких столбцов одинаковые, хотя для объединения это условие не является обязательным.
Оператор UNION DISTINCT работает так же, как и оператор UNION без ключевого слова DISTINCT. В обоих случаях дублирующие строки удаляются из конечной совокупности. Внимание: Звездочка ('*') используется для обозначения всех столбцов, имеющихся в таблице. Это сокращенное обозначение работает в большинстве случаев прекрасно, но если реляционные операторы используются во встроенном или модульном коде SQL, то это обозначение может доставить массу неприятностей. Что если в одну из таблиц или сразу во все будут добавлены дополнительные столбцы? Тогда эти таблицы больше не будут совместимыми для объединения, и программа перестанет работать. И даже если во все таблицы, для обеспечения совместимости по операции объединения, будут добавлены одни и те же столбцы, то программа скорее всего не будет готова работать с этими дополнительными данными. Таким образом, лучше явно перечислять нужные столбцы, а не полагаться на сокращение '*'. Но при вводе с консоли "одноразовой" команды SQL звездочка работает прекрасно. Если вдруг запрос не сработает, всегда можно быстро вывести структуру таблицы. Операция UNION обычно убирает любые повторяющиеся строки, которые появляются в результате ее выполнения. В большинстве случаев это желаемый результат. Впрочем, иногда повторяющиеся строки требуется сохранять. В таких случаях используйте UNION ALL (объединение всех). Обратимся к нашему примеру и предположим, что Боб Тарли по кличке "Буллит" ("пуля") был "продан" в середине сезона из команды "Нью-Йорк Янкиз", входящей в Американскую лигу, в "Бруклин Доджерс" из Национальной лиги. А теперь предположим, что в каждой команде за сезон у этого питчера было по восемь игр, в течение которых он бессменно подавал мяч. Обычный оператор UNION, показанный в примере, отбросит одну из двух строк с данными об этом игроке. И хотя будет казаться, что за сезон он полностью провел на подаче мяча только восемь игр, но ведь на самом деле таких игр — замечательный результат — было 16. Корректные данные можно получить с помощью следующего запроса: SELECT * FROM NATIONAL UNION ALL SELECT * FROM AMERICAN ; Иногда оператор UNION можно применять и к двум таблицам, которые не являются совместимыми для объединения. Если в таблицу, которая должна получиться, войдут столбцы, имеющиеся в обеих исходных таблицах и являющиеся совместимыми, то можно использовать оператор UNION CORRESPONDING (объединение соответствующих). В этом случае учитываются только указанные столбцы, и только они войдут в получившуюся таблицу. Полностью отличаются друг от друга данные, которые бейсбольные статистики собирают по питчерам и игрокам в дальней части поля. Однако каждый раз и в том и в другом случае записываются имя (first name), фамилия (last name) игрока, выходы на поле (putouts), ошибки (errors) и доля принятых мячей (fielding percentage). Конечно, по игрокам в дальней части поля не собирают данные о выигрышах/проигрышах (won/lost record), остановленных мячах (saves) или другие сведения, относящиеся только к подаче мяча. Но все равно, чтобы получить некоторую общую информацию об умении играть в защите, можно выполнять оператор UNION, который будет брать данные из двух таблиц — OUTFIELDER (игрок в дальней части поля) и PITCHER (питчер): SELECT * FROM OUTFIELDER UNION CORRESPONDING (FirstName, LastName, Putouts, Errors, FieldPct) SELECT * FROM PITCHER ; В результате получается таблица, где для каждого питчера или игрока в дальней части поля указаны имя и фамилия, а также количество выходов на поле, ошибок и доля принятых мячей. Здесь, как и при использовании простого оператора UNION, повторяющиеся строки удалены. Таким образом, если игрок некоторое время играл в дальней части поля, а также был питчером, то при выполнении оператора UNION CORRESPONDING часть статистики этого игрока будет потеряна. Чтобы этого не случилось, используйте UNION ALL CORRESPONDING (объединение всех соответствующих). Совет: В списке, находящемся сразу за ключевым словом CORRESPONDING (соответствующие), должны быть только те имена столбцов, которые имеются во всех объединяемых таблицах. Если этот список имен будет пропущен, то будет неявно использован полный список имен. Однако, если в одну или несколько таблиц будут добавлены новые столбцы, этот неявный список может измениться. Так что имена столбцов лучше не пропускать, а указывать явно. INTERSECT В результате выполнения оператора UNION создается таблица, где появляются все строки, которые могут находиться в какой-либо из исходных таблиц. А если нужны только те строки, каждая из которых находится одновременно во всех исходных таблицах, то можно использовать оператор INTERSECT (пересечь). Он является реализацией в SQL оператора пересечения из реляционной алгебры. Выполнение INTERSECT будет показано на примере из воображаемого мира, в котором Боб Тарли был в середине сезона "продан" команде "Доджерс".
В таблице, полученной в результате выполнения оператора INTERSECT, будут показаны только те строки, которые находятся одновременно во всех исходных таблицах: SELECT * FROM NATIONAL INTERSECT SELECT * FROM AMERICAN;
В полученной таким образом таблице сообщается, что Боб Тарли был единственным питчером, который и в той и в другой лиге бессменно подавал мяч в течение одного и того же количества игр. Обратите внимание, что, как и в случае с UNION, INTERSECT DISTINCT выдает тот же результат, что и оператор INTERSECT, используемый без ключевого слова. В этом примере возвращается только одна строка с именем Боба Тарли. Роль ключевых слов ALL и CORRESPONDING в операторе INTERSECT такая же, как и в операторе UNION. Если используется ALL, то получится таблица, в которой повторяющиеся строки остаются. А когда используется CORRESPONDING, то исходные таблицы не обязательно должны быть совместимыми для объединения, хотя у соответствующих столбцов должны быть одинаковые тип и длина. Проанализируем следующий пример. В муниципалитете хранят данные о пейджерах, используемых полицейскими, пожарниками, уборщиками улиц и другими работниками городского хозяйства. Данные обо всех ныне используемых пейджерах находятся в таблице PAGERS (пейджеры) из базы данных. А данные обо всех пейджерах, которыми по той или иной причине не пользуются, находятся в другой таблице, OUT (вышедший из строя), имеющей такую же структуру, что и PAGERS. Информация ни по одному из пейджеров не может одновременно быть в двух таблицах. Выполнив оператор INTERSECT, можно проверить, не произошло ли такое ненужное дублирование строк: SELECT * FROM PAGERS INTERSECT CORRESPONDING (PagerlD) SELECT * FROM OUT ; В результате появляется таблица, и если в ней будут находиться какие-либо строки, то это будет означать, что база данных обновлена некорректно. Необходимо проверить все значения, которые появляются в этой таблице в столбце PagerlD (идентификатор пейджера). Ведь пейджер, соответствующий этому идентификатору, либо используется, либо не работает. Одновременно и того и другого не бывает. Обнаружив противоречивые данные, можно провести работы по восстановлению целостности базы данных — выполнить в одной из двух таблиц операцию DELETE (удалить). EXCEPT Оператор UNION выполняется с двумя таблицами и возвращает все строки, которые имеются как минимум в одной из них. Другой же оператор, INTERSECT, возвращает все те строки, которые имеются одновременно в первой и второй таблицах. А оператор EXCEPT (за исключением), наоборот, возвращает все строки, которые имеются в первой таблице, но не имеются во второй. Теперь вернемся к примеру с базой данных, в которой находится информация о муниципальных пейджерах. Скажем, группа пейджеров, объявленных неработающими, была возвращена поставщику для ремонта, но к настоящему времени эти пейджеры уже исправлены и используются снова. В таблицу PAGERS данные о возвращенных пейджерах уже занесены, но из таблицы OUT их данные по некоторой причине не удалены, хотя это надо было сделать. С помощью оператора EXCEPT можно вывести все номера пейджеров, находящиеся в столбце PagerlD таблицы OUT, за исключением тех номеров, которые принадлежат уже исправленным пейджерам: SELECT * FROM OUT EXCEPT CORRESPONDING (PagerlD) SELECT * FROM PAGERS; При выполнении этого запроса возвращаются все строки из таблицы OUT, у которых значения PageiTD не находятся в таблице PAGERS. Операторы объединения Операторы UNION, INTERSECT и EXCEPT представляют ценность в тех многотабличных базах данных, таблицы которых являются совместимыми. Однако во многих случаях приходится брать данные из наборов таблиц, имеющих между собой мало общего. Мощными реляционными операторами являются операторы объединения JOIN, в результате выполнения которых данные, взятые из множества таблиц, объединяются в одну. Таблицы из этого множества могут иметь мало общего друг с другом. Стандарт SQL:2003 поддерживает разные типы операторов объединения. Какой из них лучше всего подходит в конкретной ситуации — это зависит от того результата, который требуется получить. Простой оператор объединения Любой многотабличный запрос является оператором объединения. Исходные таблицы объединяются в том смысле, что в таблице, полученной в результате этого объединения, будет находиться информация из всех исходных таблиц. Самым простым оператором объединения является оператор SELECT, выполняемый с двумя таблицами и не имеющий никаких ограничителей из предложения WHERE. Так что каждая строка из первой таблицы объединяется с каждой строкой из второй. В результате получается таблица, которая является декартовым произведением двух исходных таблиц. Понятие декартова произведения обсуждалось в главе 9 в связи с использованием предложения FROM. Количество строк в полученной таблице равно произведению числа строк в первой исходной таблице и числа строк во второй. Представьте, что вы, например, являетесь в какой-либо компании менеджером по персоналу и что часть вашей работы состоит в том, чтобы вести учет сотрудников. Большинство данных о сотруднике, таких, например, как домашний адрес и номер телефона, не являются особо засекреченными. Однако доступ к некоторым данным, таким, например, как зарплата, должен быть только утех, кто имеет соответствующее разрешение. Чтобы защитить секретную информацию, держите ее в отдельной таблице, имеющей парольную защиту. Посмотрите на следующие две таблицы:
Заполните таблицы какими-либо взятыми для примера данными.
Создайте виртуальную таблицу с помощью следующего запроса: SELECT * FROM EMPLOYEE, COMPENSATION ; Вот что вышло:
В получившейся таблице, представляющей собой декартово произведение таблиц EMPLOYEE и COMPENSATION, имеется значительный излишек данных. Кроме того, эта таблица не имеет большого смысла. В ней каждая строка из таблицы EMPLOYEE добавляется к каждой строке из таблицы COMPENSATION. Единственными строками в этой таблице, передающими содержательную информацию, являются те, в которых число из столбца EmpID, взятого из таблицы EMPLOYEE, равняется числу из столбца Employ, взятого из таблицы COMPENSATION. В этих строках имя, фамилия и адрес сотрудника объединены с выплатами того же сотрудника. Когда вы пытаетесь получить из множества таблиц полезную информацию, то декартово произведение, созданное с помощью простого объединения, почти никогда не бывает тем, что вам нужно. Впрочем, почти всегда первым шагом к тому, что вам нужно, бывает декартово произведение. Отфильтровывать из объединения ненужные строки можно с помощью ограничений, указываемых в предложении WHERE. Самым распространенным объединением, использующим фильтрующее предложение WHERE, является объединение, основанное на равенстве. Объединение, основанное на равенстве Объединение, основанное на равенстве, — это простое объединение с предложением WHERE, в котором находится условие, определяющее, что значение из одного столбца первой таблицы должно быть равно значению из соответствующего столбца второй таблицы. Если применить такое объединение к таблицам, имеющимся в примере из предыдущего раздела, то можно получить намного более содержательный результат: SELECT * FROM EMPLOYEE, COMPENSATION WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ; И вот что вышло:
В этой таблице зарплаты и премии, расположенные справа, прилагаются к данным о сотрудниках, находящимся слева. Впрочем, лишние данные есть и в этой таблице, так как столбец EmpID повторяет столбец Employ. Исправить этот недостаток можно, сформулировав запрос немного по-другому: SELECT EMPLOYEE.*, COMPENSATION.SALARY, COMPENSATION.Bonus FROM EMPLOYEE, COMPENSATION WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ; В результате получилось следующее:
Эта таблица сообщает вам то, что вы хотите знать, при этом не "нагружая" вас никакими лишними данными. Впрочем, писать сам запрос было несколько утомительно. Чтобы избежать двусмысленности, в именах столбцов приходилось явно указывать имена таблиц. Единственная выгода от этого — тренировка пальцев. Можно облегчить труд по вводу кода SQL, если использовать псевдонимы (или имена корреляции). Псевдоним — это другое, более короткое имя таблицы. Если переделать предыдущий запрос с помощью псевдонимов, то получится примерно следующее: SELECT Е.*, С.Salary, С.Bonus FROM EMPLOYEE E, COMPENSATION С WHERE E.EmpID = С.Employ ; В этом примере Е — это псевдоним для EMPLOYEE, а С — для COMPENSATION. Действие псевдонима ограничено только тем оператором, в котором он определен. После объявления псевдонима в предложении FROM его необходимо использовать в пределах оператора. При этом нельзя одновременно использовать и длинную форму имени таблицы, и псевдоним. Смешение полных имен с псевдонимами приводит к путанице. Проанализируйте следующий пример: SELECT T1.C, T2.С FROM Т1 Т2, Т2 Т1 WHERE T1.C > Т2.С ; В этом примере псевдонимом для Т1 является Т2, а для Т2 — Т1. Конечно, такой выбор неразумен, однако формально он не противоречит никаким правилам. Если допустить возможность совместного использования полных имен и псевдонимов, невозможно определить, о какой таблице идет речь. Предыдущий пример с псевдонимами эквивалентен следующему оператору SELECT без них: SELECT T2.C, T1.C FROM T1, T2 WHERE T2.С > T1.C ; Стандарт SQL:2003 позволяет объединять больше двух таблиц. Их максимальное количество зависит от конкретной реализации. Синтаксис, используемый при таких объединениях, аналогичен тому, который применяется в случае двух таблиц: SELECT Е.*, С.Salary, С.Bonus, Y.TotalSales FROM EMPLOYEE E, COMPENSATION C, YTD_SALES Y WHERE E.EmpID = С Employ AND C.Employ = Y.EmpNo ; Этот оператор проводит с тремя таблицами объединение, основанное на равенстве. При выполнении этого оператора извлекаются данные, хранящиеся в соответствующих столбцах каждой из таблиц. Это делается для того, чтобы можно было получить таблицу, в которой будут имена и фамилии продавцов, число проведенных каждым из них продаж и полученная ими компенсация. Менеджер по продажам сможет быстро увидеть, заслужил ли продавец свое вознаграждение. Совет: Если данные о продажах, проведенных продавцами за последние 12 месяцев до текущей даты, будут храниться в отдельной таблице YTD_SALES (продажи за предшествующий год), то производительность и надежность будут выше, чем при хранении этих данных в таблице EMPLOYEE. Данные в EMPLOYEE относительно стабильные. Имя и фамилия человека, его адрес и номер телефона меняются не слишком часто. А данные о продажах за год меняются, наоборот, достаточно часто. Так как в таблице YTD_SALES столбцов меньше, чем в EMPLOYEE, то таблица YTD_SALES, скорее всего, сможет обновляться быстрее. И если при обновлении итогов продаж можно не трогать таблицу EMPLOYEE, то уменьшается риск случайного изменения хранящихся в ней данных. Перекрестное объединение CROSS JOIN (перекрестное объединение) — это ключевое слово для простого объединения, не имеющего предложение WHERE. Поэтому оператор SELECT * FROM EMPLOYEE, COMPENSATION ; также может быть записан как SELECT * FROM EMPLOYEE CROSS JOIN COMPENSATION ; В результате получается декартово произведение (также известное как перекрестное произведение) двух исходных таблиц. CROSS JOIN редко дает тот окончательный результат, который вам нужен, но его применение может быть полезно в качестве первого шага в той цепочке манипуляций данными, которая в конце концов приведет к нужному результату. Естественное объединение Частным случаем объединения, основанного на равенстве, является естественное объединение (natural join). В предложении WHERE из объединения, основанного на равенстве, проверяется равенство значения из столбца первой исходной таблицы значению из столбца второй. У двух столбцов должны быть одинаковые тип и длина, как, впрочем, у сравниваемых столбцов должно быть одно и то же имя. На самом же деле при естественном объединении равенство проверяется для всех столбцов из первой таблицы, имеющих те же имена, что и соответствующие им столбцы из второй. Представьте, что в таблице COMPENSATION из предыдущего примера также имеются столбцы Salary и Bonus, но Employ заменен на ЕтрШ. В таком случае можно выполнить естественное объединение таблиц COMPENSATION и EMPLOYEE. Традиционный синтаксис объединения должен выглядеть примерно так: SELECT E.*, С.Salary, С.Bonus FROM EMPLOYEE E, COMPENSATION С WHERE E.EmpID = C.EmpID ; Этот запрос является естественным произведением. Для той же самой операции есть и альтернативный синтаксис: SELECT E.*, С.Salary, С.Bonus FROM EMPLOYEE E NATURAL JOIN COMPENSATION С ; Условное объединение Условное объединение похоже на объединение, основанное на равенстве, но в проверяемом условии присутствие равенства не обязательно (хотя и не исключается). Проверяемым условием может быть любой правильно составленный предикат. Если условие в проверяемой строке выполняется, то эта строка станет частью полученной таблицы. Синтаксис условного объединения немного отличается от того, который вы видели до сих пор. Это отличие состоит в том, что условие содержится в предложении ON (в), а не в WHERE (где). Скажем, бейсбольному статисту надо знать, какие питчеры из Национальной лиги провели полностью на подаче столько игр, сколько это сделал хотя бы один питчер Американской лиги. Этот вопрос предназначен для объединения, основанного на равенстве, а также может быть выражен с помощью синтаксиса условного объединения: SELECT * FROM NATIONAL JOIN AMERICAN ON NATIONAL.СompleteGames = AMERICAN.CompleteGames ; Объединение по именам столбцов Объединение по именам столбцов похоже на естественное, но является более гибким. При естественном объединении проверяется равенство значений из всех одноименных столбцов, которые только имеются в исходных таблицах. А что касается объединения по именам столбцов, то в нем можно выбирать, какие одноименные столбцы должны проверяться, а какие — нет. Если хотите, то можете выбрать их все, фактически превращая объединение по именам столбцов в естественное. Можете также выбрать и меньшее количество этих столбцов. Таким образом, есть возможность в большей степени определить, какие строки из перекрестного произведения должны оказаться в полученной вами итоговой таблице. Скажем, вы изготовитель шахмат и имеете инвентарную таблицу, в которой хранятся данные о белых фигурах, а также другую такую же таблицу, но с данными о черных фигурах. Эти таблицы называются WHITE (белая) и BLACK (черная), и в каждой из них имеются следующие поля: Piece (фигура), Quant (количество), Wood (дерево). В таблицах хранятся такие данные:
Для каждой разновидности фигур — короля (King), королевы (Queen), ладьи (Rook), слона (Bishop), коня (Knight), пешки (Pawn), — изготавливаемых из дуба (Oak) или из черного дерева (Ebony), количество белых и черных фигур должно быть равным. Если же равенство нарушено, то это означает, что некоторые фигуры или потеряны, или украдены, и, следовательно, вам надо улучшить условия хранения товара. При естественном объединении проверяется равенство значений во всех одноименных столбцах. В таком случае получится пустая таблица, потому что в таблице WHITE нет таких строк, где значение в столбце Wood бьло бы равно какому-либо значению из столбца Wood таблицы BLACK. Таблица, полученная в результате естественного объединения, не позволит определить, пропало что-нибудь или нет. Поэтому надо использовать объединение по именам столбцов, в котором столбец Wood исключается из рассмотрения. Это объединение может быть представлено в таком виде: SELECT * FROM WHITE JOIN BLACK USING (Piese, Quant) ; В результате объединения получается таблица только с теми строками, в которых количество белых и черных фигур, имеющихся на складе, совпадает:
Внимательный читатель может заметить, что из списка пропали королева и пешка, — признак того, что каких-либо из этих фигур не хватает. Внутреннее объединение Объединения — мистические операторы, и для правильного обращения с ними требуется недюжинная внутренняя сила. Возможно, вы даже слышали о внутреннем объединении (inner join), — оно-то и является квинтэссенцией реляционных операций. Я вас разыграл! Во внутренних объединениях вовсе нет ничего таинственного. На самом деле внутренними являются все объединения, о которых уже говорилось в этой главе. Объединение по именам столбцов из последнего примера можно сформулировать и как внутреннее, если воспользоваться следующим синтаксисом: SELECT * FROM WHITE INNER JOIN BLACK USING (Piese, Quant) ; Результат при этом получится тот же самый. Внутреннее объединение называется "внутренним", чтобы его можно было отличить от внешнего объединения. Из таблицы, получаемой при внутреннем объединении, выбрасываются все строки, у которых нет соответствующих строк одновременно в обеих исходных таблицах. А при внешнем объединении несоответствующие строки сохраняются. Такая вот между ними разница, и нет в ней ничего метафизического. Внешнее объединение При объединении двух таблиц в первой из них (назовем ее левой) могут быть строки, которых нет во второй (правой) таблице. И наоборот, в правой таблице могут быть строки, которых нет в левой. При выполнении внутреннего объединения этих таблиц все несоответствующие строки из вывода удаляются. Однако при внешнем объединении (outer join) такие строки остаются. На самом деле любое внешнее объединение бывает трех видов: левое, правое и полное. Левое внешнее объединение В запросе, имеющем объединение, левая таблица — это та, которая в операторе запроса предшествует ключевому слову JOIN, а правая — та, которая следует за ним. При левом внешнем объединении (left outer join) несоответствующие строки, имеющиеся в левой таблице, в выводе сохраняются, а имеющиеся в правой — из него, наоборот, удаляются. Чтобы понять работу внешних объединений, представьте себе корпоративную базу данных, в которой хранятся записи о сотрудниках компании, ее отделах и представительствах. Примеры данных этой компании приведены в табл. 10.1-10.3. Таблица 10.1. LOCATION (представительство)
Таблица 10.2. DEPT (отдел)
Таблица 10.3. EMPLOYEE (сотрудник)
Теперь предположим, что вам нужно просмотреть все данные обо всех сотрудниках, в том числе, в каком отделе и представительстве сотрудник работает. Такую задачу можно выполнить с помощью объединения, основанного на равенстве: SELECT * FROM LOCATION L, DEPT D, EMPLOYEE E WHERE L.LocationlD a D.LocationID AND D.DeptID = E.DeptID ; Результат выполнения этого оператора следующий:
Полученная в результате таблица содержит все данные обо всех сотрудниках, в том числе, в каком отделе и представительстве сотрудник работает. Так как каждый сотрудник компании работает в каком-либо представительстве и в одном из отделов, то для этого примера как раз и подходит объединение, основанное на равенстве. А теперь предположим, что вам требуются данные как о представительствах, так и связанные с представительствами данные об отделах и сотрудниках. Это "уже совсем другая история", потому что в представительстве может не быть никаких отделов. Поэтому для получения нужных данных используйте, как показано в следующем примере, внешнее объединение: SELECT * FROM LOCATION L LEFT OUTER JOIN DEPT D ON (L.LocationID = D.LocationID) LEFT OUTER JOIN EMPLOYEE E ON (D.DeptID = E.DeptID); В этом объединении данные берутся из трех таблиц. Сначала объединяются таблицы LOCATION и DEPT. Затем получившаяся таблица объединяется с таблицей EMPLOYEE. Даже если строки из таблицы, расположенной левее оператора LEFT OUTER JOIN, и не имеют соответствующих строк в таблице, расположенной правее этого оператора, они все равно входят в результат. Таким образом, при первом объединении в результат войдут все представительства, даже без отделов. А при втором объединении — войдут все отделы, даже без персонала. И вот какой получается результат:
В нем первые две строки такие же, как и строки из предыдущего примера. А в третьей строке в столбцах, относящихся к отделам и сотрудникам, находятся неопределенные значения, потому что в Тампе нет никаких отделов и никто из сотрудников там постоянно не работает. В четвертой и пятой строках находятся данные о складе и об отделе продаж, но в столбцах этих строк, относящихся к сотрудникам, находятся неопределенные значения, так как в этих двух отделах персонала нет. Это внешнее объединение сообщает все то же, что и объединение, основанное на равенстве, а также предоставляет следующую информацию:
Нет никакой гарантии, что строки из последнего примера выведены в нужном вам порядке. Этот порядок в различных реализациях может быть разным. Чтобы выводить строки в том порядке, который вам нужен, вставьте предложение ORDER BY в оператор SELECT, как, например, в этот: SELECT * FROM LOCATION L LEFT OUTER JOIN DEPT D ON (L.LocationID = D.LocationID) LEFT OUTER JOIN EMPLOYEE E ON (D.DeptID = E.DeptID) ORDER BY L.LocationID, D.DeptID, E.EmpID; Совет: Так как левого внутреннего объединения не существует, то левое внешнее объединение можно назвать покороче —левое объединение (в коде SQL это ключевые слова LEFT JOIN). Правое внешнее объединение Готов поклясться, что вы уже знаете, как ведет себя правое внешнее объединение. И вы правы! Правое внешнее объединение (right outer join) сохраняет в выводе несоответствующие строки, взятые из правой таблицы, но удаляет из него несоответствующие строки, взятые из левой. Это внешнее объединение можно использовать с теми же таблицами, что в левом внешнем объединении, и получить при этом те же результаты. Для этого надо, заменив в операторе ключевые слова левого внешнего объединения на ключевые слова правого, поменять порядок следования таблиц на обратный: SELECT * FROM EMPLOYEE E RIGHT OUTER JOIN DEPT D ON (D.DeptID = E.DeptID) RIGHT OUTER JOIN LOCATION L ON (L.LocationID = D.LocationID) ; В такой формулировке первое объединение создает таблицу, в которой находятся все отделы, с персоналом или без него. А второе объединение создает таблицу со всеми представительствами, независимо от наличия в них отделов. Так как правого внутреннего объединения не существует, то правое внешнее объединение можно называть правое объединение (в коде SQL это ключевые слова RIGHT JOIN). Полное внешнее объединение Полное внешнее объединение (full outer join) соединяет в себе функции левого и правого внешних объединений. В результате выполнения в выводе остаются несоответствующие строки как из левой, так и из правой таблицы. Проанализируем теперь самый общий вариант корпоративной базы данных, которая уже рассматривалась в предыдущих примерах. В этой базе могут быть:
Чтобы показать все представительства, отделы и сотрудников, независимо от того, имеют они соответствующие строки в других таблицах или не имеют, используйте полное внешнее объединение, заданное в следующем виде: SELECT * FROM LOCATION L FULL JOIN DEPT D ON (L.LocationID = D.LocationID) FULL JOIN EMPLOYEE E ON (D.DeptID = E.DeptID) ; Совет: Так как полного внутреннего объединения не существует, то полное внешнее объединение можно называть, полное объединение (в коде SQL это ключевые слова FULL JOIN). Объединение-слияние В отличие от других видов объединения, объединение-слияние (union join) не пытается искать для строки из левой исходной таблицы хотя бы одну соответствующую строку из правой исходной таблицы. Это объединение создает виртуальную таблицу, в которой находятся все столбцы обеих исходных таблиц. В созданной виртуальной таблице столбцы, взятые из левой исходной таблицы, содержат все строки этой исходной таблицы. В этих строках все столбцы, взятые из правой исходной таблицы, содержат неопределенные значения. И, аналогично, столбцы, взятые из правой исходной таблицы, содержат все строки этой исходной таблицы. А в этих строках все столбцы, взятые из левой исходной таблицы, содержат неопределенные значения. Таким образом, таблица, получившаяся в результате объединения-слияния, содержит все столбцы из обеих исходных таблиц, причем количество ее строк равно сумме количества строк, имеющихся в обеих исходных таблицах. В большинстве случаев сам по себе результат объединения-слияния лишь промежуточный. В процессе обработки данных он является таблицей с большим количеством неопределенных значений. Впрочем, для получения полезной информации эту таблицу можно использовать вместе с выражением COALESCE (соединение) (см. главу 8). Предположим, что вы работаете в компании, которая проектирует и производит ракеты, предназначенные для экспериментальных запусков. У вас в работе имеется несколько проектов. Под вашим руководством работают несколько инженеров-проектировщиков, каждый из которых имеет квалификацию в нескольких видах деятельности. Как менеджера вас интересует, какие инженеры в каких видах деятельности имеют квалификацию и над какими проектами работали. В настоящее время эти данные разбросаны по трем таблицам: EMPLOYEE (сотрудник), PROJECTS (проекты) и SKILLS (области квалификации). В таблице EMPLOYEE хранятся данные о сотрудниках, и ее первичным ключом является EMPLOYEE.EmpID. Каждый проект, над которым работал сотрудник, занимает одну строку в другой таблице — PROJECTS. PROJECTS.EmpID — это внешний ключ, который ссылается на таблицу EMPLOYEE. В таблице SKILLS для каждого сотрудника перечислены те виды деятельности, в которых он имеет квалификацию. SKILLS.EmpID— внешний ключ, который ссылается на таблицу EMPLOYEE. В таблице EMPLOYEE для каждого сотрудника имеется в точности одна строка. А в таблицах PROJECTS и SKILLS таких строк может быть сколько угодно, в том числе и ни одной. Примеры данных, хранящихся в трех указанных таблицах, приведены в табл. 10.4-10.6. Таблица 10.4. Таблица EMPLOYEE
Таблица 10.5. Таблица PROJECTS
Как видно в этих таблицах, Фергюсон работал над проектами устройства ракет Х-63 и Х-64, а также является специалистом по механическому проектированию и расчетам аэродинамической нагрузки. Теперь предположим, что вы как менеджер хотите увидеть всю информацию обо всех своих сотрудниках. Для этого вы решили применить к таблицам EMPLOYEE, PROJECTS и SKILLS объединение, основанное на равенстве: SELECT * FROM EMPLOYEE E, PROJECTS P, SKILLS S WHERE E.EmpID = P.EmpID AND E.EmpID = S.EmpID ; Таблица 10.6. Таблица SKILLS
Эту же операцию можно представить в виде внутреннего объединения, используя для этого следующий синтаксис: SELECT * FROM EMPLOYEE E INNER JOIN PROJECTS P ON (E.EmpID = P.EmpID) INNER JOIN SKILLS S ON (E.EmpID = S.EmpID) ; Обе формулировки дают одинаковый результат, показанный в табл. 10.7. Таблица 10.7. Результаты внутреннего объединения
Такое расположение данных не отличается слишком большой ясностью. В каждой строке идентификатор сотрудника появляется три раза, а для каждого сотрудника его проекты и виды квалификации указываются несколько раз. Для ответа на возникшие у вас вопросы внутреннее произведение подходит недостаточно хорошо. Более подходящий результат можно получить, используя объединение-слияние с несколькими операторами SELECT. Начнем с простого объединения-слияния: SELECT * FROM EMPLOYEE E UNION JOIN PROJECTS P UNION JOIN SKILLS S ; Обратите внимание, что в объединении-слиянии нет предложения ON. Дело в том, что сейчас данные не фильтруются, поэтому предложение ON не нужно. Результат, полученный при выполнении этого оператора, приведен в табл. 10.8. Таблица 10.8. Результат операции union join
Каждая таблица была расширена справа или слева неопределенными (NULL) значениями, после чего проведено объединение в одну таблицу всех строк, получившихся в результате этого расширения. Порядок этих строк произвольный и зависит от используемой реализации. Теперь можно представить полученные данные в более "удобоваримой" форме. Обратите внимание, что для идентификатора сотрудника в таблице есть три столбца, но в любой из строк определенным является только один из них. Вид выводимой таблицы можно улучшить, если использовать для этих столбцов выражение COALESCE (соединить). Как уже говорилось в главе 8, это выражение выбирает из переданного ему списка значений первое, не являющееся неопределенным. В данном случае COALESCE выбирает из списка столбцов единственное значение: SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID, E.Name, P.ProjectName, S.Skill FROM EMPLOYEE E UNION JOIN PROJECTS P UNION JOIN SKILLS S ORDER BY ID ; Предложение FROM здесь такое же, как и в предыдущем примере, но теперь три столбца EmpID соединяются с помощью выражения COALESCE в один, который называется ID. Кроме того, результат упорядочивается как раз по этому столбцу ID. Что в итоге получилось, показано в табл. 10.9. В каждой строке этой таблицы имеются данные или о проекте, или о квалификации, но не о том и другом вместе. При чтении результата необходимо вначале определить, какого типа данные в каждой строке. Если в строке столбец ProjectName является определенным, то в ней указан проект, над которым работал сотрудник. А если определенным является столбец Skill, то в строке указаны навыки сотрудника. Таблица 10.9. Результат применения операции union join вместе с предложением
Можно получить чуть более ясный результат, если в оператор SELECT вставить еще одно предложение COALESCE, как это сделано в следующем примере: SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID, E.Name, COALESCE (P.Type, S.Type) AS Type, P.ProjectName, S.Skill FROM EMPLOYEE E UNION JOIN (SELECT "Project" AS Type, * FROM PROJECTS) P UNION JOIN (SELECT "Skill" AS Type, * FROM SKILLS) S ORDER BY ID, Type ; В первом предложении UNION JOIN таблица PROJECTS заменена вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, Р.Туре, с постоянным значением "Project" (проект). И, аналогично, во втором предложении UNION JOIN таблица SKILLS заменена другим вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, S.Type, с постоянным значением "Skill" (квалификация). В каждой строке значением Р.Туре является или NULL, или "Project", а значением S.Type — или NULL, или "Skill". В списке внешнего предложения SELECT указано выполнение операции COALESCE, при которой два столбца Туре должны стать одним, также имеющим имя Туре. Затем этот новый столбец Туре указывается в предложении ORDER BY, которое таким образом сортирует все строки, чтобы вначале шли строки с проектами, а затем — с квалификационными навыками. Результат показан в табл. 10.10. Таблица 10.10. Усовершенствованный результат применения операции union join вместе с предложением coalesce
Полученная теперь таблица представляет собой отчет — причем очень удобный для чтения — об опыте участия в проектах и о квалификации всех сотрудников, перечисленных в таблице EMPLOYEE. Если учесть количество имеющихся сейчас разных операций объединения (JOIN), то связывание данных из разных таблиц не должно создавать проблему, какой бы ни была структура этих таблиц. Поверьте, что если только в вашей базе имеются какие-либо сырые данные, то в SQJL2OO3 найдугся средства, чтобы их оттуда извлечь, а затем показать в каком-либо содержательном виде. ON или WHERE Роль, которую в объединениях разных видов играют предложения ON и WHERE, бывает достаточно запутанной. Прояснить ситуацию можно с помощью следующих фактов.
Во внешних объединениях предложения ON и WHERE отличаются друг от друга. Предложение WHERE всего-навсего фильтрует строки, возвращаемые предложением FROM. Строки, отбракованные фильтром, просто не попадут в результат. А предложение ON, используемое во внешнем объединении, вначале фильтрует строки перекрестного произведения, а затем добавляет в результат и отбракованные строки, расширенные неопределенными значениями. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
| На правах рекламы: |