*HtRt i.l-lleja NO in CSacCT i-ilejd i-kOM sales) title

Net Etiquette

Ho посмотрите, что получается, если появляются неопределенные значения:

SELECI title FROM titles t

WHERE t.title ic! no; in (SELEC; tit;e ia FROM sales onion SELECT NULL) title

Предикат IN предоставляет npocToii способ для сравнения ска,1ярного значения с множеством значений. В данном случае подзапрос генерирует это множество. В соответствии с реко.мендация.ми ANSI/ISO SQL выражение, которое проверяет на равенство с NULL-3iia4eHHe.M, всегда возвращает NULL, так что запись Net Etiquette не про.ходит сравнештя. Другие записи не проходят проверки, так как точно известно, что они есть в сииске, и поэто.му исключаются с по.\ю-щью NOT.

Это поведение отличается от поведения NOT EXISTS, которое мы наблюдали ранее, и это главная причина, из-за которой преобразование EXISTS в IN, может быть хитрой задачей, когда участвуют неопредачепные значения. Когда ANSI NULLS отключено, сраниения на равенство с NULL разрешены, и NULL-значеиия равны друг другу. Поскольку IN представляет собой коротюп ! способ сравнения на равенство, эта настройка напрямую воздействует на него. Вот при.мер:

SET AHSI NULLS OFF SELECT tnie from titles t

WHERE t.title id NOt IN (SELECT ticie id FROM sales UNION StLECT NULL) GO

SET ANSI NULLS ON -- he забудьте снова включить ANSI NULLS title

Net Etiquette

Теперь, когда titlejd Net Etiquette можно безопасно сравнивать с NULL, до-бавлениы.ми с помощью UNION, предикат IN .может установить, существует ли оно в списке. Поскольку его та.м нет. Net Etiquette добавляется в результирующее множество.

Соединения

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

SELECT DISTINCT title

frcm titles t JOIN sales s ON (t.title ia - s,title id) (результаты сокращены)

title

But Is It User Friendly?

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

Нам пришлось задействовать DISTINCT, потому что между таблицами titli и sales отношение одщг-ко-многим .

Пустые результирующие множества

EXISTS также часто применяется для проверки результирующего множества f на.1ичие записей. Оптимизатор знает, что на.хожден1ю даже единственной запис удовлетвортгг выражению, так что часто это работает довольно быстро. Вот npi мер:

IF EXISTS(SELECT * FROM myworktable) DELETE myworktable

Так как этот запрос не ограничен предложения.ми WHERE или HAVING, м фактически проверяем таблицу на наличие записей. Это намного быстрее, чс: что-то вроде IF (SELECT COUNT(*) FROM myworktable)>0 и позволяет быстро опр делить, пуста ли таблица, не проверяя систе.миые объекты.

EXISTS снаружи WHERE и HAVING

EXISTS, как и все предикаты, способен не только ограничршать результирующ множество запроса. EXISTS можно также использовать в списке SELECT внут} выражений CASE и в предложении FROM с применением определешнг произво ных таблиц. Вот при.мер:

SELECT CASE WHEN EXISTS(SELECT * FROM titleauthor where au id=a.au id) THEN True ELSE False END FROM authors a

True

True

True

True

True

True

True

False

True

True

True

True

True

False

True

True

True

True

False

True

False

True

True

Так как значения, воз1фащае.\и,1е 1!1)(;диката.\И1, ]1ельзя задействоиать напрямую, ваши возможности ограииче1ИЛ больше, че.м .могли бы быть. То есть вы не .\южете получить результат предиката просто с п().моии>ю SELECT - вместо этого предикат должен быть испо.чьзоваи в выражеи1И1 и.чи функции, снособиой обрабатывать логические значе1И1Я - например, CASE. CASE преобразует логическое значение, которое возвращает предикат, во что-то, что запрос \южет вернуть.

Как я у11о.\ии1ач ранее, предикат Ш предоставляет краткий способ сравнения значения с каждым членом в списке. Вы .можете рассмат1)ивать его как последовательность проверок иа равенство .между значением с левой стороны и кажды.м значением из списка, обт^единениы.х гю OR. Хспя ANSI SQL-92 позволяет использовать значения загтсей в IN, Transact-SQL этого ие разрешает - южиo задействовать только скалярные значения. Последовательность значений в IN .может представлять собой список значенш !, разделенных запятыми, или .\южет быть получена с помощью подзапроса.

Вот несколько примеров применения IN:

SELECT title

FRCM titles WHERE title icl IN (SELECT title id EROM sales) (результаты сокрап1ены) title

But Is It User Friendly?

Computer Phobic AND Non-Phobic Individuals: Behavior Vanatior.s Cooking Viiith Computers: Surreptitious Balance Sheets Emotional Secunity: A Nevj Algorithm

SELECT title

FROM titles WHERE title id NOT IM (SELECT title ic FIW sales) title

Net Etiquette

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

SELECT title

FROM titles WHERE title ia IN ((SELECT title id FROM sales WHERE qty>=75), (SELECi title ia FROM sales WHERE qty=5). FC+REPLICAIEC8.4))

1111 e

Is Anger the Enemy?

Secrets of Silicon Valiey

The Busy Executives Database Guide

Оптимизация IN

Хотя естественно упорядочивать зиачеиия в списке IN в а/тфавитиом пли число-ВО.М порядке, лучше угюрядочивать их согласно частоте, поскольку предикат вернет результат, как только будет HaiLieno единственное совпадение. Один из

способов сделать это в случае подзапроса - отсортировать результат подзапроса с помощью ORDER BY: SELECT title

FROM titles WHERE title id IN (SELECT titlejd FROM

(SELECT TOP 999999 titlejd. COUNT(*) AS NumOccur FROM sales GROUP BY

title id ORDER BY NumOccur DESC) s)

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

Is Anger the Enemy?

The Busy Executives Database Guide

The Gourmet Microwave

Cooking with Computers: Surreptitious Balance Sheets

В этом запросе используется производная таблица, чтобы отсортировать таблицу sales перед передачей ее в подзапрос. Нам необходима производная таблица, пото.му что нам требуется два значения - столбец titlejd и количество раз, которое встречается каждая запись, а EXISTS - единственный предикат, позволяющий использовать подзапрос, возвращающий более одного столбца. Мы применяем сортировку по убыванию, чтобы значения titlejd, встречающиеся чаще, находились в начале. Расширение ТОР п необходимо, так как без него нельзя задействовать ORDER BY в подзапросах, производных таблицах или представлениях.

ПРИМЕЧАНИЕ --

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

Так как SELECT без ORDER BY не гарантирует конкретный порядок записей, естественно, мы не може.м доверять порядку записей в подзапросе. Если производная таблица упорядочена, это не значит, что подзапрос тоже будет упорядочен. На практике кажется, что это работает так, как мы хотим. Чтобы проверить это, можно выделить подзапрос и выполнить его отдельно от главного запроса, как здесь:

SELECT titlejd

FROM (SELECT TOP 999999 t1tle id. COUNT(*) AS NumOccur FROM sales GROUP BY title id ORDER BY NumOccur OESC) s

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

title Id NumOccur

PS2091 6

BU1032 3

MC3021 2

BUllll 1

BU2075 1

BU7832 1

Подзапросы

Скорее всего, вы уже 1103нако.\и1лись с подзапросами в другом месте этой книги, воз.уюжно, в разделе, касающемся предикатов, ранее в это11 главе; однако все же стоит рассмотреть их немного подробнее. Подзапросы - мощный инстру.мент

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

ANY и ALL

Предикаты ANY и ALL работают исключительно с подзапроса.ми. ANY (и его снпо-ни.м SOME) работает подобно IN. Вот пример запроса, нсполь.зующий сначала IN, а затем ANY:

SELECT title

FROM titles WHERE titlejd IN (SELECT tn-eja FROM sales) (результаты сокращены) title

But Is It User Friendly?

Computer Phobic A,ND Non-Phob1c Individuals: Behavior Variations Cocking with Computens: Surreptitious Balance Sheets Emotional Security: A New Algorithm

SELECT title

FROM titles WHERE title id=ANY(SELECl tit:e id FROM sales) (результаты сокращены) title

But Is It User Friendly?

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

