logo
Книга по БД(Вальке А

5.5.4. Логические выражения в условии sql-операторов

Когда выше обсуждались SQL-операторы и, в частности, раздел WHERE (условие), то были перечислены лишь некоторые возможные способы задания условия на выбираемые записи. В частности, были упомянуты операции сравнения (больше, меньше и т.д.) и логические связки (и, или, не). В условии поиска (в разделе WHERE) операторов SELECT, UPDATE и DELETE можно использовать и более интересные логические выражения.

Сопоставление текстовых полей с образцом

Для сравнения текстовых строк с образцом существуют логические операции LIKE и MATCHES. Операция LIKE обладает несколько меньшими возможностями по сравнению с MATCHES, поэтому рассматриваться не будет. Формат операции MATCHES:

<имя поля> MATCHES <образец>

Образец для оператора MATCHES является текстовой строкой и строится по следующим правилам:

Например, по следующему запросу

SELECT person_id FROM persons WHERE lname MATCHES "[Бб]ендер"

будут выбраны номера записей (значения поля person_id) для лиц с фамилиями "Бендер" и "бендер".

Если нам требуется отобрать все записи о товарах из таблицы items, в названии которых присутствует слово "грибы" (например, "Соленые грибы", "Грибы по-французски", "Грибы-мухоморы маринованные"), то такую задачу решает следующий запрос:

SELECT item_id FROM items WHERE name MATCHES "*[Гг]рибы*"

Операцию MATCHES, как и любую другую, можно использовать вместе с другими логическими операциями путем использования логических связок OR/NOT/AND и скобок:

SELECT item_id FROM items WHERE (name MATCHES "теннис") AND price > 20.00

Проверка на вхождение в множество

Но самая, пожалуй, мощная логическая операция, которую можно использовать в условии для операторов SELECT, UPDATE и DELETE ­это операция IN. Эта логическая операция возвращает истину, когда значение слева от слова IN входит в множество значений, указанное справа от слова IN. Множество возможный значений может быть указано явно - через запятую в скобках, а может формроваться другим оператором SELECT:

<выражение> IN (<значение 1>, <значение 2>, ....)

<выражение> IN (<оператор SELECT>)

Например, если мы хотим получить адреса фирм "АО Рога и Копыта" и "ТОО Добро пожаловать", то должны выполнить следующий запрос

SELECT name, address FROM companies WHERE name IN ("АО Рога и Копыта", "ТОО Добро пожаловать")

Очевидно, последний запрос можно сформулировать и с помощью операций "=" и "OR", но во многих случаях использование операции IN более наглядно и компактно.

Если же множество значений формируется динамически, в процессе выполнения запроса, то использование операции IN становится единственно возможным способом. Например, нам надо выполнить запрос "найти названия и адреса всех фирм, поставляющих джинсы". Этот запрос выполняется одним SQL-оператором:

SELECT name, address FROM companies WHERE company_id IN (SELECT company FROM items WHERE name = "джинсы")

В запросах подобного рода во внутренний оператор SELECT можно добавить слово UNIQUE - это позволит повысить скорость обработки запроса:

SELECT name, address FROM companies WHERE company_id IN (SELECT UNIQUE company FROM items WHERE name = "джинсы")

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

SELECT name, address FROM companies WHERE company_id NOT IN (SELECT company FROM items WHERE name = "кеды")

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

DELETE FROM companies WHERE company_id NOT IN (SELECT company FROM items).

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

BEGIN WORK DELETE FROM companies WHERE company_id NOT IN (SELECT company FROM items) DELETE FROM persons WHERE company NOT IN (SELECT company_id FROM companies) COMMIT WORK

Если известно, что оператор SELECT возвращает одну запись, то можно вместо слова IN использовать проверку на равенство. Например, если мы хотим получить список самых дорогих товаров, а именно их названия и цену (цена будет для всех одна - максимальная), то надо выплнить запрос:

SELECT name, price FROM items WHERE price = (SELECT MAX(price) FROM items)

Проверка на существование выборки

С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT-оператор какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует) какая-то информация. Формат логической операции EXISTS:

EXISTS (<оператор SELECT>)

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

SELECT name, address FROM companies WHERE NOT EXISTS (SELECT company FROM items WHERE items.company=companies.company_id)

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

Сравнивая операцию EXISTS с операцией IN, можно заметить. что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

Условия "Существует" и "Для всех"

Всем, кто когда-то более или менее серьезно, занимался математикой, известны кванторы "Существует" и "Для всех". Аналогичные операции введены и в SQL. Эти операции должны использоваться после операции сравнения (больше, меньше, равно и т.д.), а в качестве правого операнда должен задаваться оператор SELECT:

<выражение> <операция сравнения> ALL (<оператор SELECT>) <выражение> <операция сравнения> ANY (<оператор SELECT>)

Соответсвенно, операция ALL возвращает значение "истина", если операция сравнения истинна для каждого значения, возвращаемого оператором SELECT, а операция ANY - хотя бы для одного. Если оператор SELECT не вернул ни одного знчения, то операция ALL возвращает истину, а ANY - ложь. Вместо ключевого слова ANY можно использовать слоово SOME.

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

SELECT company_id, name FROM companies WHERE EXISTS (SELECT company FROM items WHERE companies.company_id = items.company AND items.name = "кеды" AND items.price <= ALL (SELECT price FROM items WHERE name = "кеды") )

Тот же самый запрос мог быть сформулирован и с помощью агрегатной функции MIN:

SELECT company_id, name FROM companies WHERE EXISTS (SELECT company FROM items WHERE companies.company_id = items.company AND items.name = "кеды" AND items.price = (SELECT MIN(price) FROM items WHERE name = "кеды") )