Особенности многотабличных запросов

 

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

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

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

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

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

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

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

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

SELECT TEACHERS.TFAM, USP.OCENKA FROM TEACHERS, PREDMET, USP WHERE TEACHERS. TNUM = PREDMET. TNUM AND PREDMET. PNUM = USP.PNUM;

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

 

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

 

В многотабличном запросе можно объединять данные таблиц, однако интересно и то, что та же самая методика может использоваться для объединения вместе двух копий одиночной таблицы. Для объединения таблицы с собой можно сделать каждую строку таблицы одновременно и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы, а затем оценить каждую комбинацию в терминах предиката. Это позволяет легко создавать определенные виды связей между различными элементами внутри одиночной таблицы. Например, допускается изобразить объединение таблицы с собой, как объединение двух копий одной и той же таблицы, причем она на самом деле не копируется, но 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. Однако такая структура позволяет более точно определить условие объединения.