Рассмотренные до этой главы SQL-запросы использовали одну таблицу. Но в реальныхприложениях часто требуется использовать сразу несколько таблиц базы данных. Запросы, которые обращаются одновременно к нескольким таблицам, называются многотабличными запросами. Именно в таких запросах проявляется одно из преимуществ реляционных баз данных — связь таблиц друг с другом.
Перекрестное объединение таблиц
Для обсуждения многотабличных запросов создадим две таблицы: tbll и tbl2, каждая из которых будет содержать два столбца — числовой и текстовый. В листинге 22.1 представлены операторы create table, создающие указанные таблицы.
После создания таблиц заполним каждую таблицу тремя записями, состоящими из целого числа и соответствующей ему буквы русского алфавита.
Для осуществления запроса сразу к нескольким таблицам их имена перечисляются после ключевого слова from через запятую. В листинге 22.2 представлена выборка из каждой таблицы, а также двухтабличный запрос с использованием оператора select к таблицам tbll, tbl2.
Двухтабличный запрос, представленный в листинге 22.2, называют также перекрестным объединением. При таком объединении каждая строка одной таблицы объединяется с каждой строкой другой таблицы, создавая тем самым все возможные комбинации строк обеих таблиц. Результирующая таблица содержит число столбцов, равное сумме столбцов в объединяемых таблицах. Таблицы tbll иtbl2 содержат по два столбца, поэтому результирующая таблица содержит 4 столбца (2 + 2 = 4). Число записей в результирующей таблице определяется произведением числа записей в таблицах, участвующих в многотабличном запросе (3*3 = 9).
Однако если в результирующую таблицу добавить столбец id, который входит в состав обеих таблиц, СУБД MySQL вернет ошибку — неоднозначность поля id в списке полей (листинг 22.4).
Для того чтобы исключить неоднозначность, т. е. определить, поле id какой таблицы имеется в виду в запросе из листинга 22.4, прибегают к полным именам столбцов. Полное имя включает имена таблицы и столбца, разделенные точкой (листинг 22.5).
Для символа "*", указывающего на необходимость выборки всех столбцов таблицы, также можно использовать полное имя: tbll. * и tbi2. * (листинг 22.6).
Полные имена можно использовать для обращения не только в пределах одной базы данных, но и для объединения таблиц из двух разных баз данных. Для этого полное имя столбца предваряется именем базы данных. Если таблицы tbll и ti: расположены в базах данных dbl и db2, то к столбцам name и letter можно осветиться по именам dbl.tbll.name и db2.tbl2.letter соответственно. При этог.' ш звания таблиц следует также записывать полным именем: dbl. tbll и db2. tbl2.
Еще одним эффективным способом ограничения числа столбцов является группировка результата выборки по одному из полей при помощи конструкции group by (листинг22.8).
При работе с результирующей таблицей в прикладных программах часто бывает неудобно работать с полными именами столбцов. Поэтому прибегают к назначению псевдонимов при помощи оператора as. В листинге 22.9 полному имени tbll.id назначается псевдоним id, имени tbll.name — name, a tbl2. letter — letter.
Оператор as может использоваться не только для назначения псевдонимов столбцам, но и для назначения псевдонимов таблицам. В листинге 22.10 таблицам tbll и tbl2 назначаются псевдонимы tl и t2 соответственно. Такой подход позволяет использовать в качестве имен таблиц более короткие имена, что приводит к более короткому SQL-запросу.
Замечание_______________________________________
После назначения псевдонимов таблицам, участвующим в запросе, использовать исходные имена таблиц в конструкциях where, group by, order by исписке столбцов после ключевого слова select уже не допускается.
Кроме того, такое назначение псевдонимов позволяет осуществлять многотабличные запросы к одной таблице. Такие запросы называют еще самообъединением таблицы. Для этого достаточно назначить одной и той же таблице разные псевдонимы (листинг 22. 11).
Рассмотрим более реальный пример. Пусть требуется вывести названия и цены всех товарных позиций каталога, которому принадлежит товарная позиция 'Maxtor 6Y120PO'. I То есть, обнаружив в нашем магазине один жесткий диск, мы хотим выяснить, какие еще жесткие диски имеются в продаже и сравнить их по цене. Данную задачу решает запрос, представленный в листинге 22.12.
В листинге 22.12 происходит самообъединение таблицы products, при этом в полных именах списка столбцов после ключевого слова select следует использовать имя таблицы, не участвующей в сравнении со строкой 'Maxtor 6Y120P0', иначе будет возвращено пять строк с данным названием (листинг 22.13).
Другим применением самообъединения является вычисление разницы между последовательными строками. В листинге 22.14 представлено содержимое таблицы orders.
Пусть требуется вычислить разницу в днях между заказами в электронном магазине. Решить эту задачу можно при помощи запроса, представленного в листинге 22.15.
Как видно из листинга 22.15, при самообъединении таблицы orders копии таблицы смещаются друг относительно друга на одну строку за счет условия ordl. id_order + 1 = ord2. id_order. При помощи функции TO_DAYS () время приводится к дням, после чего производится вычитание полученных результатов. Полученное значение different и представляет разницу в днях между заказами.
Рассмотрим еще несколько запросов на примере учебной базы данных shop. Пусть требуется решить задачу подсчета числа товарных позиций, а также общего числа товара на складе для каждого из каталогов и вывести результирующую таблицу. В главе 19 для осуществления этой задачи предлагался запрос, представленный в листинге 22.16.
Первый столбец представляет собой внешние ключи таблицы catalogs, по которым можно восстановить названия каталогов. При помощи многотабличного запроса к таблицам products иcatalogs вместо данного столбца можно вывести названия каталогов, тем самым представив результат запроса в более удобном виде (листинг 22.17).
Обязательным условием в данном запросе является равенство полей id_catalog таблиц products и catalogs (products.id_catalog = catalogs.id_catalog). Именно при помощи данного условия осуществляется связь этих двух таблиц.
Если имя столбца является уникальным, то при перечислении его в списке столбцов и конструкциях where, GROUP BY, order BY можно не использовать полное имя столбца.
Среди таблиц учебной базы данных shop примечательной таблицей является таблица orders (листинг 22.18), которая содержит внешний ключ id_product для связи с таблицей products и внешний ключ id_user для связи с таблицей users. Поэтому, чтобы результирующая таблица содержала данные заказа, фамилию покупателя и название товара, требуется осуществить трехтабличный запрос.
Запрос, результирующая таблица в результате выполнения которого содержит вместо внешних ключей id_user и id_product фамилию покупателя и название товарной позиции, представлен в листинге 22.19.
Помимо того, что после ключевого слова from требуется указать три таблицы: users, orders и products, WHERE-условие должно включать две связи: таблицы order; с users и orders с products.
Если, кроме представленной в листинге 2.19 информации, в результирующую таблицу необходимо поместить столбец с названием каталога, к которому относится выбранный товар, потребуется осуществить четырехтабличный запрос, представление в листинге 22.20.
Здесь, помимо связи таблицы orders с таблицами users и products, добавляется третья связь таблицы products с таблицей catalogs (products.id_catalog = catalogs. id__catalog).
С увеличением количества таблиц в запросе резко возрастает объем работы, необходимой для выполнения запроса, поэтому по возможности в запросе не следует использовать больше трех-четырех таблиц.
22.2. Объединение таблиц при помощи JOIN
Оператор join позволяет объединять таблицы и имеет многочисленные варианты использования, которые будут рассмотрены далее на протяжении данного раздела.ВSQL-запросе данный оператор располагается между именами объединяемых таблиц [осле ключевого слова from. Без дополнительных ключевых слов объединение при помощи ключевого слова join аналогично перекрестному объединению таблиц, рассмотренному в разделе 22.1.
Запросы, представленные в листинге 22.21, полностью идентичны. Для формирования условия в запросах, использующих объединение join, вместо ключевого слова ere предпочтительно использовать ключевое слово on, как это продемонстрированыв листинге 22.22
