Выборка данных из нескольких таблиц
На практике очень часто выборка данных оператором SELECT производится из нескольких таблиц. В частности, может использоваться результат соединения или декартова произведения нескольких таблиц. Рассмотрим варианты записи этих операций в языке SQL.
Как уже отмечалось выше, запись в разделе FROM через запятую названий нескольких таблиц позволяет получить результат их декартова произведения (сочетание всех строк «каждая с каждой»). Например, для таблиц Т1 и Т2 это будет выглядеть как
Добавив в раздел WHERE условие соединения таблиц, можно из декартова произведения получить 9-соединение. Ниже используется соединение по условию равенства значений столбцов Id в таблицах Т1 и Т2:
WHERE Tl.Id=T2. Id
Подобным образом можно соединить и более двух таблиц. В общем случае формат может быть:
Альтернатива такому способу записи соединения появилась в стандарте ANSI SQL-92 [16| – это инструкция JOIN. Для соединения двух таблиц предлагается следующий синтаксис:
(INNER | (LEFT I RIGHT I FULL> [OUTER] I CROSS > JOIN
Чтобы пояснить особенности работы разных типов соединения, воспользуемся примером со студентами и учебными группами. Исходные таблицы представлены на рис. 7.3.
Рис. 7.3. Исходные таблицы
В таблице Students есть столбцы с идентификатором студента, его фамилией и инициалами, номером учебной группы (StudID, Name и Group соответственно). В таблицу Groups заносятся номер учебной группы (Group) и факультет, к которому группа относится (Department). Внешними ключами таблицы нс связаны. В Students есть запись о студенте из отсутствующей в Groups группы 3084, аналогично в Groups есть группа 3083, номер которой не используется в Students (записи № 2 в той и другой таблице на рис. 7.3).
Как уже отмечалось выше, декартово произведение двух таблиц даст сочетания всех строк «каждая с каждой». Для нашего примера оно может быть записано как
Select * FROM Students CROSS JOIN Groups
Select * FROM Students, Groups
Результат выполнения запроса представлен на рис. 7.4, а. В нашем примере он не очень осмысленный – информация о каждом студенте во всех сочетаниях с данными об учебной группе.
Рис. 7.4. Результаты выполнения запросов:
а – результат декартова произведения; б – результат перекрестного самосоединения
Отдельно стоит обратить внимание на случай перекрестного самосоединения (англ. self-cross join), когда требуется получить декартово произведение таблицы с самой собой. На SQL для таблицы Groups это может быть записано так (результат – на рис. 7.4, б):
FROM Groups CROSS JOIN Groups as Gr
Здесь важно в разделе FROM задать псевдоним хотя бы для одного из экземпляров соединяемых таблиц: в приведенном примере это псевдоним Gr. В противном случае экземпляры таблиц будут неразличимы, и СУБД, скорее всего, вместо результата выдаст ошибку.
Если понадобится получить информацию о студенте, его группе и факультете, к которому группа относится, потребуется эквисоединение. Приставка «экви» указывает на то, что в условии соединения будет стоять равенство: в нашем случае – равенство номеров группы из таблиц Students и Groups.
Результатом внутреннего эквисоединения таблиц будет таблица, строки которой принадлежат декартовому произведению исходных таблиц, и для них выполняется условие соединения – равенство значений столбцов. В нашем примере для таблиц Students и Groups внутреннее эквисоединение (с учетом особенностей записи столбцов, совпадающих по названию с ключевыми словами SQL) будет записано следующим образом:
FROM Students INNER JOIN Groups
ON Students.[Group] = Groups.[Group];
FROM Students JOIN Groups
ON Students.[Group] = Groups.[Group];
FROM Students, Groups
WHERE Students.[Group] = Groups.[Group]
Ключевое слово INNER в конструкции JOIN используется по умолчанию, поэтому его можно пропустить. Результат выполнения запроса представлен на рис. 7.5. Видно, что в нем отсутствует запись о студенте Петрове, так как его группы нет в таблице Groups. Также нет записи о группе 3083, студенты из которой не представлены в таблице Students.
Рис. 7.5. Результат внутреннего эквисоединения (INNER JOIN)
На практике внутреннее соединение используется чаще всего. Однако в некоторых случаях может понадобиться получить в результате запроса и те строки, для которых в другой таблице не нашлось соответствия. Это можно сделать с помощью внешних (OUTER) соединений: левого (LEFT), правого (RIGHT) и полного (FULL). Ключевое слово OUTER в запросе писать не обязательно, так как LEFT, RIGHT или FULL JOIN уже указывают, что речь идет о внешнем соединении.
При выполнении внешнего левого эквисоединения в результат запроса попадают все строки из внутреннего эквисоединения, а также строки из левой таблицы (стоящей слева в конструкции JOIN), для которых не нашлось соответствия в правой. Для таких строк в столбцах, заполняемых данными из правой таблицы, будет стоять NULL. Для рассматриваемого примера со студентами и учебными группами соответствующий запрос выглядит следующим образом:
Select * FROM Students LEFT JOIN Groups
ON Students.[Group] = Groups.[Group]
Select * FROM Students LEFT OUTER JOIN Groups
ON Students.[Group] = Groups.[Group]
Как отмечалось выше, ключевое слово OUTER ставить не обязательно, в приводимых далее примерах внешнего соединения оно будет опускаться. Результат выполнения запроса представлен на рис. 7.6, а. Обратите внимание на строку со значениями NULL в правой части.
Для случая правого внешнего эквисоединения картина будет зеркально противоположной. В результат запроса берутся все строки из правой таблицы и соответствующие им – из левой. Если соответствия нет, на месте данных из левой таблицы ставится NULL.
Пример запроса приведен ниже, а его результат представлен на рис. 7.6, б. Здесь обратите внимание на строку со значениями NULL в левой части таблицы:
Select * FROM Students RIGHT JOIN Groups
ON Students.[Group] = Groups.[Group]
Результат полного внешнего эквисоединения включает все строки из внутреннего эквисоединения и те записи из правой и левой таблицы, для которых не нашлось соответствия. Пример запроса приведен ниже, а результат его выполнения – на рис. 7.6, в:
Select * FROM Students FULL JOIN Groups
ON Students .[Group] = Groups.[Group]
Рис. 7.6. Результаты внешних эквисоединений:
a – левого; б – правого; в – полного
Теперь рассмотрим, как получить те записи, которые попали в результат внешнего соединения, но отсутствуют в результате внутреннего соединения. Для этого из внешнего соединения надо взять те строки, в которых ключевые столбцы исходных таблиц имеют значения NULL:
FROM Students FULL JOIN Groups
ON Students.[Group] = Groups.[Group]
WHERE (Students.[StudID] is NULL)
OR (Groups. [Group] is NULL)
В рассматриваемых примерах таблицы не связаны внешними ключами. Однако если, например, в правой таблице будет внешний ключ, ссылающийся на левую, и для столбца (или столбцов), являющегося внешним ключом, определено ограничение целостности NOT NULL, то для таких двух таблиц результат RIGHT JOIN по условию равенства соответствующих значений будет всегда таким же, как результат INNER JOIN, ведь в правой таблице во внешнем ключе не может быть значений, которых нет в соответствующем потенциальном ключе левой таблицы.
При построении эквисоединения в условии соединения используют оператор равенства. Возможно и использование других операторов; , >= и т.д. В англоязычной литературе такое соединение называется «поп- equi join» («не-эквисоедиение»).
Пусть, например, требуется получить сочетание всех записей из таблицы Students со всеми записями этой таблицы относительно других студентов. Ниже представлен код на SQL и результат его выполнения (рис. 7.7). Обратите внимание: поскольку таблица соединяется сама с собой, здесь обязательно задание псевдонима хотя бы для одного экземпляра таблицы:
Select * FROM Students INNER JOIN Students as St ON Students.StudID <> St.StudID
Рис. 7.7. Результат соединения по условию неравенства
Конструкция JOIN позволяет соединить и более двух таблиц. Для этого после описания первого соединения пишется следующее. Пусть, например, есть третья таблица Departments, описывающая тип факультета (технический, физический и т.п.). Ниже приведен пример внутреннего соединения трех таблиц, а на рис. 7.8 представлен результат выполнения этого запроса: Select *
FROM Students INNER JOIN Groups
ON Students.[Group] = Groups.[Group]
INNER JOIN Departments
ON Groups.[Department]= Departments.[Department]
Рис. 7.8. Результат внутреннего соединения трех таблиц
SQL-запрос на выборку из нескольких таблиц
Я уже писал о самых различных SQL-запросах, но пришло время поговорить и о более сложных вещах, например, SQL-запрос на выборку записей из нескольких таблиц.
Когда мы с Вами делали выборку из одной таблицы, то всё было очень просто:
SELECT названия_нужных_полей FROM название_таблицы WHERE условие_выборки
Всё очень просто и тривиально, но при выборке сразу из нескольких таблиц становится всё несколько сложнее. Одна из трудностей — это совпадение имён полей. Например, в каждой таблице есть поле id.
Давайте рассмотрим такой запрос:
SELECT * FROM table_1, table_2 WHERE table_1.id > table_2.user_id
Многим, кто не занимался подобными запросами, покажется, что всё очень просто, подумав, что здесь добавились только названия таблиц перед названиями полей. Фактически, это позволяет избежать противоречий между одинаковыми именами полей. Однако, сложность не в этом, а в алгоритме работы подобного SQL-запроса.
Перед тем как читать дальше, попробуйте самостоятельно сообразить, как будет работать подобный SQL-запрос. Что он должен вывести?
Алгоритм работы следующий: берётся первая запись из table_1. Берётся id этой записи из table_1. Дальше полностью смотрится таблица table_2. И добавляются все записи, где значение поля user_id меньше id выбранной записи в table_1. Таким образом, после первой итерации может появиться от 0 до бесконечного количества результирующих записей. На следующей итерации берётся следующая запись таблицы table_1. Снова просматривается вся таблица table_2, и вновь срабатывает условие выборки table_1.id > table_2.user_id. Все записи, удовлетворившие этому условию, добавляются в результат. На выходе может получиться огромное количество записей, во много раз превышающих суммарный размер обеих таблиц.
Если Вы поняли, как это работает после первого раза, то очень здорово, а если нет, то читайте до тех пор, пока не вникните окончательно. Если Вы это поймёте, то дальше будет проще.
Предыдущий SQL-запрос, как таковой, редко используется. Он был просто дан для объяснения алгоритма выборки из нескольких таблиц. А теперь же разберём более приземистый SQL-запрос. Допустим, у нас есть две таблицы: с товарами (есть поле owner_id, отвечающего за id владельца товара) и с пользователями (есть поле id). Мы хотим одним SQL-запросом получить все записи, причём чтобы в каждой была информация о пользователе и его одном товаре. В следующей записи была информация о том же пользователе и следущем его товаре. Когда товары этого пользователя кончатся, то переходить к следующему пользователю. Таким образом, мы должны соединить две таблицы и получить результат, в котором каждая запись содержит информацию о пользователе и об одном его товаре.
Подобный запрос заменит 2 SQL-запроса: на выборку отдельно из таблицы с товарами и из таблицы с пользователями. Вдобавок, такой запрос сразу поставит в соответствие пользователя и его товар.
Сам же запрос очень простой (если Вы поняли предыдущий):
SELECT * FROM users, products WHERE users.id = products.owner_id
Алгоритм здесь уже несложный: берётся первая запись из таблицы users. Далее берётся её id и анализируются все записи из таблицы products, добавляя в результат те, у которых owner_id равен id из таблицы users. Таким образом, на первой итерации собираются все товары у первого пользователя. На второй итерации собираются все товары у второго пользователя и так далее.
Как видите, SQL-запросы на выборку из нескольких таблиц не самые простые, но польза от них бывает колоссальная, поэтому знать и уметь использовать подобные запросы очень желательно.
Создано 18.07.2011 16:37:33
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
- Кнопка:
Она выглядит вот так: - Текстовая ссылка:
Она выглядит вот так: Как создать свой сайт - BB-код ссылки для форумов (например, можете поставить её в подписи):
Комментарии ( 68 ):
sync.o 22.07.2011 07:28:56
Я так понимаю, разницы нет: SELECT * FROM users JOIN products ON users.id = products.owner_id; Вопрос: можно ли выбирать не все поля (*) при таких сложных запросах или * обязательно нужна?
Admin 22.07.2011 12:00:40
«Звёздочка» не обязательна нужна. Разумеется, можно вытаскивать те поля, которые нужны, а для этого необходимо вместо «звёздочки» указать их название с обязательным указанием таблицы. Например, так: SELECT table_1.id, table_2.user_id FROM .
leon 11.10.2011 22:44:50
Здравствуйте уважаемые Михаил Русаков. Есть 2 таблицы table_1 и table_2. table_1 содержит 3 столбца (|id|,|a|,|b|). table_2 содержит 2 столбца (|id|,|num|). Я из таблицы table_1 вытащил все (b) где (a) = 1; Теперь, например, у меня в массиве $myrow[‘b’] содержится (1,4,12,15). Далее мне нужно вытащить из таблицы table_2 все num, где num у меня равняется содержимому в массиве $myrow[‘b’]. А num может содержать повторяемые числа. Т.е например (num=1, num=1, num=2, num=3, num=2, num=1, num=2, num=3 ну и т.д). Это всё как пример! Подскажите, пожалуйста, как осуществить этот алгоритм. Буду Вам очень благодарин если сможете подсказать.
Admin 11.10.2011 22:51:55
Если я вообще правильно понял вопрос, то нужно сделать так. Собрать WHERE из OR, где каждый элемент — это проверка на равенство num одному из b. Например, получится так: WHERE num=’1′ OR num=’2′ OR num=’1′. То есть нужно в цикле перебрать все b, составляя подобный запрос.
leon 12.10.2011 09:39:46
В массиве $myrow[‘b’] содержится (1,4,12,15). Должно быть подобно этому запросу (WHERE num=1 OR num=4 OR num=12 OR num=15). Т.е перебрать все те числа, которые содержаться в массиве $myrow[‘b’]. Ну а так, как я сказал ранее, столбец (num) из таблицы table_2, может содержать в себе повторяемые числа. Т.е, например (num=1, num=1, num=2, num=3, num=2, num=1, num=2, num=3 ну и т.д).
Admin 12.10.2011 09:50:57
Если в WHERE будут повторяющиеся конструкции, то ничего страшного не произойдёт, всё будет работать, однако, если Вам хочется, чтобы там не было повторяющихся значений, то воспользуйтесь функцией: array_unique().
leon 12.10.2011 10:35:59
Нужно что бы примерно было так (WHERE num=$myrow[‘b’];) в массиве $myrow[‘b’]чиса часто могут менятся
Admin 12.10.2011 10:44:17
Выше я написал, как это делать. Используйте OR, либо делайте много запросов WHERE num=$myrow[‘b’], каждый раз подставляя новый элемент, хотя это нерационально.
lion337 15.01.2012 00:22:13
Здравствуйте Михаил. У меня выбор данных из двух баз данных, но почему то не срабатывает, можете помочь в чём моя ошибка. Спасибо $result_com = $mysqli->query(«SELECT comm_block.id,comm_block.name,comm_block.comment,DATE_FORMAT(date, ‘%d %M %Y %H:%m’) as comm_block.date,users.name FROM comm_block,users WHERE comm_block.id_data=» AND comm_block.name = ‘users.name’ ORDER BY id DESC «);
Admin 15.01.2012 00:38:13
Попробуйте убрать кавычки здесь: comm_block.name = ‘users.name’
lion337 15.01.2012 00:25:53
В этом запросе к базе данных я пытаюсь к коментам выбрать нужный аватар
lion337 15.01.2012 02:11:07
Спасибо я уже разобрался сам, вы правы ошибка была в кавычках. Всё равно спасибо за помощь
malina95 05.10.2012 21:09:27
Я правильно понимаю? Вы собираетесь перевести скрипт древовидных комментариев с голого SQL на ООП подключение?
beressst 22.01.2012 12:04:02
Здравствуйте, Михаил! У меня запрос по выборке из 2 таблиц не выдает информацию в столбцах, в результате пишет только имена столбцов. Эти две таблицы объединены ключевым полем так, что код при запросе прописывается во «FROM» следующим образом: dbo.Клиенты INNER JOIN dbo.Услуги ON dbo.Клиенты.[Код услуги 1] = dbo.Услуги.[Код услуги] AND dbo.Клиенты.[Код услуги 2] = dbo.Услуги.[Код услуги] AND dbo.Клиенты.[Код услуги 3] = dbo.Услуги.[Код услуги]. Если же я в коде удаляю связи [код услуги 2] и [код услуги 2 и 3] информация отбражается. Но нужно чтоб она отбражалась в варианте полного кода, т. е. 3 столбиков. Подскажите, пожалуйста, как это сделать?
Admin 22.01.2012 17:10:39
На вид всё правильно. Покажите реальный запрос, который отправляется.
beressst 22.01.2012 18:21:43
Уже разобралась, спасибо. Надо было вместо одной ключевой таблицы добавить 3 таблицы одинаковых теперь всё получилось. Спасибо!
Mikle-NSK 25.01.2012 12:21:20
Михаил, здравствуйте! Мне надо сделать выборку из нескольких (5-6) таблиц, на основе введенных пользователем значений: tab1: id, name tab2: id, name tab3: id, name1, name2 Пытаюсь сделать так: SELECT * FROM tab1, tab3 WHERE tab1.id = 16 AND tab3.name1=tab1.name — данный запрос выдает 0 строк 🙁 т.е. — мне сначала надо получить значение name tab1 по id, а в tab3 сделать выборку уже по полученному значению tab1.name Фактически же аналогичная выборка делается по всем таблицам, получается имя по идентификатору, а потом по этому имени производится поиск в общей таблице. Подскажите, пожалуйста, в чем ошибка?
Admin 25.01.2012 13:43:09
Возможно, вместо tab3.name1=tab1.name надо написать tab3.name=tab1.name
Mikle-NSK 25.01.2012 14:02:09
Нет, именно так. В tab3 поля name1 и name2, и их надо связать с полями name в таблицах tab1 и tab2.
Admin 25.01.2012 15:45:55
Здесь нет очевидной ошибки, всё на вид правильно. Возможно, какая-то путаница с именами таблиц и полей, но это только Вы можете знать. Например, Вы здесь писали про tab2, однако, в запросе она никак не фигурирует. Ничего другого предположить не могу, так как запрос правильный и рабочий. Он выбирает те записи, в которых name в таблице tab3 имеет значение, которое хранится в таблице tab1 поля name, где >
Mikle-NSK 25.01.2012 17:14:12
В том-то и проблема. Я подобные запросы лет 10 назад строил, все работало, а сейчас не идет выборка, и все тут. Путаницы с именами таблиц нет, все перепроверено на несколько раз. tab2 я написал для примера, у меня выборка идет из 5-6 таблиц: WHERE tab1.id=16 AND tab2.id=33 AND tab3.name1=tab1.name AND tab3.name2=tab2.name AND . tab5. Беда в том, что не работает именно простой запрос, который я привел выше. Не работает именно часть tab3.name1=tab1.name. такое ощущение, что после определения в tab1 индекса не происходит позиционирования на найденную строку, отсюда и невозможность выборки по tab1.name.
dwok 05.03.2012 11:55:20
Михаил здравствуйте. Мне надо сделать выборку следующего типа:’Select Sum(Prihod.COUNT),Sum(Spis.Count),Sum(Vid.Count),Sum(Vozv.Count) from Prihod,Spis,Vid,Vozv Where Prihod.COD=aa and Spis.COD=aa and Vid.COD=aa and Vozv.COD=aa ; aa передаю значение при формировании запроса. Проблема в том что запрос отрабатывает все поля на NULL если хоть в одной из таблиц нет данных по переданному условию. Где ошибка?
[email protected] 14.03.2012 10:50:41
здравствуйте, Михаил! подскажите, как можно вытащить столбцы под одинаковым названием из всех таблиц в одной папке?
Как вывести данные из нескольких таблиц в sql
Может я просто перегорел, но не мог понять, что такое декартово произведение. Когда вы выполняете запрос, который объединяет две таблицы, их строки комбинируются во всех возможных комбинациях. Декартово произведение строк таблицы A и таблицы B означает, что каждая строка из таблицы A будет соединена с каждой строкой из таблицы B.
24 января 2023
Судя по итоговой таблице должно быть так:
for (String row2 : table2) < for (String row1 : table1) < System.out.println(row1 + row2); >>
Потому что берем 1 строку из task (table2) и прикрепляем к её к каждой строке таблицы employee (table1) и так повторяем с каждой следующей строкой task.
Владимир Уровень 108 Expert
29 ноября 2022
id — уникальный номер задания (и стоки в таблице); Товаищи, здесь не хватает одной буквы! Для этого в SQL можно выполоть запрос кто у нас тут был плохим заплосиком? ��
Igor Уровень 108 Expert
4 ноября 2022
Поправьте пожалуйста таблицу с результатом вызова из двух таблиц, там начиная с employee_id все сдвинуто из-за того что employee_id сплитнули
Эдуард Уровень 108 Expert
15 октября 2022
Да здесь половина задач с темой из следующей лекции
Сообщество
JavaRush — это интерактивный онлайн-курс по изучению Java-программирования c нуля. Он содержит 1200 практических задач с проверкой решения в один клик, необходимый минимум теории по основам Java и мотивирующие фишки, которые помогут пройти курс до конца: игры, опросы, интересные проекты и статьи об эффективном обучении и карьере Java‑девелопера.
Подписывайтесь
Язык интерфейса
«Программистами не рождаются» © 2023 JavaRush
Скачивайте наши приложения
«Программистами не рождаются» © 2023 JavaRush
Этот веб-сайт использует данные cookie, чтобы настроить персонально под вас работу сервиса. Используя веб-сайт, вы даете согласие на применение данных cookie. Больше подробностей — в нашем Пользовательском соглашении.
Глава 8. ЗАПРАШИВАНИЕ НЕСКОЛЬКИХ ТАБЛИЦ
ТАК ЖЕ, КАК ОДНОЙ
До этого каждый запрос, который мы рассматривали, основывался на одиночной таблице. В этой главе вы узн́аете, как сделать запрос любого числа таблиц с помощью одной команды. Это чрезвычайно мощное средство, потому что оно не только объединяет вывод из многочисленных таблиц, но и определяет связи между ними. Вы обучитесь различным формам, использующим эти связи, а также их настройке и использованию, чтобы удовлетворять возможным специальным требованиям.
ОБЪЕДИНЕНИЕ ТАБЛИЦ
Одна из наиболее важных особенностей запросов SQL — их способность определять связи между многочисленными таблицами и выводить информацию из них, в терминах этих связей, всю внутри одной команды. Этот вид операции называется объединением, которое является одним из видов операций в реляционных базах данных. Как установлено в Главе 1, главное в реляционном подходе это связи, которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым числом таблиц и таким образом способны создавать связи между сравнимыми фрагментами данных. При объединении, таблицы, представленные списком в предложении FROM, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ими. Обычно предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию.
ИМЕНА ТАБЛИЦ И СТОЛБЦОВ
Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой, и затем имени столбца. Вот несколько примеров имён:
Salespeople.snum Salespeople.city Orders.odate
До этого вы могли опускать имена таблиц, потому что вы запрашивали единовременно только одну таблицу, а SQL достаточно интеллектуален, чтобы присвоить соответствующий префикс имени таблицы. Даже когда вы делаете запрос нескольких таблиц, вы ещё можете опускать имена таблиц, если все их столбцы имеют различные имена. Но так бывает не всегда. Например, мы имеем две типовые таблицы со столбцами, называемыми city. Если мы должны связать эти столбцы (кратковременно), мы должны будем указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.
СОЗДАНИЕ ОБЪЕДИНЕНИЯ
Предположим, что вы хотите поставить в соответствие вашему продавцу ваших заказчиков в городе, в котором они живут, поэтому вы увидите все комбинации продавцов и заказчиков для этого города. Вы должны будете брать каждого продавца и искать в таблице Заказчиков всех заказчиков того же самого города. Вы могли бы сделать это, введя следующую команду (вывод показан на Рисунке 8.1):
SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city; =============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | Salespeople.city | | FROM Salespeople, Customers | | WHERE Salespeople.city = Customers.city | | ============================================= | | cname cname city | | ------- -------- ---- | | Hoffman Peel London | | Hoffman Peel London | | Liu Serres San Jose | | Cisneros Serres San Jose | | Hoffman Motika London | | Clemens Motika London | ============================================= Рисунок 8.1 Объединение двух таблиц
Так как это city имеется и в таблице Продавцов, и таблице Заказчиков, имена таблиц должны использоваться как префиксы. Хотя это необходимо, только когда два или более полей имеют одно и то же имя, в любом случае это хорошая идея: включать имя таблицы в объединение для лучшего понимания и непротиворечивости. Несмотря на это, мы будем в наших примерах далее использовать имена таблиц только тогда, когда необходимо, так что будет ясно, когда они необходимы, а когда нет. Что SQL в основном делает в объединении, так это исследует каждую комбинацию строк двух или более возможных таблиц и проверяет эти комбинации по их предикатам. В предыдущем примере требовалась строка продавца Peel из таблицы Продавцов и объединение её с каждой строкой таблицы Пользователей, по одной в каждый момент времени. Если комбинация производит значение, которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel — это то запрашиваемое значение, которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).
ОБЪЕДИНЕНИЕ ТАБЛИЦ ЧЕРЕЗ СПРАВОЧНУЮ ЦЕЛОСТНОСТЬ
Эта особенность часто используется просто для эксплуатации связей, встроенных в БД. В предыдущем примере мы установили связь между двумя таблицами в объединении. Это прекрасно. Но эти таблицы уже были соединены через snum-поле. Эта связь называется состоянием справочной целостности, как мы уже говорили в Главе 1. Используя объединение, можно извлекать данные в терминах этой связи. Например, чтобы показать имена всех заказчиков, соответствующих продавцам, которые их обслуживают, мы будем использовать такой запрос:
SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum;
Вывод этого запроса показан на Рисунке 8.2. Это пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае snum-столбцы из обеих таблиц удалены из вывода. И это прекрасно. Вывод показывает, какие заказчики каким продавцом обслуживаются; значения поля snum, которые устанавливают связь, отсутствуют. Однако, если вы введёте их в вывод, то вы должны или удостовериться, что вывод понятен сам по себе, или должны обеспечить комментарий данных при выводе.
=============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | FROM Salespeople, Customers | | WHERE Salespeople.snum = Customers.snum | | ============================================= | | cname sname | | ------- -------- | | Hoffman Peel | | Giovanni Axelrod | | Liu Serres | | Grass Serres | | Clemens Peel | | Cisneros Rifkin | | Pereira Motika | ============================================= Рисунок 8.2 Объединение продавцов с их заказчикам
ОБЪЕДИНЕНИЕ ТАБЛИЦ ПО РАВЕНСТВУ ЗНАЧЕНИЙ
В СТОЛБЦАХ И ДРУГИЕ ВИДЫ ОБЪЕДИНЕНИЙ
Объединения, которые используют предикаты, основанные на равенствах, называются объединениями по равенству. Все наши примеры в этой главе до настоящего времени относились именно к этой категории, потому что все условия в предложениях WHERE базировались на математических выражениях, использующих знак равенства (=). Строки ‘city = ‘London’ и ‘Salespeople.snum = Orders.snum ‘ — примеры таких типов равенств, найденных в предикатах. Объединения по равенству это, вероятно, наиболее общий вид объединения, но имеются и другие. Вы можете использовать практически любую реляционную операцию в объединении. Здесь дан пример другого вида объединения (вывод показан на Рисунке 8.3):
SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200; =============== SQL Execution Log ============ | SELECT sname, cname | | FROM Salespeople, Customers | | WHERE sname < cname | | AND rating < 200; | | ============================================= | | sname cname | | -------- ------- | | Peel Pereira | | Motika Pereira | | Axelrod Hoffman | | Axelrod Clemens | | Axelrod Pereira | | | ============================================= Рисунок 8.3 Объединение, основанное на неравенстве
Эта команда не часто бывает полезна. Она воспроизводит все комбинации имени продавца и имени заказчика так, что первый предшествует последнему в алфавитном порядке, а последний имеет оценку, меньше чем 200. Обычно вы не создаёте сложных связей, подобно этой, и по этой причине вы, вероятно, будете строить наиболее общие объединения по равенству, но вы должны хорошо знать и другие возможности.
ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ
Вы можете также создавать запросы, объединяющие более двух таблиц. Предположим, что мы хотим найти все заказы заказчиков, не находящихся в тех городах, где находятся их продавцы. Для этого необходимо связать все три наши типовые таблицы (вывод показан на Рисунке 8.4):
SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city < >Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum; =============== SQL Execution Log ============== | | | SELECT onum, cname, Orders.cnum, Orders.snum | | FROM Salespeople, Customers, Orders | | WHERE Customers.city < >Salespeople.city | | AND Orders.cnum = Customers.cnum | | AND Orders.snum = Salespeople.snum; | | =============================================== | | onum cname cnum snum | | ------ ------- ----- ----- | | 3001 Cisneros 2008 1007 | | 3002 Pereira 2007 1004 | | 3006 Cisneros 2008 1007 | | 3009 Giovanni 2002 1003 | | 3007 Grass 2004 1002 | | 3010 Grass 2004 1002 | =============================================== Рисунок 8.4 Объединение трёх таблиц
Хотя эта команда выглядит скорее как комплексная, вы можете следовать за логикой, просто проверяя, что заказчики не размещены в тех городах, где размещены их продавцы (совпадение двух snum полей), и что перечисленные заказы выполнены с помощью этих заказчиков (совпадение заказов с полями cnum и snum в таблице Заказов).
РЕЗЮМЕ
Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого количества таблиц и использовать полученные результаты, чтобы решать, какую информацию вы хотели бы видеть. Фактически эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобна вещь. Это будет темой Главы 9.
РАБОТА СО SQL
Напишите запрос, который вывел бы список номеров заказов сопровождающихся именем заказчика, который создавал эти заказы.
Напишите запрос, который выдавал бы имена продавца и заказчика для каждого заказа после номера заказа.
Напишите запрос, который выводил бы всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.
Напишите запрос, который вычислил бы сумму комиссионных продавца для каждого заказа заказчика с оценкой выше 100.