Объединение таблиц
12. Объединение таблиц
Объединение таблиц – это самые мощные операции, которые можно выполнить с использованием оператора SELECT.
Как известно, хранить множество экземпляров одних и тех же данных нежелательно, именно этот принцип и лежит в основе создания реляционных баз данных. Не рекомендуется хранить данные вместе по следующим причинам:
· Повторение информации для каждой записи приведет к напрасной потере времени и места на диске;
· Если информация изменится, то придется обновлять информацию каждый раз, когда придется вносить изменяемые данные;
· Когда данные повторяются, высока вероятность того, что данные не будут каждый раз вводиться одним и тем же образом. Несовместимые данные очень трудно использовать при создании отчетов.
Реляционные таблицы разрабатываются таким образом, что вся информация распределяется по множеству таблиц, причем для данных каждого типа создается отдельная таблица. Эти таблицы связываются между собой через общие значения.
В нашем примере таблица Vendors содержит информацию о поставщиках, по одной строке для каждого поставщика. В таблице имеется первичный ключ – это идентификатор поставщика.
В таблице Products хранится только информация о продуктах, и никакой конкретной информации о поставщиках, за исключением их идентификаторов – ключа таблицы Vendors. Этот ключ и связывает таблицы. Благодаря применению идентификатора поставщика можно использовать таблицу Vendors для поиска информации о поставщике.
Рекомендуемые материалы
Распределение данных по многим таблицам обеспечивает их более эффективное хранение, упрощает манипулирование данными и повышает масштабируемость, т.е. возможность беспрепятственно расширять базу данных.
Однако эти преимущества не получаются даром – за все нужно платить. Если данные хранятся во многих таблицах, то чтобы извлечь их с помощью одного оператора SELECT, их необходимо объединить.
Объединение представляет собой механизм, используемый для объединения таблиц внутри оператора. Важно понимать, что объединение не является физическим объектом, т.е. оно не существует как реальная таблица в базе данных. Объединение создается СУБД и сохраняется только на время выполнения запроса.
12.1. Создание объединения
При создании объединения нужно указать все таблицы, которые должны быть включены в объединение.
Рассмотрим пример.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
--------------------------
vend_name prod_name prod_price
Bears R Us 8 inch teddy bear 5.99p.
Bears R Us 12 inch teddy bear 8.99p.
Bears R Us 18 inch teddy bear 11.99p.
Doll House Inc. Fish bean bag toy 3.49p.
Doll House Inc. Bird bean bag toy 3.49p.
Doll House Inc. Rabbit bean bag toy 3.49p.
Doll House Inc. Raggedy 4.99p.
Fun and Games King doll 9.49p.
Fun and Games Queen doll 9.49p.
В операторе SELECT указываются столбцы из разных таблиц: prod_name, prod_price – в одной, vend_name – в другой.
В предложении FROM содержится две таблицы Vendors и Products, которые должны быть объединены.
Предложение WHERE указывается само объединение – идентификатор поставщика vend_id из таблицы Vendors связывается со значением vend_id таблицы Products. Имена столбцов указываются полностью, включая и имя таблицы.
12.2. Важность предложения WHERE при создании объединения
Когда объединяем две таблицы, то это означает, что создаются пары, состоящие из каждой строки первой таблицы и каждой строки второй таблицы. Предложение WHERE действует как фильтр, позволяющий включать в результат только строки, которые соответствуют указанному предложению фильтрации, т.е. предложению объединения. Без предложения WHERE каждая строка в первой таблице будет образовывать пару с каждой строкой второй таблицы независимо от того, есть логика в их объединении или нет. Умножение таблиц без указания условия объединения образуют декартово произведение. Количество выбранных строк будет равно числу строк в первой таблице, умноженному на число строк во второй таблице.
Выполним следующий пример.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
--------------------------
vend_name prod_name prod_price
Bears Emporium Fish bean bag toy 3.49p.
Bears R Us Fish bean bag toy 3.49p.
Doll House Inc. Fish bean bag toy 3.49p.
Fun and Games Fish bean bag toy 3.49p.
Fun and Inc. Fish bean bag toy 3.49p.
Jouets et ours Fish bean bag toy 3.49p.
Bears Emporium Bird bean bag toy 3.49p.
. . .
Jouets et ours King doll 9.49p.
Bears Emporium Queen doll 9.49p.
Bears R Us Queen doll 9.49p.
Doll House Inc. Queen doll 9.49p.
Fun and Games Queen doll 9.49p.
Fun and Inc. Queen doll 9.49p.
Jouets et ours Queen doll 9.49p.
Из примера видим, что декартово произведение возвращает очень большое количество данных, намного больше, чем нужно.
12.3. Внутренние объединения
Объединение, которое мы рассматривали, называется объединение по эквивалентности – оно основано на проверке эквивалентности двух таблиц. Такое объединение также называется внутренним объединением. Для таких объединений можно использовать несколько иной синтаксис, явно указывающий на тип объединения.
Выполним тот же пример, но с другим синтаксисом.
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
--------------------------
vend_name prod_name prod_price
Bears R Us 8 inch teddy bear 5.99p.
Bears R Us 12 inch teddy bear 8.99p.
Bears R Us 18 inch teddy bear 11.99p.
Doll House Inc. Fish bean bag toy 3.49p.
Doll House Inc. Bird bean bag toy 3.49p.
Doll House Inc. Rabbit bean bag toy 3.49p.
Doll House Inc. Raggedy 4.99p.
Fun and Games King doll 9.49p.
Fun and Games Queen doll 9.49p.
Здесь отношение между двумя таблицами является частью предложения FROM, указанного как INNER JOIN. При использовании такого синксиса предложение объединения указывается с использованием специального предложения ON вместо предложения WHERE. Фактическое предложение , передаваемое в ON, то же самое, которое передавалось бы в предложение WHERE.
12.4. Объединение многих таблиц
Язык SQL не ограничивает число таблиц, которые могут быть объединены посредством оператора SELECT. Основные правила для создания объединения остаются теми же. Вначале перечисляются все таблицы, затем определяются отношения между ними.
Пример. Вывести предметы заказа номер 20007. Предметы заказа хранятся в таблице OrderItems. Каждый продукт хранится в соответствии с идентификатором продукта, который ссылается на продукт в таблице Products. Эти продукты связаны с соответствующими поставщиками в таблице Vendors по идентификатору поставщика, который хранится вместе с каждой записью о продукте. В предложении FROM необходимо перечислить три таблицы, а в предложении WHERE определить два предложения объединения. Для фильтрации предмета заказа 20007 надо использовать предложение WHERE.
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products ,Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
--------------------------
prod_name vend_name prod_price quantity
Fish bean bag toy Doll House Inc. 3.49p. 100
Bird bean bag toy Doll House Inc. 3.49p. 100
Rabbit bean bag toy Doll House Inc. 3.49p. 100
18 inch teddy bear Bears R Us 11.99p. 50
Raggedy Doll House Inc. 4.99p. 50
СУБД обрабатывает объединения, тратя время на обработку каждой указанной таблицы. Этот процесс может оказаться очень рекурсивным, поэтому надо использовать объединение таблиц достаточно осторожно.
Перепишем пример, в котором использовались подзапросы, для возвращения списка клиентов, заказавших продукт RGAN01, с использованием объединения.
SELECT cust_name, cust_contact
FROM Customers, Orders ,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = ‘RGAN01’;
Вам также может быть полезна лекция "21. Загазованность воздуха, характеристика, воздействие, нормирование".
--------------------------
cust_name cust_contact
Fun4All Denise L. Stephens
The Toy Store Kim Howard
Здесь требуется три таблицы и вместо подчиненных подзапросов применены два объединения для связи таблиц и фильтр по продукту.
Как видим, часто существует несколько способов для выполнения одной и той же операции. И очень трудно определить какой вариант лучше. Часто бывает целесообразно поэкспериментировать с различными механизмами выборки для выяснения того, какой из них работает быстрее.
