соединения 149

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

Конечно, лучше заранее создач ь статическое представлетче, чтобы агрегировать продажи каждого магазина. Са.м запрос ста-т бы короче, и onTHNniBarop, скорее всего, .мог бы повторно заде11Ствовать план запроса, который он генерирует для выполнения каждой агрегации:

CREATE VIEW SalesByState AS

SElECr s.stor iG. SUM(s.qty) AS TotalSales. t.state FRCM sales s JOIN stores t ON (s,stor ]d=t.stor 1a) GROUP BY t.state. s.stor id

SELECI s.state. st.stor name.s.tota!sa!es.Rank=COUNT(*) FROM SalesByState s JOIN SalesByState t ON (s.state=t.state)

JOIN stores St ON (s.stor 1d=st.stor id) WHERE s.totaisales <- t.totalsales GROUP BY s.state.St.stor name,s.totaisales HAVING COUNK*) <=1 ORDER BY s.state, rank

Од1!ако есть ситуации, в которых нельзя заранее создать представление. Если вы столкнетесь с такой ситуацией, лучше всего воспользоваться производными таблицами.

Соединения

в главе 1 рассмагрившочся различнряе типы соединени!!, !юддерживае.мые Transacl-SQL, так что здесь я остановлюсь на особенностях соединеш1Й, которые не раскрыты в ней. Перечитайте главу 1, если вы не поняли, как работают соединения, или вам необходимо освежить в памяти основы соедгигеиий.

Внешние соединения и порядок соединений

Порядок предложений во внутренне.м соединении не влияет на результирующее мрюжество. Если А = В, то, разумеется, В = А. Предложения во внутренних соединения ассоциативны. Для внешних соединений это не так. Порядок, в котором таблицы соединяются, напрямую влияет на то, какие записи будут включены в результирующее множество и какие значения они будут иметь. Вот почему так важно использовать ANSl-CHirraKcne внешних соединений - устаревший синтаксис .может генерировать ошибочные или неоднозначные результирующие .множества, иото.му что указание условий соединения в предложении WHERE препятствует их однозначному упорядочиванию.

Чтобы понять, как влияет порядок соединерпш на OUTER JOIN, давайте иссле-дуе.м, как он влияет на результирующее множество, которое генерирует запрос. Вот запрос, вычисляющий су.м.му элементов таблицы Orders, базы данных Northwind:

SELECT SUM(d.UnitPr1ce*d.Quantity) AS TotalOrdered

EROM Orders о LEFT OUTER JOIN [Order Details] d ON (o.OrderID+10=d.OrderID) LEFT OUTER JOIN Products p ON (d.ProductID=p.ProductID)

lOtdlCrdered

1339743.1900

Я специально добавил в запрос нарушение условия соединения путе.м увел чення o.OrderlD на десять, чтобы вы могли увплеТь, как порядок предложен: Lt невыполнение соединений влияют на результирующее множество. Теперь i Bairre игмени.м порядок таблиц в предложении FROM и вычисли.м тот же са.\н агрегат:

SELECT SU.;(d.Un1tPrice d,Quantity) AS TotalOndened

F.ROH [Order Details] d LEET OUTER JOIN Products p ON (d.PrccuctlD-p,ProductlD) LEFT OUTER JOIN Orders о ON (o.0rderI0+10=d.0,-derI0)

Total Ordered

1354458.5900

