ALL SOME ANY

ALL, SOME, ANY

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

Посылка 1. Все греки —люди.

Посылка 2. Все люди смертны.

Заключение. Все греки смертны.

А вот еще пример.

Посылка 1. Некоторые греки — женщины.

Посылка 2. Все женщины — люди.

Заключение. Некоторые греки — люди.

Другой способ выражения идеи второго примера состоит в следующем.

Если какие-либо греки — женщины и все женщины — люди, то некоторые греки люди.

В первом примере в обеих посылках используется квантор всеобщности ALL (все), который дает возможность сделать в заключении разумный вывод обо всех греках. Во втором примере в одной из посылок используется квантор существования SOME (некоторые), который также позволяет сделать в заключении вывод обо всех греках. А в третьем примере, чтобы сделать то же заключение, что и во втором примере, используется квантор существования ANY (какие-либо) — синоним квантора SOME.

Посмотрите, как кванторы SOME, ANY и ALL применяются в SQL.

Рассмотрим пример с бейсбольной статистикой. Бейсбол — это вид спорта, требующий значительных физических нагрузок, особенно у питчера, т.е. игрока, подающего мяч. Ему за время игры приходится 90-150 раз бросать мяч со своей возвышенности до основной базы — места, где находится игрок с битой. Такие нагрузки очень утомляют, и часто получается так, что к концу игры питчера на подаче приходится заменять. Бессменно подавать мячи в течение всей игры — это уже выдающееся достижение, причем неважно, привели такие попытки к победе или нет.

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

Возможная двусмысленность квантора any
Первоначально в SQL в качестве квантора существования использовалось слово ANY. Это использование оказалось достаточно запутанным и приводило к ошибкам, так как в английском языке слово any иногда означает всеобщность, а иногда — существование."Do any of you know where Baker Street is?" (Кто-нибудь из вас знает, где находится улица Бейкер-Стрит?) "I can eat more eggs than any of you." (Я могу съесть больше яиц, чем любой из вас.) В первом предложении, скорее всего, задается вопрос, есть ли хотя бы один человек, который знает, где находится улица Бейкер-Стрит. Any используется как квантор существования. Второе предложение — это хвастливое заявление о том, что я могу съесть больше яиц, чем самый большой едок из окружающих. В этом случае any используется как квантор всеобщности. Поэтому разработчики стандарта SQL-92 хотя и оставили в нем от предыдущих версий SQL слово ANY, чтобы была совместимость с предшествующими продуктами, но в то же время добавили его менее запутанный синоним — слово SOME. SQL:2OO3 также поддерживает оба квантора существования.

В Американской лиге разрешается, чтобы назначенный хиттер (designated hitter, DH) мог бить битой по мячу вместо одного из девяти игроков, играющих в обороне. (Кто такой хиттер? Это игрок с битой. А назначенный хиттер — это тот хиттер, которому не требуется играть в оборонительной позиции.) Обычно DH делают это вместо питчеров, потому что те, как известно, плохие хиттеры. Питчерам приходится тратить слишком много времени и усилий на совершенствование своего броска. Поэтому у них остается мало времени, чтобы тренироваться с битой, как это делают остальные игроки.

Скажем, вам теоретически известно, что в среднем у стартовых питчеров (питчеров, играющих с самого начала игры) Американской лиги насчитывается больше игр, в которых они бессменно подавали мяч, чем у стартовых питчеров Национальной лиги. Такой вывод основан на тех ваших наблюдениях, что назначенные хиттеры дают возможность хорошо бросающим, но слабым в обращении с битой питчерам Американской лиги оставаться на подаче до завершения игры. Так как DH уже работают с битой вместо них, то становится не важным, что питчеры — слабые хиттеры. Однако в Национальной лиге питчер, как правило, заменяется в конце игры хиттером, у которого обычно больше шансов подготовить лучший удар. Чтобы проверить свою теорию, вы составляете следующий запрос:

SELECT FirstName, LastName

FROM AMERICAN_LEAGUER

WHERE CompleteGames > ALL

(SELECT CompleteGames

FROM NATIONAL_LEAGUER) ;

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

Посмотрите на следующий похожий оператор:

SELECT FirstName, LastName

FROM AMERICAN_LEAGUER

WHERE CompleteGames > ANY

(SELECT CompleteGames

FROM NATIONAL_LEAGUER) ;

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

Если заменить ключевое слово ANY эквивалентным ему SOME (какой-то), то результат будет точно такой же. И если истинно утверждение, что "хотя бы один питчер Национальной лиги ни одной игры бессменно не провел на подаче", то тогда можно сказать, что "какой-то питчер Национальной лиги ни одной игры не провел на подаче бессменно".

AND

Если для получения строки необходимо, чтобы все условия из какого-либо их набора имели значение True, используйте логическую связку AND (и). Проанализируйте следующий пример, в нем используются поля InvoiceNo (номер счета-фактуры), SaleDate (дата продажи), Salesperson (продавец), TotalSale (всего продано) из таблицы SALES (продажи):

SELECT InvoiceNo, SaleDate, Salesperson, TotalSale

FROM SALES

WHERE SaleDate >= '2003-05-18'

AND SaleDate <= '2003-05-24' ;

Предложение WHERE (где) должно соответствовать следующим двум условиям.

Дата SaleDate должна была наступить не раньше 18 мая 2003 года. Дата SaleDate должна была наступить не позже 24 мая 2003 года.

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

BETWEEN

Иногда нужно выбрать ту строку, в которой значение какого-либо столбца входит в определенный диапазон. Один из способов это сделать — использовать предикаты сравнения. Можно, например, составить предложение WHERE, предназначенное для выбора всех строк таблицы FOODS, в которых значение, хранящееся в столбце CALORIES, больше 100 и меньше 300:

WHERE FOODS.Calories > 100 AND FOODS.Calories < 300

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

WHERE FOODS.Calories >= 100 AND FOODS.Calories <= 300

Другой способ указать диапазон, включая его границы, — использовать предикат BETWEEN (между):

WHERE FOODS.Calories BETWEEN 100 AND 3 00

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

DISTINCT

Предикат DISTINCT (отличающийся) похож на UNIQUE, за исключением отношения к значениям NULL. Если в таблице, полученной в результате выполнения подзапроса, все значения являются уникальными, тогда они отличаются друг от друга. Однако, в отличие от результата предиката UNIQUE, если к такой таблице применить ключевое слово DISTINCT, а в ней, кроме двух неопределенных, больше никаких строк нет, то предикат DISTINCT является ложным. Два значения NULL не считаются отличающимися друг от друга, хотя и считаются уникальными. Такая странная ситуация выглядит противоречиво, но этому есть свое объяснение. Дело в том, что в некоторых ситуациях два значения NULL должны считаться отличными друг от друга, а в некоторых — одинаковыми. Тогда в первом случае надо использовать UNIQUE, а во втором — DISTINCT.

EXISTS

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

SELECT FirstName, LastName

FROM CUSTOMER

WHERE EXISTS

(SELECT DISTINCT CustomerlD

FROM SALES

WHERE SALES.CustomerID = CUSTOMER.CustomerlD)

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

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

Предикат EXISTS, как показано в следующем запросе, эквивалентен сравнению
COUNT с нулем:

SELECT FirstName, LastName

FROM CUSTOMER

WHERE 0 <>

(SELECT COUNT(*)

FROM SALES

WHERE SALES.CustomerID = CUSTOMER.CustomerlD);

Для каждой строки таблицы SALES, в которой значение CustomerlD равно какому-либо значению CustomerlD из таблицы CUSTOMER, этот оператор выводит столбцы FirstName (имя) и LastName (фамилия) из таблицы CUSTOMER. Поэтому для каждой сделки, отмеченной в таблице SALES, этот оператор выводит имя и фамилию того покупателя, который в ней участвовал.

Пристрелка" к нужным данным

Глава 9. "Пристрелка" к нужным данным

IN и NOT IN

Предикаты IN (в) и NOT IN (не в) используются для работы с любыми указанными значениями, такими, например, как OR (Орегон), WA (Вашингтон) и ГО (Айдахо), из определенного набора значений, а именно штатов США. Например, у вас имеется таблица, в которой перечислены поставщики товаров, регулярно закупаемых вашей компанией. Вам нужно узнать телефонные номера тех поставщиков, которые размещаются в северной части Тихоокеанского побережья. Эти номера можно найти с помощью предикатов сравнения, например таких, которые показаны в следующем примере:

SELECT Company, Phone

FROM SUPPLIER

WHERE State = 'OR' OR State = 'WA' OR State = 'ID' ;

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

SELECT Company, Phone

FROM SUPPLIER

WHERE State IN ('OR1, 'WA1, 'ID') ;

Это чуть более компактная форма записи, чем та, в которой используются предикаты сравнения и логические OR.

Таким же образом работает и второй вариант этого предиката — NOT IN. Скажем, у вас есть представительства в штатах Калифорния, Аризона и Нью-Мексико. Чтобы избежать уплаты налога с продаж, вы обдумываете возможность работы только с теми поставщиками, чьи представительства находятся за пределами этих трех штатов. Используйте следующую конструкцию:

SELECT Company, Phone

FROM SUPPLIER

WHERE State NOT IN CCA1, 'AZ', 'NM') ;

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

Совет 1
Совет 1

Даже если предикат IN дает возможность вводить не намного меньше кода, то у вас все равно есть еще одна веская причина, чтобы использовать именно IN, а не предикаты сравнения. Ваша СУБД, скорее всего, поддерживает оба этих метода, и один из них может работать значительно быстрее другого. Эти два метода включения или исключения можно испытать в работе, а затем использовать тот из них, который покажет самый быстрый результат. А если в СУБД имеется хороший оптимизатор, то, независимо от вида используемых вами предикатов, во время работы, скорее всего, будет выбран самый оптимальный метод. Испытание этих двух методов как раз покажет, насколько хорошо оптимизирует код используемая СУБД. Если между временем выполнения двух операторов имеется значительная разница, то качество оптимизации под вопросом.

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