Так как IN и =ANY функционально эквивалентны, вы, воз.молсно, ду.маете, что NOT IN и <>ANY тоже эквивалентны, но суть ие в этом. Конечно же, эквиватенто.м NOT IN является oALL. Если вы заду.чшетесь над эти.м, то пой.метс, что так оно и есть, о ANY всегда возвращает True, если подзапрос возвращает больше одного значения. Когда подзапрос возвращает два или более различны.х значения, всегда найдется хотя бы одно, которое не равно сравннваемо.му значению. <>ALL же работает точ1Ю так же, как NOT IN. Этот предикат возвращает True, только если скалярное значение не равно каждо.му из значений, возвращенных подзапросо.м.

Из-за этого ALL используется чаще с операторо.м о, чем с операторо.м =. Проверка, равно ли скалярное значение каждо.му значению в списке, имеет очень ограниченную сферу применения. Проверка будет неудачна, только если все значения не будут идентичны. Если все они идентичны, заче.м вообще нужна эта проверка?

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

Многие соединения можно представить подзапросами, хотя это может быть трудно (если вообще возможно), когда подзапрос не используется в IN или EXISTS или когда он осуществляет агрегирование. Как правило, соединения более эффективны, чем подзапросы, однако не всегда.

Область применения подзапросов не сводится к ограничению записей в результирующем множестве. Они могут быть использованы в любом месте операторов SQL, где можно задействовать выражения. Их можно выбирать для получения значений столбцов, в выражениях CASE и в производных таблицах (столбец, значение которого получено из подзапроса, называется производным столбцом). Подзапросы применяются не только в операторах SELECT, их также можно использовать в UPDATE, INSERT и DELETE.

WHERE и подзапросы

Чаще всего подзапросы встречаются в предложении WHERE оператора SELECT. Вот пример:

SELECT SUM(qty) AS TotalSales FROM sales

WHERE title id=(SELECT MAX(title id) FROM titles) TotalSales

В этом запросе мы получаем суммарный объем продаж для последнего значения title id в таблице titles. Обратите внимание на использование функции МАХ, чтобы гарантировать, что подзапрос вернет только одну запись. Подзапросы, используемые с операторами сравнения (=, о, >= и <=), должны возвращать единственное значение. Подзапросы, возвращающие в этом случае более одного значения, не вызовут синтаксическую ошибку, так что будьте осторожны - вы не узнаете об этом, пока запрос не будет выполнен. Один из способов избежать этого заключается в использовании агрегатной функции, как в предыдущем примере. Другой способ - применить расширение ТОР п оператора SELECT, как здесь:

SELECT SUMCqty) AS TotalSales FROM sales

WHERE title id={SELECT TOP 1 title id FROM titles ORDER BY t1tle id DESC) TotalSales

Здесь мы используем TOP 1, чтобы гарантировать, что подзапрос вернет только одну запись. Чтобы результирующее множество было таким же, как в предыдущем примере, мы сортируем результирующее множество подзапроса в порядке убывания по столбцу titlejd, а затем возвращаем первое (на самом деле последнее) значение. Удостоверьтесь, что подзапросы, задействованные в сравнениях на равенство, возвращают не более одной записи. Код, который не защи-

nien от raKoii возможпосгп, - ,)И) .\nnia замедленного де11(твия, ждущая своего часа. Он .может нерестап) 1)абогагь просто нз-зп пезначнтедьны.х нзменент! данных, что пе очень приятно.

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

Связаниьй! подзапрос -- это в.1ожепп1,н'1 запрос, которьи! 01ранпчеи внещнеГи таблицей, а очень часто сам 01рапичивает таблицу во внепше.м запросе. Обычно он ссылается па нее с тю.мощыо исевдоии.ма таблииы. определенного во внешнем запросе.

В иекоторо.м смысле, связанные подзапросы ведут себя ПОД061К) обьиишьм конструкция.м 1ИП<лов. Для каждой записи во виеииюй таблице подзап1)ос запускается заново с новым наборо.м параметров. Между те.м, связаиньи! подзапрос работает iia.\nioro эффективнее, че.м эквпвачеитпып цикл. На.много эффективнее производить итерации с использованием связанного подзапроса, чем, скаже.м, с по.мощью цикла WHILE.

Вот пример простого связаиного запроса:

SELECT title FROM titles t

WHERE (SELECI SJM(qty) f-S TctdiSaies EROI- salts wHERf title id=t,111ie :G) > 30 title

Is Anger the Enemy

Cnions. Leeks, and Garb;;: Coo<ing Secrets of the Mediterranean

