SQL optimization. Join против In и Exists

WHERE EXISTS

Подзапрос проверяется на наличие одной или нескольких строк. Если хотя бы одна строка удовлетворяет запросу, то возвращается булево значение ИСТИНА. При указании дополнительного ключевого слова NOT булево значение ИСТИНА возвращается, если подзапрос не возвращает соответствующих ему строк.

подзапрос

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

Общие правила

Оператор EXISTS проверяет существование одной или нескольких строк в подзапросе родительского запроса.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM employee WHERE jobs.job_id=employye. job_id);

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

SELECT au_lname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city=publishers.city);

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

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

Оператор EXISTS семантически эквивалентен оператору ANY.

Подзапрос в операторе EXISTS обычно производит один из двух видов поиска. Первый вариант - это использование группового символа - звездочки (например, SELECT * FROM…), и в этом случае вы не извлекаете какой-то конкретный столбец или значение. Звездочка здесь означает «любой столбец». Второй вариант - выбор в подзапросе только одного конкретного столбца (например, SELECT aujd FROM). Некоторые отдельные платформы позволяют выполнять подзапросы по нескольким столбцам (например, SELECT aujd, aujname FROM…). Однако эта возможность достаточно редкая и ее следует избегать в коде, который нужно переносить на другие платформы.

Различия между платформами

Все платформы поддерживают оператор EXISTS в том виде, который мы описали выше.

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

Виды условий поиска:
. Сравнение с результатом вложенного запроса (=, >=)
. Проверка на принадлежность результатам подзапроса (IN)
. Проверка на существование (EXISTS)
. Многократное (количественное) сравнение (ANY, ALL)

Примечания по вложенным запросам:
. Подзапрос должен выбирать только один столбец (за исключением подзапроса с предикатом EXISTS), и тип данных его результата должен соответствовать типу данных значения, указанному в предикате.
. В ряде случаев можно использовать ключевое слово DISTINCT для гарантии получения единственного значения.
. Во вложенном запросе нельзя включать раздел ORDER BY и UNION.
. Подзапрос может находиться и лева и справа от условия поиска.
. В подзапросах могут использоваться функции агрегирования без раздела GROUP BY, которые автоматически выдают специальное значение для любого количества строк, специальный предикат IN, а также выражения, основанные на столбцах.
. По возможности следует вместо подзапросов использовать соединение таблиц JOIN.

Примеры на вложенные запросы :

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

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

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

Примеры на связанные подзапросы:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //возвращает все заказы, величина которых превосходит среднюю величины заказа для данного покупателя

3) Предикат EXISTS

Синтаксическая форма: EXISTS ()

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

Примечания по предикату EXISTS:
. EXISTS – предикат, возвращающий значение TRUE или FALSE, и его можно применять отдельно или вместе с другими булевыми выражениями.
. EXISTS не может использовать функции агрегирования в своем подзапросе.
. В коррелирующих (связанных, зависимых – Correlated) подзапросах предикат EXISTS выполняется для каждой строки внешней таблицы.
. Можно комбинировать предикат EXISTS с соединениями таблиц.

Примеры на предикат EXISTS:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – возвращает всех покупателей, если кто-то из них проживает в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – возвращает номера продавцов, обслуживших только одного покупателя.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Предикаты количественного сравнения

Синтаксическая форма: {=|>|=|} ANY|ALL ()

Эти предикаты используют в качестве аргумента подзапрос, однако, по сравнению с предикатом EXISTS, они применяются в конъюнкции с предикатами отношения (=,>=). В этом смысле они сходны с предикатом IN, но применяются только с подзапросами. Стандарт допускает использовать вместо ANY ключевое слово SOME, однако не все СУБД его поддерживают.

