Создание расширенных объединений

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

13.2. Самообъединения

Одной из причин для использования псевдонимов таблиц – это обращение к одной и той же таблице несколько раз в одном операторе SELECT.

Пример. Необходимо послать письма по всем контактным адресам клиентов, которые работают с той же компанией, с которой работает Джим Джонс. Для этого необходимо сначала выяснить, с какой компанией работает Джим Джонс, а затем – какие клиенты работают с этой же компанией.

Рассмотрим 1-ое решение с использованием подзапросов

SELECT cust_id, cust_name, cust_contact

FROM Customers

WHERE cust_name = (SELECT cust_name

FROM Customers

WHERE cust_contact = ‘Jim Jones’);

--------------------------

Cust_id cust_name cust_contact

1000000003 Fun4All Jim Jones

1000000004 Fun4All Denise L. Stephens

Теперь используем объединение.

SELECT c1.cust_id, c1.cust_name, c1.cust_contact

FROM Customers AS c1, Customers AS c2

WHERE c1.cust_name = c2.cust_name

AND c2.cust_contact = ‘Jim Jones’;

--------------------------

Cust_id cust_name cust_contact

1000000003 Fun4All Jim Jones

1000000004 Fun4All Denise L. Stephens

Здесь для выполнения запроса требуется две таблицы, на самом деле это одна и та же таблица. Если использовать один псевдоним, то СУБД может не знать, на какую таблицу идет ссылка. Используются два псевдонима. Первый раз для таблицы Customers назначается псевдоним с1, второй – псевдоним с2. Оператор SELECT использует префикс с1 для однозначного указания полного имени нужного столбца. Если этого не сделать, СУБД возратит сообщение об ошибке, потому что имеется по два столбца с именем cust_id, cust_name и cust_contract. СУБД не знает, какой именно столбец имеется в виду, даже в действительности это один и тот же столбец. Первое предложение WHERE объединяет эти таблицы, а затем оно фильтрует данные второй таблицы по столбцк cust_contact, чтобы возвратить нужные данные.

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

13.2. Естественные объединения

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

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

Пример.

SELECT C.*, o.order_num, O.order_date, OI.prod_id,

OI.quantity, OI.item_price

FROM Customers AS C, Orders AS O, OrderItems AS OI

WHERE C.cust_id = O.cust_id

AND OI.order_num = O.order_num

AND prod_id = ‘RGAN01’;

--------------------------

cust_id cust_name cust_address cust_city cust_state cust_zip

1000000004 Fun4All 829 Riverside Drive Phoenix AZ 88888

1000000005 The Toy Store 4545 53^rd Street Chicago IL 54545

cust_countr cust_contact cust_email order_num order_date

USA Denis L. Stephens dstephens@fun4all.com 20007 30/01/2004

USA Kim Howard 20008 03.02.2004

prod_id quantity item_price

RGAN01 50 4,49p.

RGAN01 5 4,49p.

В этом примере метасимвол используется только для первой таблицы. Все остальные столбцы указаны явно, поэтому никакие дубликаты столбцов не выбираются.

13.3. Внешние объединения

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

Например, может понадобиться использовать объединение для решения следующих задач:

· подсчета количества заказов каждого клиента, включая клиентов, которые еще не сделали заказ;

· составление перечня продуктов с указанием количества заказов на них, включая продукты, которые никто из клиентов не захотел заказывать;

· вычисление средних объемов продаж с учетом клиентов, которые еще не сделали заказ.

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

В примере выбирается список всех клиентов и их заказы.

SELECT Customers.cust_id, Orders.order_num

FROM Customers INNER JOIN Orders

ON Customers.cust_id = Orders.cust_id;

--------------------------

cust_id order_num

1000000001 20005

1000000001 20009

1000000003 20006

1000000004 20007

1000000005 20008

Синтаксис внешнего объединения похож на этот. Для выборки имен всех клиентов, включая тех, которые еще не сделали заказов, можно сделать следующее.

SELECT Customers.cust_id, Orders.order_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id = Orders.cust_id;

--------------------------

cust_id order_num

1000000001 20005

