10.3. Объединение таблиц

В многотабличном запросе можно объединять данные таблиц, однако интересно и то, что та же самая методика может использоваться для объединения вместе двух копий одиночной таблицы. Для объединения таблицы с собой можно сделать каждую строку таблицы одновременно и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы, а затем оценить каждую комбинацию в терминах предиката. Это позволяет легко создавать определенные виды связей между различными элементами внутри одиночной таблицы. Например, допускается изобразить объединение таблицы с собой, как объединение двух копий одной и той же таблицы, причем она на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано.

Использование команды для объединения таблицы с собой аналогично тому приему, который используется для объединения нескольких таблиц. Когда объединяется таблица с собой, все повторяемые имена столбца заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, необходимо иметь два различных имени для этой таблицы. Это можно сделать с помощью определения временных имен, называемых псевдонимами, которые определяются в предложении FROM запроса. Синтаксис в этом случае следующий: после имени таблицы оставляют пробел, а затем должен следовать псевдоним для нее.

Например, для поиска студентов, имеющих одинаковый размер стипендии, можно воспользоваться следующим запросом:

SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = SECOND.STIP;

В данном примере SQL ведет себя так, как если бы он соединял две различные таблицы, называемые FIRST и SECOND, т.е. псевдонимы разрешают одной и той же таблице быть обработанной независимо. Обратите внимание на то, что псевдонимы могут использоваться в предложении SELECT до их объявления в предложении FROM, однако SQL будет сначала допускать любые псевдонимы и может отклонить команду, если они не будут определены далее в запросе. Кроме того, необходимо помнить, что псевдоним существует только тогда, когда команда выполняется, а после завершения запроса псевдонимы, используемые в нем, больше не имеют никакого значения.

Вывод последнего примера имеет два значения для каждой комбинации фамилий, причем второй раз в обратном порядке. Это связано с тем, что каждое значение показано первый раз в каждом псевдониме и второй раз в предикате, т.е. текущее  значение в первом псевдониме сначала выбирается в комбинации со значением во втором псевдониме, а затем наоборот. Например, в нашем случае Поляков выбрался вместе с Нагорным, а затем Нагорный выбрался вместе с Поляковым и т. д. Кроме того, каждая строка была сравнена сама с собой, например Поляков с Поляковым.

Лучший способ избежать этого состоит в наложении порядка на два значения так, чтобы один мог быть меньше, чем  другой или предшествовал ему в алфавитном порядке. Это  делает предикат асимметричным относительно связи, поэтому те же самые значения в обратном порядке не будут выбраны снова. Следовательно, пример можно модифицировать таким образом:

SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP  /-FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = SECOND.STIP AND FIRST. SFAM < SECOND.SFAM;

Результат этого запроса будет такой:

  Гриценко Котеяко    0.00

  Нагорный Поляков   25.50

В частности, Гриценко предшествует Котенко в алфавитном порядке, поэтому комбинация удовлетворяет обоим условиям предиката и появляется в выводе. Если та же самая комбинация появляется в обратном порядке, т.е. Котенко в псевдониме первой таблицы сравнивается с Гриценко во второй таблице, то вто╜рое условие не выполняется. По аналогичной причине в вывод не попадает сравнение с самим собой. Если же возникла необходимость сравнения строк с ними же, то в запросах стоит использовать < = вместо <.

Таким образом, можно использовать эту особенность SQL для проверки определенных видов ошибок. Например, если считать, что учебный предмет может вести только один преподаватель, то всякий раз в таблице PREDMET необходима проверка на это условие. При этом каждый раз, когда код предмета появляется в таблице PREDMET, он должен совпадать с соответствующим номером преподавателя. Следующая команда будет определять любые несогласованности в этой области:

SELECT FIRST.PNUM, FIRST.TNUM,

SECOND.PNUM, SECOND.TNUM

FROM PREDMET FIRST, PREDMET SECOND

 WHERE FIRST.PNUM = SECOND.PNUM

AND FIRST. TNUM 0 SECOND. TNUM;

Вывода для данного примера не будет, т.к. данных, удовлетворяющих предикату в рассматриваемой таблице нет. Логика этого запроса достаточно проста: из таблицы будет выбираться очередная строка, и запоминаться под первым псевдонимом. После этого SQL начнет проверять ее в комбинации с каждой строкой таблицы под вторым псевдонимом. Если комбинация строк удовлетворяет предикату, то она выбирается для вывода, т.е. если будет найден учебный предмет, у которого выяснится несовпадение номера преподавателя в таблице под первым и вторым псевдонимом.