Примечания по предикатам сравнения:
. Предикат ALL принимает значение TRUE, если каждое значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
. Предикат ANY принимает значение TRUE, если хотя бы одно значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
. Если подзапрос не возвращает строк, то ALL автоматически принимает значение TRUE (считается, что условие сравнения выполняется), а для ANY – FALSE.
. Если сравнение не имеет значения TRUE ни для одной строки и есть одна или несколько строк с NULL значением, то ANY возвращает UNKNOWN.
. Если сравнение не имеет значения FALSE ни для одной строки и есть одна или несколько строк с NULL значением, то ALL возвращает UNKNOWN.

Примеры на предикат количественного сравнения:

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE Amt ALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Предикат уникальности

UNIQUE|DISTINCT ()

Предикат служит для проверка уникальности (отсутствия дублей) в выходных данных подзапроса. Причем в предикате UNIQUT строки с NULL значениями считаются уникальными, а в предикате DISTINCT два неопределенных значения считаются равными друг другу.

6) Предикат совпадений

MATCH ()

Предикат MATCH проверяет, будет ли значение строки запроса совпадать со значением любой строки, полученной в результате подзапроса. От предикатов IN И ANY такой подзапрос отличается тем, что позволяет обрабатывать «частичные» (PARTIAL) совпадения, которые могут встречаться среди строк, имеющих часть NULL-значений.

7) Запросы в разделе FROM

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

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//подзапрос возвращает суммарную величину заказов, сделанных каждым покупателем из Лондона.

8) Рекурсивные запросы

WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …

«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу

Select * from a, b, c where a.id = b.id, b.id = c.id

и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично

Select * from a join b on a.id = b.id join c on b.id = c.id

Встроенный оптимизатор причешет быдлозапрос и все будет окей.

Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.

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

Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.

Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.

Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.

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

Select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b.id select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a.id = b.id where b.id is null

Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.

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

Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.

