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

1. Простое фильтрование оператором WHERE.

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

Пример запроса для отбора текстовых значений:

SELECT * FROM Sumproduct WHERE Product = "Bikes"

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

Пример запроса для отбора числовых значений:

SELECT > 40000 ORDER BY Amount

В этом примере мы отобрали записи, в которых выручка от реализации составила более 40 тыс. $ и, дополнительно, все записи посортировали по возрастанию по полю Amount.

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

2. Фильтрация по диапазону значений (BETWEEN ).

Для отбора данных, которые лежат в определенном диапазоне, используется оператор BETWEEN . В следующем запросе будут отобраны все значения, лежащие в пределах от 1000 $ в 2000 $ включительно, в поле Amount .

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

Очередность сортировки будет зависеть от порядка расположения полей в запросе. То есть, в нашем случае сначала данные будут посортированы по колонке Amount , а затем по City .

3. Выборка пустых записей (IS NULL ).

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

SELECT * FROM Sumproduct WHERE Amount IS NULL

В примере выше, мы нарочно удалили два значения в поле Amount , чтобы продемонстрировать работу оператора NULL .

4. Расширенное фильтрации (AND, OR ).

Язык SQL не ограничивается фильтрацией по одному условию, для собственных целей вы можете использовать достаточно сложные конструкции для выборки данных одновременно по многим критериям. Для этого в SQL есть дополнительные операторы, которые расширяют возможности оператора WHERE . Такими операторами являются: AND, OR, IN, NOT . Приведем несколько примеров работы данных операторов.

SELECT * FROM Sumproduct WHERE Amount > 40000 AND City = "Toronto"

SELECT * FROM Sumproduct WHERE Month = "April" OR Month = "March"

Давайте объединим операторы AND и OR . Для этого сделаем выборку велосипедов (Bikes ) и коньков (Skates ), которые были проданы в марте (March ).

SELECT * FROM Sumproduct WHERE Product = "Bikes" OR Product = "Skates" AND Month = "March"

Видим, что в нашу выборку попало за много значений (кроме марта (March ), также январь (January ), февраль (February ) и апрель (April )). В чем же причина? А в том, что SQL имеет приоритеты выполнения команд. То есть оператор AND имеет более высокий приоритет, чем оператор OR , поэтому сначала были отобраны записи с коньками, которие проданные в марте, а потом все записи, касающиеся велосипедов.

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

SELECT * FROM Sumproduct WHERE (Product = "Bikes" OR Product = "Skates" ) AND Month = "March"

5. Расширенная фильтрация (оператор IN ).

SELECT * FROM Sumproduct WHERE ID IN (4, 12, 58, 67)

Оператор IN выполняет ту же функцию, что и OR , однако имеет ряд преимуществ:

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

6. Расширенная фильтрация (оператор NOT ).

SELECT * FROM Sumproduct WHERE NOT City IN ("Toronto", "Montreal")

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

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

Начну с простого примера выборки с использованием конструции WHERE в SQL :

SELECT * FROM table WHERE count=5

Вернутся записи, в которых поле "count " имеет значение 5 . Теперь усложним запрос:

SELECT * FROM table WHERE count=5 AND id < 100

Таким образом, вернутся записи, у которых поле "count " имеет значение 5 И поле "id " имеет значение меньше 100 .

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

• ! (отрицание )
• AND (И )
• OR (ИЛИ )
• XOR (ИСКЛЮЧАЮЩЕЕ ИЛИ , иногда ещё называют МОНТАЖНОЕ ИЛИ , но такое название встречается в основном в микропроцессорной литературе)

Пример с использованием нескольких логических операторов:

SELECT * FROM table WHERE !(id <= 120 AND (count=10 OR date > "10/11/1980"))

Вот такой, на первый взгляд, сложный SQL-запрос . Постарайтесь в нём разобраться самостоятельно.

Также конструкция WHERE в SQL может содержать LIKE . LIKE позволяет определить, совпадает ли указанная строка с определённым шаблоном. Чтобы стало немного понятнее, приведу пример:

SELECT * FROM table WHERE text LIKE "%some text%"

Данный SQL-запрос вернёт result_set , содержащий записи, в которых поле "text " имеет такой текст: "some text ". Обратите внимание, что это не проверка на равенство. Текст может быть огромным, но если в нём содержитася строка: "some text ", то LIKE вернёт true .

