Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

Учебник SQL

Урок 5. Расширенная фильтрация данных

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

Комбинирование предложений WHERE

Все предложения WHERE, представленные в уроке 4, "Фильтрация данных", отфильтровывают данные с использованием одного критерия. Чтобы увеличить уровень контроля над фильтром в SQL, можно использовать несколько предложений WHERE. Эти предложения допустимо использовать двумя способами: в виде предложений AND или OR.

Оператор

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

Использование ключевого слова AND

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

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

Посредством данного оператора извлекается название продукции и цена для всех товаров, изготовленных производителем DLL01, с ценой $4 и меньше. Предложение WHERE в операторе SELECT состоит из двух предложений, а ключевое слово AND используется для их объединения. Ключевое слово AND указывает системе управления базой данных возвращать только те строки, которые удовлетворяют всем перечисленным предложениям. Если продукт изготовлен производителем DLL01, но стоит больше $4, он не попадет в результаты. Аналогично, товары, которые стоят меньше $4 и изготовлены отличными от указанного производителями, также не будут выведены. Данные, выданные в результате выполнения этой SQL-инструкции, будут выглядеть так:

Вывод:

Prod_id  prod_price  prod_name
----------------------------------------
BNBG02   3.4900      Bird bean bag toy
BNBG01   3.4900      Fish bean bag toy
BNBG03   3.4900      Rabbit bean bag toy

AND

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

Использование ключевого слова OR

Действие ключевого слова OR противоположно действию ключевого слова AND. Ключевое слово OR указывает системе управления базой данных выбирать только те строки, которые удовлетворяют хотя бы одному предложению. На самом деле в большинстве лучших СУБД второе предложение даже не рассматривается в предложении OR WHERE, если удовлетворено первое предложение. (Если первое предложение выполнено, строка будет выведена независимо от второго предложения.)

Взгляните на следующий оператор SELECT:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'

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

Вывод:

prod_name            prod_price
-------------------------------
Fish bean bag toy    3.4900
Bird bean bag toy    3.4900
Rabbit bean bag toy  3.4900
8 inch teddy bear    5.9900
12 inch teddy bear   8.9900
18 inch teddy bear   11.9900
Raggedy Ann          4.9900

OR

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

Порядок обработки

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

Однако при комбинировании ключевых слов AND и OR возникает одна проблема. Рассмотрим следующий пример. Необходимо вывести список всех изготовленных производителями DLL01 и BRS01 товаров, цена которых $10 и выше. В следующей инструкции SELECT используется комбинация ключевых слов AND и OR для формулирования предложения WHERE:

SELECT prod_name, prod_price 
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' 
AND prod_price >= 10;

Вывод:

prod_name            prod_price
----------------------------
Fish bean bag toy    3.4900
Bird bean bag toy    3.4900
Rabbit bean bag toy  3.4900
18 inch teddy bear   11.9900
Raggedy Ann          4.9900

Взгляните на результат. В четырех возвращенных строках значатся цены ниже $10 — очевидно, строки не были отфильтрованы так, как надо. Что же произошло? Причина в порядке обработки. SQL (как и большинство других языков) вначале обрабатывает логические операторы AND, а потом уже логические операторы OR. Когда SQL "видит" такое предложение WHERE, он его считывает так: выбрать все продукты, которые стоят $10 и больше, изготовленные производителем BRS01, и все продукты, изготовленные производителем DLL01 независимо от их цены. Другими словами, так как приоритет у логического оператора AND выше, были объединены "не те" операторы.

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

SELECT prod_name, prod_price 
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS0l') 
AND prod_price >= 10;

Вывод:

prod_name           prod_price
------------------------------
18 inch teddy bear  11.9900

Единственным отличием между предыдущим выражением и этим являются скобки, в которые заключены первые два предложения оператора WHERE. Поскольку скобки имеют еще больший приоритет, чем логические операторы AND и OR, СУБД вначале обрабатывает условие OR внутри скобок. Соответственно, SQL-оператор будет пониматься так: выбрать все продукты, изготовленные либо производителем DLL01, либо производителем BRS01, которые стоят $10 и больше, а это именно то, что нужно.

Использование скобок в предложениях WHERE

Когда бы вы ни использовали предложения WHERE С ключевыми словами AND И OR, всегда вставляйте скобки, чтобы точно сгруппировать логические операторы. Не полагайтесь на порядок обработки по умолчанию, даже если он подразумевает необходимый вам результат. Нет никаких недостатков в использовании скобок, кроме того, вы всегда будете застрахованы от неопределенностей.

Использование ключевого слова IN

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

SELECT prod_name, prod_price 
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01') 
ORDER BY prod_name;

Вывод:

prod_name            prod_price
-------------------------------
12 inch teddy bear   8.9900
18 inch teddy bear   11.9900
8 inch teddy bear    5.9900
Bird bean bag toy    3.4900
Fish bean bag toy    3.4900
Rabbit bean bag toy  3.4900
Raggedy Ann          4.9900

Инструкция SELECT осуществляет выборку всех товаров, изготовленных производителями DLL01 и BRS01. После ключевого слова IN следует список значений через запятую, а весь список заключен в скобки.

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

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;

Вывод:

prod_name            prod_price
-------------------------------
12 inch teddy bear   8.9900
18 inch teddy bear   11.9900
8 inch teddy bear    5.9900
Bird bean bag toy    3.4900
Fish bean bag toy    3.4900
Rabbit bean bag toy  3.4900
Raggedy Ann          4.9900

Зачем же нужно ключевое слово IN? Его преимущества следующие.

  • При работе с длинными списками необходимых значений синтаксис логического оператора IN гораздо легче читать.
  • При использовании ключевого слова IN гораздо легче управлять порядком обработки (так как используется меньшее количество операторов).
  • Логические операторы IN почти всегда быстрее обрабатываются, чем списки логических операторов OR.
  • Самое большое преимущество логического оператора IN в том, что в данном операторе может содержаться еще одна инструкция SELECT, а это позволяет создавать очень динамичные предложения WHERE. Более подробно вы об этом узнаете в уроке 11, "Использование подзапросов".

IN

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

Использование ключевого слова NOT

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

NOT

Ключевое слово, применяемое в предложении WHERE для отрицания какого-то условия.

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

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

Вывод:

prod_name
------------------
12 inch teddy bear 
18 inch teddy bear 
8 inch teddy bear 
King doll 
Queen doll

Здесь логический оператор NOT отрицает предложение, следующее за ним. Поэтому СУБД извлекает не те значения vend_id, которые совпадают с DLL01, а все остальные.

Предыдущий запрос можно было также выполнить при помощи операции <>:

SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

Вывод:

prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll

Зачем же использовать логический оператор NOT? Конечно, для таких простых предложений WHERE, какие мы здесь рассматриваем, этот оператор не обязателен. Он полезен в более сложных предложениях. Например, для нахождения всех строк, которые не совпадают со списком критериев, можно использовать логический оператор NOT в паре с ключевым словом IN.

NOT в MySQL

Форма логического оператора NOT, который здесь описывается, не поддерживается в СУБД MySQL. В MySQL NOT используется только для отрицания вхождений EXISTS (т.е. как NOT EXISTS).

Резюме

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