Select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ((sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or exists (select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and))

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

Select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0) where (sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or (sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null) go

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

Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.

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

ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ДИСЦИПЛИНЕ

«БАЗЫ ДАННЫХ»

Лабораторная работа N 7

«Язык баз данных SQL: команды манипуляции данными »

НОВОСИБИРСК 2000

SQL – это сокращенное название языка структурированных запросов (Structured Query Language). Из названия языка понятно, что его основное назначение заключается в формировании запросов на получение информации из базы данных. Команды на выборку данных составляют основу языка манипулирования данными DML - составной части языка SQL. Однако DML состоит не только из команд выборки данных из базы. Существуют также команды модификации данными, управления данными и другие.

В лабораторной работе рассматриваются базовые средства языка DML. В процессе выполнения лабораторной работы мы будем придерживаться стандарта SQL2.

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

Для выполнения лабораторной работы требуется знание основ реляционной модели данных, основ реляционной алгебры и реляционного исчисления, принципов работы с СУБД MS SQL Server.

В результате выполнения лабораторной работы Вы освоите способы манипулирования данными с помощью команд языка SQL, рассмотрите диалект языка, реализованный в СУБД MS SQL Server.

ВВЕДЕНИЕ

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

Первоначально структура синтаксиса SQL была основана (или, по крайней мере, казалась основанной) на реляционном исчислении Кодда. Единственной поддерживаемой операцией реляционной алгебры было объе­динение.

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

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

Простые запросы

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

Простой запрос. Запрос, который обращается только к одной таблице базы данных.

Запрос: Кто работает штукатурами?

WHERE SKILL_TYPE = "Штукатур"

Результат:

Г.Риковер

Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут поме­щаться с разными отступами, а слова внутри фраз могут разделяться произ­вольным числом пробелов. Рассмотрим характеристики каждой фразы.

Select . Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таб­лицы. В нашем примере результирующая таблица состоит из одного столбца (NAME), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приве­дем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисля­ются после команды SELECT через запятую. Например, фраза SELECT WORKER_ID, NAME выдаст в результате таблицу, состоящую из столбцов WORKER_ID и NAME.

Фраза SELECT. Задает столбцы результирующей таблицы.

From . Фраза FROM задает одну или более таблиц, к которым обраща­ется запрос. Все столбцы, перечисленные во фразах SELECT и WHERE, должны существовать в одной из таблиц, перечисленных в команде FROM. В SQL2 эти таблицы могут быть напрямую определены в схеме как базовые таблицы или представления данных, или же они сами могут быть не имею­щими имен таблицами, полученными в результате запросов SQL. В послед­нем случае запрос явно приводится в команде FROM.

Фраза FROM. Задает существующие таблицы, к которым обращается запрос.

Where . Фраза WHERE содержит условие. на основании которого выби­раются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец SKILL_TYPE должен содержать константу "Штукатур", заклю­ченную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE - наиболее изменчивая команда SQL; она может со­держать множество разнообразных условий. Большая часть нашего изложе­ния будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.

Фраза WHERE. Задает условие, на основании которого выбираются строки из заданных таблиц.

Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в ко­манде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовле­творяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается NAME, и все выбранные значения выводятся в качестве результатов запроса.

Запрос: Привести все данные о зданиях офисов.

WHERE TYPE = "Офис"

Результат:

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Ул.Вязов, 123 Офис 2 2

210 Березовая ул. 1011 Офис З 1

111 Осиновая ул. 1213 Офис 4 1

Звездочка (*) в команде SELECT означает «строка целиком». Это удобное сокращение, которым мы будем часто пользоваться.

Запрос: Какова недельная зарплата каждого электрика?

SELECT NAME, "Недельная зарплата = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "Электрик"

Результат:

М.Фарадей Недельная зарплата = 500.00

Х.Колумб Недельная зарплата = 620.00

Этот запрос иллюстрирует употребление и символьных констант (в на­шем примере "Недельная зарплата = "), и вычислений в команде SELECT, Внутри команды SELECT можно производить вычисления, в которых ис­пользуются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходи­мости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним - в порядке возрастания, по другим - в порядке убывания. Первым указывается столбец первичного ключа сортировки.

Символьная константа. Константа, состоящая из букв, цифр и «специальных» символов.

Запрос: У кого почасовая ставка от 10 до 12 долларов?

WHERE HRLY_RATE > = 10 AND HRLY_RATE < - 12

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

Этот запрос иллюстрирует некоторые дополнительные возможности ко­манды WHERE: операторы сравнения и булеву операцию AND (И). Для сравнения столбцов с другими столбцами или с константами могут исполь­зоваться шесть операторов сравнения (=, <> (не равно), <, >, <=, >=). Для создания составных условий или для отрицания условия могут использо­ваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.

Операторы сравнения =, <>, <, >, <=, >=.

Булевы операции AND (И), OR (ИЛИ) и NOT (HE).

Для формулировки этого запроса также можно былоиспользоватьоператор BETWEEN (между):

WHERE HRLY_RATE BETWEEN 10 AND 12

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

Запрос: Перечислить штукатуров, кровельщиков и электриков.

WHERE SKILL_TYPE IN ("Штукатур", "Кровельщик", "Электрик")

Результат:

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1412 К.Немо 13.75 Штукатур 1520

2920 Р.Гаррет 10.00 Кровельщик 2920

1520 Г.Риковер 11.75 Штукатур 1520

Этот запрос поясняет использование оператора сравнения IN (В). Усло­вие WHERE считается истинным, если тип специальности строки располо­жен внутри множества, указанного в скобках, то есть если тип специаль­ности - штукатур, кровельщик или электрик. Мы еще встретимся с опера­тором IN в подзапросах.

Предположим, что мы не можем точно вспомнить написание специаль­ности: «электрик» или «электронщик» или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск не­точного написания в запросе.

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

Запрос: Перечислить работников, чей тип специальности начинается с «Элек».

WHERE SKILL_TYPE LIKE ("Элек%")

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1311 Х.Колумб 15.50 Электрик 1311

В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент за­мещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:

NAME LIKE "__Колумб"

NAME LIKE "__K%"

Условие в первом примере истинно, если NAME состоит из двух симво­лов, за которыми следует "Колумб". В таблице WORKER все имена начина­ются с первого инициала и точки. Таким образом, при помощи этого усло­вия мы. найдем всех работников по фамилии «Колумб». Условие второго примера позволяет найти всех работников, чьи фамилии начинаются на бу­кву «К».

Запрос: Найти все работы, которые начинаются в течение ближайших двух недель.

WHERE START _DATE BETWEEN CURRENT_DATE AND

Результат: (Предположим, что текущая дата CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE - это функция, всегда возвращающая значение сегодняшней даты. Выражение

CURRENT_DATE + INTERVAL "14" DAY

прибавляет двухнедельный промежуток к текущей дате. Таким образом, ASSIGNMENT выбирается (в предположении, что сегодня 10.10) в том слу­чае, если в ней значение столбца START_DATE лежит между 10.10 и 24.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS (ЧИСЛО НЕДЕЛЬ)). Мы можем это сделать так:

CURRENT_DATE + INTERVAL "7" DAY * NUM_WEEKS

2. Многотабличные запросы

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

Запрос:

Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:

SELECT SKILL_TYPE

FROM WORKER, ASSIGNMENT

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

AND BLDG_ID = 435

Что здесь происходит? Мы должны рассмотреть два этапа обработки сис­темой данного запроса.

1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом слу­чае, поскольку в команде указаны две таблицы, система создает декар­тово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таб­лиц, в которой каждая строка одной таблицы спарена с каждой стро­кой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m - число строк таблицы WORKER; а n - число строк таблицы ASSIGNMENT. По­скольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произве­дение всех таблиц, указанных в команде.

Декартово произведение . Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.

2. После создания гигантской реляционной таблицы система, как и пре­жде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.

Фраза WHERE в нашем запросе содержит два условия:

1. WORKER. WORKER_ID = ASSIGNMENT.WORKER_ID

2. BLDG_ID = 435

Первое из этих условий - условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с име­нем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.

Первое условие означает, что в любой выбранной строке значение столбца WORKER_ID из таблицы WORKER должно совпадать со значением столбца WORKER_ID из таблицы ASSIGNMENT. В действительности мы со­единяем две таблицы по WORKER_ID. Все строки, в которых значения этих двух столбцов не равны, исключаются из таблицы произведения. В точности то же самое происходит при выполнении операции естественного соединения реляционной алгебры. (Однако, некоторое отличие от естественного соедине­ния все же есть: язык SQL автоматически не удаляет лишний столбец WORKER_ID). Полное соединение этих двух таблиц с дополнительным усло­вием BLDG_ID = 435 представлено на рис. 1. Применение команды SELECT даст, в конце концов, следующий результат запроса:

SKILL TYPE

Штукатур

Кровельщик

Электрик

Рис. 1. Соединение таблиц WORKER и ASSIGNMENT

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

Запрос: Перечислить работников, указав имена их менеджеров.

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

WHERE B.WORKER_ID = A.SUPV_ID

Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним - это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).

