Главная | Обратная связь | Поможем написать вашу работу!

Объединение таблицы с собой

⇐ ПредыдущаяСтр 7 из 7

В ГЛАВЕ 8, МЫ ПОКАЗАЛИ ВАМ КАК ОБЪЕДИНЯТЬ ДВЕ или более таблиц -

вместе.

Достаточно интересно то, что та же самая методика может использо-

ваться чтобы объединять вместе две копии одиночной таблицы. В этой

главе, мы будем исследовать этот процесс. Как вы видете, объединение

таблицы с самой собой, далеко не простая вещь, и может быть очень по-

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

данных в конкретной таблице.

========= КАК ДЕЛАТЬ ОБЪЕДИНЕНИЕ ==========

ТАБЛИЦЫ С СОБОЙ?

Для объединения таблицы с собой, вы можете сделать каждую строку

таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой

другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терми-

нах предиката, также как в обьединениях мультитаблиц. Это позволит вам

легко создавать определенные виды связей между различными позициями

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

ем поля, например.

Вы можете изобразить обьединение таблицы с собой, как обьединение

двух копий одной и той же таблицы. Таблица на самом деле не копирует-

ся, но SQL выполняет команду так, как если бы это было сделано. Други-

ми словами, это обьединение - такое же, как и любое другое обьединение

между двумя таблицами, за исключением того, что в данном случае обе

таблицы идентичны.

ПСЕВДОНИМЫ

Синтаксис команды для объединения таблицы с собой, тот же что и для

объединения многочисленых таблиц, в одном экземпляре. Когда вы объеди-

няете таблицу с собой, все повторяемые имена столбца, заполняются пре-

фиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса,

вы должны иметь два различных имени для этой таблицы.

Вы можете сделать это с помощью определения временных имен называе-

мых переменными диапазона, переменными корреляции или просто - псевдо-

нимами. Вы определяете их в предложении FROM запроса. Это очень прос-

то: вы набираете имя таблицы, оставляете пробел, и затем набираете

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

имеющих один и тот же самый рейтинг (вывод показывается в Рисунке

9.1):

SELECT first.cname, second.cname, first.rating

FROM Customers first, Customers second

WHERE first.rating = second.rating;

=============== SQL Execution Log ==============

| |

| Giovanni Giovanni 200 |

| Giovanni Liu 200 |

| Liu Giovanni 200 |

| Liu Liu 200 |

| Grass Grass 300 |

| Grass Cisneros 300 |

| Clemens Hoffman 100 |

| Clemens Clemens 100 |

| Clemens Pereira 100 |

| Cisneros Grass 300 |

| Cisneros Cisneros 300 |

| Pereira Hoffman 100 |

| Pereira Clemens 100 |

| Pereira Pereira 100 |

| |

===============================================

Рисунок 9.1: Объединение таблицы с собой

(обратите внимание что на Рисунке 9.1, как и в некоторых дальнейших

примерах, полный запрос не может уместиться в окне вывода, и следова-

тельно будет усекаться.)

В вышеупомянутой команде, SQL ведет себя так, как если бы он соеди-

нял две таблицы называемые 'первая' и 'вторая'. Обе они - фактически,

таблицы Заказчика, но псевдонимы разрешают им быть обработаными неза-

висимо. Псевдонимы первый и второй были установлены в предложении FROM

запроса, сразу после имени копии таблицы. Обратите внимание что псев-

донимы могут использоваться в предложении SELECT, даже если они не оп-

ределены в предложении FROM.

Это - очень хорошо. SQL будет сначала допускать любые такие псевдо-

нимы на веру, но будет отклонять команду если они не определены далее

в предложении FROM запроса.

Псевдоним существует - только пока команда выполняется! Когда зап-

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

кого значения.

Теперь, когда имеются две копии таблицы Заказчиков, чтобы работать с

ними, SQL может обрабатывать эту операцию точно также как и любое дру-

гое обьединение - берет каждую строку из одного псевдонима и сравнива-

ет ее с каждой строкой из другого псевдонима.

УСТРАНЕНИЕ ИЗБЫТОЧНОСТИ

Обратите внимание что наш вывод имеет два значение для каждой комби-

нации, причем второй раз в обратном порядке. Это потому, что каждое

значение показано первый раз в каждом псевдониме, и второй раз(сим-

метрично) в предикате. Следовательно, значение A в псевдониме сначала

выбирается в комбинации со значением B во втором псевдониме, а затем

значение A во втором псевдониме выбирается в комбинации со значением B

в первом псевдониме. В нашем примере, Hoffman выбрался вместе с Cle-

mens, а затем Clemens выбрался вместе с Hoffman. Тот же самый случай с

Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того каждая стро-

ка была сравнена сама с собой, чтобы вывести строки такие как - Liu и

Liu. Простой способ избежать этого состoит в том, чтобы налагать поря-

док на два значения, так чтобы один мог быть меньше чем другой или

предшествовал ему в алфавитном порядке. Это делает предикат ассимет-

ричным, поэтому те же самые значения в обратном порядке не будут выб-

раны снова, например:

SELECT tirst.cname, second.cname, first.rating

FROM Customers first, Customers second

WHERE first.rating = second.rating

AND first.cname < second.cname;

Вывод этого запроса показывается в Рисунке 9.2.

Hoffman предшествует Periera в алфавитном порядке, поэтому комбина-

ция удовлетворяет обеим условиям предиката и появляется в выводе. Ког-

да та же самая комбинация появляется в обратном порядке - когда Perie-

