Использование в запросе нескольких источников записей

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

Результат запроса

будет выглядеть следующим образом:

Поэтому перечисление таблиц, как правило, используется совместно с условием соединения записей из разных таблиц, указываемым в предложении WHERE. Для приведенных выше таблиц таким условием может быть совпадение значений, скажем, в полях a и c:

Теперь результатом выполнения этого запроса будет следующая таблица:

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

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

    <имя таблицы>.<имя поля>

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

Пример. Найти номер модели и производителя ПК, имеющих цену менее $600:

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

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

Пример. Вывести пары моделей, имеющих одинаковые цены:

Здесь условие A.model < B.model используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой, например: 1232, 1233 и 1233, 1232. DISTINCT применяется для того, чтобы исключить одинаковые строки, поскольку в таблице PC имеются одинаковые модели по одной и той же цене. В результате получим следующую таблицу:

Переименование также требуется, если в предложении FROM используется подзапрос. Так, первый пример можно переписать следующим образом:

Обратите внимание, что в этом случае в других предложениях оператора SELECT уже нельзя использовать квалификатор Product, поскольку таблица Product уже не используется. Вместо него используется псевдоним prod. Кроме того, ссылаться теперь можно только на те поля таблицы Product, которые перечислены в подзапросе.

Явные операции соединения

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