Рис. 2. Соединение двух копий таблицы WORKER

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

А.NAMEВ.NAME

М.Фарадей Х.Колумб

К.Немо Г.Риковер Р.Гаррет Р.Гаррет

П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон

Псевдоним. Альтернативное имя, данное таблице.

A.WORKER_NAME представляет работника, a B.WORKER_NAME пред­ставляет менеджера. Обратите внимание, что некоторые работники - сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID - SUPV_ID.

В SQL можно за один раз связать более двух таблиц:

Запрос

SELECT WORKER_NAME

FROM WORKER, ASSIGNMENT, BUILDING

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

TYPE = "Офис"

Результат:

М.Фарадей

Г.Риковер

Дж.Барристер

Обратите внимание, что если имя столбца (например, WORKER_ID или BLDG_ID) встречается более, чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.

Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие

TYPE = "Офис"

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

3. Подзапросы

Подзапрос. Запрос внутри запроса

Подзапрос может помещаться в команду WHERE запроса, в результате чего возможности команды WHERE расширяются. Рассмотрим пример.

Запрос: Каковы специальности рабочих, назначенных на здание 435?

SELECT SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Подзапрос в этом примере

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом . Подзапрос приводит к созданию следующего множе­ства ИД (идентификаторов) работников:

WORKER ID