Видите несоответствие? Сумма из.меняется в зависимости от порядка та лиц, потому что несоответствие между таблицами Orders и Order Details в перв( запросе появляется до сулшировапия столбцов Unit-Price li Quantity; BTopoir запр выполняет это после. Во второ.м запросе мы получае.м сумму всех элементов та лицы Order Details, пезавнсимо от того, есть ли соответствующие записгг в та липе Orders; этого не происходит в нервом запросе. Чтобы лучше разобратьс рассмотрим данные, на которых основаны эти две су.м.мы:

SELECT 0.OrderDate, d.UmtPnce, d.Quantity

FROM Orders 0 LEFT OUTER JOIN [Order Details] d ON (o.OrderIDrlC=d.Q-derlD) LEFT OUTER JOIN Products p ON (d.ProductID=p.ProductID) WHERE 0.OrderDate IS NULL OR a.UnitPrice IS NULL

OrderDate

Unitnce

Quantity

Я включил предложение WHERE, чтобы оставить только те записи, для koti рых .мы намеренно ввели несоответствие. Поскольку мы увеличиваем OrderNo i десять, а заказы нумеруются последовательно, для десяти зршчений OrderNo в та( лице Orders не будут найдены соответствующие записи в таблипе Order Detai и, следовательно, значения полей UnitPrice и Quantity будут равны NULL. Вот cm .мок данных второго запроса (снова для ограпнчения включено предложенг. WHERE):

SELECT 0.OrderDate, d.UnitPnce. d.Quantity

FROM [Order Details] d LEFT OUTER JOIN Products p ON (d.ProouctlD-p.ProauctlD) LEFT OUTER JOIN Orders о ON (o.0rderID+10=d.0rderID) WHERE 0.OrderDate IS NULL

писей, если быть точным. Почему? Двадцать девять записей были пропущены в результирующем множестве первого запроса из-за несоответствия соединения, хотя сразу это не было очевидно. Для каждой разорванной связи данное число занисей Order Detail было пропущено, потому что .между таблица.ми Orders и Order Details существует отношение одип-ко-мпогим . Это, конечно, искажаю су.мму, полученную запросо.м.

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

Предикаты

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

Предикаты обычно используются в предложениях WHERE или HAVING запросов, хотя они могут быть расположены и в друго.м месте (например, в выражениях CASE). Предикаты могут являться простыми логическими выражения или могут быть составлены из функций, которые возвращают TRUE или NOT TRUE.

Хотя, технически, любая ()у1и<цня может быт!, ик.ночепа в выраженне-нреднкат, Transact-SQL определяет некеггорое количество предикативных функций, которые специально предназначены для фильтрации запросов и результирующих множеств, В следующем разделе мы под))обно расс.чютрнм каждую из них.

BETWEEN

Предикат BETWEEN, наверное, наиболее часто пспользуе.мый из предикатов Transact-SQL. Он определяет, попадает ли заданное 31гачен11е в интервал между двумя значения.мн:

SELECT au lname, au fname EROM authors

WHERE au lname BETWEEN S AND ZZ ORDER BY au lname

au Iname au frame

Smith Meander

Straight Dean

Stringer Dirk

White Johnson

Yokomoto Akiko

BETWEEN работает со скалярньь\Н! диапазоналн!, так что он может работать с датами, числа.ми и другими скалярнылиг тина,\П! да1П1ых. Он объединяет то, что обычно требует двух условий в предложенни WHERE: выражеше больще-че.м-или-равно , сопровождае,\юе вьгражение.м меньше-чем-или-равно . WHERE aujname BETWEEN S AND ZZ - сокращенный вариант для WHERE aujname >= S AND aujname <=ZZ.

В BETWEEN можно использовать не только конста!ггы, но и подзапросы, переменные и выражения: DECLARE laaujd id

SELECT iaauJd=(SELECT MAX(au ia) FROM titleauthor) SELECT au lname. au fname FROM authors

WHERE aujd BETWEEN (SELECT MIN(au id) FROM titleauthor) AND

ISNULL(iaaujd,ZZZZZZZZZZZ)

ORDER BY aujname

(результаты сокращены)

au Iname au fnaaie

Bennet ADraham

Blotchet-HalIs Reginalo

Canson Cheryl

DeFrance Michel

del Castillo Innes

(.,.)

White Johnson

Yokomoto Akiko

Так как основное предназначение этого предиката - определять, лежит ли значение в заданном диапазоне, обычно BETWEEN применяется для определения, произошло лн некоторое событие между двумя другими. Нахождение пересе-

кающихся coobLTiiii - задача оолее сложная, че.м кажется с первого взгляда, и эта ее oб.vкнчивaя простота может nptnsecTn ко nhiothm неправильным рещения.м.

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

CREATE TABLE lengagements (Engagement varcharOC).

EngagementStart smalldatetime.

EngagementEnd smalldatetime)

INSERT fengagements VALUES (Gulf of 1оп:ч1л'.19640802.19640804) INSERT fengagements VALUESCDa Neng.1965030Г .19650331) INSERT engagements VALUESCTet Offensive.19630131. 19680930) INSERT engagements VALUES(Bombing of Camoodfa.19690301,1970033Г) INSERT #engagements VALUES(Invasion of Cambodia.19700401.19700430) INSERT #en,gagements VALUESCFall of Saigon. 19/50430.19750430) CREATE TABLE #soldien tours (Soloien vanchar(30).

lounStantsmalldatetime.

TounEnd smalldatetime)