Давайте напишу, как задаётся шаблон для LIKE :

• % - это то, что мы с Вами использовали. Используется он чаще всего и означает он любую строку любой длины . Фактически, строкой "%some text% " мы говорим, что сначала идёт любая строка любой длины, затем "some text ", а затем вновь любая строка любой длины. Если текст удовлетворяет этому шаблону, то вернуть true , иначе false .
• - это одиночный символ. Чтобы использовать этот шаблон необходимо задавать диапазоны, например, так: "some% ". Данный шаблон будет означать, что сначала идёт 1 символ (любой символ от a до z ), далее "some " и потом любая строка любой длины.
• _ - это любой одиночный символ.
• [^] - это противоположность . Например, можно привести такой пример: "[^az]some_ ". Данный шаблон означает, что вначале идёт любой символ, но только НЕ "a " и НЕ "z ". Далее должна идти строка "some ", а после только один одиночный символ.

Знание и умение использования LIKE очень важно, поверьте моему опыту. Самый простой пример использования LIKE - это поиск по сайту . Ведь контент находится в базе данных, и необходимо вытащить только те записи, в которых содержится строка, заданная в строке поиска. И тут приходит на помощь LIKE . Именно так реализован поиск мною на этом сайте.

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

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

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

Однако, в рамках данной статьи я рассмотрю именно второй вариант, так как у него не возникает проблем с ситуациями вроде CASE WHEN NULL (null в рамках БД не является конкретным значением, поэтому его нельзя использовать в switch подобном операторе). Кроме того, в обыденной жизни чаще всего встречаются задачи именно для второго варианта - вычисление через логические выражения. Поэтому лучше сразу научиться и продолжать использовать именно его.

Обычно, он описывается так (синтаксис может меняться в зависимости от БД):

CASE WHEN bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END

bool_expressionX - это логическое условие

valueX - это то значение, которое будет подставлено, если выполнено соответствующее логическое условие

valueElse - это значение, которое будет подставлено, если ранее не выполнилось ни одно условие.

После такой небольшой справки, перейдем к практике.

Примечание : Кстати, стоит знать, что обычно этот оператор можно применять не только в select, но и в любом месте, где можно использовать поля. Например, при join таблиц или же даже фильтрации (having) при группировке (group by) .

Условный оператор CASE...WHEN...THEN

Чтобы лучше понять условный оператор CASE...WHEN...THEN, представим себе небольшую задачу. Допустим, у вас есть таблица с данными о клиентах и их суммарном количестве покупок. И стоит задача динамически формировать скидку. Можно было бы, конечно, вручную выставлять скидку. Но, она у вас пороговая, причем пороги жестко зашиты (что-то вроде - сумма больше 1000 получи скидку в 2%, а больше 5000 - получи 5%) и вы бы хотели этот процесс автоматизировать, чтобы не пришлось искать ошибки и каждый раз копаться в базе данных (накопил клиент нужную сумму - скидка автоматически появилась).

Возьмем условную таблицу client с тремя клиентами. Для примера их будет вполне достаточно.

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

1. Сумма от 1000 - скидка 2%

2. Сумма от 5000 - скидка 5%

3. Сумма от 10000 - скидка 8%

4. Количество заказов от 10 - скидка 7%

5. Количество заказов от 20 - скидка 8%

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

1. 2% - Сумма от 1000 до 4999 и количество заказов меньше 10.

2. 5% - Сумма от 5000 до 9999 и количество заказов меньше 10.

3. 7% - Количество заказов от 10 до 19 и сумма меньше 10000

4. 8% - Количество от 20 или сумма от 10000

Теперь, осталось только записать. Получим следующий sql-запрос

Выводим имя и остальные данные select name, order_count, total_sum, -- А теперь выводим скидку CASE -- Первое правило 2% WHEN c.total_sum >= 1000 and c.total_sum <= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 and c.total_sum <= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 and c.order_count <= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 or c.order_count >= 20 THEN 5 -- Ни одно правило не выполнено, значит скидка 0. ELSE 0 END as discount from client c

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

Как видите, два клиента получили скидку 8 процентов, а один клиент получил скидку в 2 процента. При этом с каждым заказом процент будет автоматически вычисляться и вам ничего не нужно будет корректировать. К примеру, если у Петя сумма увеличится до 5000, то его скидка автоматически поднимется до 5% (как минимум, так как есть еще число заказов).

