Выборка данных с помощью усложненных методик работы с запросами

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

Извлечение данных с помощью соединений

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

Соединения задают в конструкциях FROM или WHERE. Условия соединения вместе с условиями конструкций WHERE и HAVING определяют строки, выбранные из основной, указанной в конструкции FROM таблицы. Однако определение условий соединения в конструкции FROM позволяет отделить их от других условий поиска, например, заданных в конструкции WHERE. Поэтому рекомендуется задавать соединения именно этим методом.

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

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

Хотя в условии соединения обычно используется знак равенства (=), разрешается применять и другие реляционные операторы или операторы сравнения (или иные предикаты).

Когда SQL Server обрабатывает соединение, механизм обработки запросов выбирает для этого наиболее эффективный метод (из нескольких возможных). Хотя при физическом исполнении различных соединений оптимизация производится no-разному, логическая последовательность операций такова:

• применяются условия соединения из конструкции FROM;

• применяются условия соединения и условия поиска из конструкции WHERE;

• применяются условия поиска из конструкции HAVING.

Иногда эта последовательность может влиять на результат запроса, если перемещать условия между конструкциями FROM и WHERE.

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

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

Большинство соединений можно переписать в виде подзапросов (запросов, вложенных внутри других запросов), а большинство подзапросов удается переписать в виде соединений.

Большинство соединений относятся к внутренним или внешним.

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

Внешние соединения возвращают все строки, по крайней мере по одной из указанных в конструкции FROM таблиц или представлений, которые соответствуют любому из условий поиска, заданных конструкциями WHERE или HAVING. Также допустимы перекрестные соединения и самосоединения. Более подробно о перекрестных соединениях и самосоединениях — в SQL Server Books Online.

Внутренние соединения

Во внутренних соединениях значения соединяемых столбцов сравниваются посредством оператора сравнения. В стандарте SQL-92 внутренние соединения задаются в конструкциях FROM или WHERE. Внутренние — единственный тип соединений, которые SQL-92 поддерживает в составе конструкции WHERE; их называют «устаревшими» внутренними соединениями. В следующем операторе SELECT внутреннее соединение используется для получения данных из таблиц Persons и Titles базы данных Writers:

SELECT p.Name, t.Title

FROM Persons AS p INNER JOIN Titles AS t

ON p.PersonId = t.AuthorId

ORDER BY Title ASC

GO

Этот оператор SELECT получает данные из столбца Title таблицы (t) и столбца Name таблицы Persons (p). Поскольку в этом операторе применяется внутреннее соединение, он возвращает только те строки, для которых в столбцах соединения (p.PersonId и t.AuthorId) имеются равные значения.

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

SQL Server поддерживает три типа внешних соединений: левые, правые и полные. Левое внешнее соединение ссылается на все строки левой таблицы, а правое внешнее соединение — на все строки правой таблицы. Полное внешнее соединение возвращает все строки из обеих таблиц.

Результирующий набор, сгенерированный оператором SELECT, в который входит левое внешнее соединение, состоит из всех строк таблицы, на которую ссылается конструкция LEFT OUTER JOIN, расположенной слева от этой конструкции. Из таблицы, которая расположена справа, извлекаются только те строки, которые соответствуют условию соединения.

В следующем сценарии в операторе SELECT для получения имен, фамилий авторов и (где возможно) названий всех издательств, расположенных в тех же городах, где проживают авторы, применяется левое внешнее соединение:

USE Master

SELECT a.LastName. a.FirstName, p.Place

FROM Authors a LEFT OUTER JOIN Publishers p

ON a.Place = p.Place

ORDER BY p.Name ASC, a.LastName ASC, a.FirstName ASC

GO

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

Результирующий набор, сгенерированный оператором SELECT, в который входит правое внешнее соединение, состоит из всех строк таблицы, стоящей справа от конструкции RIGHT OUTER JOIN. Из таблицы, которая расположена слева, извлекаются только те строки, которые соответствуют условию соединения.

В следующем сценарии в операторе SELECT правое внешнее соединение применяется для получения списка названий издательств и имен с фамилиями авторов, если последние проживают в том же городе, где расположено издательство:

USE Master

SELECT a.LastName. a.FirstName, p.Name

FROM Authors a RIGHT OUTER JOIN Publishers p

ON a.Place = p.Place

ORDER BY p.Name ASC, a.LastName ASC, a.FirstName ASC

GO

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

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

В следующем операторе SELECT полное внешнее соединение используется для получения списка названий издательств, а также имен и фамилий авторов:

USE Master

SELECT a.LastName. a.FirstName, p.Name

FROM Authors a FULL OUTER JOIN Publishers p