ra в псевдониме первой таблицы сравнтвается с Hoffman во второй табли-

це псевдонима - второе условие не встречается. Аналогично Hoffman не

выбирается при наличии того же рейтинга что и он сам потому что его

имя не предшествует ему самому в алфавитном порядке. Если бы вы захо-

=============== SQL Execution Log ==============

| |

| SELECT first.cname, second.cname, first.rating |

| FROM Customers first, Customers second |

| WHERE first.rating = second.rating |

| AND first.cname < second.cname |

| =============================================== |

| cname cname rating |

| ------- --------- ------- |

| Hoffman Pereira 100 |

| Giovanni Liu 200 |

| Clemens Hoffman 100 |

| Pereira Pereira 100 |

| Gisneros Grass 300 |

=================================================

Рисунок 9.2: Устранение избыточности вывода в обьединении с собой.

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

могли бы просто использовать < = вместо <.

ПРОВЕРКА ОШИБОК

Таким образом мы можем использовать эту особенность SQL для проверки

определенных видов ошибок. При просмотре таблицы Порядков, вы можете

видеть что поля cnum и snum должны иметь постоянную связь. Так как

каждый заказчик должен быть назначен к одному и только одному продав-

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

Порядков, он должен совпадать с таким же номером продавца. Следующая

команда будет определять любые несогласованности в этой области:

SELECT first.onum, tirst.cnum, first.snum,

second.onum, second.cnum,second.snum

FROM Orders first, Orders second

WHERE first.cnum = second.cnum

AND first.snum < > second.snum;

Хотя это выглядит сложно, логика этой команды достаточно проста. Она

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

псевдонимом, и проверять ее в комбинации с каждой строкой таблицы По-

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

удовлетворяет предикату, она выбирается для вывода. В этом случае пре-

дикат будет рассматривать эту строку, найдет строку где поле cnum=2008

а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же

самым значением поля cnum. Если он находит что какая -то из их имеет

значение отличное от значения поля snum, предикат будет верен, и выве-

дет выбранные поля из текущей комбинации строк. Если же значение snum

с данным значением cnum в наш таблице совпадает, эта команда не произ-

ведет никакого вывода.

БОЛЬШЕ ПСЕВДОНИМОВ

Хотя обьединение таблицы с собой - это первая ситуация когда понятно

что псевдонимы необходимы, вы не ограничены в их использовании что бы

только отличать копию одлной таблицы от ее оригинала. Вы можете ис-

пользовать псевдонимы в любое время когда вы хотите создать альтерна-

тивные имена для ваших таблиц в команде. Например, если ваши таблицы

имеют очень длинные и сложные имена, вы могли бы определить простые

односимвольные псевдонимы, типа a и b, и использовать их вместо имен

таблицы в предложении SELECT и предикате. Они будут также использо-

ваться с соотнесенными подзапросами(обсуждаемыми в Главе 11).

ЕЩЕ БОЛЬШЕ КОМПЛЕКСНЫХ ОБЪЕДИНЕНИЙ

Вы можете использовать любое число псевдонимов для одной таблицы в

запросе, хотя использование более двух в данном предложении SELECT *

будет излишеством. Предположим что вы еще не назначили ваших заказчи-

ков к вашему продавцу. Компании должна назначить каждому продавцу пер-

воначально трех заказчиков, по одному для каждого рейтингового значе-

ния. Вы лично можете решить какого заказчика какому продавцу назна-

чить, но следующий запрос вы используете чтобы увидеть все возможные

комбинации заказчиков которых вы можете назначать. (Вывод показывает-

ся в Рисунке 9.3):

SELECT a.cnum, b.cnum, c.cnum

FROM Customers a, Customers b, Customers c

WHERE a.rating = 100

AND b.rating = 200

AND c.rating = 300;

=============== SQL Execution Log ==============

| |

| AND c.rating = 300; |

| =============================================== |

| cnum cnum cnum |

| ----- ------ ------ |

| 2001 2002 2004 |

| 2001 2002 2008 |

| 2001 2003 2004 |

| 2001 2003 2008 |

| 2006 2002 2004 |

| 2006 2002 2008 |

| 2006 2003 2004 |

| 2006 2003 2008 |

| 2007 2002 2004 |

| 2007 2002 2008 |

| 2007 2003 2004 |

| 2007 2003 2008 |

=================================================

Рисунок 9.3 Комбинация пользователей с различными значениями

рейтинга

Как вы можете видеть, этот запрос находит все комбинации заказчиков

с тремя значениями оценки, поэтому первый столбец состоит из заказчи-

ков с оценкой 100, второй с 200, и последний с оценкой 300. Они повто-

ряются во всех возможных комбинациях. Это - сортировка группировки ко-

торая не может быть выполнена с GROUP BY или ORDER BY, поскольку они

сравнивают значения только в одном столбце вывода.

Вы должны также понимать, что не всегда обязательно использовать

каждый псевдоним или таблицу которые упомянуты в предложении FROM зап-

роса, в предложении SELECT. Иногда, предложение или таблица становятся

запрашиваемыми исключительно потому что они могут вызываться в преди-

кате запроса. Например, следующий запрос находит всех заказчиков раз-

мещенных в городах где продавец Serres (snum 1002) имеет заказиков (

вывод показывается в Рисунке 9.4):

SELECT b.cnum, b.cname

FROM Customers a, Customers b

WHERE a.snum = 1002

AND b.city = a.city;