1000000001 20009

1000000002

1000000003 20006

1000000004 20007

1000000005 20008

Аналогично внутреннему объединению в этом операторе SELECT используются ключевые слова OUTER JOIN для указания типа объединения. Но, в отличие от внутренних объединений, которые связывают строки двух таблиц, внешние объединения включают в результат также строки, не имеющие связанных с ними строк. При использовании синтаксиса LEFT OUTER необходимо использовать ключевое слово RIGHT или LEFT, чтобы указать таблицу, все строки которой будут включены в результат. RIGHT для таблицы, имя которой стоит справа от OUTER JOIN, LEFT – для той, имя которой стоит слева.

В следующем примере попробуем использовать RIGHT.

SELECT Customers.cust_id, Orders.order_num

FROM Customers RIGHT OUTER JOIN Orders

ON Customers.cust_id = Orders.cust_id;

--------------------------

cust_id order_num

1000000001 20005

1000000001 20009

1000000003 20006

1000000004 20007

1000000005 20008

В разных СУБД синтаксис внешнего объединения может отличаться. SQL Ыукмук поддерживает упрощенный синтаксис внешнего объединения. Для выбора перечня всех клиентов, включая тех, которые не разместили ни одного заказа запрос будет иметь вид.

SELECT Customers.cust_id, Orders.order_num

FROM Customers, Orders

WHERE Customers.cust_id *= Orders.cust_id;

--------------------------

cust_id order_num

1000000001 20005

1000000001 20009

1000000002

1000000003 20006

1000000004 20007

1000000005 20008

Здесь предложение объединения указано в предложении WHERE. Вместо проверки на равенство с помощью оператора = используется оператор *= для указания того, что в результат должна быть включена каждая строка таблицы Customers. Оператор *= это оператор левого внешнего объединения. Для правого внешнего объединения используется оператор =*.

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

Пример полного внешнего объединения.

SELECT Customers.cust_id, Orders.order_num

FROM Orders FULL OUTER JOIN Customers

ON Orders.cust_id = Customers.cust_id;

Синтаксис полного внешнего объединения не поддерживается в СУБД Access, MySQL, SQL Server.

13.4. Использование объединений со статистическими функциями

Статистические функции можно использовать также по отношению к объединениям.

Рассмотрим пример. Надо выбрать список всех клиентов и число сделанных ими заказов. Чтобы получить искомое, применим функцию COUNT().

SELECT Customers.cust_id,COUNT( Orders.order_num) AS num_ord

FROM Customers INNER JOIN Orders

ON Customers.cust_id = Orders.cust_id

GROUP BY Customers.cust_id;

--------------------------

cust_id num_ord

1000000001 2

1000000003 1

1000000004 1

1000000005 1

Для связи таблиц Customers и Orders используются ключевые слова INNER JOIN. Предложение GROUP BY служит для получкения итоговых данных по клиентам, и, таким образом, обращение к функции COUNT(Orders.order_num) позволяет подсчитать количество заказов каждого клиента.

Статистические функции можно использовать с объединениями других типов.

SELECT Customers.cust_id,COUNT( Orders.order_num) AS num_ord

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id = Orders.cust_id

GROUP BY Customers.cust_id;

--------------------------

cust_id num_ord

1000000001 2

1000000002 0

1000000003 1

1000000004 1

1000000005 1

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

13.5. Использование объединений и условий объединения

В лекции "5. Человек как примат" также много полезной информации.

При использовании объединений необходимо учитывать:

· Надо быть внимательным при выборе типа объединения, которое собираетесь использовать. Чаще используется внутреннее объединение, но может понадобиться и внешнее объединение.

· Необходимо уточнить в документации СУБД синтаксис объединений.

· Необходимо проверить условие объединения, иначе будут возвращены неверные данные.

· Не забывать указывать предложение объединения, в противном случае получите декартово произведение.

· Можно включать в объединение много таблиц и даже применять для каждой из них свой тип объединения.

Поделитесь ссылкой:

Популярные услуги

Создание расширенных объединений

Рекомендуемые материалы

Рекомендуемые лекции