INSERT #so!dier touns /ALli£S(hencerson. Robert Lee.19700126.19700615) INSERT #soldier tours VALUES(Henaerson. Kayle Dean.19690110.19690706) INSERT fsoldier tours VALUES(Hendenson. Isaac Lee.19680529.19680722) INSERT #soldier tours VALUES(Henderson, James D,.19660509.19670201) INSERT #so;aien tours VALUES(Henderson. Robert Knapp.

19700218.19700619) INSERl #sold;er tours VALUES(Hende-son. Rufus 0..19670909.19680320) INSERT Isoldierjours VALUESIHenaerson, Robert Michael.

19680107.19680131) INSERT #soldier tours VALUES(Hendenson, Stephen Carl,

19690102,19690914) INSERl #soldier tours VAuUES(Henderson, tommy Ray,19700713.19710303) INSERT #soldier tours VALUES(Henderson, Greg Neal,19701022,19710410) INSERT #soldierjours VALUES(Henderson, Cha-les E.,19661001,19/50430)

Вот предварительное решепие:

SELECT Solaier served during the -Engagement FROM #soldier tours, #engagements

WHERE (TounStart BETWEEN EngagementStart AND EngagementEnd) OR (TourEnd BETWEEN EngagementStart AhD EngagementEnd) OR (EngagementStant BETWEEN TourStart AND TourEnd)

Henderson. Isaac Lee served during ttie Tet Offensive

Henderson. Rufus Q, served during the Tet Offensive

Henderson. Robert Michael served during tne Tot Offensive

Henderson. Charles E, serveo during the Tet Offensive

Henaerson, Robert Lee served during the Bombing of Cambodia

Henderson, Kayle Dean served during the Bombing of Cambodia

Henderson. Rooert Knapp served during the Bombing of Cambocia

Henderson, Stephen Carl served during the Bombing of Cambodia

Henderson. Charles E. served during the Bombing of СатЬсаа

Fer-derson. Rober;, Lee served ar-ng ire Invasion c Canccaia rienderson. Robert Krapp served during the Invasion of CaitDGG;a Henderson. Charles E. served our-ng the Invasion o Canbodia Henderson, Charles E. served during the Fall of Saigon

После того как таблицы со.здаиы и заполнены, запрос включаег записи в результирующее .чиюжество, используя три отдельных предиката BETWEEN; солдат нач11л служить во время события, его служба закончилась во время события или событие началось в период его службы. Заче.м на.м нужна эта последняя проверка? Заче.м необходимо проверять, начаюсь лн событие во время службы солдата, - это ведь то же caNK)c, что спрашивать - закончилась лн служба солдата вс вре.мя события, не так ли? Нет, не совсем. Без третьего предиката мы упустили бы возможность, что событие началось и закончилось в период стужбы.

Хотя этот запрос работает, существует лучшее решение Для него необходн.мо представить себе обратную задачу. Вместо того чтобы определять, когда период службы и событие пересекаются, давайте определи.м, когда они не пересекаются. Для того чтобы период службы и событие не совпада.ти, одно из двух утверждений должно быть истинным; лнбо период службы закончился до начала события, либо он начался после окончания события. Зная это, мы можем значительно упростить запрос и убрать предикаты BETWEEN, как здесь:

SELECT Soldier-f served during the +Engagement EROM #soldier tours. #engagements

WHERE NOT ((TourEnd < EngagenentStart) OR (TourStart > EngagementEnd})

Henderson, Isaac Lee senved during the let Offensive

Henderson, Rufus 0, served during the let Offensive

Henderson, Robert Michael served during the let Offensive

Henderson, Charles E. served during the let Offensive

Henderson. Robent Lee served during the Bombing of Cambocia

Henderson, Kayle Dean served during the Bomoing of Cambodia

Henderson, Rooert Knapp served during the Bombing of Camboda

Henderson. Stephen Carl served curing the Bombing of Cambodia

Henderson, Charles E. served during the Bombing of Cambodia

Henderson. Robent Lee served during the Invasion of Cambooia

Henderson. Robert Knapp served during the Invasion of Cambodia

Henoerson. Charles E, served during the Invasion of Cambodia

Henderson. Chanles E, served during the Fall of Saigon

LIKE

LIKE проверяет значение на соответствие строково.му образцу;

SELECT au lname. au fname FROM authors

WHERE au iname LIKE Green

au iname au frame

Green Marjorie

