Таблица 6.1. Примеры результатов вызова функции isnull О

Теперь рассмотрим, как можно применить эту функцию в данном примере запроса:

SELECT с.LastName,

ISNULL(CAST((SELECT MIN(OrderDate)

FROM Sales.SalesOrderHeader о

WHERE o.ContactID = e.ContactID) AS varchar), NEVER ORDERED) AS Order Date FROM Person.Contact с

После этого в строках, в которых формировались не удовлетворяющие нас простые NULL-значения, появляются следующие, гораздо более полезные результаты:

Akers Sep 1 2001 12:00AM

Alameda NEVER ORDERED

Alberts Sep 1 2002 12:00AM

Обратите внимание на то, что для успешной реализации данного запроса пришлось применить функцию CAST (). Это потребовалось в связи с тем, что при выполнении рассматриваемого запроса возникают проблемы приведения типа и неявного преобразован . Посколысу при обработке первой строки происходит возврат действетельной даты, принимается предположение, что столбец Order Date относится к тиггу DateTime. А после того как впервые происходит замена NULL-значения строковым значением NEVER ORDERED с помощью функции ISNULL, возникает ошибка, поскольку значение строки NEVER ORDERED невозможно преобразовать в значение с типом данных DateTime. Рекомендуем читателю учесть возможность применен функции CAST (), так как она позволяет справиться с небольшими сложностями, подобными описанной. Дополнительная информация по этой теме приведена ниже в данной главе.

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

Производные таблицы

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

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

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

Предположим, например, что требуется получить список заказчиков, заказавших определенный товар, скажем, мини-насосы. Необходимый для этого запрос может быть сформулирован без каких-либо сложностей. Соответствующий запрос может выглядеть примерно так:

SELECT с.FirstName, с.LastName FROM Person.Contact AS с JOIN Sales.SalesOrderHeader AS soh

ON e.ContactID = soh.ContactID JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderlD = sod.SalesOrderlD JOIN Production.Product AS p

ON sod.ProductID = p.ProductID WHERE p.Name = Minipump

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

WHERE р.Name = MinipumpAND p.Name = AWC Logo Cap

Ho подобный запрос является абсолютно неприемлемым, так как каждая строка содержит сведения только об одном товаре, поэтому таких строк, в которых были бы одновременно указаны названия товаров Minipump и AWC Logo Сар, в базе данных нет. Это означает, что попытка выборки подобных строк окончится неудачей (и действительно, после вызова запроса на выполнение формируется результирующий набор, не содержащий строк).

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

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

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

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

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

SELECT <select list>

FROM (<query that returns a regular result set>) AS <alias name> JOIN <some other base or derived table>

Итак, применим этот общий замысел для решения поставленной задачи. Еще раз отметим, чем необходимо определить названия всех компаний, которые заказали и мини-насосы, и бейсболки с логотипом AWC. Поэтому итоговый запрос должен выглядеть следующим образом:

SELECT DISTINCT с.FirstName, с.LastName FROM Person.Contact AS с JOIN (SELECT ContactID

FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderlD = sod.SalesOrderlD JOIN Production.Product AS p

ON sod.ProductID = p.ProductID WHERE p.Name = Minipump) pumps ON e.ContactID = pumps.ContactID JOIN (SELECT ContactID

FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderlD = sod.SalesOrderlD JOIN Production.Product AS p

ON sod.ProductID = p.ProductID WHERE p.Name = AWC Logo Cap) caps ON e.ContactID = caps.ContactID

Как оказалось, в заказах строки с упоминанием мини-насосов и бейсболок часто встречаются одновременно, поскольку полученные результаты состоят из 83 строк: FirstName LastName

Aidan Delaney

Alexander Deborde

Amy Alberts

Valerie Hendricks

Yale Li

Yuping Tian

(83 row(s) affected)

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

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

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

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