Использование множества таблиц в одном запросе.

На лаб. раб. мы рассматривали запросы выбирающие информация из нескольких таблиц.

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

Операция такого рода называется соединением (Join) и является одной из самых мощных операций для рел. баз данных.

При операции соединения таблицы перечисляются через запятую в предложении запроса FROM.

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

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

Наиболее часто встречающимся примером эквисоединения может служить операция соединения таблиц посредством ссылочной целостности.

Но в операции соединения могут участвовать любые столбцы различных таблиц (и даже одной и той же таблицы) не обязательно связанных отношением “предок - потомок”.

Выполнение операции соединения – декартово произведение.

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

Объединенную таблицу образуют пары тех строк из различных таблиц, у которых в связанных столбцах содержаться одинаковые значения. Если пара отсутствует (соответствующие значение еще не присвоено, т.е. имеет значение NULL), то стандартное SQL – объединение может привести к потере информации.

Рассмотренное объединение иногда называют внутренним объединением таблиц.

Внешнее соединение

В стандарте SQL 89 определено только внутренне соединение.

Построение внешнего объединения(объединения “сохраняющего информацию”)

1. Создать внутреннее соединение двух таблиц обычным образом.

2. Каждую строку первой таблицы, которая не имеет связи ни с одной строкой второй таблицы, добавить в результате запроса, присваивая всем столбцам второй таблицы значения NULL.

3. Каждую строку второй таблицы, которая не имеет связи ни с одной строкой первой таблицы, добавить в результате запроса, присваивая всем столбцам первой таблицы значения NULL.

4. Результирующая таблица является внешним объединением двух таблиц.

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

Левое внешнее объединение

Выполнить пункты 1 и 2, пункт 3 пропустить.(т.е. попадают в рез-т все несвязанные строки из левой таблицы). Обозначается *=.

Правое внешнее объединение

Выполнить пункты 1 и 3, пункт 2 пропустить. (т.е. попадают в рез-т все несвязанные строки из правой таблицы). Обозначается =*.

*=*

В sql2

Select * from tabl inner Join tabl2

On tabl.col1 = tabl2.col2

Или

Select * from tabl inner Join tbl2

Using (col1, col2)

Естественное соединение

Select * from tabl Natural Inner Join tbl2

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

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

SQL позволяет выполнять вложенные подзапросы. Обычно внутренний запрос генерирует значения, которые тестируются на предмет истинности предиката.

Пример:

Выбор всех записей в которых поле “имя”=<значение>

Старый вариант - использовался в лаб. работе

if !empty(thisform.combo2.value)

select nam

locate for nam_val = alltrim(thisform.combo2.value)

w_n_num=n_num

else

w_n_num=0

endif

select test_lb.uniq_id,fam.f_val as family, nam.nam_val as nam, otc.otc_val as otc,;

street.s_val as street, test_lb.bldn as bld, test_lb.bldn_kor as bld_k,;

test_lb.appr as appr, test_lb.telef as tel;

from test_lb, fam, nam, otc, street;

where nam.n_num=test_lb.name_ AND;

fam.f_num=test_lb.fam AND;

street.s_num=test_lb.street AND;

otc.otc_n=test_lb.sndname AND;

nam.n_num=w_n_num;

INTO table test

Новый вариант - с подзапросом:

select test_lb.uniq_id,fam.f_val as family, nam.nam_val as nam, otc.otc_val as otc,;

street.s_val as street, test_lb.bldn as bld, test_lb.bldn_kor as bld_k,;

test_lb.appr as appr, test_lb.telef as tel;

from test_lb, fam, nam, otc, street;

where nam.n_num=test_lb.name_ AND;

fam.f_num=test_lb.fam AND;

street.s_num=test_lb.street AND;

otc.otc_n=test_lb.sndname AND;

nam.n_num = (SELECT n_num

FROM Nam

WHERE Nam_val =‘Иван’);

INTO table test

Чтобы оценить внешний (основной) подзапрос, SQL должен оценить внутренний запрос в предложении WHERE. Эта оценка осуществляется так, как будто внутренний запрос является одиночным:

Просматриваются все строки таблицы Nam и выбираются строки для которых значение поля Nam_val=‘Иван’ для таких строк выбирается значение поля n_num. Выбранной оказывается одна строка.

Выбранное значение подставляется в предикат основного запроса. Затем основной запрос выполняется как обычный.

Преимущества: - сработает при изменении номера, более гибкий (годится для всех имен);

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

Контрольные вопросы

1. Перечислите функции агрегирования, используемые в предложении Select.

2. Опишите операцию соединения таблиц.

3. Особенности операции внешнего соединения таблиц.

4. Синтаксис SQL92 операции соединения таблиц.

Лекция 7

Подзапрос должен выбирать только одну (или ни одной - значение предиката -unknown) записи если выбирается несколько записей - подзапрос оценивается как ошибочный;

Предикаты с подзапросами являются неперемещаемыми.

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

используют форму <скалярное выражение> <оператор сравнения> <подзапрос>.

Конструкции <подзапрос> <оператор> <скалярное выражение> или <подзапрос> <оператор> <подзапрос> недопустимы.

Использование агрегатных функций в подзапросах.

Одним из видов функций, которые автоматически выдают единственное значение для любого количества строк - являются агрегатные функции. Любой запрос, использующий единственную агрегатную функцию без предложения GROUP BY, дает в результате единственное значение для использования его в основном предикате.

EX ?

SELECT * FROM Orders

WHERE amt >

(SELECT AVG(amt)

FROM Orders

WHERE Odate = 02/28/2011)

При применении предложения GROUP BY агрегатные функции могут дать в результате множество значений. Поэтому их нельзя применять в подзапросах.

Такие команды отвергаются в принципе(при синтаксическом разборе запроса - без выборки данных). Несмотря на то, что применение GROUP BY и HAVING в некоторых случаях дает единственную группу в качестве результата подзапроса.

Использование подзапросов возвращающих более одной строки.

Для использования подзапросов возвращающих более одной записи можно применить оператор IN во внешнем запросе. (Нельзя применять BEETWEEN, LIKE, IS NULL) . IN - определяет множество значений, которые тестируются на совпадение с другим значением для определения истинности предиката. Когда IN применяется

с подзапросом, SQL строит это множество из выходных данных этого подзапроса.

Orders

Структура справочников:

Найти все заказы для продавцов из Москвы

SELECT *

FROM Orders

WHERE Sl_num IN

( SELECT sl_num

FROM Sales_p

WHERE Sl_city=‘Москва’ )

Данную задачу можно решить с использованием Join

SELECT Orders.U_id, Orders.part, Sales_p.sl_nam

FROM Orders, Sales_p

WHERE Orders.Sl_num = Sales_p.sl_num

AND Sales_p.Sl_city = ‘Москва’

(Достоинства и недостатки: результаты запроса непосредственно не видны и если есть ошибки в данных обнаружить их будет трудно).

Результат работы этих запросов должен быть одинаковым (с точностью до столбцов).

Эффективность: оптимизатор, зависящий от реализации, join -> подзапрос

Общим во всех рассмотренных подзапросах было использование в качестве результата единственного столбца. Это необходимо - поскольку выходные данные подзапроса сравниваются с единственным значением. Следовательно, вариант SELECT * нельзя использовать в подзапросе. (Исключением являются подзапросы с оператором EXISTS)

Связанные подзапросы

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

Строка внешнего запроса, для которой выполняется внутренний запрос, называется текущей строкой - кандидатом. Алгоритм выполнения связанного подзапроса состоит в следующем:

1. Выбрать строку из таблицы, имя которой указано во внешнем запросе. Это текущая строка-кандидат.

2. Сохранить значения этой строки в алиасе, имя которого указано в предложении FROM внешнего запроса.

3. Выполнить подзапрос. Использование в подзапросе значения из строки-кандидата внешнего запроса называется внешней ссылкой.

4. Оценить предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Это позволяет определить, будет ли строка кандидат включена в состав выходных данных.

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

найти всех клиентов сделавших заказы 26.02.15

SELECT *

FROM Clients as outer

WHERE 26/02/2015 IN

(SELECT Odate

FROM Orders as inner

WHERE outer.Client_num=inner.Client_num)