It is difficult to explain the syntax for the SQL Server WHERE clause, so let"s look at some examples.

We"ll start by looking at how to use the WHERE clause with only a single condition.

SELECT * FROM employees WHERE first_name = "Jane";

In this SQL Server WHERE clause example, we"ve used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is "Jane". Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Example - Using AND condition

Let"s look at how to use the WHERE clause with the AND condition.

SELECT * FROM employees WHERE last_name = "Anderson" AND employee_id >= 3000;

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees that have a last_name of "Anderson" and the employee_id is greater than or equal to 3000.

Example - Using OR condition

Let"s look at how to use the WHERE clause with the OR condition.

SELECT employee_id, last_name, first_name FROM employees WHERE last_name = "Johnson" OR first_name = "Danielle";

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition , it uses the OR condition . In this case, this SELECT statement would return all employee_id , last_name , and first_name values from the employees table where the last_name is "Johnson" or the first_name is "Danielle".

Example - Combining AND & OR conditions

Let"s look at how to use the WHERE clause when we combine the AND & OR conditions in a single SQL statement.

SELECT * FROM employees WHERE (state = "California" AND last_name = "Smith") OR (employee_id = 82);

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition . This example would return all employees that reside in the state of "California" and whose last_name is "Smith" as well as all employees whose employee_id is equal to 82.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

Let"s look at how to use the WHERE clause when we join multiple tables together.

SELECT employees.employee_id, contacts.last_name FROM employees INNER JOIN contacts ON employees.employee_id = contacts.contact_id WHERE employees.first_name = "Sarah";

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is "Sarah". And the employee s and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.

При выборке записей из таблицы практически всегда требуется задавать определённые условия, по которым мы определяем, какие записи нам нужны, а какие - нет. И именно эти условия можно задавать с помощью конструкции 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 . Именно так реализован поиск мною на этом сайте.

В реализациях языка 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	Самый дорогой

Поиск объектов во всех базах данных сервера

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

К счастью, есть процедура sp_MSforeachdb , которая позволяет написать такой скрипт довольно компактно:

DECLARE @name AS SYSNAME,
@strSQL AS VARCHAR (MAX )
SET @name = "Hierarchy"

SET @strSQL = "
IF EXISTS(SELECT * FROM ?..sysobjects WHERE name LIKE " "%" +@name+"%" ")
BEGIN
SELECT " "?" " db, name FROM ?..sysobjects WHERE name LIKE " "%" +@name+"%" "
END
"
CREATE TABLE #result (
db SYSNAME,
name SYSNAME,
PRIMARY KEY (db,name))

INSERT #result EXEC sp_MSforeachdb @strSQL
SELECT * FROM #result

DROP TABLE #result

Здесь всё достаточно просто:

1. Задаём часть названия, по которому будем искать (в данном случае - Hierarchy).
2. Собираем динамический SQL, который ищет по sysobjects (для некоторых старых версий MS SQL можно вместо VARCHAR(MAX) использовать VARCHAR(2000), например).
   При этом символ "?" заменится на название БД.
3. С помощью конструкции INSERT ... EXEC сохраняем все результаты во временную таблицу, чтобы отобразить, в итоге, единый результат.

Поиск подстроки в текстах хранимых процедур

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

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

SET @strSQL = "
SELECT DISTINCT " "?" " as db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s. LIKE " "%" +@name+"%" ""

Обратите внимание на DISTINCT - может пригодиться, потому что иногда для одного объекта бывает несколько записей в syscomments.

Поиск подстроки во всех строковых полях всех таблиц

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

Для этого случая помощник в виде процедуры от MS не нашёлся, поэтому пришлось использовать курсоры:

declare @pattern as nvarchar(MAX )
set @pattern = N"%Test%"
set nocount on
declare @sql as nvarchar(MAX )
declare @table as sysname
declare tables cursor local static read_only for select name from sys.tables t where t.is_ms_shipped = 0
open tables

create table #results(name sysname not null , value nvarchar(MAX ) not null )
while @@FETCH_STATUS = 0
begin
set @sql = ""
select @sql = @sql + "
insert into #results select " "" + @table + "." + name + "" " as name, [" + name + "] from [" + @table + "] where [" + name + "] like " "" + @pattern + "" ""
from sys.columns c where c.object_id = OBJECT_ID(@table ) and c.system_type_id in
(select system_type_id from sys.types where collation_name is not null )
exec (@sql )
fetch next from tables into @table
end
select * from #results
close tables
drop table #results

Помимо курсоров принципы те же. Есть пара нюансов:

• Ищем таблицы без флага is_ms_shipped, чтобы просматривать только таблицы, созданные на этапе разработки.
• Для отбора строковых столбцов вместо списка типов (который поддерживать утомительно) используется фильтр "collation_name is not null".

Резюме

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

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

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях,