Выборка из нескольких таблиц

В самом простом случае список таблиц задаётся перечислением:

Select * /*все поля исходной таблицы*/ from Customer, Employee

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

 

Поля таблицы 1	Поля таблицы 2
Запись 1	Запись 1
Запись 1	Запись 2
Запись 1	Запись 3

 

Именно в силу этого данный пример совершенно непотребный: m´n – это очень много.

select * from Orders, Item, Product

where ID=Order_Ref, ID=Product_Ref;

Коллизия имён различных таблиц разрешается, как обычно, использованием полных, или квалифицированных, имён.

where Order. Id=Order_Ref

Product. Id=Product_Ref;

Правда такой приём не спасает при выборке из декартовых степеней.

Выделим однофамильцев:

select cust1.name, cust1.Id, cust2.Id

from Customer. cust1, Customer. cust2

where (cust1.name=cust2.name) and (cust1.Id<cust2.Id);

В таких случаях используют псевдонимы, локальные имена, или алиасы. В более сложном синтаксисе условие связи отделяется от условия фильтрации и записывается в списке выборки: (имя таблицы 1) join (имя таблицы 2) on (условие связи).

select cust1.name, cust1.Id, cust2.Id

from Cusromer. cust1 join

Customer. cust2 on

cust1.name=cust2.name

where cust1.Id<cust2.Id;

Опция join имеет дополнительные опции, а именно: Inner, Left, Right, Full.

Inner – по умолчанию.

Что делать с официальными сиротами, то есть значениями ключей, равными null? Внутреннее соединение inner join игнорирует такие записи. Left join добавляет в результирующую выборку все записи из левой таблицы со значениями ключа null в комбинации с фиктивной нулевой записью второй таблицы.

select (что-то) from T₁,T₂ where B(r₁,r₂) /*предикат*/ /*r_i – поле таблицы T_i*/

То же самое:

select (что-то) from T₁ join T₂ on B(r₁,r₂)

T₁ [inner] join T₂ on B(r₁,r₂)

{r₁År₂: r₁ÎT₁ & r₂ÎT­₂ /*все возможные комбинации записей*/ & B(r₁,r₂)}

 

T₁ left join T₂ on B(r₁,r₂) = {r₁År₂: r₁ÎT₁ & r₂ÎT­₂ & B(r₁,r₂)} U

U{r₁ÅNull₂: r₁ÎT₁ & Ø$ r₂ÎT­₂: B(r₁,r₂)}

Ø$ - not $ - не существует.

Null₂­ – фиктивная запись таблицы T₂, то есть запись с именами из T₂ со значениями, равными null.

select cust1.name, cust1.Id, cust2.Id

from Customer. cust1 left join Customer. cust2

on (cust1.name=cust2.name) and (cust1.Id>cust2.Id)

 

T₁ right join T₂ on B(r₁,r₂) = {r₁År₂: r₁ÎT₁ & r₂ÎT­₂ & B(r₁,r₂)} U

U{r₁ÅNull₁År₂: r₁ÎT₁ & Ø$ r₁ÎT­₁: B(r₁,r₂)}

T₁ full join T₂ on B(r₁,r₂) = Left U Right U Inner – объединение.

 

Опции GROUP BY и HAVING

Группировка и групповые вычисления

Group by (список полей)

Поля могут задаваться именем или порядковым номером поля в списке выборке.

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

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

Функции, определённые на группах (не записях), называют агрегатными. При использовании опции group by список выборки обязан состоять только из обращений к таким функциям.

К групповым функциям относятся (по определению):

· поля, входящие в список группировки, а также следующие функции:

· count (выражение над полями записи) – количество значений данного выражения в группе;

· count (*) – число записей в группе;

· min (выражение), max (выражение) – соответственно минимальное и максимальное значение заданного выражения для каждой группы; определены для значений произвольного скалярного типа;

· sum (выражение числового типа) – сумма значений по группе

· avg (выражение числового типа) – среднее арифметическое значение по группе.

Примечание. Обращение к агрегатным функциям возможно и без явной группировки, то есть использования опции group by; в этом случае группой считается вся таблица.

Пример. Для каждого покупателя подсчитать количество заказов.

select Cust_Ref, count (Id)

from Orders

group by Cust_Ref;

Не выдаст записи со значением null, то есть покупателей без заказов. В реальности синтаксис практически свободный.

Опция having употребляется только вместе с group by. Это предикат, ссылающийся на значения групповых функций.

Синтаксис: having (условие на группу)

Как правило, сравнение значений агрегатной функции с константой.

Семантика опции having часто путается с семантикой опции where. Having включает условие, показывающее, вычислять ли значения соответствующих агрегатных функций для группы.

Пример. Вычислить среднее число покупок казанских покупателей – для достаточно крупных покупателей (то есть тех, у кого больше ста покупок).

select count (order.Id)

from Custoner, Orders

where (customer.Id=orders.Cust_Ref) and (customer.city=’Казань’)

group by Cust_Ref

having count (orders.Id)>100;

 

Опции ORDER BY и UNION

Order by (список полей) [Asc, Desc]