Secrets of Silicon Valley

The Busy Executives DataDase 6u-dc

The Gourmet Microwave

You Can Combat Comcuter Stress!

В этом запросе подзапрос выполняется для каждой записи в таблице titles, Каждьнй раз, когда ои запускается, он ограничивается :)начение.м столбца titlejd виепптей таблицы. Это означает, что значение SUM, которое он вернет, будет соответствовать текуще.му значению title id во внешнем запросе. В свою очередь, эта сумма нсполь.зуется, чтобы ограничить возвращае.\И51е названия теми, продажи по которым превысили 30 единиц.

Конечно, запрос .может быть просто переформулирован с применением со-еднне1П1я, но с\и>1сл этого упражиеитгя - показать, как мотут быть связаны иод-запросы с главпььмн запроса.\иг Обратите вни.маиие, что связанные подзапросы не обязательно должны располагаться в предложеппн WHERE. Вот пример, показывающий задействование связанного подзапроса в списке оператора SELECT:

SELECT title.

(SELECT SUM(qty) FROM sales WHERE tifie id=t.title id) AS TotaiSales FROM titles t

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

title lotalSales

But Is It User Friendly? 30

Computer Phobic AND Non-Pnobic Indivicuals: Benavior Variations 20

Cooking with Computers; Surreptitious Balance Sheets 25

Emotional Security; A New Algorithm 25

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

Как показано в разделе, касающемся предикатов, скалярное значение можно сравнивать с результирующим множеством подзапроса с помощью специальных функций-предикатов, таких как IN, EXISTS, ANY и ALL:

SELECT title FROM titles t

WHERE titlejd IN (SELECT s.titlejd FROM sales s

WHERE (t.ytd sales+((SELECT SUM(sl,qty) FROM sales si

WHERE si.title id=t.title id)*t.price))

> 5000)

title

You Can Combat Computer Stress!

The Gourmet Microwave

But Is It User Friendly?

Secrets of Silicon Valley

Fifty Years in Buckingham Palace Kitchens

В этом примере подзапрос использует два различных поля из внешнего запроса - ytd sales и price - для того, чтобы вычислить суммарные продажи на текущую дату для каждой позиции. Здесь применяются два подзапроса, один внутри другого, и оба они связаны с главным запросом. Самый внутренний подзапрос вычисляет общее количество продаж для заданного наименования. Это необходимо, так как для каждой позиции может существовать несколько записей о покупках. Внешний подзапрос получает эту сумму, умножает на цену книги и добавляет результат к сумме продаж за этот год, чтобы получить сумму на, текущий момент для каждой книги. Затем книги, продажи по которым превышают $5000, возвращаются подзапросом и проверяются предикатом IN.

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

SELECT t.title

FROM titles t JOIN sales s ON (t.titlejd=s.titlejd) GROUP BY t.titlejd, t.title, t.ytd sales. t,price HAVING (t.ytd sales+(SUM(s.qty)*t.price)) > 5000

title

You Can Combat Computer Stress!

The Gourmet Microwave

But Is It User Friendly?

Secrets of Silicon Valley

Fifty Years in Buckingham Palace Kitchens

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

поиска дуб.чирующихся зиачеии!! среди записей таблицы. Допусти\!, что у пас есть список веб-до.меиов и сервереи! п.мен и .мы хотим найти каждый до.мен с та-ки.ми же cepBcpa.nh! имен, как и у некоторого другого домена. До.мен не .может и.меть более двух серверов имен, так что в нашей таблице будет три столбца (проигнорируем тот факт, что она не является нор.мализованной, поскольку содержит повторяющиеся значения). Эту задачу .\южн() решить с помощью связанных подзапросов или с по.мощью са\юсоедииенпя, но лучше использовать рстпение с подзапросом. Чтобы понять, почему, дава11те исследуе.м оба .метода. Для начала вот подход с прп.менение.м самосоединепия:

CREATE TABLE #nameservers (domain varcnarOO), nsl varchardS), ns2 varchardS)) INSERT #narr-eservers V,ALUES ( foolsrus .com,24,99.3.9,24,99. С ,8) INSERf fr.ameservers VALUES (wewantLirbLi.ks.gov,127.C,0.2.127.0.C.3) INSERT #ndmeservers VALUES (sayhitomom.ecu,127,0,0,4,24.99,9,8) INSERl #nameserverb VALUES