Внешний запрос. Главный запрос, в котором содержатся все подзапросы.

Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таб­лицы WORKER в соответствии с условием WHERE. Если WORKER_ID строки лежит в (IN) множестве, созданном подзапросом, то SKILL_TYPE строки выбирается и выводится в результирующей таблице:

SKILL TYPE

Штукатур

Кровельщик

Электрик

Очень важно, что фраза SELECT подзапроса содержит WORKER_ID и только WORKER_ID. В противном случае фраза WHERE внешнего запроса, означающая, что WORKER_ID лежит в множестве ИД работников, не имела бы смысла.

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

Некоррелированный подзапрос. Подзапрос, значение которого не зависит ни от какого внешнего запроса.

Приведем пример подзапроса внутри подзапроса.

Запрос : Перечислить работников, назначенных на здания офисов.

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

SELECT WORKER_MAME

WHERE WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID IN

WHERE TYPE = "Офис"))

Результат:

М.Фарадей

Г.Риковер

Дж.Барристер

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

Выполнение запроса происходит в порядке изнутри наружу. То есть са­мый внутренний запрос (или «самый нижний») выполняется первым, затем выполняется содержащий его подзапрос, а затем внешний запрос.

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

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

Запрос: Перечислить работников, чьи почасовые ставки выше, чем ставки их менеджеров.

SELECT WORKER_NAME

WHERE A.HRLY_RATE >

(SELECT B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Результат:

Логические этапы выполнения этого запроса таковы:

1. Система создает две копии таблицы WORKER: копию А и копию В. В соответствии с тем, как мы их определили, А относится к работнику, В - к менеджеру.

2. Затем система рассматривает каждую строку А. Данная строка выбира­ется, если она удовлетворяет условию WHERE. Это условие означает, что строка будет выбрана, если величина HRLY_RATE в ней больше, чем HRLY_RATE, порожденная подзапросом.

3. Подзапрос выбирает величину HRLY_RATE из строки В, WORKER_ID которой равен SUPV_ID строки А, в данный момент рассматриваемой главным запросом. Это HRLY_RATE менеджера.

Обратите внимание, что поскольку A.HRLY_RATE может сравниваться только с одной величиной, подзапрос должен выдавать только одну вели­чину. Эта величина меняется в зависимости от того, какая строка А рас­сматривается. Таким образом, подзапрос коррелирует с главным запросом. Мы встретимся с другими примерами коррелированных подзапросов позже, когда будем изучать встроенные функции.

Операторы EXISTS и NOT EXISTS

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

SELECT WORKER_ID

WHERE BLDG_ID NOT 435

К сожалению, это неверная формулировка решения. Запрос просто вы­даст нам ИД работников, работающих на других зданиях. Очевидно, что не­которые из них могут также быть назначены и на здание 435.

В правильно сформулированном решении используется оператор NOT EXISTS (не существует):

SELECT WORKER_ID

WHERE NOT EXISTS

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

Результат :

WORKER_ID

Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапро­сом. EXISTS принимает значение «истина», если множество, порожденное подзапросом, не пусто. Если порожденное подзапросом множество пусто, то EXISTS принимает значение «ложь». Оператор NOT EXISTS, естественно, работает в точности наоборот. Он истинен, если результат подзапроса пуст, и ложен в противном случае.

Оператор EXISTS . Принимает значение «истина», если результирующее множество не пусто.

Оператор NOT EXISTS . Принимает значение «истина», если результирующее множество пусто.

В этом примере мы воспользовались оператором NOT EXISTS. Подзапрос выбирает все такие строки таблицы ASSIGNMENT, в которых WORKER_ID имеет то же значение, что и в строке, рассматриваемой главным запросом, а BLDG_ID равен 435. Если это множество пусто, тогда строка работника, рас­сматриваемая главным запросом, выбирается, поскольку это означает, что данный работник не работает на здании 435.

В приведенном нами решении использовав коррелированный подзапрос. Если мы воспользуемся вместо NOT EXISTS оператором IN, то можем обой­тись некоррелированным подзапросом:

SELECT WORKER_ID

WHERE WORKER_ID NOT IN

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Это решение проще, чем решение с оператором NOT EXISTS. Возникает естественный вопрос, зачем нам вообще нужны EXISTS и NOT EXISTS. От­вет состоит в том, что NOT EXISTS является единственным средством реше­ния запросов, содержащих в условии слово «каждый». Такие запросы решаются в реляционной алгебре с помощью операции деления, а в реляционном исчислении - с помощью квантора всеобщности. Приведем пример запроса, в условии которого есть слово «каждый»:

Запрос: Перечислить работников, назначенных на каждое здание.

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

Запрос: Перечислить таких работников, для которых не существует здания, на которое они не назначены.

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

Теперь мы хотим сформулировать решение на SQL. Для того чтобы уп­ростить понимание окончательного решения, мы сначала дадим решение предварительной задачи: задачи идентификации всех зданий, на которые гипотетический работник, «1234» не назначен.

(I) SELECT BLDG_ID

WHERE NOT EXISTS

ASSIGNMENT.WORKER_ID = 1234)

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