Поля могут задаваться либо именем, либо порядковым номером в списке выборки. Сортирует результат выборки по значению указанного поля (лексикографически, если их несколько).

Asc ending – по возрастанию (по умолчанию).

Desc ending – по убыванию.

Опция All/Distinct: выбор distinct удаляет из результата выборки полностью совпадающие записи. По умолчанию действует опция all, то есть такое удаление не производится.

Опция Union – объединение выборок.

(команда select) union [all] (команда select)

Обычно интерпретируется как подопция команды select. Легче интерпретировать как операцию над двумя выборками, а именно – объединения двух таблиц, то есть именованных отношений.

Для того, чтобы гарантировать, что результат объединения снова будет таблицей, типы таблиц должны быть согласованы по числу и типу полей (не по именам). Число и типы полей должны совпадать. Размерность – снова тёмное место языка.

Довольно неожиданно применение union убирает по умолчанию все записи-дубликаты из результата. Чтобы избежать этого, используется опция [ all ]. В отличие от этого, в варианте select all/distinct, оставляющем в единичной выборке либо все записи, включая дубликаты (опция [ all ]), либо только различные (опция [ distinct ]). По умолчанию действует опция [ all ].

Замечание. Union запрещается использовать в подзапросах. При использовании опции union, опция order by может использоваться лишь однократно после последней выборки. В этом случае упорядочивается не последняя выборка, а всё объединение.

Пример. Вывести фамилии покупателей и продавцов в лексикографическом порядке.

select name from Customer

Union

select name from Employee

order by 1;

Результат выборки, вообще говоря, - логическая таблица. Значения выборки не сохраняются, на них нельзя сослаться в последующих вычислениях.

Многие версии языка включают опцию [ into ] (преемник выборки), позволяющую сохранять результат либо в реальной таблице (в файле), либо в курсоре (CurSOR).

CurSOR – Current Set Of Record – текущее множество записей во временном файле, доступном в текущем сеансе работы, но исчезающим по завершении работы.

Представления дают удобный способ сослаться на результаты выборки, не прибегая к их сохранению.

 

Предикаты, использующие выборку

Вложенные подзапросы

Предикаты – условия на запись, - используемые в опции where (как в командах модификации, так и в самой команде select), в свою очередь могут содержать ссылку на выборку. К таким предикатам относятся:

1) r c all (команда select), где r – выражение над записью, c – знак сравнения.

Предикат истинен, если сравнение выполняется для значения выражения r и всех значений, выбранных командой select. При этом предполагается, что select выдаёт некий список значений list, то есть, формально, таблицу с единственным полем, совместимым по типу с типом выражения r.

» " lÎ list (r c l)

Если выборка пуста, значение предиката true.

2) r c [ any ½ some ] (команда select)

При тех же условиях на select эквивалентно предикату:

$ lÎ list (r c l)

3) r [ not ] in (команда select)

При тех же условиях на select: rÎ list.

4) [ not ] exists (команда select)

В данном случае на select не накладывается никаких ограничений. Предикат истинен, если выборка не пуста: $ r: r Î select

Пример. Молодые покупатели_1.

select name, Id

from Customer

where birthday>(select avg (birthday) from Employee);

 

Молодые покупатели_2.

select name, Id

from Customer

where birthday>(select avg (birthday) from Customer);

Коллизии имён нет, по умолчанию каждое имя поля ссылается на таблицы из ближайшего from. Синтаксис SQL не запрещает именам полей внутренней выборки ссылаться на таблицы внешней выборки. Такие подзапросы называются соотнесёнными.

Молодые покупатели_3.

select name, Id

from Customer, Cust1

where birthday>(select avg (birthday) from Customer, Cust2

where Cust1.city=Cust2.city);

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

Создание представлений

Create view (пользовательское имя представления) as (команда select)

[ with check option ] – с опцией проверки

Также популярны опции local (локальный, ближний), remote (удалённый).

В первом случае соответствующая команда select ссылается только на данные клиента. Удалённое представление ссылается на данные сервера.

Представление – это именованная выборка. Таблица получает пользовательское имя.

Представления считаются частью определения БД и хранятся вместе с физическими, или базовыми, таблицами. В реальности, конечно, хранится не виртуальная таблица, но её определение, то есть текст команды select. Основное назначение представления – позволить программисту определять сложные иерархии понятий, базирующиеся на основной реляционной модели, ориентированные на различные группы пользователей.

create view Young_Customers as select (что-то)

create view Young_Kazan_Customers

as select * from Young_Customers

where city=’Казань’;

Customer®Young _Customers®Young_Kazan_Customers

Процесс создания представлений обратен процессу проектирования БД.

В какой степени к такой виртуальной таблице можно относиться как к таблице реальной, физической? Команды выборки могут ссылаться не только на базовые таблицы, но и на представления. В реальности это означает, что при выполнении соответствующей выборки исполняется вложенный подзапрос, соответствующий определению данного представления. В отличие от хранения промежуточных результатов в реальных таблицах (например, CurSOR), обращение к представлению даёт гарантированно актуальную информацию.