Объединение таблицы с собой - это наиболее часто встречающаяся ситуация, когда используются псевдонимы, однако их можно использовать в любое время для создания альтернативных имен для таблиц в запросе, например, в случае, если таблицы имеют очень длинные и сложные имена.

Более того, допускается использовать любое число псевдонимов для одной таблицы в запросе, хотя использование более двух в одном предложении SELECT часто будет излишеством. Например, для назначения стипендии на следующий семестр необходимо просмотреть все варианты комбинаций студентов с разными размерами стипендии: 25.50, 17.00 и 0.00 у. е.

Как видно из результата, этот запрос находит все комбинации студентов с тремя различными размерами стипендии, поэтому первый столбец вывода состоит из студентов со стипендией 25.50, второй с 17.00 и последний - с 0.00, которые повторяются во всех возможных комбинациях. Интересно, что такой запрос не может быть выполнен с GROUP BY или ORDER BY, поскольку они сравнивают значения только в одном столбце вывода.

В предложении SELECT допускается не использовать каждый псевдоним или таблицу, которые упоминались в предложении FROM запроса, т.е. псевдоним или таблица используются только для создания предиката. Рассмотрим чисто иллюстративный пример следующего запроса:

SELECT FIRST.SFAM, FIRST.SIMA, FIRST.SOTCH FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = 25.50 AND SECOND. STIP =0.00;

Фактически здесь данные о студентах, имеющих стипендию 25.50, повторяются столько раз, сколько существует их сочетаний со студентами, не получающих стипендию (т.е. для которых STIP =0.00).

В SQL предусматривается создание объединения, которое включает и различные таблицы, и псевдонимы одиночной таблицы. Следующий запрос объединяет таблицу с данными об успеваемости для того, чтобы найти учебные предметы, которые уже сданы более чем одним студентом, и таблицу учебных предметов:

SELECT PREDMET.PNAME, FIRST.SNUM, SECOND.SNUM

FROM USP FIRST, USP SECOND, PREDMET WHERE FIRST.PNUM = SECOND.PNUM AND PREDMET. PNUM = FIRST. PNUM AND FIRST. SNUM < SECOND. SNUM;

Результаты этого запроса следующие:

      PNAME             SNUM                 SNUM

       Математика       3413                 3412

В выводе имеем пары номеров студентов, сдавших тот или иной предмет, определяемый по названию из таблицы учебных предметов.

Как уже было сказано, операция объединения в SQL соединяет информацию из двух таблиц, формируя пары связанных строк из них. Объединенную таблицу образуют пары тех строк из различных таблиц, у которых в связанных столбцах содержатся одинаковые значения. Если строка одной из таблиц не имеет пары, то объединение может привести к неожиданным результатам.

Казалось бы, что эти два запроса должны давать одинаковое количество строк, но результаты первого запроса насчитывают шесть строк, а второго - только пять. Это связано с тем, что преподаватель Федченко в настоящий момент еще не получила номера и соответствующая запись имеет значение NULL в поле TNUM, следовательно, это значение не совпадает ни с одним идентификатором учебного предмета в таблице PREDMET. Значит, вывода для этой строки во втором запросе не будет - она просто исчезает из объединения. Таким образом, стандартное SQL-объеяинение может привести к потере информации, если объединяемые таблицы содержат несвязанные строки.

Такие результаты запроса получаются с помощью другой операции объединения, называемой внешним объединением таблиц, которое в предложении WHERE обозначается символом *=. Внешнее объединение является расширением стандартного объединения, и иногда называемого внутренним объединением таблиц. Имейте в виду, что в стандарте SQL1 дано определение только внутреннего объединения, а понятие внешнего объединения в нем отсутствует.

Этот запрос формирует информацию о студентах и кодах учебных предметов, которые ими сданы. Это внутреннее объединение дает пять строк вывода, показывая соответствующие пары: студент - код предмета. Для студента Котенко никакого вывода нет, поскольку он никаких дисциплин не сдавал.