(II) SELECT WORKER_ID

WHERE NOT EXISTS

WHERE NOT EXISTS

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Результат:

WORKER ID

Обратите внимание, что подзапрос, начинающийся с четвертой строки запроса (II), идентичен запросу (I), в котором «1234» заменено WORKER.WORKER_ID. Запрос (II) можно прочитать следующим образом:

Выбрать WORKER_ID из WORKER, если не существует здания, на которое WORKER_ID не назначен.

Это соответствует условиям исходного запроса.

Мы видим, что оператор NOT EXISTS может использоваться для форму­лировки тех запросов, при решении которых в реляционной алгебре требо­валась операция деления, а в реляционном исчислении - квантор всеобщ­ности. С точки зрения простоты использования оператор NOT EXISTS не дает никаких особых преимуществ, то есть в запросах SQL, в ко­торых дважды используется NOT EXISTS, ничуть не проще разобраться, чем в решениях реляционной алгебры с операцией деления или решениях реля­ционного исчисления с квантором всеобщности. Для создания языковых конструкций, позволяющих более естественным образом решать подобные запросы, потребуются дополнительные исследования.

Встроенные функции

Рассмотрим вопросы такого типа:

Каковы максимальная и минимальная почасовые ставки? Каково сред­нее число дней работы служащих на здании 435? Каково общее число дней, отведенных на штукатурные работы на здании 312? Сколько всего разных специальностей?

Для ответа на эти вопросы требуются статистических функции, которые рассматривают множество строк таблицы и выдают одно значение. В SQL есть пять таких функций, называемых встроенными функциями или функ­циями множества. Это функции SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум) и MIN (минимум).

Встроенная функция (функция множества) . Статистическая функция, оперирующая множеством строк: SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум), MIN (минимум).

Запрос: Каковы максимальная и минимальная почасовые ставки?

SELECT MAX(HRLY_RATE), MIN(HRLY_RATE)

Результат: 17.40, 8.20

Функции MAX и MIN оперируют одним столбцом таблицы. Они выби­рают максимальное или минимальное значение, соответственно, из этого столбца. Формулировка нашего запроса не содержит команды WHERE. Для большинства запросов это может быть не так, как показывает наш следую­щий пример.

Запрос: Каково среднее число дней работы служащих на здании 435?

SELECT AVG(NUM_DAYS)

WHERE BLDG_ID =435

Результат : 12.33

Запрос: Каково общее число дней, отведенных на штукатурные работы на здании 312?

SELECT SUM(NUM_DAYS)

FROM ASSIGNMENT, WORKER

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = "Штукатур" AND

Результат : 27