ON a.Place = p.Place

ORDER BY p.Name ASC, a.LastName ASC, a.FirstName ASC

GO

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

Определение подзапросов внутри операторов SELECT

Подзапросом называется возвращающий единственное значение оператор SELECT, вложенный в другой оператор SELECT, INSERT, UPDATE, DELETE или в другой подзапрос. Его разрешается применять в любом месте, где разрешено использование выражения. Подзапрос также называется внутренним запросом или внутренней выборкой, а содержащий подзапрос оператор — внешним запросом или внешней выборкой.

В следующем сценарии подзапрос вложен в конструкцию WHERE внешнего оператора SELECT:

USE Master

SELECT Name

FROM Products

WHERE Price =

(

SELECT Price

FROM Products

WHERE Name = 'Helicopter'

)

GO

Встроенный оператор SELECT сначала определяет значение Price для продукта под названием “Helicopter”, которое равно, скажем, 1000000 рублей. После этого значение 1000000 используется во внешнем операторе SELECT для извлечения имен всех продуктов, цена которых равно миллиону.

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

В некоторых операторах Transact-SQL подзапрос обрабатывается так, как будто независимый запрос и его результаты подставляются во внешний запрос (хотя реальный способ обработки SQL Server операторов Transact-SQL с подзапросами может отличаться).

Подзапросы можно задавать в самых различных местах оператора SELECT. Однако операторы с подзапросами обычно представлены в одном из следующих форматов:

• WHERE <выражение> [ NOT ] IN (<подзапрос>)

• WHERE <выражение> <оператор_сравнения> [ ANY | ALL ] (<подзапрос>)

• WHERE [ NOT ] EXISTS (<подзапрос>)

Результатом подзапроса с ключевым словом IN (или NOT IN) является список, состоящий из нуля или более значений. Результат, который возвращает подзапрос, используется внешним запросом.

В следующем фрагменте кода подзапрос вложен в конструкцию WHERE и используется ключевое слово IN:

USE Master

SELECT LastName, FirstName

FROM Person

WHERE PersonId IN

(

SELECT TitleId

FROM Titles

WHERE Type = 'Worker'

)

GO

Сначала внутренний запрос возвращает номера идентификаторов должностей, которые определена типом Worker. Затем эти значения подставляются во внешний запрос, который находит названия, соответствующие номерам идентификаторов из таблицы Titles.

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

В операторах сравнения, используемых с подзапросами, разрешается применять ключевые слова ALL или ANY. Ключевое слово SOME в SQL считается эквивалентом ANY. Подзапросы в операторах сравнения возвращают нуль или более значений и могут включать в себя конструкции GROUP BY или HAVING. Эти подзапросы можно переопределить с помощью ключевого слова EXISTS.

Ключевые слова ALL и ANY сравнивают скалярное значение с набором значений одного столбца. Ключевое слово ALL применяется ко всем значениям, а ключевое слово ANY — как минимум к одному.

В следующем сценарии ключевое слово ANY используется с оператором сравнения «больше»:

USE Master

SELECT Title

FROM Titles

WHERE Advance > ANY

(

SELECT Advance

FROM Persons INNER JOIN Titles

ON Titles.PersonId = Person.PersonId

AND Name = 'worker'

)

GO

Этот оператор находит работников, которые авансированы суммой, превышающей минимальную сумму аванса, уплаченного персонам на рабочей должности (скажем, 5 000 000 рублей).

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

Когда подзапрос содержит ключевое слово EXISTS, он функционирует как проверка наличия той или иной сущности. Конструкция WHERE внешнего запроса проверяет, есть ли строки, которые возвращает подзапрос. Этот подзапрос на самом деле не выдает никаких данных, вместо этого он возвращает значение TRUE или FALSE.

В следующем сценарии конструкция WHERE из внешнего оператора SELECT содержит подзапрос и использует ключевое слово EXISTS:

USE Master

SELECT Name

FROM Persons

WHERE EXISTS

(

SELECT * FROM Titles

WHERE Titles.TitleId = Persons.TitleId

AND Type = 'director'

)

GO

Чтобы определить результат этого запроса, необходимо по очереди рассмотреть имя каждого работника. Есть ли в таблице Titles строки, значение поля TitleId которых равно такому же полю таблицы персон, а должнсть — директор? Если да, то соответствующее значение должно быть среди выбранных. Этот процесс повторяется для каждого имени персоны.

Ключевые слова NOT EXISTS работают аналогично EXISTS за исключением того, что условие конструкции WHERE, где используется NOT EXISTS, выполняется, когда подзапрос не возвращает ни одной строки.

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