1.3.27. Особенности многотабличных запросов
Многотабличные запросы, рассмотренные до сих пор, не требовали применения специальных синтаксических форм или каких-либо других особенностей языка SQL помимо тех, что использовались для создания однотабличных запросов. Однако некоторые многотабличные запросы нельзя создать без использования дополнительных особенностей языка SQL. В частности:
иногда в многотабличных запросах требуется использовать полные имена столбцов, чтобы исключить неоднозначные ссылки на столбцы;
в многотабличных запросах особый смысл имеет выбор всех столбцов (SELECT *);
для создания многотабличных запросов, связывающих таблицу саму с собой, создаются самообъединения;
в предложении FROM часто используются псевдонимы таблиц, чтобы упростить полные имена столбцов и обеспечить однозначность ссылок на столбцы в самообъединении.
Полные имена столбцов
Например, столбцы с именем SALES имеются в таблицах OFFICES и SALESREPS. В столбце SALES таблицы OFFICES содержится объём продаж на текущий день для каждого офиса; в аналогичном столбце таблицы SALESREPS содержится текущий объём продаж для каждого служащего. Обычно с этими двумя столбцами затруднений не возникает, поскольку в предложении FROM задаётся соответствующая таблица.
Но запрос «показать имя, офис и объем продаж каждого служащего»
SELECT NAME, SALES, CITY FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE
вернёт ошибку: «Error: Ambiguous column name "SALES"»
Хотя в формулировке запроса на естественном языке подразумевается столбец SALES из таблицы SALESREPS, созданный запрос SQL является неоднозначным. Чтобы исключить разночтения, при указании столбцов необходимо использовать их полные имена. Полное имя столбца содержит непосредственно имя столбца и имя таблицы, в которой он находится. Полные имена двух столбцов SALES будут такими: OFFICES.SALES и SALESREPS.SALES
В инструкции SELECT вместо простых имён столбцов всегда можно использовать полные имена. Таблица, заданная в полном имени столбца, должна, конечно, соответствовать одной из таблиц, указанных в предложении FROM. Вот исправленный вариант предыдущего запроса:
SELECT NAME, SALESREPS.SALES, CITY FROM SALESREPS, OFFICES WHERE REP OFFICE = OFFICE
Никогда не помешает использовать в многотабличных запросах полные имена столбцов. Недостатком, естественно, является то, что запросы становятся длиннее. В интерактивном режиме можно сначала попробовать выполнить запрос с простыми именами столбцов, чтобы найти все сомнительные столбцы. Если выдаётся сообщение об ошибке, запрос редактируют и точно указывают столбцы с повторяющимися именами.
1.3.28. Самообъединения
Некоторые многотабличные запросы используют отношения, существующие внутри одной таблицы. Предположим, например, что требуется вывести список имён всех служащих и их руководителей. Каждому служащему соответствует одна строка в таблице SALESREPS, а столбец MANAGER содержит идентификатор служащего, являющегося руководителем. Столбцу MANAGER следовало бы быть внешним ключом для таблицы, в которой хранятся данные о руководителях. И он им, фактически, является – это внешний ключ для самой таблицы SALESREPS.
Если бы вы попытались создать этот запрос как любой другой запрос к двум таблицам с отношением "первичный ключ - внешний ключ", то он выглядел бы так:
SELECT NAME, NAME
FROM SALESREPS, SALESREPS WHERE MANAGER = EMPL_NUM
Эта инструкция SELECT является неправильной из-за двойной ссылки на таблицу SALESREPS в предложении FROM. Вы могли бы попробовать убрать вторую ссылку на
таблицу SALESREPS:
SELECT NAME, NAME FROM SALESREPS WHERE MANAGER = EMPL_ENUM
MANAGER
Такой запрос будет правильным, но он не сделает того, что вам нужно. Это од-нотабличный запрос, поэтому СУБД поочерёдно просматривает все строки таблицы SALESREPS, чтобы найти те, которые удовлетворяют условию отбора.
Этому условию удовлетворяют строки, в которых два столбца имеют одинаковые значения, т.е. служащий является своим руководителем. Таких строк нет, поэтому запрос не даст никакого результата.
Чтобы понять, как в SQL решается эта проблема, представим себе, что имеются две идентичные копии таблицы SALESREPS. Одна копия называется EMPS и содержит список служащих, а другая называется MGRS и содержит список руководителей. Столбец MANAGER таблицы EMPS является внешним ключом для таблицы MGRS, И следующий запрос будет работать:
SELECT EMPS.NAME, MGRS.NAME FROM EMPS, MGRS WHERE EMPS.MANAGER = MGRS.EMPL_NUM
Так как столбцы в двух таблицах имеют идентичные имена, все ссылки на столбцы являются полными. Иначе этот запрос выглядит как обычный запрос к двум таблицам.
Для объединения таблицы с самой собой в SQL применяется именно такой подход: использование "воображаемой копии". Вместо того чтобы на самом деле сделать копию таблицы, СУБД просто позволяет вам сослаться на нее, используя другое имя, которое называется псевдонимом таблицы. Вот тот же самый запрос, записанный с использованием псевдонимов EMPS и MGRS для таблицы SALESREPS:
SELECT EMPS.NAME, MGRS.NAME
FROM SALESREPS EMPS, SALESREPS MGRS WHERE EMPS.MANAGER = MGRS.EMPL NUM
В предложении FROM для каждой "копии" таблицы SALESREPS назначается псевдоним. Это осуществляется следующим образом: псевдоним ставится непосредственно после действительного имени таблицы. Как видно из примера, если в предложении FROM содержится псевдоним таблицы, то в полной ссылке на столбец должен использоваться псевдоним, а не действительное имя таблицы. Конечно, на самом деле в этом запросе необходимо применять псевдоним только для одной из двух "копий" таблицы. Запрос вполне можно записать так:
SELECT SALESREPS.NAME, MGRS.NAME FROM SALESREPS, SALESREPS MGRS WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
Здесь псевдоним MGRS присваивается только одной "копии" таблицы, а для другой используется собственное имя таблицы. Вот еще два примера самообъединения.
Вывести список служащих, планы которых превышают планы их руководителей:
SELECT SALESREPS.NAME, SALESREPS.QUOTA, MGRS.QUOTA
FROM SALESREPS, SALESREPS MGRS
WHERE SALESREPS.MANAGER = MGRS.EMPL_NUM
AND SALESREPS.QUOTA > MGRS.QUOTA
Вывести список служащих, которые работают со своими руководителями в различных офисах, включая имена и офисы как служащих, так и руководителей:
SELECT EMPS.NAME, EMP_OFFICE.CITY, MGRS.NAME, MGR_OFFICE.CITY FROM SALESREPS EMPS, SALESREPS MGRS, OFFICES EMP_OFFICE, OFFICES MGR_OFFICE WHERE EMPS.REP_OFFICE = EMP_OFFICE.OFFICE
AND MGRS.REP_OFFICE = MGR_OFFICE.OFFICE
AND EMPS.MANAGER = MGRS.EMPL_NUM
AND EMPS.REP OFFICE <> MGRS.REP OFFICE
Псевдонимы таблиц
Как уже было сказано в предыдущем разделе, псевдонимы таблиц необходимы в запросах, включающих самообъединения. Однако псевдоним можно использовать в любом запросе (например, если запрос касается таблицы другого пользователя или если имя таблицы очень длинное и употреблять его в полных именах столбцов утомительно). Следующий запрос ссылается на таблицу BIRTHDAYS, принадлежащую пользователю по имени SAM.
Вывести список имён, плановых объемов продаж и дней рождения служащих:
SELECT SALESREPS.NAME, QUOTA, SAM.BIRTHDAYS.BIRTH_DATE FROM SALESREPS, BIRTHDAYS WHERE SALESREPS.NAME = SAM.BIRTHDAYS.NAME
Если вместо имён двух таблиц использовать псевдонимы S и B, то и вводить, и читать этот запрос будет легче.
Вывести список имен, плановых объемов продаж и дней рождения служащих:
SELECT S.NAME, S.QUOTA, B.BIRTH_DATE FROM SALESREPS S, SAM.BIRTHDAYS В WHERE S.NAME = В.NAME
На рисунке изображена структура предложения FROM для многотабличной инструкции SELECT, содержащей псевдонимы таблиц. Это предложение выполняет две важные функции.
Рисунок 1.3.28.1 – Структура предложения
В предложении FROM перечислены все таблицы, из которых извлекаются данные. Любой столбец, указанный в инструкции SELECT, должен принадлежать одной из таблиц, заданных в предложении FROM. (Имеется исключение для внешних ссылок, содержащихся в подчиненном запросе.)
Предложение FROM содержит метку таблицы, которая в инструкции SELECT используется для идентификации таблицы в полном имени столбца. Если в предложении FROM вводится псевдоним таблицы, то он становится её меткой; в противном случае меткой становится имя таблицы в том виде, в каком оно присутствует в предложении FROM.
Единственное требование, предъявляемое к меткам таблиц в предложении FROM, состоит в том, что все они должны отличаться друг от друга. Стандарт SQL2 допускает вставку ключевого слова AS между именем и псевдонимом таблицы. Хотя это ключевое слово облегчает чтение предложения FROM, оно поддерживается не во всех СУБД.