В решении используется соединение таблиц ASSIGNMENT и WORKER. Это необходимо, так как SKILL_TYPE находится в таблице WORKER, a BLDG_ID - в таблице ASSIGNMENT.

Запрос: Сколько всего разных специальностей?

SELECT COUNT (DISTINCT SKILL_TYPE)

Результат : 4

Поскольку одна и та же специальность может повторяться в нескольких разных строках, в этом запросе необходимо использовать ключевое слово DISTINCT (различный), чтобы система не сосчитала один и тот же тип спе­циальности более одного раза. Оператор DISTINCT может использоваться с любой из встроенных функций, хотя, разумеется, с функциями МАХ и MIN он избыточен.

DISTINCT . Оператор, исключающий повторяющиеся строки.

Функции SUM и AVG должны использоваться только с числовыми столбцами. Другие функции могут использоваться и с числовыми, и с сим­вольными данными. Все функции, кроме COUNT, можно использовать с вы­числяемыми выражениями. Например:

Запрос: Какова средняя недельная зарплата?

SELECT AVG (40 * HRLY_RATE)

Результат : 509.14

COUNT может ссылаться на строку целиком, а не на отдельныйстолбец:

Запрос : Сколько зданий имеют уровень качества З?

SELECT COUNT (*)

FROM BUILDING WHERE

Результат : 3

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

Фразы GROUP BY и HAVING

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

Запрос: Для каждого менеджера выяснить максимальную почасовую ставку среди его подчиненных.

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

GROUP BY SUPV_ID

Результат :

SUPV_IDMAX(HRLY RATE )

При обработке этого запроса система сначала разбивает строки таблицы WORKER на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает SUPV_ID. Затем фраза SELECT применяется к каждой группе. Поскольку в данной группе только одно значение SUPV_ID, то никакой неопределенности SUPV_ID в группе нет. Для каждой группы, фраза SELECT выводит SUPV_ID, a также вычисляет и выводит значение MAX(HRLY_RATE). Результат представлен выше.

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

Фраза GROUP BY . Означает, что строки должны быть разбиты на группы с общими значениями указанного столбца (столбцов).

Фраза GROUP BY позволяет выполнять определенные сложные вычис­ления. Например, нам может понадобиться выяснить среднее значение этих максимальных ставок. Однако, вычисления со встроенными функциями ог­раничены в том смысле, что не разрешается использование встроенных функции внутри других встроенных функций. Таким образом, выражение типа

AVG(MAX(HRLY_RATE))

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

С командой GROUP BY можно использовать команду WHERE:

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1.

SELECT TYPE, AVG(QLTY_LEVEL)

WHERE STATUS = 1

Результат :

TYPEAVG(QLTY_LEVEL)

Магазин 1

Жилой дом 3

Фраза WHERE выполняется перед командой GROUP BY. Таким образом, ни одна группа не может содержать строку, в которой статус отличен от 1. Строки статуса 1 группируются по значению TYPE, а затем к каждой группе применяется фраза SELECT.

Фраза HAVING . Накладывает условия на группы.

Мы также можем применять условия и к группам, созданным фразой GROUP BY. Это делается при помощи фразы HAVING. Предположим, на­пример, что мы решили конкретизировать один из предыдущих запросов:

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

Мы можем отразить это условие соответствующей командой HAVING:

SELECT SUPV_ID, MAX(HRLY_RATE)

FROM WORKER GROUP BY SUPV_ID

HAVING COUNT(*) > 1

Результат :

SUPV_ID MAX(HRLY_RATE)

Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.

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

Запрос : Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1. Рассматривать только те типы зданий, максимальный уровень качества которых не превышает 3.

SELECT TYPE, AVG (QLTY_JLEVEL)

WHERE STATUS = 1

HAVING MAX(QLTY_LEVEL) <= 3

Результат :

TYPE AVG(QLTY_LEVEL)

Магазин 1

Жилой дом 3

