Итоговые запросы. Запросы с группировкой

Внешнее соединение таблиц

Многотабличные запросы SQL. Соединения таблиц. Самосоединения. Псевдонимы

Оператор SELECT языка SQL. Запросы на чтение из одной таблицы. NULL-значения

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

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

Рассмотрим сначала простейший вид оператора SELECT для выборки данных из одной таблицы

SELECT [DISTINCT] <список полей> FROM <таблица> WHERE <условие>

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

Например, оператор

SELECT Name, Adress, Rost-Ves AS Ind FROM S WHERE SCity = ‘Москва’ обеспечивает чтение содержимого полей Name, Adress и Ind (вычисляемого поля, определяемого выражением Rost-Ves) из таблицы S для всех записей, у которых значением поля SCity является ‘Москва’.

Символ ‘*’ в списке полей обозначает полный перечень полей таблицы.

Условие представляет собой логическое выражение и может быть одним из следующих типов:

· сравнение значений с помощью операций ‘=’, ‘<>’, ‘<’, ‘>’, ‘<=’, ‘>=’;

· диапазон значений (BETWEEN);

· членство в множестве ([NOT] IN);

· соответствие шаблону ([NOT] LIKE);

· сравнение с неизвестным значением (IS [NOT] NULL).

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

Условие с BETWEEN можно реализовать и обычными операциями сравнения. Например, запрос

SELECT Name, Adress FROM S WHERE Age BETWEEN Age1 AND Age2

эквивалентен запросу

SELECT Name, Adress FROM S WHERE (Age >= Age1) AND (Age <= Age2)

Членство в множестве задается путем перечисления элементов множества, например

SELECT Name, Adress FROM S WHERE SCity NOT IN (‘Москва’, ‘Петербург’)

Соответствие шаблону требуется обычно для строковых данных. Знак ‘%’ предполагает задание любой последовательности символов, в том числе пустой строки, а знак ‘_’ – одного произвольного символа. Подобное назначение при поиске файлов по маске имеют привычные символы ‘*’ и ’?’.

Тип NULL служит для обозначения неизвестных или неопределенных значений. Раньше такие значения обычно задавали пустой строкой символов или нулем для числовых типов. Однако пустая строка или нуль могут иметь содержательный смысл. Например, коренным образом отличаются по смыслу нулевое или неизвестное количество правительственных наград для некоторого ветерана в соответствующей БД.

Использование NULL-значений приводит к трехзначной логике, в которой логические выражения могут принимать значения TRUE, FALSE и NULL или UNKNOWN (неизвестно). Значения NULL естественным образом дополняют обычные правила. Например, выражение (Age >= Age1) AND (Age <= Age2) заведомо ложно в том случае, когда Age2 имеет значение NULL, а (Age ≥ Age1) принимает значение FALSE.

Приведем таблицы истинности трехзначной логики для логических операций NOT, OR и AND.

Таблица 5

Таблица истинности операции NOT

A	NOT A
TRUE	FALSE
FALSE	TRUE
NULL	NULL

Таблица 6

Таблица истинности операции AND

A	B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
TRUE	NULL	NULL
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE
FALSE	NULL	FALSE
NULL	TRUE	NULL
NULL	FALSE	FALSE
NULL	NULL	NULL

Таблица 7

Таблица истинности операции OR

A	B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	NULL	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	TRUE	TRUE
NULL	FALSE	NULL
NULL	NULL	NULL

Например, запрос

SELECT * FROM Anketa WHERE Adress IS NULL

выдает все записи таблицы Anketa с неизвестным значением поля Adress.

Рассмотрим дополнительные возможности оператора SELECT. Записи результата могут быть отсортированы путем включения опции

ORDER BY Field1 [DESC], Field2 [DESC], …

Сортировка производится в первую очередь по значениям поля Field1, записи с одинаковыми значениями этого поля сортируются по Field2 и т. д. Необязательный параметр DESC вызывает сортировку по убыванию значений.