Предположим, что вам нужно вывести имена всех тех, кто за последние 30 дней купил товар F-117A. Имена и фамилии покупателей находятся в таблице CUSTOMER (покупатель), а данные о сделках— в таблице TRANSACT (заключение сделок). Для этого вы сможете использовать следующий запрос:

SELECT FirstName, LastName

FROM CUSTOMER

WHERE CustomerlD IN

(SELECT CustomerlD

FROM TRANSACT

WHERE ProductID = 'F-117A'

AND TransDate >= (CurrentDate - 30)) ;

В первой из этих таблиц используются поля CustomerlD (идентификатор покупателя), FirstName (имя), LastName (фамилия), а во второй — CustomerlD, ProductID (идентификатор товара) и TransDate (дата сделки). Кроме того, используется переменная CurrentDate (текущая дата). Внутренний оператор SELECT, работающий с таблицей TRANSACT, вложен во внешний оператор SELECT, работающий с таблицей CUSTOMER. Первый из них ищет в столбце CustomerlD номера всех тех, кто за последние 30 дней купил товар F-l 17A. А внешний оператор SELECT выводит имена и фамилии всех покупателей, номера которых получены с помощью внутреннего оператора SELECT.

LIKE и NOT LIKE

Для сравнения двух символьных строк, чтобы выяснить их частичное соответствие друг другу, можно использовать предикат LIKE (похожий). Частичное соответствие представляет ценность тогда, когда о разыскиваемой строке что-то все-таки известно, но не известно, как она в точности выглядит. Кроме того, частичные соответствия можно использовать, чтобы получить из таблицы множество строк, в которых один из столбцов содержит похожие друг на друга символьные строки.

Чтобы указать частичные соответствия, в SQL используются два символа-маски (wildcard character). Знак процента (%) означает любую строку, состоящую из любого количества символов (в том числе и равного нулю). Символ подчеркивания О означает любой одиночный символ. Некоторые примеры того, как можно использовать предикат LIKE, показаны в табл. 9.3.

Логические связки

Как видно из массы предыдущих примеров, чтобы из таблицы получить нужные строки, одного условия в запросе часто бывает недостаточно. В некоторых случаях условий, которым должны удовлетворять строки, должно быть не меньше двух. В других же случаях, чтобы быть выбранной, строка должна удовлетворять одному из нескольких условий. А иногда нужно получить только те строки, которые указанному условию не удовлетворяют. Для выполнения этих требований в SQL имеются логические связки AND, OR и NOT.

MATCH

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

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

Как используется предикат MATCH, можно узнать с помощью примера, где применяются опять же таблицы CUSTOMER и SALES. CustomerED (идентификатор покупателя) — это первичный ключ таблицы CUSTOMER, и работает он как внешний ключ таблицы SALES. В каждой строке таблицы CUSTOMER должно быть уникальное значение CustomerlD, не равное NULL. А в таблице SALES ключ CustomerlD не является уникальным, потому что в ней повторяются его значения, относящиеся к тем, кто покупал больше одного раза. Это нормальная ситуация, которая не угрожает целостности, потому что в этой таблице CustomerlD является не первичным, а внешним ключом.

Совет 3
Совет 3

По-видимому, в столбце CustomerlD таблицы SALES могут быть и значения NULL, потому что кто-то может зайти к вам с улицы, купить что-то и выйти еще до того, как вы сможете ввести его или ее имя, фамилию и адрес в таблицу CUSTOMER. Тогда в дочерней таблице может появиться строка, у которой нет соответствующей строки в родительской таблице. Чтобы справиться с этой трудностью, можно включить в таблицу CUSTOMER строку для "общего" пользователя и заносить все эти анонимные продажи в базу на его идентификатор.

Скажем, к кассиру подходит покупательница и утверждает, что 18 мая 2003 года она купила истребитель-невидимку F-117A "Стеле". Теперь же она хочет вернуть самолет, потому что его, словно авианосец, видно на вражеских радарах. Ее заявление может быть подтверждено с помощью проверки вашей базы SALES с помощью MATCH. Прежде всего необходимо найти в столбце CustomerlD идентификатор покупательницы и присвоить его значение переменной .vcustid, а затем можно использовать следующий синтаксис, в котором применяются столбцы CustomerlD, ProductID (идентификатор товара), SaleDate (дата продажи):