ANSI SQL определяет два типа подстановочных си.\шолов: си.чпюл % (процент и символ (подчеркивание); % соответствует любому количеству символов, то гда как соответствует точно одному. Вот при.мер:

SELECT au lname. au fname FROM authors

Green Manjonie

Greene Mormngstar

Gring-esPy Burt

Помимо подстановочных си.\п!олов ANSI SQL, Transact-SQL также поддерживает рег^лярные выражепия в качестве подстановочных символов. С помощью этих выражений вы можете проверить символ на принадлежность .множеству сн.мволов. Вот пример:

select аи 1пзте, au fname from aut.ho.-s

ivhere au 1nam,e like StrLaijr au lname au fname

au Iname au fname

Stnaight Dean

Stmngen Dirk

В при.мере выще [ai] - это регулярное выражение, которое соответствует любой строке, и.меющей а или i в четверто!! позиции. Для исключения строк с по-лющью регулярных выражент! добавьте в начало регулярного выражения си.мвол , как здесь:

SELECT au lname, au fname FROM authons

WHERE au lname LIKE Gn[e]X

au Iname au fna.me

Gninglesby Bunt

В это.м при.мере .мы запращивае.м авторов, чьи фа.милии начинаются с Gr и содержат си.мволы, отличные от е в третьей позиции. Есть несколько тонких различий .между и %. Си.мвол требует наличия по меньшей .мере одного сп.м-вола; % ие требует ни одного. Это различие лучше всего объяснить иа при.мере. Во-первых, рассмотри.м этот запрос:

SELECT au lname. au fname FROM authors

WHERE au lname LIKE GreenX

au Iname au fname

Green Marjorie

Greene Morningstar

A теперь этот:

SELECT au 1name. au fname FROM authors

WHERE au 1name LIKE Gneeh

au lname au fhame

Greene Konningstar

AhtRt а.г.аж like G.r

a и name au fnam.e

Видите разшту? Так как требует не менее одного символа, Green не соответствует Green. Другой .мо.мент, заслуживаюиннТ вни.мания, ~ строки .могут успешно проходить сравнение на равенство, но не проходить сравнение с по.мошью LIKE. Это трудно понять, поскольку LIKE кажется менее строгим по сравнению с обычным сравнением на равенство. Причина, по которой это становится воз.\южны.м, следующая: правила ANSI SQL, связанные с догюлнешге.м строк пробела.\И!, требуют, чтобы строки, проверяемые на равенство, были перед сравнением дополнены пробела.ми до одинаковой длины. Это не касается LIKE. Есл1г одно значение дополнено пробела.ми, а другое нет, сравнение, скорее всего, будет неудачнььм. Вот пример:

SELECT aujname. au fname FROM authors

WHERE aujname = Green

au lname aujname

Green Marjorie

SELECT au lname. au fname FROM authors

WHERE aujname LIKE Green

au Iname au fname

Обратите вниамание, что второй запрос не возвран1ает ни одной записи из-за дополнения пробелами строковой константы даже при том, что сравнение на равенство прекрасно работает.

EXISTS

EXISTS - это предикативная функция, которая прини.мает подзапрос в качестве своего единственного параметра. Она работает очень просто: если подзапрос возврашает результирующее множество - любое результирующее множество, - EXISTS возвращает True; иначе - False.

Хотя параметр EXISTS не требуется заключать в скобкл, лучи1е нх не опускать. Это необходимо, чтобы не запутать синтаксический анали:5атор запросов Transact-SQL, Подзапрос, передаваемый EXISTS, обычно представляет собой связанный подзапрос. Под связанным я имею в виду, что подзапрос использует в своем предложении столбцы WHERE или HAVING из внешнего соединения - с нх помощью он соединяется. Конечно, это не относится к применению EXISTS в операторах управлениях ходом выполнения, таких как IF или WHILE, а только к при.ме-нению оператора SELECT.

Как правило, в подзапросах, передаваемых EXISTS, лучше нри.менять SELECT *. Это позволяет оптимизатору выбрать столбец для использовантш и ускоряет процесс. Вот пример простого предиката EXISTS:

SELECT title FROM titles t

WHERE EXISTSCSELECT * FROM sales s WHERE s.title id4.title id)

But Is It User Friendly?

Compiiter Phobic AND Non-Plobc Ir;G; v xua I s benavor Variatiors Cocking w.th Computers: Surreptitious Bclarce Sheets Emotional Security: A New Algorithm