Можно объединить результаты двух запросов SELECT, вставив между ними c слово UNION. Необходимым условием в этом случае является одинаковое количество выбираемых атрибутов и соответствие их типов в обоих операторах SELECT. Опция ORDER BY при объединении может применяться только к конечному результату.

Запросы могут выбирать данные изнескольких таблиц. Эти таблицы должны быть перечислены после слова FROM. Если таблицы не связаны между собой, то результатом запроса будут всевозможные комбинации (декартово произведение) записей отдельных таблиц, что не имеет практического смысла.

Снова рассмотрим отношения по поставщикам S (S#, Sn, Scity), изделиям P (P#, Pn, Pcity, W) и поставкам SP (S#, P#, Q). Здесь S# - номер поставщика, Sn – его имя, Scity – место проживания, P# - номер изделия, Pn – его наименование, Pcity – место хранения, W – вес, Q – объем поставки.

Пусть требуется получить список поставок поставщиков из Москвы с указанием имени поставщика. Запрос

SELECT S.S#, Sn, P#, Q FROM S, SP WHERE Scity =’Москва’

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

SELECT S.S#, Sn, P#, Q FROM S, SP WHERE Scity =’Москва’ AND S.S#=SP.S#

Условие S.S#=SP.S# обеспечивает связь таблиц S и SP, что соответствует операции соединения реляционной алгебры. Если атрибут присутстствует более чем в одной таблице, сначала указывается имя таблицы, а затем после точки имя атрибута.

Как и в реляционной алгебре, возможно и θ-соединение таблиц, где θ задает знак операции сравнения (‘<’, ’>’ и т. п.), но такой вариант соединения редко применяется на практике. Для соединений таблиц имеются и другие синтаксические конструкции, которые будут описаны ниже. Рассмотрим еще два примера.

1. Найти список поставщиков из Казани, поставляющих изделие с номером P2

SELECT Sname FROM S, SP WHERE

S.S#=SP.S# AND

Scity =’Казань’ AND

P# = P1

Здесь выбирается поле Sname из таблицы S, но в качестве источника данных указывается и таблица SP, поскольку условия выборки и связи используют поля этой таблицы.

2. Получить список поставок объема более 100 единиц с указанием имен поставщиков и наименований деталей

SELECT Sname, Pname, Q FROM S, P, SP WHERE

S.S#=SP.S# AND

P.P#=SP.P# AND

Q > 100

Соединение таблиц может быть и по нескольким атрибутам. Например, деталь в разных таблицах может идентифицироваться двумя атрибутами совместно: номером изделия и номером детали в изделии.

Таблица может соединяться и сама с собой. Рассмотрим для примера таблицу сотрудников и их непосредственных руководителей S (S#, Sname, Shef#), где S# - номер сотрудника, Sname – его имя, Shef# - номер руководителя. Пусть требуется дать полный список имен сотрудников и их руководителей.

Если бы существовали две копии этой таблицы S1 и S2, то решением был бы запрос

SELECT S1.Sname, S2.Sname FROM S1, S2 WHERE S1.Shef# = S2.S#

А как обойтись без копирования содержимого таблицы S?

Для подобных случаев в SQL введены локальные псевдонимы или алиасы таблиц. Псевдоним действует в пределах запроса и позволяет обращаться к одной и той же таблице, как к двум разным таблицам. При использовании псевдонимов S1 и S2 в нашем случае вид запроса почти не изменится

SELECT S1.Sname, S2.Sname FROM S S1, S S2 WHERE S1.Shef# = S2.S#

Рассмотренные соединения называют внутренними (INNER JOIN). В некоторых случаях требуются соединения другого вида – внешние соединения (OUTER JOIN). Рассмотрим две таблицы A (Stud, Prep) и B (Prep, Kaf) с информацией о студентах-дипломниках и их руководителях. Здесь Stud – фамилия студента, Prep – фамилия руководителя диплома, Kaf – название кафедры, где работает руководитель. Будем для определенности считать, что однофамильцы отсутствуют.

Пусть, например, таблицы заполнены следующим образом

Таблица 8

Записи таблицы A

Stud	Prep
Иванов	Гусев
Петров	Николаев
Сидоров	NULL

Таблица 9

Записи таблицы B

Prep	Kaf
Гусев	Информатика
Николаев	Математика

Значение NULL для студента Сидорова показывает, что данные о его руководителе отсутствуют.

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

SELECT Stud, A.Prep, Kaf FROM A, B WHERE A.Prep = B.Prep

Результатом будет таблица

Таблица 10

Результат запроса

Stud	A.Prep	Kaf
Иванов	Гусев	Информатика
Петров	Николаев	Математика

А где же Сидоров? Хотелось бы получить таблицу

Таблица 11

Желаемый результат

Stud	A.Prep	Kaf
Иванов	Гусев	Информатика
Петров	Николаев	Математика
Сидоров	NULL	NULL

Как это сделать?

Выходом является внешнее соединение таблиц A и B, которое будем обозначать пока символами ‘*=*’. Для получения полного внешнего соединения двух таблиц необходимо

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

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

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

В приведенном выше запросе достаточно заменить знак ‘=’, определяющий внутреннее соединение таблиц A и B, на операцию внешнего соединения ‘*=*’.

Добавим еще одну запись в таблицу A

Таблица 12

Записи таблицы A

Stud	Prep
Иванов	Гусев
Петров	Николаев
Сидоров	NULL
Волков	Митрофанов

Результатом запроса с внешним соединением будет таблица

Таблица 13

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

Stud	A.Prep	Kaf
Иванов	Гусев	Информатика
Петров	Николаев	Математика
Сидоров	NULL	NULL
Волков	Митрофанов	NULL

Кафедра преподавателя Митрофанова неизвестна.

Добавим сейчас запись в таблицу B

Таблица 14

Записи таблицы B

Prep	Kaf
Гусев	Информатика
Николаев	Математика
Кокорин	Математика

В результате получим таблицу

Таблица 15

Результат запроса

Stud	A.Prep	Kaf
Иванов	Гусев	Информатика
Петров	Николаев	Математика
Сидоров	NULL	NULL
Волков	Митрофанов	NULL
NULL	NULL	Математика

В последней строке фамилия Кокорин отсутствует, так как в запросе указано поле A.Prep, а не B.Prep.

Предположим, что нас в первую очередь интересует информация о студентах, а не о преподавателях. Тогда нам не нужна последняя строка. Можно получить требуемый результат, используя операцию левого внешнего соединения ‘*=’. При выполнении этой операции третий пункт операции полного внешнего соединения не выполняется. Аналогично, при правом внешнем соединении ‘=*’ пропускается второй пункт полного внешнего соединения.

Пусть, например, таблица A содержит полный список студентов-дипломников, а B – список преподавателей, планируемых в качестве руководителей. Сместим акцент запроса, потребовав дать сведения о руководстве дипломами только данных преподавателей. Выполним запрос

SELECT Stud, B.Prep, Kaf FROM A, B WHERE A.Prep =* B.Prep

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

Таблица 16

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

Stud	A.Prep	Kaf
Иванов	Гусев	Информатика
Петров	Николаев	Математика
NULL	Кокорин	Математика

Информации о студентах Сидорове и Волкове не будет, поскольку их руководители отсутствуют в таблице B.

По стандарту SQL соединения таблиц задаются в предложении FROM. Внутреннее соединение именуется INNER JOIN, полное внешнее соединение -FULL [OUTER] JOIN, левое и правое соединения – LEFT [OUTER] JOIN и RIGHT [OUTER] JOIN (слово OUTER может опускаться). Условие соединения определяется после слова ON. Соединение, как и прежде, возможно и по нескольким полям.

Например, рассмотренный выше запрос может быть задан в форме

SELECT Stud, A.Prep, Kaf FROM A LEFT JOIN B ON A.Prep = B.Prep

Для одноименных полей двух таблиц вместо условия со словом ON можно использовать форму USING (<список одноименных полей>) или просто слово NATURAL. В последнем случае соединение происходит по всем одноименным полям.

Полное внешнее соединение требуется редко и реализовано не во всех СУБД.

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

Дата добавления: 2014-01-07