FROM <таблица 1> [INNER]
       | {{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]

Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом - LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.

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

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

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

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

Пример. Привести все модели ПК, их производителей и цену:

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

Поскольку моделей 2111 и 2112 из таблицы Product нет в таблице PC, в полях из таблицы PC содержится NULL.
Соединение RIGHT JOIN обратно соединению LEFT JOIN, т.е. в результирующий набор попадут все строки из второй таблицы, которые будут соединяться только с теми строками из первой таблицы, для которых выполняется условие соединения. В нашем случае левое соединение

Product LEFT JOIN PC ON PC.model = Product.model

будет эквивалентно правому соединению

PC RIGHT JOIN Product ON PC.model = Product.model

Запрос же

даст те же результаты, что и внутреннее соединение, поскольку в правой таблице (PC) нет таких моделей, которые отсутствовали бы в левой таблице (Product), что вполне естественно для типа связи "один-ко-многим", которая имеется между таблицами PC и Product. Наконец, при полном соединении (FULL JOIN) в результирующую таблицу попадут не только те строки, которые имеют одинаковые значения в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющие соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия, заполняются NULL-значениями. Полное соединение представляет собой комбинацию левого и правого внешних соединений.
Так запрос для таблиц A и B, приведенных в начале главы,

даст следующий результат:

Заметим, что это соединение симметрично, т.е. "A FULL JOIN B" эквивалентно "B FULL JOIN A". Обратите также внимание на обозначение A.*, что означает "все поля таблицы А".

Устранение избыточности

Обратите внимание что наш вывод имеет два значение для каждой комбинации, причем второй раз в обратном порядке. Это потому, что каждое значение показано первый раз в каждом псевдониме, и второй раз( симметрично) в предикате.

Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Hoffman выбрался вместе с Clemens, а затем Clemens выбрался вместе с Hoffman. Тот же самый случай с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того каждая строка была сравнена сама с собой, чтобы вывести строки такие как - Lui и Lui. Простой способ избежать этого состoит в том, чтобы налагать порядок на два значения, так чтобы один мог быть меньше чем другой или предшествовал ему в алфавитном порядке. Это делает предикат асимметричным, поэтому те же самые значения в обратном порядке не будут выбраны снова, например:

SELECT tirst.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cname < second.cname;

Вот,что Вы получите:

Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе. Когда та же сама комбинация появляется в обратном порядке - когда Periera в псевдониме первой таблицы сравнивается с Hoffman во второй таблице псевдонима - второе условие не встречается.

Аналогично Hoffman не выбирается при наличии того же рейтинга, что и он сам, потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же в запросах подобно этому, вы могли бы просто использовать < = вместо

Хотя объединение таблицы с собой - это первая ситуация когда понятно что псевдонимы необходимы, вы не ограничены в их использовании что бы только отличать копию одной таблицы от ее оригинала. Вы можете использовать псевдонимы в любое время, когда вы хотите создать альтернативные имена для ваших таблиц в команде. Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа a и b, и использовать их вместо имен таблицы в предложении SELECT и предикате.

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

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

Использование вложенных запросов

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

    SELECT *

       FROM Castemers

       WHERE city =

           ( SELECT city

                FROM Salespeople

                WHERE sname = 'Motika');

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

Единственной найденной строкой естественно будет city = 'London'. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы SQL прочитал:

       WHERE city = 'London'

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

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

Имея выбранным поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку.

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

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

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

Select .....

Where ...  (

            Select....

            Where....(

            );

практически без ограничений. Но вы не должны записывать ничего такого:

                   SELECT *

                      FROM SalesPeople

                      WHERE ( SELECT DISTINCT snum

                           FROM Customers

                           WHERE cnum = 2001 )

                      = snum;

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

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

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

Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне:  

SELECT AVG (comm)

FROM Salespeople

GROUP BY city

HAVlNG city = "London";

не может использоваться в подзапросе! Во всяком случае, это не лучший способ формировать запрос. Другим способом может быть:  

SELECT AVG (comm)

FROM Salespeople

WHERE city = "London";

Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN ( операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами ). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был верным.

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

В принципе, если вы знаете что подзапрос должен( по логике) вывести только одно значение, вы должны использовать = . IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов обслуживающих заказчиков в Лондоне:

SELECT comm

FROM Salespeople

WHERE snum IN ( SELECT snum FROM Customers WHERE city = "London" );

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel ( snum = 1001 ), который имеет обоих заказчиков в Лондоне. Это - только для данного случая. Нет никакой причины, чтобы некоторые заказчики в Лондоне не могли быть назначенными к кому-то еще. Следовательно, IN - это наиболее логична форма, чтобы использовать ее в запросе.

Между прочим, префикс таблицы для поля city необязателен в предыдущем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов.

SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса.

Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает что имеется ссылка к Customer.city (поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это - правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.
Вы можете так же использовать в подзапросах различные выражения, так же как и в обычных запросах.

Оператор EXIST 

Оператор EXISTS используется, чтобы указать предикату, производить ли подзапросу вывод или нет.

EXISTS - это оператор, который производит верное или неверное значение, другими словами, это выражение Буля.

Это означает что он может работать автономно в предикате или в комбинации с другими выражениями, использующими Булевые операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков, если один или более заказчиков в этой таблице находятся в San Jose:

   SELECT cnum, cname, city

                  FROM Customers

                  WHERE EXISTS

                      ( SELECT *

                          FROM Customers

                          WHERE city = " San Jose' );

Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос( не соотнесенный ) был выполнен только один раз для всего внешнего запроса, и следовательно имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат верным или неверным для всех строк сразу, что это не так уж полезно для извлечения определенной информации.

В вышеупомянутом примере, EXISTS должен быть установлен так чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборе звезду( SELECT *) В этом состоит его отличие от подзапроса который ( как вы видели ранее мог выбрать только один столбец ) . Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает - выполняется или нет вывод из подзапроса - а не использует выведенные значения.

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

               SELECT DISTINCT snum

                  FROM Customers outer

                  WHERE EXISTS

                      ( SELECT *

                           FROM Customers inner

                           WHERE inner.snum = outer.snum

                               AND inner.cnum < > outer.cnum );

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

Однако для нас может быть полезнее вывести больше информации об этих продавцах а не только их номера. Мы можем сделать это, объединив таблицу Заказчиков с таблицей Продавцов:

              SELECT DISTINCT first.snum, sname, first.city

                FROM Salespeople first, Customers second

                WHERE EXISTS

                   ( SELECT *

                      FROM Customers third

                      WHERE second.snum = third.snum

                            AND second.cnum < > third.cnum )

                   AND first.snum = second.snum;

Внутренний запрос здесь - как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос - это объединение таблицы Продавцов с таблицей Заказчиков, наподобие того, что мы видели прежде. Новое предложение основного предиката ( AND first.snum = second.snum ) естественно оценивается на том же самом уровне что и предложение EXISTS. Это - функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах пол snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в порядке для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях когда вторая часть запроса верна, а объединение - выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.

Предыдущий пример дал понять, что EXISTS может работать в комбинации с операторами Буля. Конечно, то, что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS - это оператор NOT. Один из способов, которым мы могли бы найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример.

               SELECT DISTINCT snum

                  FROM Customers outer

                  WHERE NOT EXISTS

                        ( SELECT *

                            FROM Customers inner

                            WHERE inner.snum = outer.snum

                                  AND inner.cnum < > outer.cnum );==  

ОБЪЕДИНЕНИЕ МНОГОЧИСЛЕННЫХ ЗАПРОСОВ В ОДИН
    Вы можете поместить многочисленые запросы вместе и объединить их вы вод  используя  предложение UNION.  Предложение UNION обьединяет вывод  двух или более SQL запросов в единый набор строк и столбцов.  Например,  чтобы получить всех продавцов и заказчиков размещенных в Лондоне и вы вести их как единое целое вы могли бы ввести:
 
                   SELECT snum, sname
                     FROM Salespeople
                     WHERE city = 'London'
 
                     UNION

                   SELECT cnum, cname
                     FROM Customers
                     WHERE city = 'London';

 и получить вывод 

       -----    --------                          

      1001    Peel                              
                  1004    Motika                            
                  2001    Hoffman                            
                  2006    Climens                                             |                                               |
                 ==========

   Как вы можете видеть, столбцы выбранные двумя командами выведены так,  как  если  она была одна.  Заголовки столбца исключены,  потому что ни  один из столбцов выведенных объединением,  не был извлечен  непосредс твенно из только одной таблицы.  Следовательно, все эти столбцы вывода не имеют никаких имен.  Кроме того, только последний запрос заканчивается точкой с запятой.  Отсутствие точки с запятой дает понять  SQL, что имеется еще один или более запросов.
          КОГДА ВЫ МОЖЕТЕ ДЕЛАТЬ ОБЪЕДИНЕНИЕ МЕЖДУ ЗАПРОСАМИ?
   Когда два ( или более ) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения.  Это означает, что каж дый запрос должен указывать одинаковое число столбцов и в том  же  порядке что и первый, второй, третий, и так далее, и каждый должен иметь тип, совместимый с каждым. Числовые поля должны иметь  одинаковый числовой тип и размер. Символьные  поля должны иметь одинаковое число символов.
  Другое ограничение  на  совместимость  -  это  когда  пустые  значения(NULL)  запрещены в любом столбце объединения,  причем эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения.  Пустые значения(NULL) запрещены с ограничением NOT NULL.  

Кроме того, вы не можете использовать UNION в подзапросах, а также не можете использовать агрегатные функции в предложениии SELECT запроса в  объединении.
                   
               ИСПОЛЬЗОВАНИЕ СТРОК И ВЫРАЖЕНИЙ С UNION
   Иногда, вы можете вставлять константы и выражения в предложения  SE-
LECT  используемые с UNION.  Это не следует строго указаниям ANSI,  но
это полезная и необычно используемая возможность.  Константы и выраже-
ния которые вы используете, должны встречать совместимые стандарты ко-
торые мы выделяли ранее.  Эта свойство полезно,  например, чтобы уста-
навливать  комментарии  указывающие  какой запрос вывел данную строку.
Предположим что вы должны сделать отчет о том, какие продавцы произво-
дят наибольшие и наименьшие порядки по датам.  Мы можем объединить два
запроса, вставив туда текст чтобы различать вывод для каждого из них.

 
     SELECT a.snum, sname, onum, 'Highest on', odate
        FROM (Salespeople a, Orders b
        WHERE a.snum = b.snum
        AND b.amt =
          ( SELECT MAX (amt)
               FROM Orders c
               WHERE c.odate = b.odate )


        UNION


     SELECT a.snum, (sname, (onum ' Lowest on', odate
        FROM ( Salespeople a, Orders b
        WHERE a.snum = b.snum
        AND b.amt =
           ( SELECT  MIN (amt)
                FROM Orders c
                WHERE c.odate = b.odate );


Вывод из этой команды  показывается в Рисунке 14.4.


  Мы должны были добавить дополнительный пробел в строку 'Lowest  on',
чтобы сделать ее совпадающей по длине со строкой 'Highest on'. Обрати-
те внимание что Peel выбран при наличии и  самого  высокого  и  самого
низкого  (  фактически он единственый ) порядка на 5 Октября.  Так как
вставляемые строки двух этих запросов различны, строки не будут устра-
нены как дубликаты.


                ===============  SQL Execution Log ============
               |                                               |
               | AND b.amt =                                   |
               | ( SELECT min (amt)                            |
               | FROM Orders c                                 |
               | WHERE c.odate = b.odate);                     |
               | ============================================= |
               |                                               |
               | -----  ------- ------  ---------- ----------- |
               |  1001  Peel     3008   Highest on  10/05/1990 |
               |  1001  Peel     3008   Lowest  on  10/05/1990 |
               |  1001  Peel     3011   Highest on  10/06/1990 |
               |  1002  Serres   3005   Highest on  10/03/1990 |
               |  1002  Serres   3007   Lowest  on  10/04/1990 |
               |  1002  Serres   3010   Lowest  on  10/06/1990 |
               |  1003  Axelrod  3009   Highest on  10/04/1990 |
               |  1007  Rifkin   3001   Lowest  on  10/03/1990 |
                ===============================================


Рисунок 14.4: Выбор наивысших и наинизших порядков, определяемых с по-
              мощью строк


              =====ИСПОЛЬЗОВАНИЕ UNION С ORDER BY =====


  До сих пор,  мы не оговаривали что данные многочисленых запросов бу-
дут выводиться в каком то особом порядке.  Мы просто показывали  вывод
сначала  из одного запроса а затем из другого.  Конечно,  вы не можете
полагаться на вывод приходящий в произвольном порядке. Мы как раз сде-
лаем  так чтобы этот способ для выполнения примеров был более простым.
Вы можете,  использовать предложение ORDER BY чтобы упорядочить  вывод
из объединения,  точно так же как это делается в индивидуальных запро-
сах.  Давайте пересмотрим наш последний пример чтобы упорядочить имена
с помощью их порядковых номеров.  Это может внести противоречие, такое
как повторение имени Peel в последней команде,  как вы сможете увидеть
из вывода показанного в Рисунке 14.5.


        SELECT a.snum, sname, onum, 'Highest on', odate
           FROM Salespeople a, Orders b
           WHERE a.snum = b.snum
           AND b.amt =
             ( SELECT MAX (amt)
                  FROM Orders c
                  WHERE c.odate = b.odate )


           UNION


       SELECT a.snum, (sname, (onum, 'Lowest on', odat
           FROM Salespeople a, Orders b
           WHEREa.snum = b.snum
           AND b.amt =
              ( SELECT MIN (amt)
                   FROM Orders c
                   WHERE c.odate = b.odate )


       ORDER BY 3;


                ===============  SQL Execution Log ============
               | ( SELECT min (amt)                            |
               | FROM Orders c                                 |
               | WHERE c.odate = b.odate)                      |
               | ORDER BY 3;                                   |
               | ============================================= |
               |                                               |
               | -----  ------- ------  ---------- ----------- |
               |  1007  Rifkin   3001   Lowest  on  10/03/1990 |
               |  1002  Serres   3005   Highest on  10/03/1990 |
               |  1002  Serres   3007   Lowest  on  10/04/1990 |
               |  1001  Peel     3008   Highest on  10/05/1990 |
               |  1001  Peel     3008   Lowest  on  10/05/1990 |
               |  1003  Axelrod  3009   Highest on  10/04/1990 |
               |  1002  Serres   3010   Lowest  on  10/06/1990 |
               |  1001  Peel     3011   Highest on  10/06/1990 |
                ===============================================


Рисунок 14.5: Формирование объединения с использованием ORDER BY


  Пока ORDER BY используется по умолчанию, мы не должны его указывать.
Мы можем упорядочить наш вывод с помощью нескольких полей, одно внутри
другого и указать ASC или DESC для каждого,  точно также как мы делали
это для одиночных запросов.  Заметьте, что номер 3 в предложении ORDER
BY указывает какой столбец из предложения SELECT будет упорядочен. Так
как столбцы объединения - это столбцы вывода,  они не  имеют  имен,  и
следовательно,  должны определяться по номеру. Этот номер указывает на
их место среди других столбцов вывода.  (Смотрите Главу 7, обсуждающую
столбцы вывода.)


                         ВНЕШНЕЕ ОБЪЕДИНЕНИЕ


  Операция которая бывает часто полезна - это объединение из двух зап-
росов в котором второй запрос выбирает строки, исключенные первым. На-
иболее часто,  вы будете делать это, так чтобы не исключать строки ко-
торые не удовлетворили предикату при объединении таблиц. Это называет-
ся - внешним обьединением. Предположим что некоторые из ваших заказчи-
ков еще не были назначены к продавцам. Вы можете захотеть увидеть име-
на и города всех ваших заказчиков, с именами их продавцов, не учитывая
тех кто еще не был назначен. Вы можете достичь этого, формируя объеди-
нение из двух запросов,  один из которых выполняет обьединение, а дру-
гой выбирает заказчиков с пустыми(NULL)  значениями  поля  snum.  Этот
последний  запрос должен вставлять пробелы в поля соответствующие полю
sname в первом запросе.  Как и раньше,  вы можете вставлять  текстовые
строки  в ваш вывод чтобы идентифицировать запрос который вывел данную
строку.  Использование этой методики во внешнем обьединении, дает воз-
можность  использовать предикаты для классификации,  а не для исключе-
ния.
  Мы использовали  пример нахождения продавцов с заказчиками размещен-
ными в их городах и раньше.  Однако вместо просто выбора  только  этих
строк, вы возможно захотите чтобы ваш вывод перечислял всех продавцов,
и указывал тех,  кто не имел заказчиков в их городах, и кто имел. Сле-
дующий запрос, чей вывод показывается в Рисунке 14.6, выполнит это:


             SELECT Salespeople.snum, sname, cname, comm
                FROM (Salespeople, Customers
                WHERE Salespeople.city = Customers.city.


                UNION


             SELECT snum, sname, ' NO MATCH    ', comm
                FROM (Salespeople
                WHERE NOT city = ANY
                   ( SELECT city
                        FROM Customers )


                ORDER BY 2 DESC;


                ===============  SQL Execution Log ============
               |                                               |
               | FROM Salespeople                              |
               | WHERE NOT city = ANYate)                      |
               | ( SELECT city                                 |
               | FROM Customers)                               |
               | ORDER BY 2 DESC;                              |
               | ============================================= |
               |                                               |
               | -----  -------  ---------     ------------    |
               |  1002  Serres   Cisneros           0.1300     |
               |  1002  Serres   Liu                0.1300     |
               |  1007  Rifkin   NO MATCH           0.1500     |
               |  1001  Peel     Clemens            0.1200     |
               |  1001  Peel     Hoffman            0.1200     |
               |  1004  Motika   Clemens            0.1100     |
               |  1004  Motika   Hoffman            0.1100     |
               |  1003  Axelrod  NO MATCH           0.1000     |
               |                                               |
                ===============================================


Рисунок 14. 6: Внешнее обьединение


  Строка 'NO MATCH' была дополнена пробелами,  чтобы получить совпаде-
ние  поля  cname по длине ( это не обязательно во всех реализациях SQL
).  Второй запрос выбирает даже те строки которые исключил первый.  Вы
можете также добавить комментарий или выражение к вашему запросу, вви-
де дополнительного поля.  Если вы сделаете это, вы будете должны доба-
вить некоторый дополнительный комментарий или выражение,  в той же са-
мой позиции среди выбранных полей, для каждого запроса в операции объ-
единения. Совместимость UNION предотвращает вас от добавления дополни-
тельного поля для первого запроса,  но не для второго.  Имеется запрос
который  добавляет строки к выбранным полям,  и указывает совпадает ли
данный продавец с его заказчиком в его городе:


          SELECT a.snum, sname, a.city, ' MATCHED '
             FROM Salespeople a, Customers b
             WHERE a.city = b.city


             UNION


          SELECT snum, sname, city, 'NO MATCH'
             FROM Salespeople
             WHERE NOT city = ANY
                ( SELECT city
                    FROM Customers )


             ORDER BY 2 DESC;


Рисунок 14,7  показывает вывод этого запроса.


                ===============  SQL Execution Log ============
               |                                               |
               | WHERE a.city = b.city                         |
               | UNION                                         |
               | SELECT snum,sname,city, 'NO MATCH'            |
               | FROM Salespeople                              |
               | WHERE NOT city = ANYate)                      |
               | ( SELECT city                                 |
               | FROM Customers)                               |
               | ORDER BY 2 DESC;                              |
               | ============================================= |
               |                                               |
               | -----  -------   ------------  ---------      |
               |  1002  Serres     San Jose     MATCHED        |
               |  1007  Rifkin     Barselona    NO MATCH       |
               |  1001  Peel       London       MATCHED        |
               |  1004  Motika     London       MATCHED        |
               |  1003  Axelrod    New York     NO MATCH       |
               |                                               |
                ===============================================


Рисунок 14. 7: Внешнее обьединение с полем коментария


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


      SELECT snum, city, 'SALESPERSON - MATCH'
         FROM Salespeople
         WHERE NOT city = ANY
           (SELECT city
                FROM Customers)


         UNION


      SELECT snum, city, 'SALESPERSON - NO MATCH'
         FROM Salespeople
         WHERE NOT city = ANY
           (SELECT city
                FROM Customers))


         UNION


      (SELECT cnum, city, 'CUSTOMER - MATCHED'
         FROM Customers
         WHERE city = ANY
            (SELECT city
                 FROM Salespeople)


         UNION


      SELECT cnum, city, 'CUSTOMER - NO MATCH'
         FROM Customers
         WHERE NOT city = ANY
           (SELECT city
               FROM Salespeople))


         ORDER BY 2 DESC;


           ===============  SQL Execution Log ===============
          |                                                  |
          | FROM  Salespeople)                               |
          | ORDER BY 2 DESC;                                 |
          |                                                  |
          | ================================================ |
          |                                                  |
          |  ----   --------     ------------------------    |
          |  2003   San Jose     CUSTOMER     -   MATCHED    |
          |  2008   San Jose     CUSTOMER     -   MATCHED    |
          |  2002   Rome         CUSTOMER     -   NO MATCH   |
          |  2007   Rome         CUSTOMER     -   NO MATCH   |
          |  1003   New York     SALESPERSON  -   MATCHED    |
          |  1003   New York     SALESPERSON  -   NO MATCH   |
          |  2001   London       CUSTOMER     -   MATCHED    |
          |  2006   London       CUSTOMER     -   MATCHED    |
          |  2004   Berlin       CUSTOMER     -   NO MATCH   |
          |  1007   Barcelona    SALESPERSON  -   MATCHED    |
          |  1007   Barcelona    SALESPERSON  -   NO MATCH   |
          |                                                  |
           ==================================================


Рисунок 1.8: Полное внешнее обьединение


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


                 query X UNION query Y UNION query Z;


вы должны указать, или


               ( query X UNION query Y )UNION query Z;


или
               query X UNION ( query Y UNION query Z );


  Это потому,  что UNION и UNION ALL могут быть скомбинированны, чтобы
удалять одни дубликаты, не удаляя других. Предложение -


             ( query X UNION ALL query Y )UNION query Z;


не обязательно воспроизведет те же результаты что предложение -


             query X UNION ALL( query Y UNION query Z );


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

Ввод, удаление и изменение значений полей

Значения могут быть помещены и удалены из полей, трем командами языка DML ( Язык Манипулирования Данными ):

1. INSERT (ВСТАВИТЬ),
2. UPDATE (МОДИФИЦИРОВАТЬ),
3. DELETE (УДАЛИТЬ).

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

INSERT INTO (table name) VALUES ( (value), (value) . . .);

Так, например, чтобы ввести строку в таблицу Продавцов, вы можете использовать следующее выражение:

INSERT INTO Salespeople

    VALUES (1001, 'Peel', 'London', .12);

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

Им таблицы ( в нашем случае - Salespeople (Продавцы)), должно быть предварительно определено, а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI, эти значения не могут составлять выражений, что означает что 3 - это доступно, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поименном порядке, поэтому первое значение с именем, автоматически попадает в столбец 1, второе в столбец 2, на так далее.

Если вам нужно ввести пустое значение(NULL), вы вводите его точно так- же как и обычное значение. Предположим, что еще не имелось поля city для мистера Peel. Вы можете вставить его строку со значением = NULL в это поле, следующим образом:

INSERT INTO Salespeople

  VALUES (1001, 'Peel', NULL, .12);

Так как значение NULL - это специальный маркер, а не просто символьное значение, он не включается в одиночные кавычки.

Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим, что вы берете значения для таблицы Заказчиков из отчета выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке:

  INSERT INTO Customers (city, cnamе, cnum)

     VALUES ('London', 'Honman', 2001);

Обратите внимание, что столбцы rating и snum - отсутствуют. Это значит, что эти строки автоматически установлены в значение - по умолчанию. По умолчанию может быть введено или значение NULL или другое значение определяемое как - "по умолчанию". Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен как по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT, которая относится к таблице.

Вы можете также использовать команду INSERT, чтобы получать или выбирать значения из одной таблицы и помещать их в другую, чтобы использовать их вместе с запросом. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос:

    INSERT INTO Londonstaff

       SELECT *

          FROM Salespeople

          WHERE city = 'London';

Здесь выбираются все значения произведенные запросом - то есть все строки из таблицы Продавцов со значениями city = "London" - и помещаются в таблицу называемую Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:

• Она должна уже быть создана командой CREATE TABLE или любыми другими командами СУБД.
• Она должна иметь четыре столбца которые совпадают с таблицей Продавцов в терминах типа данных; то есть первый, второй, и так далее, столбцы каждой таблицы, должны иметь одинаковый тип данных ( причем они не должны иметь одинаковых имен ).

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

     DELETE FROM Salespeople;

Теперь когда таблица пуста ее можно окончательно удалить командой DROP TABLE.

Обычно, вам нужно удалить только некоторые определенные строки из таблицы. Чтобы определить какие строки будут удалены, вы используете предикат, так же как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести

       DELETE FROM Salespeople

          WHERE snum = 1003;

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

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

     UPDATE Customers

        SET rating = 200;

Конечно, вы не всегда захотите указывать все строки таблицы для изменения единственного значения, так что UPDATE, наподобие DELETE, может брать предикаты. Вот как например можно выполнить изменение одинаковое для всех заказчиков продавца Peel ( имеющего snum=1001 ):

        UPDATE Customers

           SET rating = 200

           WHERE snum = 1001;

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

     UPDATE Salespeople

        SET sname = 'Gibson',city = 'Boston',comm = 0.10

        WHERE snum = 1004;

Эта команда передаст новому продавцу Gibson, всех текущих заказчиков бывшего продавца Motika и порядки, в том виде, в котором они были скомпонованы для Motika с помощью поля snum. Вы не можете, однако, модифицировать сразу много таблиц в одной команде, частично потому, что вы не можете использовать префиксы таблицы со столбцами измененными предложением SET. Другими словами, вы не можете сказать - "SET Salespeople.sname = Gibson" в команде UPDATE, вы можете сказать только так - "SET sname = Gibson".

Вы можете использовать скалярные выражения в предложении SET команды UPDATE, однако, включив его в выражение пол которое будет изменено. В этом их отличие от предложения VALUES команды INSERT, в котором выражения не могут использоваться; это свойство скалярных выражений - весьма полезная особенность.

Предположим, что вы решили удвоить комиссионные всем вашим продавцам. Вы можете использовать следующее выражение:

           UPDATE Salespeople

               SET comm = comm * 2;

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

               UPDATE Salespeople

                  SET comm = comm * 2

                  WHERE city = 'London';

Предложение SET - это не предикат. Он может вводить пустые NULL значения также как он вводил значения не используя какого-то специального синтаксиса ( такого например как IS NULL ). Так что, если вы хотите установить все оценки заказчиков в Лондоне в NULL, вы можете ввести следующее предложение:

             UPDATE customers

                SET rating = NULL

                WHERE city = 'London';

что обнулит все оценки заказчиков в Лондоне.

Создание и удаление таблиц

Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет имя таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.

Синтаксис команды CREATE TABLE:

    CREATE TABLE (table-name )

         ((column name ) (data type)[(size)],

         (column name ) (data type) [(size)] ... );

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

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

Один тип данных, для которого, в основном, нужно назначать размер - CHAR.

Аргумент размера - это целое число, определяющее максимальное число символов, которое может вместить поле. Фактическое число символов поля может быть от нуля (если поле - NULL ) до этого числа. По умолчанию, аргумент размера = 1 означает, что поле может содержать только одну букву.

Таблицы принадлежат пользователю, который их создал, и имена всех таблиц принадлежащих данному пользователю должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Отдельные таблицы могут использовать одинаковые имена столбцов, даже если они принадлежат одному и тому же пользователю. Примером этому - столбец city в таблице Заказчиков и в таблице Продавцов. Пользователи не являющиеся владельцами таблиц могут ссылаться к этим таблицам с помощью имени владельца этих таблиц сопровождаемого точкой; например, таблица Employees создана Smith будет называться Smith.Employees когда она упоминается каким-то другим пользователем ( мы понимаем что Smith - это Идентификатор Разрешения (ID). (ID) сообщаемый пользователем ( ваш разрешенный ID - это ваше имя в SQL.

Эта команда будет создавать таблицу Продавцов:

  CREATE TABLE Saleepeople

      ( snum    integer,

        sname   char (10),

        city     char (10),

        comm   declmal );

Порядок столбцов в таблице определяется порядком в котором они указаны. Им столбца не должно разделяться при переносе строки ( что сделано для удобочитаемости ), но отделяется запятыми.

Команда ALTER TABLE не часть стандарта ANSI; но это - широко доступная, и довольно содержательная форма, хотя ее возможности несколько ограничены. Она используется, чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис чтобы добавить столбец к таблице:

 ALTER TABLE (имя таблицы) ADD (имя столбца)

                          (тип данных)(размер);

Столбец будет добавлен со значением NULL для всех строк таблицы. Новый столбец станет последним по порядку столбцом таблицы. Вообще то, можно добавить сразу несколько новых столбцов, отделив их запятыми, в одной команде. Имеется возможность удалять или изменять столбцы. Наиболее часто, изменением столбца может быть просто увеличение его размера, или добавление( удаление ) ограничения.

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

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

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

          DROP TABLE ( имя таблицы );

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

Эта команда фактически не является частью стандарта ANSI, но она, в основном,  поддерживаемая и полезна. К счастью, она более проста, и, следовательно, более непротиворечивая, чем ALTER TABLE . ANSI просто не имеет способа для определения разрушенных или неправильных таблиц.