Этот запрос возвращает все те названия позиций, для которых существуют продажи (записи в таблице sales). Конечно, этот запрос можно также перегиюать с использованием BnyTpeiniero соединения, ио об этом позже. Добавление NOT к EXISTS приводит к отрицанию выражения;

SELEC title FROM titles t

WHERE NOT EXiSTS(SELECl FROM sd :es s whERt s. 111 io d-t. 11 tie id; title

Net Etiquette

Это так, иото.му что в таблице sales нет за1И1сей для названия Net Etiqnette.

NULL

NULL влияют па EXISTS некоторым интересным образо.\г Давайте исследуем, что произойдет, когда в таблицу sales добавятся NULL-значения:

SELECT title FROM titles t

WHERE EXISTS(SELEC * FRCM

(SELECT * FROM sales -- Ha са.мом деле в этом нет необходимости, только в целях иллюстрации UNION ALL

SELECT NULL. NULL. NULL. 90. NULL, NULL) s WHERE s.title id=t.title id AND s,qty>=75)

title

В этом запросе применяется UNION для добавления на лету в таблицу sales записи, содержащей в основно.м NULL-зпачеиия. Все поля, кроме qty, установлены в NULL. Даже при том, что соответствующие столбцы в таблице sales не могут и.меть значение NULL, подзапрос использует результат объединения sales и записи с неоиределеины.ути значеиня.мп (производная таблица), а пе са.му таблрщу. Использование UNION для добавления виртуа/Тьной записи, избавляет пас от необходимости .модификации таблицы sales для исследования воздействия NULL-значенин на EXISTS.

Хотя мы и добавили запись, содержащую qty со значением больще 75, результирующее множество пустое, пото.му что значение titlejd этой записи равно NULL, что ие соответствует ни одной записи в таблице titles. Поскольку значение titlejd в этой записи неизвестно, вы могли поду.\5ать, что она будет соответствовать каждой записи в таблице titles, по это не так. Даже если бы таблица titles содержала записи со значение.м NULL в столбце titlejd, они все равно не соответствовали бы друг другу, так как один NULL никогда не равен другому (это можно из.менить

(резу;1 ьтаты сокращены) title

с помощью команды SET ANSI NULLS - за более детальной информацией обратитесь к главе 3, Отсутствующие значения ). Это кажется не.много странным и npoTHBoecTecTBeHHbi.vi, но SQL Server так работает.

Отрицание выражения EXISTS также приводит к некоторы.м странным эффектам. Вот при.мер:

SELECT title FROM titles t

WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM sales UNION ALL

SELECT NULL, NULL. fJULL, NULL, NUlL. NULL) s WHERE s,tit;e id=t,title id)

title

Net Etiquette

Так как сервер не может знать, соответствует ли titlejd для Net Etiquette не-определенно.му значению из объединения, вы могли бы подумать, что будет получено пустое результирующее множество. С NULL в объединении .мы не .може.м определенно знать, что Net Etiquette titlejd не существует; однако запрос, несмотря на это, возвращает Net Etiquette. Очевидное несоответствие здесь появляется из-за способа вычисления выражешгй. Сначала SQL Server определяет, существует ли значение, а затем производит отрицание выражения. Мы вычисляем отрицание положительного, а пе отрицательного предиката. Мы имеем выражение NOT EXISTS (обратите вии.ущние на пробел между ключевы.\н1 слова.ми), а не NOTEXISTS(). Итак, когда запрос получает the titlejd для Net Etiquette, он пытается определить, существует ли соответствующее значение titlejd в таблице, полученной с помощью UNION. Конечно, он не южeт .этого сделать, потому что та.м нет этого идентификатора. Поэтому EXISTS возвращает False, в результате отр1.Н1ания получается True, п запись включается в результирующее множество, даже несмотря на тот факт, что эта запись пе существует в таблтще подзапроса.

EXISTS и IN

Преобразование предиката IN к EXISTS и.меет несколько своих особенностей. На-при.мер, первый запрос с EXISTS может быть переписан с использованием IN, на-при.мер так:

. SELECT title FROM titles t

WHERE t.title id IN (SELECT title id FROM sales) (результаты сокращены) title

But Is It User Friendly?

Computer Phobic AND Non-Phobic Individuals; Behavior Variations Cooking with Computers: Surreptitious Balance Sheets Emotional Security: A New Algoritnm

A вот - инверсия;

SELECT title FROM titles t