В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в T-SQL при сравнении или комбинировании значений типов smallint и int , данные типа smallint неявно преобразуются к типу int . Подробно о явном и неявном преобразовании типов в MS SQL Server можно прочитать в BOL.

Пример. Вывести среднюю цену ПК-блокнотов с предваряющим текстом "средняя цена = ".
Попытка выполнить запрос

в результате получим то, что требовалось:

даст результат 1926. В принципе все правильно, т.к. мы получили в результате то, что просили - ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут следует отметить, что агрегатные функции (за исключением функции COUNT , которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched - целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте - не округленное).
А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,
Результат - 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

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

Аналогичные преобразования типа можно выполнить с помощью функции CONVERT :

Здесь мы преобразуем строковое представление даты к типу datetime , после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано, используется значение по умолчанию (0 или 100). В результате получим

Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.

Оператор CASE

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

SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c ON product.model=c.model WHERE product.type="pc";

В результирующем наборе отсутствующая цена будет заменена NULL-значением:

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	NULL
2112	NULL

Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE :
Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст "Нет в наличии", в противном случае (ELSE ) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим

model	price
1121	850
1232	350
1232	400
1232	600
1233	600
1233	950
1233	980
1260	350
2111	Нет в наличии
2112	Нет в наличии

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

1-я форма
CASE <проверяемое выражение>
WHEN <сравниваемое выражение 1>
THEN <возвращаемое значение 1>
…
WHEN <сравниваемое выражение N>
THEN <возвращаемое значение N>
END

2-я форма
CASE
WHEN <предикат 1>
THEN <возвращаемое значение 1>
…
WHEN <предикат N>
THEN <возвращаемое значение N>
END

Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN . При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN . Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE . При отсутствии ELSE , будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.
В приведенном выше примере была использована вторая форма оператора CASE .
Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора - COALESCE . Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE :

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:

Использование первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере : Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели.

SELECT DISTINCT model, price, CASE price WHEN (SELECT MAX(price) FROM pc) THEN "Самый дорогой" WHEN (SELECT MIN(price) FROM pc) THEN "Самый дешевый" ELSE "Средняя цена" END comment FROM pc ORDER BY price;

В результате выполнения запроса получим

model	price	comment
1232	350	Самый дешевый
1260	350	Самый дешевый
1232	400	Средняя цена
1233	400	Средняя цена
1233	600	Средняя цена
1121	850	Средняя цена
1233	950	Средняя цена
1233	980	Самый дорогой

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

Для выбора данных с базы используется конструкция Select [набор данных] from [имя таблицы]. Как показывает опыт, в 80 % случаев использования запросов на выборку данных нужно применять разнообразные условия — параметры. Для этого в язык, как дополнение к запросу, его усложнение, введено условие SQL-Where.

Способы применения условия Where

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

Конструкция SQL-Where может быть использована для задания условий выборки данных или для проверки вхождения данных в выборку или стороннюю таблицу.

Использование Where для задания параметров выборки

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

Нужно простроить стандартный запрос, с помощью конструкции Select * from.

Определить с помощью ключевой конструкции Join, с каких таблиц будет делаться выбор.

С помощью конструкции Where задать перечень параметров для выборки.

Такого рода запросы довольно просты в построении и не вызывают трудностей даже у новичков.

Использование конструкции для проверки вхождения

Если программисту поставлена задача не только выбрать из таблицы данные по условию, но и проверить их вхождение в одну или несколько таблиц другого плана, конструкция SQL-Where будет незаменима.

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

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

Примеры использования Where

Сейчас будут приведены примеры Для начал представим, что есть две таблицы с данными — Tovar и TovarAmount. В первой имеются названия продаваемого товара, цена, дата продажи и клиент, который приобрел товар. Во второй указано наличие товара, а если точнее, какой есть в наличии.

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

Select * from Tovar

Where T_Date >= ‘12/01/2016’ and T_Date<= ‘’12/07/1016 and T_PriceOut >0

Такого плана запрос вернет список товаров, данных с таблицы, которые были проданы за первые семь дней декабря, на что указывает одно из условий выборки: T_PriceOut >0.

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

Select * from Tovar

Where T_Tovar in (select TA_Tovar where TA_ Amount >0)

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