Теперь предположим, что необходимо вывести ту же информацию, но с учетом студентов, которым не соответствует ни один учебный предмет. Такой запрос, построенный по правилам полного внешнего объединения, будет выглядеть так:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM FROM STUDENTS, USP WHERE STUDENTS . SNUM *=* USP.SNUM;

В этот вывод попали строки со значениями NULL, т.е. запись для студента Котенко была, как бы привязана к несуществующей строке таблицы USP, содержащей только значения NULL, и добавлена в результат запроса. Как видно из этого примера, внешнее объединение является сохраняющим информацию и каждая строка таблицы - операнда представлена в выводе результатов запроса. Следовательно, полное внешнее объединение таблиц получается из внутреннего объединения двух таблиц обычным способом, при этом каждая запись первой таблицы, которая не имеет связи ни с одной строкой во второй таблице, дополняется в результатах запроса значениями NULL. В то же время, каждая строка второй-таблицы, которая не имеет связи ни с одной строкой первой таблицы, дополняется в результатах запроса значениями NULL.

Существует еще так называемые левое и правое внешние объединения. Если полное внешнее объединение симметрично по отношению к обеим таблицам-операндам, то эти два типа внешних объединений не симметричны относительно двух таблиц.

Левое внешнее объединение двух таблиц записывается в команде WHERE в виде *= и получается в результате выполнения внутреннего объединения таблиц. При этом происходят действия, аналогичные полному объединению, однако без замены NULL значениями информации, взятой из второй таблицы. Пример левого внешнего объединения фактически уже был рассмотрен выше в примере для преподавателей и предметов.

Правое внешнее объединение двух таблиц записывается в команде WHERE в виде =* и получается из обыкновенного внутреннего объединения таблиц в последовательности реализации полного объединения, при этом замена на NULL значения в первой таблице не производится.

На практике левое и правое внешние объединения более полезны, чем полное внешнее объединение, особенно при связи таблиц через ключи. Система записи внешнего объединения позволяет использовать не только знак равенства, но и другие знаки отношений. Например, допустим следующий запрос, представляющий, по сути, левое внешнее объединение:

SELECT STUDENTS.SFAM, STUDENTS,SNUM,

USP.PNUM, USP.SNUM FROM STUDENTS, USP WHERE STUDENTS. SNUM *> USP.SNUM;

Данный запрос выводит информацию о студентах и о сданных учебных предметах, при этом данные о предметах выбираются для студентов, имеющих номер меньше, чем текущий. В случае, если таковых не обнаруживается, SQL формирует строку из первой таблицы, заполняя NULL значениями данные, которые должны были быть получены из второй.

Хотя система записи внешнего объединения достаточно удобна, она имеет свои недостатки. Дело в том, что проблемы с записью внешних объединений возникают при расширении объединения до трех или более таблиц. Например, при записи

TABLE1 *=* TABLE2 *=*               TABLE3

фактически выполняется внешнее объединение

(TABLE1 *=* TABLE2) *=* TABLE3

что, вообще говоря, приведет к различным результатам. Для создателей стандарта SQL2 внешние объединения были серьезной проблемой, т.к. внешние объединения являются порой единственным способом предоставления результатов ряда крайне необходимых запросов. Поэтому в стандарте SQL2 был определен совершенно новый метод поддержки внешних объединений: в спецификации стандарта SQL2 поддержка внешних объединений осуществляется в предложении FROM со специальным синтаксисом, позволяющим пользователю точно определить, как исходные таблицы должны быть объединены в запросе.

Например, уже рассмотренный нами запрос внутреннего объединения в стандарте SQL1 выглядит так:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM FROM STUDENTS, USP ∙WHERE STUDENTS.SNUM = USP.SNUM;

а в стандарте SQL2 - так (хотя использование первого варианта допускается):

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM FROM STUDENTS INNER JOIN USP ON STUDENTS.SNUM = USP.SNUM;

В последнем случае две объединяемые таблицы соединяются явно посредством операции JOIN. а условие поиска, описывающее объединение, находится теперь в предложении ON внутри предложения FROM. В условии поиска. следующем за ключевым словом ON, могут быть заданы любые критерии сравнения строк двух объединяемых таблиц, в том числе с использованием булевских операторов

Таким образом, в этих двухтабличных объединениях все содержимое предложения WHERE просто перешло в предложение ОN, следовательно, ничего принципиально нового ON не добавляет в язык SQL. Однако такая структура позволяет более точно определить условие объединения.