=============== SQL Execution Log ============

| |

| SELECT b.cnum, b.cname |

| FROM Customers a, Customers b |

| WHERE a.snum = 1002 |

| AND b.city = a.city; |

| ==============================================|

| cnum cname |

| ------ --------- |

| 2003 Liu |

| 2008 Cisneros |

| 2004 Grass |

=============================================

Рисунок 9.4 Нахождение заказчиков в городах относящихся

к Serres.

Псевдоним a будет делать предикат неверным за исключением случая

когда его значение столбца snum = 1002. Таким образом псевдоним опус-

кает все, кроме заказчиков продавца Serres. Псевдоним b будет верным

для всех строк с тем же самым значением города что и текущее значение

города для a; в ходе запроса, строка псевдонима b будет верна один раз

когда значение города представлено в a. Нахождение этих строк псевдо-

нима b - единственая цель псевдонима a, поэтоиму мы не выбираем все

столбцы подряд. Как вы можете видеть, собственные заказчики Serres вы-

бираются при нахождении их в том же самом городе что и он сам, поэтому

выбор их из псевдонима a необязателен. Короче говоря, псевдоним назхо-

дит строки заказчиков Serres, Liu и Grass. Псевдоним b находит всех

заказчиков размещенных в любом из их городов (San Jose и Berlin соот-

ветственно) включая, конечно, самих - Liu и Grass.

Вы можете также создать обьединение которое включает и различные

таблицы и псевдонимы одиночной таблицы. Следующий запрос объединяет

таблицу Пользователей с собой: чтобы найти все пары заказчиков обслу-

живаемых одним продавцом. В то же самое время, этот запрос объединяет

заказчика с таблицей Продавцов с именем этого продавца (вывод показан

на Рисунке 9.5):

SELECT sname, Salespeople.snum, first.cname

second.cname

FROM Customers first, Customers second, Salespeople

WHERE first.snum = second.snum

AND Salespeople.snum = first.snum

AND first.cnum < second.cnum;

=============== SQL Execution Log ==================

| |

| SELECT cname, Salespeople.snum, first.cname |

| second.cname |

| FROM Customers first, Customers second, Salespeople |

| WHERE first.snum = second.snum |

| AND Salespeople.snum = first.snum |

| AND first.cnum < second.cnum; |

| ====================================================|

| cname snum cname cname |

| ------ ------ -------- -------- |

| Serres 1002 Liu Grass |

| Peel 1001 Hoffman Clemens |

=====================================================

Рисунок 9.5: Объединение таблицы с собой и с другой таблицей

================ РЕЗЮМЕ =================

Теперь Вы понимаете возможности объединения и можете использовать их

для ограничения связей с таблицей, между различными таблицами, или в

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

использовании его способностей. Вы теперь познакомились с терминами

порядковые переменные, корреляционные переменные и предложения (эта

терминология будет меняться от изделия к изделию, так что мы предлага-

ем Вам познакомится со всеми тремя терминами). Кроме того Вы поняли,

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

Следующим шагом после комбинации многочисленых таблиц или многочис-

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

запросов, где один запрос будет производить вывод который будет затем

управлять работой другого запроса. Это другое мощное средство SQL, о

котором мы расскажем в Главе 10 и более тщательно в последующих гла-

вах.

10. ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО В КОНЕЦ ГЛАВЫ 9, МЫ ГОВОРИЛИ ЧТО ЗАПРОСЫ могут управлять другими запросами. В этой главе, вы узнаете как это делается (большей частью), помещая запрос внутрь предиката другого запроса, и используя вывод внутреннего запроса в верном или неверном условии предиката. Вы сможе- те выяснить какие виды операторов могут использовать подзапросы и пос- мотреть как подзапросы работают со средствами SQL, такими как DIS- TINCT, с составными функциями и выводимыми выражения. Вы узнаете как использовать подзапросы с предложением HAVING и получите некоторые наставления как правильно использовать подзапросы. ======== КАК РАБОТАЕТ ПОДЗАПРОС? ========= С помощью SQL вы можете вкладывать запросы внутрь друга друга. Обыч- но, внутренний запрос генерирует значение которое проверяется в преди- кате внешнего запроса, определяющего верно оно или нет. Например, предположим что мы знаем имя продавца: Motika, но не знаем значение его поля snum, и хотим извлечь все порядки из таблицы Порядков. Имеет- ся один способ чтобы сделать это(вывод показывается в Рисунке 10.1): SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika'); Чтобы оценить внешний(основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он дела- ет это так как и должен делать запрос имеющий единственную цель - отыскать через таблицу Продавцов все строки, где поле sname равно зна- чению Motika, и затем извлечь значения поля snum этих строк. Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основно- го запроса вместо самого подзапроса, так чтобы предиката прочитал что WHERE snum = 1004 =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT snum | | FROM Salespeople | | WHERE sname = 'Motika'); | |=================================================| | onum amt odate cnum snum | | ----- ------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | | ================================================= Рисунок 10.1: Использование подзапроса Основной запрос затем выполняется как обычно с вышеупомянутыми ре- зультатами. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена(в этом случае, snum), но это необязательно. Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и выполнять далее с подзапросом в целом, но это было бы не так универ- сально. Это будет продолжать работать даже если номер Motika изменил- ся, а, с помощью простого изменения имени в подзапросе, вы можете ис- пользовать его для чего угодно. ЗНАЧЕНИЯ, КОТОРЫЕ МОГУТ ВЫДАВАТЬ ПОДЗАПРОСЫ Скорее всего было бы удобнее, чтобы наш подзапрос в предыдущем при- мере возвращал одно и только одно значение. Имея выбранным поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку. При использовании подзапросов в предикатах основанных на реляционных операторах (уравнениях или неравенствах, как объяснено в Главе 4), вы должны убедиться что использовали подзапрос который будет выдавать одну и только одну строку вывода. Если вы используете подзапрос кото- рый не выводит никаких значений вообще, команда не потерпит неудачи; но основной запрос не выведет никаких значений. Подзапросы которые не производят никакого вывода (или нулевой вывод) вынуждают рассматривать предикат ни как верный ни как неверный, а как неизвестный. Однако, не- известный предикат имеет тот же самый эффект что и неверный: никакие строки не выбираются основным запросом (смотри Главу 5 для подробной информации о неизвестном предикате). Это плохая стратегия, чтобы делать что-нибудь подобное следующему: SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = Barcelona); Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum и следовательно будет принят. Но это - только в данном случае. Большинство SQL баз данных имеют многочисленых пользователей, и если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значе- ния, и ваша команда потерпит неудачу. DISTINCT С ПОДЗАПРОСАМИ Вы можете, в некоторых случаях, использовать DISTINCT чтобы вынудить подзапрос генерировать одиночное значение. Предположим что мы хотим найти все порядки кредитований для тех продавцов которые обслуживают Hoffmanа (cnum = 2001). Имеется один способ чтобы сделать это (вывод показывается в Рисунке 10.2): SELECT * FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT DISTINCT snum | | FROM Orders | | Where cnum = 2001); | | =============================================== | | onum amt odate cnum snum | | ----- --------- --------- ------ ------- | | 3003 767.19 10/03/1990 2001 1001 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.2: Использование DISTINCT чтобы вынудить получение одного значения из подзапроса Подзапрос установил что значение поля snum совпало с Hoffman - 1001, и затем основной запрос выделил все порядки с этим значением snum из таблицы Порядков(не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы зна- ем что каждая строка в таблице Порядков с данным значением cnum должна иметь такое же значение snum. Однако так как там может быть любое чис- ло таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом. Альтернативный подход должен быть чтобы ссылаться к таблице Заказчиков а не к таблице Порядков в подзапросе. Так как поле cnum - это первич- ный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Порядков но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое мы показали выше. (SQL имеет механизмы которые определяют - кто имеет привилегии чтобы делать что-то в определенной таблице. Это будет объясняться в Главе 22.) По- жалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она являеться исключением из пра- вил. ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ Вы должны обратить внимание что предикаты включающие подзапросы, ис- пользуют выражение < скалярная форма > < оператор > < подзапрос >, а не < подзапрос > < оператор > < скалярное выражение > или, < подзапрос > < оператор > < подзапрос >. Другими словами, вы не должны записывать предыдущий пример так: SELECT * FROM Orders WHERE (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001) = snum; В строгой ANSI реализации, это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи. ANSI также предохраняет вас от появления обеих значений при сравнении, которые нужно вывести с по- мощью подзапроса. ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, - агрегатная функция. Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основ- ном предикате. Например, вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября (вывод показан на Рисуноке 10.3): SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders WHERE odate = 10/04/1990);
=============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders | | WHERE odate = 01/04/1990); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 2345.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.3: Выбор всех сумм со значением выше средней на 10/04/1990 Средняя сумма приобретений на 4 Октября - 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется = 894.49. Все строки со значе- нием в поле amt выше этого - являются выбраными. Имейте ввиду что сгруппированные агрегатные функции, которые являют- ся агрегатными функциями определенными в терминах предложения GROUP BY, могут производить многочисленые значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HA- VING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны ис- пользовать одиночную агрегатную функцию с предложением WHERE что уст- ранит нежелательные группы. Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне - SELECT AVG (comm) FROM Salespeople GROUP BY city HAVlNG city = "London"; не может использоваться в подзапросе! Во всяком случае это не лучший спо- соб формировать запрос. Другим способом может быть - SELECT AVG (comm) FROM Salespeople WHERE city = "London"; ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Как вы пом- ните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был вер- ным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляци- онным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне (вывод показывается в Рисунке 10.4): SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city = "LONDON"); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum IN | | (SELECT snum | | FROM Salespeople | | WHERE city = 'London'); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3006 1098.19 10/03/1990 2008 1007 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10. 4: Использование подзапроса с IN В ситуации подобно этой, подзапрос - более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали обьединение: SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = "London"; Хотя это и произведет тот же самый вывод что и в примере с подзапро- сом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эф- фективнее извлекать из таблицы Продавцов значения поля snum где city = "London", и затем искать эти значения в таблице Порядков, как это де- лается в варианте с подзапросом. Внутренний запрос дает нам snums=1001 и snum=1004. Внешний запрос, затем, дает нам строки из таблицы Поряд- ков где эти поля snum найдены. Строго говоря, быстрее или нет работает вариант подзапроса, практи- чески зависит от реализации - в какой программе вы это используете. Эта часть вашей программы называемой - оптимизатор, пытается найти на- иболее эффективный способ выполнения ваших запросов. Хороший оптимизатор во всяком случае преобразует вариант обьединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем пола- гаться полностью на оптимизатор. Конечно вы можете также использовать оператор IN, даже когда вы уве- рены что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может зас- тавить команду потерпеть неудачу если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различия в выводе основного запро- са. Например, рассмотрим команду, которая похожа на предыдущую: SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT snum FROM Orders WHERE cnum = 2001); Вы можете устранить потребность в DISTINCT используя IN вместо (=), подобно этому: SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001); Что случится если есть ошибка и один из порядков был акредитован к различным продавцам? Версия использующая IN будет давать вам все по- рядки для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения сделанные на основе этого запроса не будут содержать ошибки. Вариант использующий (=), просто потерпит неудачу. Это, по крайней мере, позволило вам узнать что имеется такая пробле- ма. Вы должны затем выполнять поиск неисправности, выполнив этот под- запрос отдельно и наблюдая значения которые он производит. В принципе, если вы знаете что подзапрос должен(по логике) вывести только одно значение, вы должны использовать =. IN является подходя- щим, если запрос может ограниченно производить одно или более значе- ний, независимо от того ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов обслуживающих заказчиков в Лондоне: SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city = "London"); Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Это - только для данного случая. Нет ника- кой причины чтобы некоторые заказчики в Лондоне не могли быть назначе- ными к кому-то еще. Следовательно, IN - это наиболее логичная форма чтобы использовать ее в запросе. =============== SQL Execution Log ============== | | | SELECT comm | | FROM Salespeople | | WHERE snum IN | | (SELECT snum | | FROM Customers | | WHERE city = 'London'); | | =============================================== | | comm | | ------- | | 0.12 | | | | | ================================================ Рисунок 10.5 Использование IN с подзапросом для вывода одного значения Между прочим, префикс таблицы для поля city необязателен в предыду- щем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в пред- ложении WHERE означает что имеется ссылка к Customer.city(поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это - правильно. Это пред- положение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже когда будем говорить об соот- несенных подзапросах. Если возможен беспорядок, конечно же, лучше все- го использовать префиксы. ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ Смысл всех подзапросов обсужденных в этой главе тот, что все они вы- бирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому то, что SE- LECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, который мы будем представлять в Главе 12. ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ Вы можете использовать выражение основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос использует реляционный оператор = (вывод показывается в Рисун- ке 10.6): SELECT * FROM Customers WHERE cnum = (SELECT snum + 1000 FROM Salespeople WHERE sname = Serres); Он находит всех заказчиков чье значение поля cnum равное 1000, выше поля snum Serres. Мы предполагаем что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуж- даемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18); иначе =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE cnum = | | (SELECT snum + 1000 | | WHERE Salespeople | | WHERE sname = 'Serres' | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2002 Giovanni Rome 200 1003 | ============================================= Рисунок 10.6: Использование подзапроса с выражением подзапрос может произвести многочисленые значения. Когда поля snum и сnum не имеют такого простого функционального значения как например первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого не- вероятно полезен. ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции если они не производят многочисленых значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером (вывод пока- зывается в Рисунке 10.7): SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = " San Jose'; =============== SQL Execution Log ============ | | | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = 'San Jose' | |============================================ | | rating | | -------- -------- | | 200 2 | ================================================ Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков которые имели эту оценку. =============== РЕЗЮМЕ =============== Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим, расши- ряет возможности позволяющие выполнить большее количество функций. Вы теперь понимаете как использовать подзапросы с реляционными оператора- ми также как и со специальным оператором IN, или в предложении WHERE или в предложении HAVING внешнего запроса. В следующих главах, мы будем разрабатывать подзапросы. Сначала в Главе 11, мы обсудим другой вид подзапроса, который выполняется от- дельно для каждой строки таблицы вызываемой во внешнем запросе. Затем, в Главе 12 и 13, мы представим вас нескольким специальным операторам которые функционируют на всех подзапросах, как это делает IN, за иск- лючением когда эти операторы могут использоваться только в подзапро- сах. *************** РАБОТА С SQL ************* 1. Напишите запрос, который бы использовал подзапрос для получения всех порядков для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum. 2. Напишите запрос который вывел бы имена и оценки всех заказчиков ко- торые имеют усредненые порядки. 3. Напишите запрос который бы выбрал общую сумму всех приобретений в порядках для каждого продавца, у которого эта общая сумма больше

чем сумма наибольшего порядка в таблице.

10. ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО В КОНЕЦ ГЛАВЫ 9, МЫ ГОВОРИЛИ ЧТО ЗАПРОСЫ могут управлять другими запросами. В этой главе, вы узнаете как это делается (большей частью), помещая запрос внутрь предиката другого запроса, и используя вывод внутреннего запроса в верном или неверном условии предиката. Вы сможе- те выяснить какие виды операторов могут использовать подзапросы и пос- мотреть как подзапросы работают со средствами SQL, такими как DIS- TINCT, с составными функциями и выводимыми выражения. Вы узнаете как использовать подзапросы с предложением HAVING и получите некоторые наставления как правильно использовать подзапросы. ======== КАК РАБОТАЕТ ПОДЗАПРОС? ========= С помощью SQL вы можете вкладывать запросы внутрь друга друга. Обыч- но, внутренний запрос генерирует значение которое проверяется в преди- кате внешнего запроса, определяющего верно оно или нет. Например, предположим что мы знаем имя продавца: Motika, но не знаем значение его поля snum, и хотим извлечь все порядки из таблицы Порядков. Имеет- ся один способ чтобы сделать это(вывод показывается в Рисунке 10.1): SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika'); Чтобы оценить внешний(основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он дела- ет это так как и должен делать запрос имеющий единственную цель - отыскать через таблицу Продавцов все строки, где поле sname равно зна- чению Motika, и затем извлечь значения поля snum этих строк. Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основно- го запроса вместо самого подзапроса, так чтобы предиката прочитал что WHERE snum = 1004 =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT snum | | FROM Salespeople | | WHERE sname = 'Motika'); | |=================================================| | onum amt odate cnum snum | | ----- ------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | | ================================================= Рисунок 10.1: Использование подзапроса Основной запрос затем выполняется как обычно с вышеупомянутыми ре- зультатами. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена(в этом случае, snum), но это необязательно. Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и выполнять далее с подзапросом в целом, но это было бы не так универ- сально. Это будет продолжать работать даже если номер Motika изменил- ся, а, с помощью простого изменения имени в подзапросе, вы можете ис- пользовать его для чего угодно. ЗНАЧЕНИЯ, КОТОРЫЕ МОГУТ ВЫДАВАТЬ ПОДЗАПРОСЫ Скорее всего было бы удобнее, чтобы наш подзапрос в предыдущем при- мере возвращал одно и только одно значение. Имея выбранным поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку. При использовании подзапросов в предикатах основанных на реляционных операторах (уравнениях или неравенствах, как объяснено в Главе 4), вы должны убедиться что использовали подзапрос который будет выдавать одну и только одну строку вывода. Если вы используете подзапрос кото- рый не выводит никаких значений вообще, команда не потерпит неудачи; но основной запрос не выведет никаких значений. Подзапросы которые не производят никакого вывода (или нулевой вывод) вынуждают рассматривать предикат ни как верный ни как неверный, а как неизвестный. Однако, не- известный предикат имеет тот же самый эффект что и неверный: никакие строки не выбираются основным запросом (смотри Главу 5 для подробной информации о неизвестном предикате). Это плохая стратегия, чтобы делать что-нибудь подобное следующему: SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = Barcelona); Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum и следовательно будет принят. Но это - только в данном случае. Большинство SQL баз данных имеют многочисленых пользователей, и если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значе- ния, и ваша команда потерпит неудачу. DISTINCT С ПОДЗАПРОСАМИ Вы можете, в некоторых случаях, использовать DISTINCT чтобы вынудить подзапрос генерировать одиночное значение. Предположим что мы хотим найти все порядки кредитований для тех продавцов которые обслуживают Hoffmanа (cnum = 2001). Имеется один способ чтобы сделать это (вывод показывается в Рисунке 10.2): SELECT * FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT DISTINCT snum | | FROM Orders | | Where cnum = 2001); | | =============================================== | | onum amt odate cnum snum | | ----- --------- --------- ------ ------- | | 3003 767.19 10/03/1990 2001 1001 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.2: Использование DISTINCT чтобы вынудить получение одного значения из подзапроса Подзапрос установил что значение поля snum совпало с Hoffman - 1001, и затем основной запрос выделил все порядки с этим значением snum из таблицы Порядков(не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы зна- ем что каждая строка в таблице Порядков с данным значением cnum должна иметь такое же значение snum. Однако так как там может быть любое чис- ло таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом. Альтернативный подход должен быть чтобы ссылаться к таблице Заказчиков а не к таблице Порядков в подзапросе. Так как поле cnum - это первич- ный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Порядков но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое мы показали выше. (SQL имеет механизмы которые определяют - кто имеет привилегии чтобы делать что-то в определенной таблице. Это будет объясняться в Главе 22.) По- жалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она являеться исключением из пра- вил. ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ Вы должны обратить внимание что предикаты включающие подзапросы, ис- пользуют выражение < скалярная форма > < оператор > < подзапрос >, а не < подзапрос > < оператор > < скалярное выражение > или, < подзапрос > < оператор > < подзапрос >. Другими словами, вы не должны записывать предыдущий пример так: SELECT * FROM Orders WHERE (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001) = snum; В строгой ANSI реализации, это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи. ANSI также предохраняет вас от появления обеих значений при сравнении, которые нужно вывести с по- мощью подзапроса. ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, - агрегатная функция. Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основ- ном предикате. Например, вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября (вывод показан на Рисуноке 10.3): SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders WHERE odate = 10/04/1990); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders | | WHERE odate = 01/04/1990); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 2345.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10.3: Выбор всех сумм со значением выше средней на 10/04/1990 Средняя сумма приобретений на 4 Октября - 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется = 894.49. Все строки со значе- нием в поле amt выше этого - являются выбраными. Имейте ввиду что сгруппированные агрегатные функции, которые являют- ся агрегатными функциями определенными в терминах предложения GROUP BY, могут производить многочисленые значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HA- VING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны ис- пользовать одиночную агрегатную функцию с предложением WHERE что уст- ранит нежелательные группы. Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне - SELECT AVG (comm) FROM Salespeople GROUP BY city HAVlNG city = "London"; не может использоваться в подзапросе! Во всяком случае это не лучший спо- соб формировать запрос. Другим способом может быть - SELECT AVG (comm) FROM Salespeople WHERE city = "London"; ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Как вы пом- ните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был вер- ным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляци- онным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне (вывод показывается в Рисунке 10.4): SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city = "LONDON"); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum IN | | (SELECT snum | | FROM Salespeople | | WHERE city = 'London'); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3006 1098.19 10/03/1990 2008 1007 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================ Рисунок 10. 4: Использование подзапроса с IN В ситуации подобно этой, подзапрос - более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали обьединение: SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = "London"; Хотя это и произведет тот же самый вывод что и в примере с подзапро- сом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эф- фективнее извлекать из таблицы Продавцов значения поля snum где city = "London", и затем искать эти значения в таблице Порядков, как это де- лается в варианте с подзапросом. Внутренний запрос дает нам snums=1001 и snum=1004. Внешний запрос, затем, дает нам строки из таблицы Поряд- ков где эти поля snum найдены. Строго говоря, быстрее или нет работает вариант подзапроса, практи- чески зависит от реализации - в какой программе вы это используете. Эта часть вашей программы называемой - оптимизатор, пытается найти на- иболее эффективный способ выполнения ваших запросов. Хороший оптимизатор во всяком случае преобразует вариант обьединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем пола- гаться полностью на оптимизатор. Конечно вы можете также использовать оператор IN, даже когда вы уве- рены что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может зас- тавить команду потерпеть неудачу если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различия в выводе основного запро- са. Например, рассмотрим команду, которая похожа на предыдущую: SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT snum FROM Orders WHERE cnum = 2001); Вы можете устранить потребность в DISTINCT используя IN вместо (=), подобно этому: SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001); Что случится если есть ошибка и один из порядков был акредитован к различным продавцам? Версия использующая IN будет давать вам все по- рядки для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения сделанные на основе этого запроса не будут содержать ошибки. Вариант использующий (=), просто потерпит неудачу. Это, по крайней мере, позволило вам узнать что имеется такая пробле- ма. Вы должны затем выполнять поиск неисправности, выполнив этот под- запрос отдельно и наблюдая значения которые он производит. В принципе, если вы знаете что подзапрос должен(по логике) вывести только одно значение, вы должны использовать =. IN является подходя- щим, если запрос может ограниченно производить одно или более значе- ний, независимо от того ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов обслуживающих заказчиков в Лондоне: SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city = "London"); Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Это - только для данного случая. Нет ника- кой причины чтобы некоторые заказчики в Лондоне не могли быть назначе- ными к кому-то еще. Следовательно, IN - это наиболее логичная форма чтобы использовать ее в запросе. =============== SQL Execution Log ============== | | | SELECT comm | | FROM Salespeople | | WHERE snum IN | | (SELECT snum | | FROM Customers | | WHERE city = 'London'); | | =============================================== | | comm | | ------- | | 0.12 | | | | | ================================================ Рисунок 10.5 Использование IN с подзапросом для вывода одного значения Между прочим, префикс таблицы для поля city необязателен в предыду- щем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в пред- ложении WHERE означает что имеется ссылка к Customer.city(поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это - правильно. Это пред- положение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже когда будем говорить об соот- несенных подзапросах. Если возможен беспорядок, конечно же, лучше все- го использовать префиксы. ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ Смысл всех подзапросов обсужденных в этой главе тот, что все они вы- бирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому то, что SE- LECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, который мы будем представлять в Главе 12. ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ Вы можете использовать выражение основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос использует реляционный оператор = (вывод показывается в Рисун- ке 10.6): SELECT * FROM Customers WHERE cnum = (SELECT snum + 1000 FROM Salespeople WHERE sname = Serres); Он находит всех заказчиков чье значение поля cnum равное 1000, выше поля snum Serres. Мы предполагаем что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуж- даемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18); иначе =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE cnum = | | (SELECT snum + 1000 | | WHERE Salespeople | | WHERE sname = 'Serres' | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2002 Giovanni Rome 200 1003 | ============================================= Рисунок 10.6: Использование подзапроса с выражением подзапрос может произвести многочисленые значения. Когда поля snum и сnum не имеют такого простого функционального значения как например первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого не- вероятно полезен. ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции если они не производят многочисленых значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером (вывод пока- зывается в Рисунке 10.7): SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = " San Jose'; =============== SQL Execution Log ============ | | | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = 'San Jose' | |============================================ | | rating | | -------- -------- | | 200 2 | ================================================ Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков которые имели эту оценку. =============== РЕЗЮМЕ =============== Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим, расши- ряет возможности позволяющие выполнить большее количество функций. Вы теперь понимаете как использовать подзапросы с реляционными оператора- ми также как и со специальным оператором IN, или в предложении WHERE или в предложении HAVING внешнего запроса. В следующих главах, мы будем разрабатывать подзапросы. Сначала в Главе 11, мы обсудим другой вид подзапроса, который выполняется от- дельно для каждой строки таблицы вызываемой во внешнем запросе. Затем, в Главе 12 и 13, мы представим вас нескольким специальным операторам которые функционируют на всех подзапросах, как это делает IN, за иск- лючением когда эти операторы могут использоваться только в подзапро- сах. *************** РАБОТА С SQL ************* 1. Напишите запрос, который бы использовал подзапрос для получения всех порядков для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum. 2. Напишите запрос который вывел бы имена и оценки всех заказчиков ко- торые имеют усредненые порядки. 3. Напишите запрос который бы выбрал общую сумму всех приобретений в порядках для каждого продавца, у которого эта общая сумма больше чем сумма наибольшего порядка в таблице.11. СООТНЕСЕННЫЕ ПОДЗАПРОСЫ. В ЭТОЙ ГЛАВЕ, МЫ ПРЕДСТАВИМ ВАС ТИПУ подзапроса о котором мы не го- ворили в Главе 10 - посвященной соотнесенному подзапросу. Вы узнаете как использовать соотнесенные подзапросы в предложениях запросов WHERE и HAVING. Сходства и различия между соотнесенными подзапросами и обь- единениями будут обсуждаться далее, и вы сможете повысить ваше знание псевдонимов и префиксов имени таблицы - когда они необходимы и как их использовать. КАК СФОРМИРОВАТЬ ========= СООТНЕСЕННЫЙ ПОДЗАПРОС =========== Когда вы используете подзапросы в SQL, вы можете обратиться к внут- реннему запросу таблицы в предложении внешнего запроса FROM, сформи- ровав - соотнесенный подзапрос. Когда вы делаете это, подзапрос выпол- няется неоднократно, по одному разу для каждой строки таблицы основно- го запроса. Соотнесенный подзапрос - один из большого количества тон- ких понятий в SQL из-за сложности в его оценке. Если вы сумеете овла- деть им, вы найдете что он очень мощный, потому что может выполнять сложные функции с помощью очень лаконичных указаний. Например, имеется один способ найти всех заказчиков в порядках на 3-е Октября (вывод показывается в Рисунке 11.1): SELECT * FROM Customers outer WHERE 10/03/1990 IN (SELECT odate FROM Orders inner WHERE outer.cnum = inner.cnum); КАК РАБОТАЕТ СООТНЕСЕННЫЙ ПОДЗАПРОС В вышеупомянутом примере, "внутренний"(inner) и "внешний"(outer), это псевдонимы, подобно обсужденным в Главе 9. Мы выбрали эти имена для большей ясности; они отсылают к значениям внутренних и внешних запросов, соответственно. Так как значение в поле cnum внешнего запро- са меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса для которого внутрен- =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE 10/03/1990 IN | | (SELECT odate | | FROM Orders inner | | WHERE outer.cnum = inner.cnum); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2001 Hoffman London 100 1001 | | 2003 Liu San Jose 200 1002 | | 2008 Cisneros San Jose 300 1007 | | 2007 Pereira Rome 100 1004 | ============================================= Рисунок 11.1: Использование соотнесенного подзапроса ний запрос каждый раз будет выполнен, называется - текущей стро- кой-кандидатом. Следовательно, процедура оценки выполняемой соотнесен- ным подзапросом - это: 1. Выбрать строку из таблицы именованной в внешнем запросе. Это будет текущая строка-кандидат. 2. Сохранить значения из этой строки-кандидата в псевдониме с именем в предложении FROM внешнего запроса. 3. Выполнить подзапрос. Везде, где псевдоним данный для внешнего зап- роса найден (в этом случае "внешний"), использовать значение для текущей строки-кандидата. Использование значения из строки-кандида- та внешнего запроса в подзапросе называется - внешней ссылкой. 4. Оценить предикат внешнего запроса на основе результатов подзапроса выполняемого в шаге 3. Он определяеть - выбирается ли строка-канди- дат для вывода. 5. Повторить процедуру для следующей строки-кандидата таблицы, и так далее пока все строки таблицы не будут проверены. В вышеупомянутом примере, SQL осуществляет следующую процедуру: 1. Он выбирает строку Hoffman из таблицы Заказчиков. 2. Сохраняет эту строку как текущую строку-кандидат под псевдонимом - "внешним". 3. Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу Порядков чтобы найти строки где значение cnum поле - такое же как значение outer.cnum, которое в настоящее время равно 2001, - поле cnum строки Hoffmanа. Затем он извлекает поле odate из каждой стро- ки таблицы Порядков для которой это верно, и формирует набор значе- ний поля odate. 4. Получив набор всех значений поля odate, для поля cnum = 2001, он проверяет предикат основного запроса чтобы видеть имеется ли значе- ние на 3 Октября в этом наборе. Если это так(а это так), то он вы- бирает строку Hoffmanа для вывода ее из основного запроса. 5. Он повторяет всю процедуру, используя строку Giovanni как стро- ку-кандидата, и затем сохраняет повторно пока каждая строка таблицы Заказчиков не будет проверена. Как вы можете видеть, вычисления которые SQL выполняет с помощью этих простых инструкций - это полный комплекс. Конечно, вы могли бы решить ту же самую проблему используя обьединение, следующего вида (вывод для этого запроса показывается в Рисунке 11.2): SELECT * FROM Customers first, Orders second WHERE first.cnum = second.cnum AND second.odate = 10/03/1990; Обратите внимание что Cisneros был выбран дважды, по одному разу для каждого порядка который он имел для данной даты. Мы могли бы устранить это используя SELECT DISTINCT вместо просто SELECT. Но это необяза- тельно в варианте подзапроса. Оператор IN, используемый в варианте подзапроса, не делает никакого различия между значениями которые выби- раются подзапросом один раз и значениями которые выбираются неоднок- ратно. Следовательно DISTINCT необязателен. =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers first, Orders second | | WHERE first.cnum = second.cnum | | (SELECT COUNT (*) | | FROM Customers | | WHERE snum = main.snum; | | ============================================= | | cnum cname | | ----- -------- | | 1001 Peel | | 1002 Serres | ============================================= Рисунок 11. 2 Использование обьединения вместо соотнесенного подзапроса Предположим что мы хотим видеть имена и номера всех продавцов кото- рые имеют более одного заказчика. Следующий запрос выполнит это для вас (вывод показывается в Рисунке 11.3): SELECT snum, sname FROM Salespeople main WHERE 1 < (SELECT COUNT (*) FROM Customers WHERE snum = main.snum); Обратите внимание что предложение FROM подзапроса в этом прим

⇐ Предыдущая 1 2 3 4 5 67