(knickstink.org,192.168.0,254-,192,168,0,255) INSERT #rcmeservers VALUES (nukemnut com,24,99,0,6,24,99.0.7) INSERT #nameservers VALUES (wedigd-aplo,org,24.99,0.9,24.99,0.8) INSERl Inacieservers VALUES

(gospamurself,edu.192.168.0,255,192,168.0,254) INSERT #namesenvers VALUES ( ou812.com.100,10.0.100,100,1С,0,101) INSERT Ina.meservers VALUES (rothrulz.org,100,10,0.102.24,99.0,8)

SELECT n.domain, n.nsl, n,ns2

FROM fnamesenvers n JOIN #nameservers a ON

(n.domain<>a,domain AND ((n.nsl=a.nsl AND п.пбг-а.ns2) OR (n.nsl=a.rs2 AND n,ns2=a.nsl))) ORDER BY 2,3,1

domain nsl ns2

kniCKStink.o.rg 192.168.0.254 192.168.0.255

gospamurself.edu 192.168.0,255 192.168,0,254

foolsrus.com 24.99.0.9 24,99.0,8

wedigdiablo.org 24,99.0.9 24.99.0.8

Мы соединяем две копии одной и той же таблицы с сервералт и.мен и определяем условия соединения в предложеити! ON. Для каждой записи первой ко-тги таблицы мы сканируем вторую копию, чтобы найти заниси, в которых:

О названия до.менов различаются;

о пары серверов и.мен совпадают.

Мы корректно обрабатываем и те до.мены, в которых порядок названий серверов имен был из.менен.

Теперь тот же са.мый запрос с использованием подзапроса:

SELECT п.domain, n,nsl, п,р$2 FROM fnameservers n

WHERE EXISTS(SELECT a,nsl. a.ns2 FROM #na.meservers a

WHERE (a,domainon.domain) AND ((a.nsl=n,n$l AND a.ns2=n.ns2) OR. (a.nsl=n.ns2 AND a.ns2=n.nsl)))

ORDER BY 2,3,1

domain nsl ns2

kpickstink.ong 192.168.0.254 192,168,0.255

gospamurself.edu 192.168.0.255 192.168.0.254

foolsrus.com 24.99.0.9 24.99.0.8

wedigdiablo.org 24.99.0.9 24.99.0.8

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

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

SELECT п.domain, n.nsl, n,ns2 FROM #nameservers n

WHERE EXISTS(SELECT a.nsl. a.ns2 FROM #nameservers a

WHERE ((a.nsl=n.nsl AND a.ns2=n.ns2) OR (a.nsl=n.ns2 AND

a.ns2=n.nsl))

GROUP BY a.nsl. a.ns2

HAVING C02UNT(*)>1) ORDER BY 2.3,1

domain nsl ns2

foo1srus.com 24,99,0,9 24,99,0,8

wedigdiablo.org 24.99,0,9 24,99,0,8

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

Реляционное деление

Область, в которой без связанных подзапросов не обойтись, - реляционное деление. В своем оригинальном трактате по теории реляционных баз данных, доктор Е. Ф. Кодд определил восемь основных операций реляционной алгебры: объединение, пересечение, разность множеств, включение, выборка, проекция, соединение и реляционное деление. Последняя из них, реляционное деление, - это средство, с по.мощью которого мы получаем результаты таких запросов: Покажите мне всех студентов, посещающих каждый курс химии или Выведите клиентов, которые купили не менее одной единицы каждого товара из каталога . В случае реляционного деления вы делите таблицу-делимое на таблицу-делитель, чтобы получить таблицу-частное. Как вы могли догадаться, частное - это то, что нам требуется, - результирующая таблица запроса. На самом деле не все так сложно, как кажется. Предположим, мы хотим решить последний из приведенных запросов - вывести список клиентов, каждый из которых заказал не менее одной единицы каждого товара из каталога. Допустим, у нас есть две таблицы: таблица с заказами клиентов и таблица с каталогом товаров. Чтобы решить задачу, мы можем реляционно разделить таблицу заказов на таблицу

Codd Е. F. А Relational Model of Data for Large Shared Data Banks, Communications of the ACM. New York: Association for Computing Machinery. 1970