... WHERE (-.vcustid, 'F-117-A1, '2003-05-18')

MATCH

(SELECT CustomerlD, ProductID, SaleDate

FROM SALES).

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

Технические подробности: Разработчики языка SQL добавили в него предикаты MATCH и UNIQUE по одной и той же причине — эти предикаты дают возможность явно выполнять проверки, которые определены для неявных ограничений, связанных со ссылочной целостностью и уникальностью.

Предикат MATCH имеет такой общий вид:

Значение_типа_записи ROW MATCH [UNIQUE] [SIMPLE| PARTIAL |

FULL ] Подзапрос

Ключи UNIQUE (уникальный), SIMPLE (простой), PARTIAL (частичный) и FULL (полный) связаны с правилами обработки выражения типа записи, имеющего столбцы с неопределенными значениями. Правила для предиката MATCH являются точной копией соответствующих правил ссылочной целостности.

NOT

Для отрицания условия служит связка NOT (не). Если к условию, которое возвращает значение True, добавить NOT, то после этого условие будет возвращать значение False. А если до изменения условие возвращало False, то после добавления к нему NOT оно будет возвращать True. Посмотрите на следующий пример:

SELECT InvoiceNo, SaleDate, Salesperson, TotalSale

FROM SALES

WHERE NOT (Salesperson = 'Ford') ;

Этот запрос возвращает строки для всех сделок по продажам, совершенных всеми продавцами, кроме Форда.

NULL

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

SELECT (FOOD)

FROM FOODS

WHERE Carbohydrate IS NULL ;

Этот запрос возвращает следующие значения:

Гамбургер с нежирной говядиной

Нежное мясо цыплят

Жареный опоссум

Свиной окорок

Как вы, возможно, и предполагаете, если вставить ключевое слово NOT (нет), то получится совершенно противоположный результат:

SELECT (FOOD)

FROM FOODS

WHERE Carbohydrate IS NOT NULL ;

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

Общие продажи по каждому продавцу

Рисунок 9.3. Общие продажи по каждому продавцу

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

Общие продажи по каждому продавцу за исключением Фергюсона

Рисунок 9.4. Общие продажи по каждому продавцу за исключением Фергюсона

Чтобы показать таблицу, выводимую запросом, в алфавитном порядке или в обратном алфавитном порядке, используйте предложение ORDER BY (по порядку). В то время как предложение GROUP BY собирает строки в группы и сортирует группы по алфавиту, ORDER BY сортирует отдельные строки. ORDER BY должно быть последним предложением в запросе. Если в запросе также имеется и предложение GROUP BY, то оно вначале собирает строки вывода в группы. Затем предложение ORDER BY сортирует строки, находящиеся внутри каждой группы. А если предложения GROUP BY нет, то оператор рассматривает всю таблицу как группу и предложение ORDER BY сортирует все ее строки таким образом, чтобы были упорядочены значения в столбцах, указанных в этом предложении.

Это можно проиллюстрировать с помощью данных из таблицы SALES (продажи). В ней имеются столбцы InvoiceNo (номер счета-фактуры), SaleDate (дата продажи), Salesperson (продавец), TotalSale (всего продано). Все данные SALES можно увидеть, но в произвольном порядке, если воспользоваться следующим простым примером:

SELECT * FROM SALES ;

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

SELECT * FROM SALES ORDER BY SaleDate ;

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

А порядок расположения тех строк, у которых в столбце SaleDate одинаковые значения, зависит от используемой реализации. Впрочем, и для строк с одинаковыми значениями Sale-Date можно также указать порядок сортировки. Возможно, вам, например, нужно для каждого значения SaleDate видеть строки таблицы SALES, расположенные по порядку, которые заданы значениями InvoiceNo:

SELECT * FROM SALES ORDER BY SaleDate, InvoiceNo ;

В этом примере таблица SALES вначале упорядочивается по значениям SaleDate; затем для каждого такого значения строки этой таблицы располагаются по порядку, задаваемому InvoiceNo. Однако не путайте этот пример со следующим запросом:

SELECT * FROM SALES ORDER BY InvoiceNo, SaleDate ;

При выполнении этого запроса SALES упорядочивается по столбцу InvoiceNo. Затем для каждого значения InvoiceNo строки таблицы SALES располагаются по порядку, задаваемому столбцом SaleDate. Скорее всего, нужный вам результат вы не получите, потому что мало вероятно, чтобы для одного номера счета-фактуры было множество дат продажи.

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

SELECT * FROM SALES ORDER BY Salesperson, SaleDate ;

В этом примере упорядочивание сначала идет по столбцу Salesperson, а затем — по SaleDate. Просмотрев данные, расположенные в таком порядке, вы, возможно, захотите его изменить:

SELECT * FROM SALES ORDER BY SaleDate, Salesperson ;

Теперь упорядочивание сначала идет по столбцу SaleDate, а затем — по Salesperson.

Во всех этих примерах упорядочивание идет в порядке возрастания (ASC), который является порядком сортировки по умолчанию. Последний оператор SELECT вначале показывает самые ранние продажи (строки таблицы SALES), а в пределах определенной даты ставит продажи, проведенные Адамсом, перед продажами, проведенными Бейкером. А если вы предпочитаете порядок убывания (DESC), можете указать его для одного или множества столбцов из предложения ORDER BY:

SELECT * FROM SALES

ORDER BY SaleDate DESC, Salesperson ASC ;

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

OR...

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

SELECT invoiceNo, SaleDate, Salesperson, TotalSale

FROM SALES

WHERE Salesperson = 'Ford'

OR TotalSale > '200' ;

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

OVERLAPS

Предикат OVERLAPS (перекрывает) применяется для того, чтобы определить, не перекрывают ли друг друга два промежутка времени. Он полезен тогда, когда нужно избежать "накладок" в расписании. Когда два промежутка времени перекрываются, то этот предикат возвращает значение True. Если они не перекрываются, то будет возвращено значение False.

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

(TIME '2:55:00', INTERVAL 'I' HOUR)

OVERLAPS

(TIME '3:30:00' , INTERVAL '2' HOUR)

В только что приведенном примере будет возвращено значение True, так как 3:30 наступает после 2:55 меньше чем через час.

(TIME '9:00:00', TIME '9:30:00')

OVERLAPS

(TIME '9:29:00', TIME '9:31:00')

Во втором примере будет возвращено значение True, потому что два промежутка времени перекрываются в течение одной минуты.

(TIME '9:00:00', TIME '10:00:00')

OVERLAPS

(TIME '10:15:00', INTERVAL '3' HOUR)

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

(TIME '9:00:00', TIME '9:30:00')

OVERLAPS

(TIME '9:30:00', TIME '9:35:00')

И наконец, в последнем примере будет возвращено значение False — хотя два промежутка времени и являются смежными, но они не перекрываются.

Правила ссылочной целостности

Правила ссылочной целостности требуют, чтобы значения в столбце (или столбцах) одной таблицы соответствовали значениям в столбце (или столбцах) другой. Столбцы в первой таблице называются внешним ключом, а во второй — первичным, или уникальным, ключом. Например, столбец EmpDeptNo (номер отдела, где работает сотрудник) из таблицы EMPLOYEE (сотрудник) можно объявить внешним ключом, который ссылается на столбец DeptNo (номер отдела) из таблицы DEPT (отдел). Это соответствие дает гарантию, что когда в таблицу EMPLOYEE о сотруднике заносится информация, что он работает в отделе 123, то в таблице DEPT появляется запись, в которой значением столбца DeptNo является 123.

Такая ситуация является довольно простой, если внешний и первичный ключи состоят из одного столбца каждый. Однако оба эти ключа могут состоять также из множества столбцов. Например, значение в столбце DeptNo может быть уникальным только для одного и того же значения в столбце Location (представительство). Поэтому, чтобы однозначно определить строку из таблицы DEPT, необходимо указать значение и для столбца Location, и для столбца DeptNo. Если, например, отдел 123 имеется в двух представительствах, расположенных соответственно в Бостоне и в Тампе, то отделы необходимо указывать как ('Boston', '123') и ('Tampa', '123'). В таком случае для указания в таблице EMPLOYEE строки из таблицы DEPT необходимо использовать два столбца. Их можно назвать EmpLoc (представительство, где работает сотрудник) и EmpDeptNo. Если сотрудник работает в каком-либо отделе, расположенном в Бостоне, то значениями столбцов EmpLoc и EmpDeptNo будут соответственно Boston' и '123'. Таким образом, объявление внешнего ключа в EMPLOYEE будет следующим:

FOREIGN KEY (EmpLoc, EmpDeptNo)

REFERENCES DEPT (Location, DeptNo)

Вывод правильных заключений из ваших данных в громадной степени усложняется, если в этих данных содержатся неопределенные значения. Иногда данные с такими значениями надо интерпретировать одним способом, а иногда — другим. Разные интерпретации данных, в которых встречаются значения NULL, можно задавать с помощью ключевых слов UNIQUE, SIMPLE, PARTIAL и FULL. Если в ваших данных нет неопределенных значений, то вы в значительной степени избавитесь от необходимости ломать голову, просто возьмете и перейдете к следующему разделу "Логические связки". Ну а если в ваших данных такие значения есть, то тогда от режима скорочтения сейчас лучше отказаться и начать медленно и внимательно читать последующие абзацы. В каждом из них описана отдельная ситуация, связанная со значениями NULL, и рассказывается, как с ней справляется предикат MATCH.

Если значения EmpLoc и EmpDeptNo вместе являются или не являются неопределенными, то правила ссылочной целостности будут такие же, как и для ключей, состоящих из одного столбца с неопределенными или определенными значениями. Но если значение EmpLoc неопределенное, a EmpDeptNo — нет или наоборот, то тогда нужны новые правила. И какие же правила нужны, когда в таблицу EMPLOYEE при вставке или обновлении ее строк вводятся значения EmpLoc и EmpDeptNo как (NULL, '123') или ('Boston', NULL)? Существует шесть вариантов, при которых используются SIMPLE, PARTIAL и FULL вместе с ключевым словом UNIQUE или без него. Присутствие этого ключевого слова означает следующее. Чтобы предикат был истинным, значение типа записи, найденное с помощью MATCH в таблице-результате выполнения подзапроса, должно быть уникальным. И если в значении выражения R, имеющем тип записи, оба компонента являются неопределенными, то предикат MATCH возвращает значение True, каким бы ни было содержимое сравниваемой таблицы, полученной при выполнении подзапроса.

Если в значении выражения R типа записи с ключевым словом SIMPLE, но без UNIQUE, ни один из компонентов не является неопределенным и при этом хотя бы одна строка в таблице, полученной при выполнении подзапроса, соответствует R, то предикат MATCH возвращает значение True. В противном случае он возвращает значение False.

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

Если в значении выражения R типа записи с ключевым словом SIMPLE какой-нибудь из компонентов является неопределенным, то предикат MATCH возвращает значение True.

Если в значении выражения R типа записи с ключевым словом PARTIAL, но без UNIQUE, какой-нибудь из компонентов не является неопределенным и при этом определенные значения хотя бы одной строки в таблице, полученной при выполнении подзапроса, соответствуют R, то тогда предикат MATCH возвращает значение True. В противном случае он возвращает значение False.

Если в значении выражения R типа записи с ключевыми словами PARTIAL и UNIQUE какой-нибудь из компонентов не является неопределенным и при этом определенные части R соответствуют определенным частям хотя бы одной уникальной строки в таблице, полученной при выполнении подзапроса, то тогда предикат MATCH возвращает значение True. В противном случае он возвращает значение False.

Если ни один из компонентов значения выражения R типа записи с ключом FULL, но без UNIQUE, не является неопределенным и при этом хотя бы одна строка в таблице, полученной при выполнении подзапроса, соответствует R, то тогда предикат MATCH возвращает значение True. В противном случае он возвращает значение False.

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

Если какой-либо из компонентов выражения R является неопределенным и указано ключевое слово FULL, то предикат MATCH возвращает значение False.

Правила комитета по стандартам
С появлением SQL-89 стало подразумеваться, что по умолчанию используется правило UNIQUE. Это случилось еще до того, как кто-либо успел предложить или обсудить другие варианты. Но такие предложения появились уже во время разработки SQL-92. Кто-то упорно предпочитал правила PARTIAL и считал, что они должны быть единственными. С этой точки зрения правила SQL-89 (UNIQUE) были настолько нежелательны, что рассматривались как ошибка, которую необходимо исправить с помощью правил PARTIAL. Были и те, кому больше нравились правила UNIQUE, а правила PARTIAL для них были непонятными, двусмысленными и неэффективными. Впрочем, были и те, кто предпочитал еще более строгие правила FULL. В конце концов этот спор был решен следующим образом: пользователи получили в свое распоряжение все три ключевых слова и теперь могли выбирать тот подход, который им нужен. А потом, с появлением SQL: 1999, добавились еще и правила SIMPLE. Впрочем, рост числа правил делает работу с неопределенными значениями какой угодно, но только не простой (simple). Итак, если не указаны ключевые слова SIMPLE, PARTIAL или FULL, то будут выполняться правила SIMPLE.

Предикаты сравнения

Примеры, приведенные в предыдущем разделе, демонстрируют обычное использование предикатов сравнения, в которых одно значение сравнивается с другим. Каждая строка, где в результате сравнения получается значение True, выполняет условие предложения WHERE, и с ней выполняется определенная операция (SELECT, UPDATE, DELECT и т.д.). Строки, где в результате сравнения получается значение False, пропускаются. Проанализируйте, например, следующий оператор SQL:

SELECT * FROM FOODS

WHERE Calories < 219 ;

Этот оператор выводит все строки таблицы FOODS, в которых значение, хранящееся в столбце Calories, меньше 219.

В табл. 9.2 приведены шесть предикатов сравнения.

Предложение HAVING

Предложение HAVING позволяет еще лучше анализировать сгруппированные данные. Предложение HAVING (при условии) — это фильтр, который по своему действию похож на предложение WHERE, но, в отличие от предложения WHERE, HAVING работает не с отдельными строками, а с их группами. Проиллюстрируем действие предложения HAVING, используя следующий пример. Предположим, что менеджеру по продажам нужно сосредоточиться на работе других продавцов. Для этого ему необходимо исключить из общих данных количество продаж Фергюсона, поскольку его продажи находятся "в другой весовой категории". Чтобы сделать это, воспользуемся предложением HAVING:

SELECT Salesperson, SUM(TotalSale)

FROM SALES

GROUP BY Salesperson

HAVING Salesperson <> 'Ferguson';

Результат этого запроса приведен на Рисунок 9.4. Строки, которые относятся к продавцу по фамилии Фергюсон, на экран не выводятся.

Предложения From

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

SELECT * FROM SALES ;

Этот оператор возвращает все данные, находящиеся во всех строках каждого столбца таблицы SALES (продажи). Впрочем, в предложении FROM можно указывать больше, чем одну таблицу. Например:

SELECT *

FROM CUSTOMER, SALES ;

Этот оператор создает виртуальную таблицу, в которой данные из таблицы CUSTOMER (покупатель) объединены с данными из таблицы SALES. Для создания новой таблицы каждая строка из CUSTOMER объединяется с каждой строкой из SALES. Поэтому в создаваемой таким способом новой виртуальной таблице количество строк равно количеству строк первой таблицы, умноженному на количество строк второй. И если в CUSTOMER десять строк, а в SALES — сто, то в новой таблице их будет тысяча.

Такая операция называется декартовым произведением двух исходных таблиц. Декартово произведение на самом деле является разновидностью операции объединения (JOIN). Об операциях объединения подробно рассказывается в главе 10.

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

Предложения GROUP BY

Иногда вместо того, чтобы получить отдельные записи, вам может понадобиться узнать что-либо о группе записей. В этом случае вам поможет предложение GROUP BY (группировать по).

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

SELECT InvoiceNo, SaleDate, Salesperson, TotalSale

FROM SALES;

Полученный результат приведен на Рисунок 9.1.

Предложения WHERE

В этой книге предложение WHERE использовалось много раз без всякого объяснения, потому что его значение и способ использования очевидны. Оператор выполняет операцию (такую как SELECT, DELETE или UPDATE) только с теми табличными строками, для которых определенное условие истинно. У предложения WHERE такой синтаксис:

SELECT список_столбцов

FROM имя_таблицы

WHERE условие ;

DELETE FROM имя_таблицы

WHERE условие ;

UPDATE имя_таблицы

SET столбец1=значение1, столбец2=значение2, ...,

столбец_n=значение_n

WHERE условие ;

Во всех случаях условие в предложении WHERE может быть или простым, или сколь угодно сложным. Чтобы из множества условий создать одно, их можно соединить друг с другом при помощи логических связок AND, OR и NOT. В этой главе мы еще вернемся к ним.

Вот некоторые типичные примеры предложений WHERE.

WHERE CustomerlD = SALES.CustomerlD

WHERE FOODS.Calories = COMIDA.Caloria

WHERE FOODS.Calories < 219

WHERE FOODS.Calories > 3 * base_value

WHERE FOODS.Calories < 219 AND FOODS.Protein > 27.4

Условия, выражаемые предложениями WHERE, называются предикатами. Предикат — это выражение, которое утверждает факт, относящийся к значениям из этого выражения.

Например, предикат FOODS.Calories < 219 является истинным, если в текущей строке значение столбца FOODS.Calories меньше 219. Если утверждение является истинным, то оно удовлетворяет условию. Утверждение может быть истинным (т.е. его значение равно True), ложным (его значение равно False) или с неопределенным логическим значением. Последний случай бывает тогда, когда в утверждении какие-либо элементы имеют значение NULL. Наиболее распространенными являются предикаты сравнения (=, <, >, О, <= и >=), но в SQL имеются и некоторые другие, которые значительно увеличивают возможности "отфильтровывать" требуемые данные от всех остальных. Ниже приведен список с предикатами, предоставляющими такую возможность.

Предикаты сравнения. BETWEEN. IN [NOT IN]. LIKE [NOT LIKE]. NULL. ALL, SOME, ANY. EXISTS. UNIQUE. OVERLAPS. MATCH. SIMILAR. DISTINCT.

Результат выбора информации о

Рисунок 9.1. Результат выбора информации о продажах с 07.01.2001 по 07.07.2001

Этот результат дает вам лишь некоторое представление о том, как работают ваши продавцы, поскольку здесь выводится информация о небольшом количестве продаж. Однако в реальной жизни продажи компаний очень велики, и в этом случае уже непросто будет определить, как были достигнуты результаты. Чтобы проверить, была ли коммерчески эффективной работа продавцов, вы можете скомбинировать предложение GROUP BY с одной из функций агрегирования (также называемыми итоговыми функциями), чтобы получить количественную картину о выполнении продаж. К примеру, вы можете просмотреть, кто из продавцов работает с более дорогостоящими и прибыльными позициями, используя функцию среднего значения (AVG):

SELECT Salesperson, AVG(TotalSale)

FROM SALES

GROUP BY Salesperson;

Полученный результат приведен на Рисунок 9.2.

SIMILAR

Вместе с SQL: 1999 появился и предикат SIMILAR (подобный), который позволяет находить частичное соответствие более эффективно, чем это делает предикат LIKE. С помощью предиката SIMILAR можно сравнить символьную строку с регулярным выражением. Скажем, например, вы просматриваете в таблице программной совместимости столбец OperatingSys-tem (операционная система), чтобы проверить совместимость с Microsoft Windows. Можно составить примерно такое предложение WHERE:

WHERE OperatingSystem SIMILAR TO

'(Windows (3.1195|98|Millenium Edition|CE|NT|2 000|XP)) '

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

Средний уровень продаж по каждому продавцу

Рисунок 9.2. Средний уровень продаж по каждому продавцу

Как видно из Рисунок 9.2, средние продажи Фергюсона значительно выше, чем у двух других продавцов. Чтобы сравнить общие продажи по каждому продавцу, выполните следующий запрос:

SELECT Salesperson, SUM(TotalSale)

FROM SALES

GROUP BY Salesperson;

Результат этого запроса приведен на Рисунок 9.3.

Уточняющие предложения и их назначение

Таблица 9.1. Уточняющие предложения и их назначение

Уточняющее предложение	Для чего предназначено
from	Указывает, из каких таблиц брать данные
where	Фильтрует строки, которые не соответствуют условию поиска
group by	Группирует строки в соответствии со значениями в столбцах группирования
having	Фильтрует группы, которые не соответствуют условию поиска
order by	Сортирует результаты предыдущих предложений перед получением окончательного вывода

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

SELECT список_столбцов

FROM список_таблиц

[WHERE условие_поиска]

[GROUP BY столбец_группирования]

[HAVING условие_поиска]

[ORDER BY условие_упорядочивания] ;

Ниже описывается, как работают уточняющие предложения.

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

Квадратные скобки ([]) означают, что предложения WHERE, GROUP BY, HAVING и ORDER BY не являются обязательными.

SQL выполняет эти предложения в следующем порядке: FROM, WHERE, GROUP BY, HAVING и SELECT. Предложения работают по принципу конвейера, когда каждое из них получает результат выполнения предыдущего предложения, обрабатывает этот результат и передает то, что получилось, следующему предложению. Если этот порядок выполнения переписать в виде функций, то он будет выглядеть следующим образом:

SELECT (HAVING (GROUP BY (WHERE (FROM...) ) ) )

Предложение ORDER BY выполняется уже после SELECT. Оно может обращаться только к тем столбцам, которые перечислены в списке, находящемся после SELECT. К другим же столбцам из таблиц, перечисленных в предложении FROM, предложение ORDER BY обращаться не может.

Предикаты сравнения языка SQL

Таблица 9.2. Предикаты сравнения языка SQL

Сравнение	Символ
Равно	=
Не равно	<>
Меньше	<
Меньше или равно	<=
Больше	>
Больше или равно	>=

Предикат like используемый в SQL

Таблица 9.3. Предикат like, используемый в SQL

Выражение	Возвращаемые значения
WHERE WORD LIKE 'intern%'	intern
	internal
	international
	internet
	interns
WHERE WORD LIKE '%Peace%'	Justice of the Peace
	Peaceful Warrior
WHERE WORD LIKE 't_p_'	tape
	taps
	tipi
	tips
	tops
	type

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

WHERE PHONE NOT LIKE '503%'

В этом случае будут возвращены все строки таблицы, в которых телефонный номер, содержащийся в столбце PHONE (телефон), не начинается с 503.

Совет 2
Совет 2

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

SELECT Quote

FROM BARTLETTS

WHERE Quote LIKE '20#%'

ESCAPE '#' ;

Символ % превращается в обычный из символа-маски с помощью стоящего перед ним символа #. Точно таким же способом можно отключить и символ подчеркивания, а также сам управляющий символ. Например, предшествующий запрос должен найти такую цитату из "Bartlett's Familiar Quotations" (Известные цитаты Бартлетта):

20% of the salespeople produce 80% of the results.

Данный запрос найдет также следующее:

20%

UNIQUE

Вместе с подзапросом, как и предикат EXISTS, можно также использовать предикат UNIQUE (уникальный). Если первый из этих предикатов является истинным тогда, когда подзапрос возвращает хотя бы одну строку, то второй из них будет истинным тогда, когда среди возвращенных подзапросом строк нет двух одинаковых. Другими словами, предикат UNIQUE будет истинным, если все возвращаемые подзапросом строки будут уникальными. Проанализируйте следующий пример:

SELECT FirstName, LastName

FROM CUSTOMER

WHERE UNIQUE

(SELECT CustomerID FROM SALES

WHERE SALES.CustomerID = CUSTOMER.CustomerID);

Этот оператор возвращает только имена и фамилии всех новых покупателей, которые участвовали лишь в одной из сделок, указанных в таблице SALES. Два значения NULL считаются не равными друг другу и, следовательно, уникальными. И когда ключевое слово UNIQUE применяется к таблице, полученной в результате выполнения подзапроса, а в этой таблице никаких строк, кроме двух неопределенных, больше нет, то и тогда предикат UNIQUE является истинным.

Уточняющие предложения

В SQL имеются следующие уточняющие предложения: FROM, WHERE, HAVING, GROUP BY и ORDER BY. Предложение FROM (из) сообщает ядру базы данных, с какой таблицей (или таблицами) он должен работать. Что касается WHERE (где) и HAVING (при условии), то эти предложения указывают характеристику, определяющую необходимость выполнения текущей операции над конкретной строкой. И наконец, предложения GROUP BY (группировать по) и ORDER BY (упорядочивать по) указывают, каким образом следует выводить строки, полученные из базы данных. Основные сведения по уточняющим предложениям приведены в табл. 9.1.

Отделение нужных строк от всех

В этой главе...

Указание требуемых таблиц Отделение нужных строк от всех остальных Создание эффективных предложений where Как работать со значениями null Создание составных выражений с логическими связками Группирование вывода результата запроса по столбцу Упорядочение результата запроса У системы управления базой данных есть две главные обязанности: хранение данных и обеспечение к ним легкого доступа. В хранении данных нет ничего особенного, ту же работу может выполнять и шкаф. А что действительно трудно — так это обеспечить удобный доступ к данным. Для этого необходимо иметь возможность выловить небольшое количество нужных данных из океана ненужных.
SQL позволяет использовать некоторые характеристики самих данных для определения, представляют ли они для вас интерес. Операторы SELECT (выбрать), DELETE (удалить) и UPDATE (обновить) сообщают ядру (engine) базы данных, т.е. той части СУБД, которая как раз и взаимодействует с данными, какие именно строки необходимо выбрать, удалить или обновить. Чтобы обрабатывались требуемые строки, в операторы SELECT, DELETE и UPDATE добавляются уточняющие предложения.

Ключевое слово BETWEEN может привести

Внимание

Ключевое слово BETWEEN может привести к путанице, потому что неочевидно, включены ли в предложение границы диапазона. На самом деле границы в предложение включены. Кроме того, первая граница обязательно должна быть не больше второй. Если, например, в FOODS.Calories содержится значение 200, то следующее предложение возвращает значение True: WHERE FOODS.Calories BETWEEN 100 AND 300
Однако предложение, казалось бы, эквивалентное предыдущему примеру, на самом деле возвращает противоположный результат False:
WHERE FOODS.Calories BETWEEN 300 AND 100
Помни:Для ключевого слова BETWEEN первая граница обязательно должна быть не больше второй.
Предикат BETWEEN можно использовать со следующими типами данных: символьными, битовыми, даты-времени, а также с числовыми. Вам могут встретиться примеры, похожие на следующий:
SELECT FirstName, LastName
FROM CUSTOMER
WHERE CUSTOMER.LastName BETWEEN 'A' AND 'Mzzz' ;
При его выполнении возвращаются данные обо всех покупателях, фамилии которых находятся в верхней половине списка по алфавиту.

Естественное объединение

Частным случаем объединения, основанного на равенстве, является естественное объединение (natural join). В предложении WHERE из объединения, основанного на равенстве, проверяется равенство значения из столбца первой исходной таблицы значению из столбца второй. У двух столбцов должны быть одинаковые тип и длина, как, впрочем, у сравниваемых столбцов должно быть одно и то же имя. На самом же деле при естественном объединении равенство проверяется для всех столбцов из первой таблицы, имеющих те же имена, что и соответствующие им столбцы из второй.

Представьте, что в таблице COMPENSATION из предыдущего примера также имеются столбцы Salary и Bonus, но Employ заменен на ЕтрШ. В таком случае можно выполнить естественное объединение таблиц COMPENSATION и EMPLOYEE. Традиционный синтаксис объединения должен выглядеть примерно так:

SELECT E.*, С.Salary, С.Bonus

FROM EMPLOYEE E, COMPENSATION С

WHERE E.EmpID = C.EmpID ;

Этот запрос является естественным произведением. Для той же самой операции есть и альтернативный синтаксис:

SELECT E.*, С.Salary, С.Bonus

FROM EMPLOYEE E NATURAL JOIN COMPENSATION С ;

EXCEPT

Оператор UNION выполняется с двумя таблицами и возвращает все строки, которые имеются как минимум в одной из них. Другой же оператор, INTERSECT, возвращает все те строки, которые имеются одновременно в первой и второй таблицах. А оператор EXCEPT (за исключением), наоборот, возвращает все строки, которые имеются в первой таблице, но не имеются во второй.

Теперь вернемся к примеру с базой данных, в которой находится информация о муниципальных пейджерах. Скажем, группа пейджеров, объявленных неработающими, была возвращена поставщику для ремонта, но к настоящему времени эти пейджеры уже исправлены и используются снова. В таблицу PAGERS данные о возвращенных пейджерах уже занесены, но из таблицы OUT их данные по некоторой причине не удалены, хотя это надо было сделать. С помощью оператора EXCEPT можно вывести все номера пейджеров, находящиеся в столбце PagerlD таблицы OUT, за исключением тех номеров, которые принадлежат уже исправленным пейджерам:

SELECT *

FROM OUT

EXCEPT CORRESPONDING (PagerlD)

SELECT *

FROM PAGERS;

При выполнении этого запроса возвращаются все строки из таблицы OUT, у которых значения PageiTD не находятся в таблице PAGERS.

Реляционные операторы

Глава 10. Реляционные операторы

INTERSECT

В результате выполнения оператора UNION создается таблица, где появляются все строки, которые могут находиться в какой-либо из исходных таблиц. А если нужны только те строки, каждая из которых находится одновременно во всех исходных таблицах, то можно использовать оператор INTERSECT (пересечь). Он является реализацией в SQL оператора пересечения из реляционной алгебры. Выполнение INTERSECT будет показано на примере из воображаемого мира, в котором Боб Тарли был в середине сезона "продан" команде "Доджерс".

SELECT * FROM NATIONAL ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Sal	Maglie	11
Don	Newcombe	9
Sandy	Koufax	13
Don	Drysdale	12
Bob	Turley	8
SELECT * FROM AMERICAN ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Whitey	Ford	12
Don	Larson	10
Bob	Turley	8
Allie	Reynolds	14

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

SELECT *

FROM NATIONAL

INTERSECT

SELECT *

FROM AMERICAN;

FirstName	LastName	СompleteGames
-----------	-----------	------------------
Bob	Turley	8

В полученной таким образом таблице сообщается, что Боб Тарли был единственным питчером, который и в той и в другой лиге бессменно подавал мяч в течение одного и того же количества игр. Обратите внимание, что, как и в случае с UNION, INTERSECT DISTINCT выдает тот же результат, что и оператор INTERSECT, используемый без ключевого слова. В этом примере возвращается только одна строка с именем Боба Тарли.

Роль ключевых слов ALL и CORRESPONDING в операторе INTERSECT такая же, как и в операторе UNION. Если используется ALL, то получится таблица, в которой повторяющиеся строки остаются. А когда используется CORRESPONDING, то исходные таблицы не обязательно должны быть совместимыми для объединения, хотя у соответствующих столбцов должны быть одинаковые тип и длина.

Проанализируем следующий пример. В муниципалитете хранят данные о пейджерах, используемых полицейскими, пожарниками, уборщиками улиц и другими работниками городского хозяйства. Данные обо всех ныне используемых пейджерах находятся в таблице PAGERS (пейджеры) из базы данных. А данные обо всех пейджерах, которыми по той или иной причине не пользуются, находятся в другой таблице, OUT (вышедший из строя), имеющей такую же структуру, что и PAGERS. Информация ни по одному из пейджеров не может одновременно быть в двух таблицах. Выполнив оператор INTERSECT, можно проверить, не произошло ли такое ненужное дублирование строк:

SELECT *

FROM PAGERS

INTERSECT CORRESPONDING (PagerlD)

SELECT *

FROM OUT ;

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

Левое внешнее объединение

В запросе, имеющем объединение, левая таблица — это та, которая в операторе запроса предшествует ключевому слову JOIN, а правая — та, которая следует за ним. При левом внешнем объединении (left outer join) несоответствующие строки, имеющиеся в левой таблице, в выводе сохраняются, а имеющиеся в правой — из него, наоборот, удаляются.

Чтобы понять работу внешних объединений, представьте себе корпоративную базу данных, в которой хранятся записи о сотрудниках компании, ее отделах и представительствах. Примеры данных этой компании приведены в табл. 10.1-10.3.

Объединение основанное на равенстве

Объединение, основанное на равенстве

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

SELECT *

FROM EMPLOYEE, COMPENSATION

WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;

И вот что вышло:

EmpID	FName	LName	City	Phone	Employ	Salary	Bonus
---------	---------	--------	------	--------	---------	--------	--------
1	Whitey	Ford	Orange	555-1001	1	33000	10000
2	Don	Larson	Newark	555-3221	2	18000	2000
3	Sal	Maglie	Nutley	555-6905	3	24000	5000
4	Bob	Turley	Passaic	555-8908	4	22000	7000

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

SELECT EMPLOYEE.*, COMPENSATION.SALARY, COMPENSATION.Bonus

FROM EMPLOYEE, COMPENSATION

WHERE EMPLOYEE.EmpID = COMPENSATION.Employ ;

В результате получилось следующее:

EmpID	FName	LName	City	Phone	Salary	Bonus
---------	---------	--------	------	--------	--------	--------
1	Whitey	Ford	Orange	555-1001	33000	10000
2	Don	Larson	Newark	555-3221	18000	2000
3	Sal	Maglie	Nutley	555-6905	24000	5000
4	Bob	Turley	Passaic	555-8908	22000	7000

Эта таблица сообщает вам то, что вы хотите знать, при этом не "нагружая" вас никакими лишними данными. Впрочем, писать сам запрос было несколько утомительно. Чтобы избежать двусмысленности, в именах столбцов приходилось явно указывать имена таблиц. Единственная выгода от этого — тренировка пальцев.

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

SELECT Е.*, С.Salary, С.Bonus

FROM EMPLOYEE E, COMPENSATION С

WHERE E.EmpID = С.Employ ;

В этом примере Е — это псевдоним для EMPLOYEE, а С — для COMPENSATION. Действие псевдонима ограничено только тем оператором, в котором он определен. После объявления псевдонима в предложении FROM его необходимо использовать в пределах оператора. При этом нельзя одновременно использовать и длинную форму имени таблицы, и псевдоним.

Смешение полных имен с псевдонимами приводит к путанице. Проанализируйте следующий пример:

SELECT T1.C, T2.С

FROM Т1 Т2, Т2 Т1

WHERE T1.C > Т2.С ;

В этом примере псевдонимом для Т1 является Т2, а для Т2 — Т1. Конечно, такой выбор неразумен, однако формально он не противоречит никаким правилам. Если допустить возможность совместного использования полных имен и псевдонимов, невозможно определить, о какой таблице идет речь.

Предыдущий пример с псевдонимами эквивалентен следующему оператору SELECT без них:

SELECT T2.C, T1.C

FROM T1, T2

WHERE T2.С > T1.C ;

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

SELECT Е.*, С.Salary, С.Bonus, Y.TotalSales

FROM EMPLOYEE E, COMPENSATION C, YTD_SALES Y

WHERE E.EmpID = С Employ

AND C.Employ = Y.EmpNo ;

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

Совет 2
Совет 2

Если данные о продажах, проведенных продавцами за последние 12 месяцев до текущей даты, будут храниться в отдельной таблице YTD_SALES (продажи за предшествующий год), то производительность и надежность будут выше, чем при хранении этих данных в таблице EMPLOYEE. Данные в EMPLOYEE относительно стабильные. Имя и фамилия человека, его адрес и номер телефона меняются не слишком часто. А данные о продажах за год меняются, наоборот, достаточно часто. Так как в таблице YTD_SALES столбцов меньше, чем в EMPLOYEE, то таблица YTD_SALES, скорее всего, сможет обновляться быстрее. И если при обновлении итогов продаж можно не трогать таблицу EMPLOYEE, то уменьшается риск случайного изменения хранящихся в ней данных.

Объединение по именам столбцов

Объединение по именам столбцов похоже на естественное, но является более гибким. При естественном объединении проверяется равенство значений из всех одноименных столбцов, которые только имеются в исходных таблицах. А что касается объединения по именам столбцов, то в нем можно выбирать, какие одноименные столбцы должны проверяться, а какие — нет. Если хотите, то можете выбрать их все, фактически превращая объединение по именам столбцов в естественное. Можете также выбрать и меньшее количество этих столбцов. Таким образом, есть возможность в большей степени определить, какие строки из перекрестного произведения должны оказаться в полученной вами итоговой таблице.

Скажем, вы изготовитель шахмат и имеете инвентарную таблицу, в которой хранятся данные о белых фигурах, а также другую такую же таблицу, но с данными о черных фигурах. Эти таблицы называются WHITE (белая) и BLACK (черная), и в каждой из них имеются следующие поля: Piece (фигура), Quant (количество), Wood (дерево). В таблицах хранятся такие данные:

WHITE	BLACK
--------			--------
Piece	Quant	Wood	Piece	Quant	Wood
------	-------	-------	------	-------	-------
King	502	Oak	King	502	Ebony
Queen	398	Oak	Queen	397	Ebony
Rook	1020	Oak	Rook	1020	Ebony
Bishop	985	Oak	Bishop	985	Ebony
Knight	950	Oak	Knight	950	Ebony
Pawn	431	Oak	Pawn	431	Ebony

Для каждой разновидности фигур — короля (King), королевы (Queen), ладьи (Rook), слона (Bishop), коня (Knight), пешки (Pawn), — изготавливаемых из дуба (Oak) или из черного дерева (Ebony), количество белых и черных фигур должно быть равным. Если же равенство нарушено, то это означает, что некоторые фигуры или потеряны, или украдены, и, следовательно, вам надо улучшить условия хранения товара.

При естественном объединении проверяется равенство значений во всех одноименных столбцах. В таком случае получится пустая таблица, потому что в таблице WHITE нет таких строк, где значение в столбце Wood бьло бы равно какому-либо значению из столбца Wood таблицы BLACK. Таблица, полученная в результате естественного объединения, не позволит определить, пропало что-нибудь или нет. Поэтому надо использовать объединение по именам столбцов, в котором столбец Wood исключается из рассмотрения. Это объединение может быть представлено в таком виде:

SELECT *

FROM WHITE JOIN BLACK

USING (Piese, Quant) ;

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

Piece	Quant	Wood	Piece	Quant	Wood
------	-------	-------	------	-------	-------
King	502	Oak	King	502	Ebony
Rook	1020	Oak	Rook	1020	Ebony
Bishop	985	Oak	Bishop	985	Ebony
Knight	950	Oak	Knight	950	Ebony

Внимательный читатель может заметить, что из списка пропали королева и пешка, — признак того, что каких-либо из этих фигур не хватает.

Объединениеслияние

Объединение-слияние

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

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

Предположим, что вы работаете в компании, которая проектирует и производит ракеты, предназначенные для экспериментальных запусков. У вас в работе имеется несколько проектов. Под вашим руководством работают несколько инженеров-проектировщиков, каждый из которых имеет квалификацию в нескольких видах деятельности. Как менеджера вас интересует, какие инженеры в каких видах деятельности имеют квалификацию и над какими проектами работали. В настоящее время эти данные разбросаны по трем таблицам: EMPLOYEE (сотрудник), PROJECTS (проекты) и SKILLS (области квалификации).

В таблице EMPLOYEE хранятся данные о сотрудниках, и ее первичным ключом является EMPLOYEE.EmpID. Каждый проект, над которым работал сотрудник, занимает одну строку в другой таблице — PROJECTS. PROJECTS.EmpID — это внешний ключ, который ссылается на таблицу EMPLOYEE. В таблице SKILLS для каждого сотрудника перечислены те виды деятельности, в которых он имеет квалификацию. SKILLS.EmpID— внешний ключ, который ссылается на таблицу EMPLOYEE.

В таблице EMPLOYEE для каждого сотрудника имеется в точности одна строка. А в таблицах PROJECTS и SKILLS таких строк может быть сколько угодно, в том числе и ни одной.

Примеры данных, хранящихся в трех указанных таблицах, приведены в табл. 10.4-10.6.

ON или WHERE

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

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

Во внешних объединениях предложения ON и WHERE отличаются друг от друга. Предложение WHERE всего-навсего фильтрует строки, возвращаемые предложением FROM. Строки, отбракованные фильтром, просто не попадут в результат. А предложение ON, используемое во внешнем объединении, вначале фильтрует строки перекрестного произведения, а затем добавляет в результат и отбракованные строки, расширенные неопределенными значениями.

Операторы объединения

Операторы UNION, INTERSECT и EXCEPT представляют ценность в тех многотабличных базах данных, таблицы которых являются совместимыми. Однако во многих случаях приходится брать данные из наборов таблиц, имеющих между собой мало общего. Мощными реляционными операторами являются операторы объединения JOIN, в результате выполнения которых данные, взятые из множества таблиц, объединяются в одну. Таблицы из этого множества могут иметь мало общего друг с другом.

Стандарт SQL:2003 поддерживает разные типы операторов объединения. Какой из них лучше всего подходит в конкретной ситуации — это зависит от того результата, который требуется получить.

Перекрестное объединение

CROSS JOIN (перекрестное объединение) — это ключевое слово для простого объединения, не имеющего предложение WHERE. Поэтому оператор

SELECT *

FROM EMPLOYEE, COMPENSATION ;

также может быть записан как

SELECT *

FROM EMPLOYEE CROSS JOIN COMPENSATION ;

В результате получается декартово произведение (также известное как перекрестное произведение) двух исходных таблиц. CROSS JOIN редко дает тот окончательный результат, который вам нужен, но его применение может быть полезно в качестве первого шага в той цепочке манипуляций данными, которая в конце концов приведет к нужному результату.

Полное внешнее объединение

Полное внешнее объединение (full outer join) соединяет в себе функции левого и правого внешних объединений. В результате выполнения в выводе остаются несоответствующие строки как из левой, так и из правой таблицы. Проанализируем теперь самый общий вариант корпоративной базы данных, которая уже рассматривалась в предыдущих примерах. В этой базе могут быть:

представительства без отделов; отделы без представительств; отделы без сотрудников; сотрудники без отделов.

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

SELECT *

FROM LOCATION L FULL JOIN DEPT D

ON (L.LocationID = D.LocationID)

FULL JOIN EMPLOYEE E

ON (D.DeptID = E.DeptID) ;

Совет 4
Совет 4

Так как полного внутреннего объединения не существует, то полное внешнее объединение можно называть, полное объединение (в коде SQL это ключевые слова FULL JOIN).

Правое внешнее объединение

Готов поклясться, что вы уже знаете, как ведет себя правое внешнее объединение. И вы правы! Правое внешнее объединение (right outer join) сохраняет в выводе несоответствующие строки, взятые из правой таблицы, но удаляет из него несоответствующие строки, взятые из левой. Это внешнее объединение можно использовать с теми же таблицами, что в левом внешнем объединении, и получить при этом те же результаты. Для этого надо, заменив в операторе ключевые слова левого внешнего объединения на ключевые слова правого, поменять порядок следования таблиц на обратный:

SELECT *

FROM EMPLOYEE E RIGHT OUTER JOIN DEPT D

ON (D.DeptID = E.DeptID)

RIGHT OUTER JOIN LOCATION L

ON (L.LocationID = D.LocationID) ;

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

Так как правого внутреннего объединения не существует, то правое внешнее объединение можно называть правое объединение (в коде SQL это ключевые слова RIGHT JOIN).

Простой оператор объединения

Любой многотабличный запрос является оператором объединения. Исходные таблицы объединяются в том смысле, что в таблице, полученной в результате этого объединения, будет находиться информация из всех исходных таблиц. Самым простым оператором объединения является оператор SELECT, выполняемый с двумя таблицами и не имеющий никаких ограничителей из предложения WHERE. Так что каждая строка из первой таблицы объединяется с каждой строкой из второй. В результате получается таблица, которая является декартовым произведением двух исходных таблиц. Понятие декартова произведения обсуждалось в главе 9 в связи с использованием предложения FROM. Количество строк в полученной таблице равно произведению числа строк в первой исходной таблице и числа строк во второй.

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

EMPLOYEE (сотрудник)	COMPENSATION (компенсация)
--------------------------	---------------------------------
EmpID (идентификатор сотрудника)	Employ (сотрудник)
FName (имя)	Salary (зарплата)
LName (фамилия)	Bonus (премиальные)
City (город)
Phone (телефон)

Заполните таблицы какими-либо взятыми для примера данными.

EmpID	FName	LName	City	Phone	Employ	Salary	BONUS
--------	--------	--------	----	-------	--------	-------	--------
1	Whitey	Ford	Orange	555-1001	1	33000	10000
2	Don	Larson	Newark	555-3221	2	18000	2000
3	Sal	Maglie	Nutley	555-6905	3	24000	5000
4	Bob	Turley	Passaic	555-8908	4	22000	7000

Создайте виртуальную таблицу с помощью следующего запроса:

SELECT *

FROM EMPLOYEE, COMPENSATION ;

Вот что вышло:

EmpID	FName	LName	City	Phone	Employ	Salary	Bonus
-------	--------	--------	----	-------	--------	-------	-------
1	Whitey	Ford	Orange	555-1001	1	33000	10000
1	Whitey	Ford	Orange	555-1001	2	18000	2000
1	Whitey	Ford	Orange	555-1001	3	24000	5000
1	Whitey	Ford	Orange	555-1001	4	22000	7000
2	Don	Larson	Newark	555-3221	1	33000	10000
2	Don	Larson	Newark	555-3221	2	18000	2000
2	Don	Larson	Newark	555-3221	3	24000	5000
2	Don	Larson	Newark	555-3221	4	22000	7000
3	Sal	Maglie	Nutley	555-6905	1	33000	10000
3	Sal	Maglie	Nutley	555-6905	2	18000	2000
3	Sal	Maglie	Nutley	555-6905	3	24000	5000
3	Sal	Maglie	Nutley	555-6905	4	22000	7000
4	Bob	Turley	Passaic	555-8908	1	33000	10000
4	Bob	Turley	Passaic	555-8908	2	18000	2000
4	Bob	Turley	Passaic	555-8908	3	24000	5000
4	Bob	Turley	Passaic	555-8908	4	22000	7000

В получившейся таблице, представляющей собой декартово произведение таблиц EMPLOYEE и COMPENSATION, имеется значительный излишек данных. Кроме того, эта таблица не имеет большого смысла. В ней каждая строка из таблицы EMPLOYEE добавляется к каждой строке из таблицы COMPENSATION. Единственными строками в этой таблице, передающими содержательную информацию, являются те, в которых число из столбца EmpID, взятого из таблицы EMPLOYEE, равняется числу из столбца Employ, взятого из таблицы COMPENSATION. В этих строках имя, фамилия и адрес сотрудника объединены с выплатами того же сотрудника.

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

LOCATION (представительство)

Таблица 10.1. LOCATION (представительство)

LOCATION_ID (идентификатор представительства)	CITY (город)
1	Boston
3	Tampa
5	Chicago

DEPT (отдел)

Таблица 10.2. DEPT (отдел)

DEPT_ID (идентификатор отдела)	LOCATION_ID	NAME (название)
21	1	Sales
24	1	Admin
27	5	Repair
29	5	Stock

EMPLOYEE (сотрудник)

Таблица 10.3. EMPLOYEE (сотрудник)

EMP_ID (идентификатор сотрудника)	DEPT_ID	NAME (фамилия)
61	24	Kirk
63	27	McCoy

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

SELECT *

FROM LOCATION L, DEPT D, EMPLOYEE E

WHERE L.LocationlD a D.LocationID

AND D.DeptID = E.DeptID ;

Результат выполнения этого оператора следующий:

1	Boston	24	Admin	61	24	Kirk
5	Chicago	27	Repair	63	27	McCoy

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

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

SELECT *

FROM LOCATION L LEFT OUTER JOIN DEPT D

ON (L.LocationID = D.LocationID)

LEFT OUTER JOIN EMPLOYEE E

ON (D.DeptID = E.DeptID);

В этом объединении данные берутся из трех таблиц. Сначала объединяются таблицы LOCATION и DEPT. Затем получившаяся таблица объединяется с таблицей EMPLOYEE. Даже если строки из таблицы, расположенной левее оператора LEFT OUTER JOIN, и не имеют соответствующих строк в таблице, расположенной правее этого оператора, они все равно входят в результат. Таким образом, при первом объединении в результат войдут все представительства, даже без отделов. А при втором объединении — войдут все отделы, даже без персонала. И вот какой получается результат:

1	Boston	24	1	Admin	61	24	Kirk
5	Chicago	27	5	Repair	63	27	McCoy
3	Tampa	NULL	NULL	NULL	NULL	NULL	NULL
5	Chicago	29	5	Stock	NULL	NULL	NULL
1	Boston	21	1	Sales	NULL	NULL	NULL

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

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

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

SELECT *

FROM LOCATION L LEFT OUTER JOIN DEPT D

ON (L.LocationID = D.LocationID)

LEFT OUTER JOIN EMPLOYEE E

ON (D.DeptID = E.DeptID) ORDER BY L.LocationID, D.DeptID, E.EmpID;

Совет 3
Совет 3

Так как левого внутреннего объединения не существует, то левое внешнее объединение можно назвать покороче —левое объединение (в коде SQL это ключевые слова LEFT JOIN).

Таблица EMPLOYEE

Таблица 10.4. Таблица EMPLOYEE

EmpID	Name (фамилия)
1	Ferguson
2	Frost
3	Toyon

Таблица PROJECTS

Таблица 10.5. Таблица PROJECTS

ProjectName (название проекта)	EmpID
X-63 Structure (устройство ракеты Х-63)	1
X-64 Structure (устройство ракеты Х-64)	1
X-63 Guidance (система управления Х-63)	2
X-64 Guidance (система управления Х-64)	2
X-63 Telemetry (телеметрия Х-63)	3
X-64 Telemetry (телеметрия X-64)	3

Как видно в этих таблицах, Фергюсон работал над проектами устройства ракет Х-63 и Х-64, а также является специалистом по механическому проектированию и расчетам аэродинамической нагрузки.

Теперь предположим, что вы как менеджер хотите увидеть всю информацию обо всех своих сотрудниках. Для этого вы решили применить к таблицам EMPLOYEE, PROJECTS и SKILLS объединение, основанное на равенстве:

SELECT *

FROM EMPLOYEE E, PROJECTS P, SKILLS S

WHERE E.EmpID = P.EmpID

AND E.EmpID = S.EmpID ;

Таблица SKILLS

Таблица 10.6. Таблица SKILLS

Skill (квалификация)	EmpID
Mechanical Design (механическое проектирование)	1
Aerodynamic Loading (расчеты аэродинамической нагрузки)	1
Analog Design (проектирование аналоговых устройств)	2
Gyroscope Design (проектирование гироскопов)	2
Digital Design (проектирование цифровых устройств)	3
R/F Design (проектирование РЛС)	3

Эту же операцию можно представить в виде внутреннего объединения, используя для этого следующий синтаксис:

SELECT *

FROM EMPLOYEE E INNER JOIN PROJECTS P

ON (E.EmpID = P.EmpID)

INNER JOIN SKILLS S

ON (E.EmpID = S.EmpID) ;

Обе формулировки дают одинаковый результат, показанный в табл. 10.7.

Результаты внутреннего объединения

Таблица 10.7. Результаты внутреннего объединения

Е.EmpID	E.Name	P.EmpID	ProjectName	S.EmpID	S.Skill
1	Ferguson	1	X-63 Structure	1	Mechanical Design
1	Ferguson	1	X-63 Structure	1	Aerodynamic Loading
1	Ferguson	1	X-64 Structure	1	Mechanical Design
1	Ferguson	1	X-64 Structure	1	Aerodynamic Loading
2	Frost	2	X-63 Guidance	2	Analog Design
2	Frost	2	X-63 Guidance	2	Gyroscope Design
2	Frost	2	X-64 Guidance	2	Analog Design
2	Frost	2	X-64 Guidance	2	Gyroscope Design
3	Toyon	3	X-63 Telemetry	3	Digital Design
3	Toyon	3	X-63 Telemetry	3	R/F Design
3	Toyon	3	X-64 Telemetry	3	Digital Design
3	Toyon	3	X-64 Telemetry	3	R/F Design

Такое расположение данных не отличается слишком большой ясностью. В каждой строке идентификатор сотрудника появляется три раза, а для каждого сотрудника его проекты и виды квалификации указываются несколько раз. Для ответа на возникшие у вас вопросы внутреннее произведение подходит недостаточно хорошо. Более подходящий результат можно получить, используя объединение-слияние с несколькими операторами SELECT. Начнем с простого объединения-слияния:

SELECT *

FROM EMPLOYEE E

UNION JOIN PROJECTS P UNION JOIN SKILLS S ;

Обратите внимание, что в объединении-слиянии нет предложения ON. Дело в том, что сейчас данные не фильтруются, поэтому предложение ON не нужно. Результат, полученный при выполнении этого оператора, приведен в табл. 10.8.

Результат операции union join

Таблица 10.8. Результат операции union join

E.EmpID	E.Name	P.EmpID	ProjectName	S.EmpID	S.Skill
1	Ferguson	NULL	NULL	NULL	NULL
NULL	NULL	1	X-63 Structure	NULL	NULL
NULL	NULL	1	X-64 Structure	NULL	NULL
NULL	NULL	NULL	NULL	1	Mechanical Design
NULL	NULL	NULL	NULL	1	Aerodynamic Loading
2	Frost	NULL	NULL	NULL	NULL
NULL	NULL	2	X-63 Guidance	NULL	NULL
NULL	NULL	2	X-64 Guidance	NULL	NULL
NULL	NULL	NULL	NULL	2	Analog Design
NULL	NULL	NULL	NULL	2	Gyroscope Design
3	Toyon	NULL	NULL	NULL	NULL
NULL	NULL	3	X-63 Telemetry	NULL	NULL
NULL	NULL	3	X-64 Telemetry	NULL	NULL
NULL	NULL	NULL	NULL	3	Digital Design
NULL	NULL	NULL	NULL	3	R/F Design

Каждая таблица была расширена справа или слева неопределенными (NULL) значениями, после чего проведено объединение в одну таблицу всех строк, получившихся в результате этого расширения. Порядок этих строк произвольный и зависит от используемой реализации. Теперь можно представить полученные данные в более "удобоваримой" форме.

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

SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID,

E.Name, P.ProjectName, S.Skill

FROM EMPLOYEE E UNION JOIN PROJECTS P

UNION JOIN SKILLS S

ORDER BY ID ;

Предложение FROM здесь такое же, как и в предыдущем примере, но теперь три столбца EmpID соединяются с помощью выражения COALESCE в один, который называется ID. Кроме того, результат упорядочивается как раз по этому столбцу ID. Что в итоге получилось, показано в табл. 10.9.

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

Результат применения

Таблица 10.9. Результат применения операции union join вместе с предложением

ID	Name	ProjectName	Skill
1	Ferguson	X-63 Structure	NULL
1	Ferguson	X-64 Structure	NULL
1	Ferguson	NULL	Mechanical Design
1	Ferguson	NULL	Aerodynamic Loading
2	Frost	X-63 Guidance	NULL
2	Frost	X-64 Guidance	NULL
2	Frost	NULL	Analog Design
2	Frost	NULL	Gyroscope Design
3	Toyon	X-63 Telemetry	NULL
3	Toyon	X-64 Telemetry	NULL
3	Toyon	NULL	Digital Design
3	Toyon	NULL	R/F Design

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

SELECT COALESCE (E.EmpID, P.EmpID, S.EmpID) AS ID,

E.Name, COALESCE (P.Type, S.Type) AS Type,

P.ProjectName, S.Skill

FROM EMPLOYEE E

UNION JOIN (SELECT "Project" AS Type, *

FROM PROJECTS) P

UNION JOIN (SELECT "Skill" AS Type, *

FROM SKILLS) S

ORDER BY ID, Type ;

В первом предложении UNION JOIN таблица PROJECTS заменена вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, Р.Туре, с постоянным значением "Project" (проект). И, аналогично, во втором предложении UNION JOIN таблица SKILLS заменена другим вложенным предложением SELECT, которое добавляет к столбцам, взятым из этой таблицы, еще один столбец, S.Type, с постоянным значением "Skill" (квалификация). В каждой строке значением Р.Туре является или NULL, или "Project", а значением S.Type — или NULL, или "Skill".

В списке внешнего предложения SELECT указано выполнение операции COALESCE, при которой два столбца Туре должны стать одним, также имеющим имя Туре. Затем этот новый столбец Туре указывается в предложении ORDER BY, которое таким образом сортирует все строки, чтобы вначале шли строки с проектами, а затем — с квалификационными навыками. Результат показан в табл. 10.10.

Усовершенствованный

Таблица 10.10. Усовершенствованный результат применения операции union join вместе с предложением coalesce

ID	Name	Type	ProjectName	Skill
1	Ferguson	Project	X-63 Structure	NULL
1	Ferguson	Project	X-64 Structure	NULL
1	Ferguson	Skill	NULL	Mechanical Design
1	Ferguson	Skill	NULL	Aerodynamic Loading
2	Frost	Project	X-63 Guidance	NULL
2	Frost	Project	X-64 Guidance	NULL
2	Frost	Skill	NULL	Analog Design
2	Frost	Skill	NULL	Gyroscope Design
3	Toyon	Project	X-63 Telemetry	NULL
3	Toyon	Project	X-64 Telemetry	NULL
3	Toyon	Skill	NULL	Digital Design
3	Toyon	Skill	NULL	R/F Design

Полученная теперь таблица представляет собой отчет — причем очень удобный для чтения — об опыте участия в проектах и о квалификации всех сотрудников, перечисленных в таблице EMPLOYEE.

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

UNION

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

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

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

Скажем, вы создаете базу данных по бейсбольной статистике (см. главу 9). Она состоит из двух таблиц, совместимых для объединения, которые называются AMERICAN (Американская лига) и NATIONAL (Национальная лига). В каждой из них имеются по три столбца, и типы у всех соответствующих столбцов совпадают. На самом деле даже имена у таких столбцов одинаковые, хотя для объединения это условие не является обязательным.
В таблице NATIONAL перечислены имена, фамилии питчеров Национальной лиги и количество тех игр, в которых они все время были на подаче. Эти данные находятся в столбцах FirstName (имя), LastName (фамилия) и CompleteGames (полностью сыгранные игры). Та же информация, но только о питчерах Американской лиги, содержится в таблице AMERICAN. Если объединить таблицы NATIONAL и AMERICAN с помощью оператора UNION, то в результате получится виртуальная таблица со всеми строками из первой и второй таблиц. В этом примере, чтобы показать работу оператора UNION, я вставил в каждую таблицу всего лишь по несколько строк:

SELECT * FROM NATIONAL ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Sal	Maglie	11
Don	Newcombe	9
Sandy	Koufax	13
Don	Drysdale	12
SELECT * FROM AMERICAN ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Whitey	Ford	12
Don	Larson	10
Bob	Turley	8
Allie	Reynolds	14
SELECT * FROM NATIONAL
UNION
SELECT * FROM AMERICAN ;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Allie	Reynolds	14
Bob	Turley	8
Don	Drysdale	12
Don	Larson	10
Don	Newcombe	9
Sal	Maglie	11
Sandy	Koufax	13
Whitey	Ford	12

Оператор UNION DISTINCT работает так же, как и оператор UNION без ключевого слова DISTINCT. В обоих случаях дублирующие строки удаляются из конечной совокупности.

Условное объединение

Условное объединение похоже на объединение, основанное на равенстве, но в проверяемом условии присутствие равенства не обязательно (хотя и не исключается). Проверяемым условием может быть любой правильно составленный предикат. Если условие в проверяемой строке выполняется, то эта строка станет частью полученной таблицы. Синтаксис условного объединения немного отличается от того, который вы видели до сих пор. Это отличие состоит в том, что условие содержится в предложении ON (в), а не в WHERE (где).

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

SELECT *

FROM NATIONAL JOIN AMERICAN

ON NATIONAL.СompleteGames = AMERICAN.CompleteGames ;

Получение нужных данных из множества

В этой главе...

Объединение таблиц, имеющих похожую структуру Объединение таблиц, имеющих разную структуру Получение нужных данных из множества таблиц SQL — это язык запросов, используемый в реляционных базах данных. Почти во всех примерах предыдущих глав рассматривались простые базы данных с одной таблицей. Теперь настало время показать, в чем же состоит реляционность реляционной базы. Вообще говоря, эти базы называются "реляционными" потому, что состоят из множества связанных друг с другом таблиц (а "связанные друг с другом" — это по-английски "related").
Так как данные, хранящиеся в реляционной базе, распределены по множеству таблиц, то запрос обычно извлекает данные из более чем одной таблицы. В SQL:2OO3 имеются операторы, которые объединяют данные из множества исходных таблиц в одну. Это операторы UNION, INTERSECTION и EXCEPT, а также семейство операторов объединения JOIN. Причем каждый из них объединяет данные своим особым способом.

Внешнее объединение

При объединении двух таблиц в первой из них (назовем ее левой) могут быть строки, которых нет во второй (правой) таблице. И наоборот, в правой таблице могут быть строки, которых нет в левой. При выполнении внутреннего объединения этих таблиц все несоответствующие строки из вывода удаляются. Однако при внешнем объединении (outer join) такие строки остаются. На самом деле любое внешнее объединение бывает трех видов: левое, правое и полное.

используется для обозначения всех столбцов,

Внимание

Звездочка ('*') используется для обозначения всех столбцов, имеющихся в таблице. Это сокращенное обозначение работает в большинстве случаев прекрасно, но если реляционные операторы используются во встроенном или модульном коде SQL, то это обозначение может доставить массу неприятностей. Что если в одну из таблиц или сразу во все будут добавлены дополнительные столбцы? Тогда эти таблицы больше не будут совместимыми для объединения, и программа перестанет работать. И даже если во все таблицы, для обеспечения совместимости по операции объединения, будут добавлены одни и те же столбцы, то программа скорее всего не будет готова работать с этими дополнительными данными. Таким образом, лучше явно перечислять нужные столбцы, а не полагаться на сокращение '*'. Но при вводе с консоли "одноразовой" команды SQL звездочка работает прекрасно. Если вдруг запрос не сработает, всегда можно быстро вывести структуру таблицы. Операция UNION обычно убирает любые повторяющиеся строки, которые появляются в результате ее выполнения. В большинстве случаев это желаемый результат. Впрочем, иногда повторяющиеся строки требуется сохранять. В таких случаях используйте UNION ALL (объединение всех).
Обратимся к нашему примеру и предположим, что Боб Тарли по кличке "Буллит" ("пуля") был "продан" в середине сезона из команды "Нью-Йорк Янкиз", входящей в Американскую лигу, в "Бруклин Доджерс" из Национальной лиги. А теперь предположим, что в каждой команде за сезон у этого питчера было по восемь игр, в течение которых он бессменно подавал мяч. Обычный оператор UNION, показанный в примере, отбросит одну из двух строк с данными об этом игроке. И хотя будет казаться, что за сезон он полностью провел на подаче мяча только восемь игр, но ведь на самом деле таких игр — замечательный результат — было 16. Корректные данные можно получить с помощью следующего запроса:
SELECT * FROM NATIONAL
UNION ALL
SELECT * FROM AMERICAN ;
Иногда оператор UNION можно применять и к двум таблицам, которые не являются совместимыми для объединения. Если в таблицу, которая должна получиться, войдут столбцы, имеющиеся в обеих исходных таблицах и являющиеся совместимыми, то можно использовать оператор UNION CORRESPONDING (объединение соответствующих). В этом случае учитываются только указанные столбцы, и только они войдут в получившуюся таблицу.
Полностью отличаются друг от друга данные, которые бейсбольные статистики собирают по питчерам и игрокам в дальней части поля. Однако каждый раз и в том и в другом случае записываются имя (first name), фамилия (last name) игрока, выходы на поле (putouts), ошибки (errors) и доля принятых мячей (fielding percentage). Конечно, по игрокам в дальней части поля не собирают данные о выигрышах/проигрышах (won/lost record), остановленных мячах (saves) или другие сведения, относящиеся только к подаче мяча. Но все равно, чтобы получить некоторую общую информацию об умении играть в защите, можно выполнять оператор UNION, который будет брать данные из двух таблиц — OUTFIELDER (игрок в дальней части поля) и PITCHER (питчер):
SELECT *
FROM OUTFIELDER
UNION CORRESPONDING
(FirstName, LastName, Putouts, Errors, FieldPct)
SELECT *
FROM PITCHER ;
В результате получается таблица, где для каждого питчера или игрока в дальней части поля указаны имя и фамилия, а также количество выходов на поле, ошибок и доля принятых мячей. Здесь, как и при использовании простого оператора UNION, повторяющиеся строки удалены. Таким образом, если игрок некоторое время играл в дальней части поля, а также был питчером, то при выполнении оператора UNION CORRESPONDING часть статистики этого игрока будет потеряна. Чтобы этого не случилось, используйте UNION ALL CORRESPONDING (объединение всех соответствующих).
Совет 1
Совет 1

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

Внутреннее объединение

Объединения — мистические операторы, и для правильного обращения с ними требуется недюжинная внутренняя сила. Возможно, вы даже слышали о внутреннем объединении (inner join), — оно-то и является квинтэссенцией реляционных операций. Я вас разыграл! Во внутренних объединениях вовсе нет ничего таинственного. На самом деле внутренними являются все объединения, о которых уже говорилось в этой главе. Объединение по именам столбцов из последнего примера можно сформулировать и как внутреннее, если воспользоваться следующим синтаксисом:

SELECT *

FROM WHITE INNER JOIN BLACK

USING (Piese, Quant) ;

Результат при этом получится тот же самый.

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