Обратите внимание, что начиная с фразы FROM фразы выполняются по порядку, а затем применяется фраза SELECT. Так, к таблице BUILDING применяется фраза WHERE, и все строки, в которых STATUS отличен от 1, удаляются. Оставшиеся строки группируются по TYPE; все строки с одина­ковым значением TYPE оказываются в одной группе. Таким образом, созда­ется нескрлько групп, по одной для каждого значения TYPE. Затем к каж­дой группе применяется фраза HAVING, и те группы, в которых макси­мальное значение уровня качества превышает 3, удаляются. Наконец, к ос­тавшимся группам применяется фраза SELECT.

7. Встроенные функции и подзапросы

Встроенные функции могут использоваться только во фразе SELECT или в команде HAVING. Однако фраза SELECT, содержащая встроенную функ­цию, может быть частью подзапроса. Рассмотрим пример такого подзапроса:

Запрос: У кого из работников почасовая ставка выше среднего?

SELECT WORKER_NAME

WHERE HRLY_RATE >

(SELECT AVG(HRLY_RATE)

Результат:

Х. Колумб

Обратите внимание, что подзапрос не коррелирует с главным запросом. Подзапрос выдает ровно одно значение - среднюю почасовую ставку. Глав­ный запрос выбирает работника только в том случае, если его ставка больше вычисленной средней.

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

Запрос: У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же менеджера?

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

SELECT A. WORKER_NAME

В этом учебном материале вы узнаете, как использовать SQL условие EXISTS с синтаксисом и примерами.

Описание

SQL условие EXISTS используется в сочетании с подзапросом и считается выполненным, если подзапрос возвращает хотя бы одну строку. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис условия EXISTS в SQL:

Параметры или аргументы

subquery подзапрос является оператором SELECT. Если subquery возвращает хотя бы одну запись в своем наборе результатов, предложение EXISTS оценивается как true и условие EXISTS будет выполнено. Если subquery не возвращает никаких записей, предложение EXISTS оценивается как false, и условие EXISTS не будет выполнено.

Примечание

Операторы SQL, использующие условие EXISTS, очень неэффективны, поскольку подзапрос повторно запускается для КАЖДОЙ строки в таблице внешнего запроса. Есть более эффективные способы написания большинства запросов, которые не используют условие EXISTS.

Пример — использование условия EXISTS с оператором SELECT

Давайте начнем с примера, который показывает, как использовать условие EXISTS с оператором SELECT.

В этом примере у нас есть таблица customers со следующими данными:

Теперь давайте найдем все записи из таблицы customers , где есть хотя бы одна запись в таблице orders с тем же customer_id . Выполните следующий SELECT запрос:

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL

В этом примере в таблице customers есть 4 записи, у которых значение customer_id имеется в таблице orders .

Пример — использование условия EXISTS с оператором UPDATE

Давайте рассмотрим пример, в котором используется условие EXISTS в операторе UPDATE.
В этом примере у нас есть таблица products со следующими данными:

Теперь давайте обновим таблицу summary_data значениями из таблицы products . Введите следующий SQL оператор:

PgSQL

Будет обновлено 5 записей. Снова выберите данные из таблицы summary_data :

PgSQL

SELECT * FROM summary_data;

В этом примере будет обновлено поле current_category в таблице summary_data данными category_id из таблицы products , где значения product_id совпадают. Первые 5 записей в таблице summary_data были обновлены.

Подсказка : Если бы мы не включили условие EXISTS, запрос UPDATE обновил бы поле current_category на NULL в 6-й строке таблицы summary_data (поскольку таблица products не имеет записи, где product_id = 8).

Пример — использование условия EXISTS с оператором DELETE

Давайте посмотрим на пример, который использует условие EXISTS в опертаоре DELETE.

В этом примере у нас есть таблица customer со следующими данными:

Введите следующий оператор DELETE:

PgSQL

Будет удалена 1 запись. Выберите данные из таблицы orders еще раз:

PgSQL

SELECT * FROM orders;

SELECT *FROM orders;

Вот результаты, которые вы должны получить.