Transact-SQL - инструкция SELECT: базовые возможности

108

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Инструкция SELECT: базовые возможности

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

В языке Transact-SQL имеется одна основная инструкция для выборки информации из базы данных - инструкция SELECT. Эта инструкция позволяет извлекать информацию из одной или нескольких таблиц базы данных и даже из нескольких баз данных. Результаты выполнения инструкции SELECT помещаются в еще одну таблицу, называемую результирующим набором (result set).

Самая простая форма инструкции SELECT состоит из списка столбцов выборки и предложения FROM. (Все прочие предложения являются необязательными.) Эта форма инструкции SELECT имеет следующий синтаксис:

SELECT [ ALL | DISTINCT] column_list
    FROM {table1 [tab_alias1] } ,...


Соглашения по синтаксису

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

В параметре column_list указывается один или несколько из следующих спецификаторов:

символ звездочка (*) указывает все столбцы таблиц, перечисленных в предложении FROM (или с одной таблицы, если задано указателем в виде table2.*);
явное указание имен столбцов, из которых нужно извлечь значения;
спецификатор в виде column_name [as] column_heading, что позволяет заменить имя столбца при чтении данных (не в базе) или присвоить новое имя выражению;
выражение;
системная или агрегатная функция.

Инструкция SELECT может извлекать из таблицы как отдельные столбцы, так и строки. Первая операция называется списком выбора (или проекцией), а вторая - выборкой. Инструкция SELECT также позволяет выполнять комбинацию обеих этих операций.

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

USE SampleDb;
SELECT Number, DepartmentName, Location
    FROM Department;

Результат выполнения этого запроса:

В примере инструкция SELECT извлекает все строки всех столбцов таблицы Department. Если список выбора инструкции SELECT содержит все столбцы таблицы (как это показано в примере), их можно указать с помощью звездочки (*), но использовать этот способ не рекомендуется. Имена столбцов служат в качестве заголовков столбцов в результирующем выводе.

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

SELECT select_list
    [INTO new_table_]
    FROM table
    [WHERE search_condition]
    [GROUP BY group_by_expression]
    [HAVING search_condition]
    [ORDER BY order_expression [ASC | DESC] ];


Соглашения по синтаксису

Порядок предложений в инструкции SELECT должен быть таким, как показано в приведенном синтаксисе. Например, предложение GROUP BY должно следовать за предложением WHERE и предшествовать предложению HAVING. Предложение INTO не является настолько важным, как другие предложения, и поэтому будет рассмотрено позже других.

Далее мы рассмотрим эти предложения в том порядке, в каком они следуют в запросе, а также рассмотрим свойство IDENTITY, возможность упорядочивания результатов, операторы над множествами и выражение CASE. Но так как первое в списке предложение INTO менее важно, чем остальные, оно будет рассматриваться позже, после всех других предложений.

Предложение WHERE

Часто при выборке данных из таблицы нужны данные только из определенных строк, для чего в запросе определяется одно или несколько соответствующих условий. В предложении WHERE определяется логическое выражение (т.е. выражение, возвращающее одно из двух значений: true или false), которое проверяется для каждой из строк, кандидатов на выборку. Если строка удовлетворяет условию выражения, т.е. выражение возвращает значение true, она включается в выборку; в противном случае строка пропускается.

Применение предложения WHERE показано в примере ниже, в котором происходит выборка имен и номеров отделов, расположенных в Москве:

USE SampleDb;
SELECT Number, DepartmentName
    FROM Department
    WHERE Location = N'Москва';

Результат выполнения этого запроса:

Выборка имен и номеров отделов, расположенных в Москве

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

Операторы сравнения в T-SQL
Оператор	Значение
<> (или !=)	не равно
<	меньше чем
>	больше чем
>=	больше чем или равно
<=	меньше чем или равно
!>	не больше чем
!<	не меньше чем

В примере ниже показано использование в предложении WHERE одного из этих операторов сравнения:

USE SampleDb;
SELECT FirstName, LastName
    FROM Employee
    WHERE Id >= 15000;

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

USE SampleDb;
SELECT ProjectName
    FROM Project
    WHERE Budget * 1.1 > 110000;

В этом примере происходит выборка проектов с бюджетом свыше 60 000 евро (предполагая, что в поле Budget хранится информация в долларах). Валютный курс: 1.1$ за €1.

Сравнение строк (т.е. значений с типами данных CHAR, VARCHAR, NCHAR и NVARCHAR) выполняется в действующем порядке сортировки, а именно в порядке сортировки, указанном при установке компонента Database Engine. При сравнении строк в кодировке ASCII (или в любой другой кодировке) сравниваются соответствующие символы каждой строки (т.е. первый символ первой строки с первым символом второй строки, второй символ первой строки со вторым символом второй строки и т.д.). Старшинство символа определяется его позицией в кодовой таблице: символ, чей код стоит в таблице перед кодом другого, считается меньше этого символа. При сравнении строк разной длины, более короткая строка дополняется в конце пробелами до длины более длинной строки.

Числа сравниваются алгебраически. Значения временных типов данных (таких как DATE, TIME и DATETIME) сравниваются в хронологическом порядке.

Логические операторы

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

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

USE SampleDb;
SELECT EmpId, ProjectNumber
    FROM Works_on
    WHERE 
        ProjectNumber = 'p1'
        OR
        ProjectNumber = 'p2'

В этом примере происходит выборка номеров сотрудников, которые работают над проектом p1 или p2 (или над обоими). Результат выполнения этого запроса:

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

USE SampleDb;
SELECT DISTINCT EmpId
    FROM Works_on
    WHERE 
        ProjectNumber = 'p1'
        OR
        ProjectNumber = 'p2'

Результат выполнения модифицированного кода:

Удаление дублированных записей в выборке с помощью DISTINCT

Обратите внимание, что опцию DISTINCT можно использовать только один раз в списке выбора, и она должна предшествовать всем именам столбцов. Поэтому, код в примере ниже ошибочен:

USE SampleDb;
SELECT DepartmentNumber DISTINCT EmpId
    FROM Works_on
    WHERE 
        ProjectNumber = 'p1'
        OR
        ProjectNumber = 'p2'

Результатом выполнения этого запроса будет сообщение об ошибке, выданное сервером:

Incorrect syntax near the keyword 'DISTINCT'.

Неправильный синтаксис при использовании ключевого слова 'DISTINCT'

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

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

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE Id = 2581 AND LastName = 'Фролов'
        OR FirstName = 'Василий' AND DepartamentNumber = 'd1';

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE ((Id = 2581 AND LastName = 'Фролов')
        OR FirstName = 'Василий') AND DepartamentNumber = 'd1';

Результат выполнения этих запросов:

Зависимость результата выборки от порядка следования логических операторов

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

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

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE (Id = 2581 AND LastName = 'Фролов')
        OR (FirstName = 'Василий' AND DepartamentNumber = 'd1');

Третий логический оператор NOT изменяет логическое значение, к которому он применяется, на противоположное. Это означает, что отрицание истинного значения (true) дает ложь (false) и наоборот. Отрицание значения NULL также дает NULL. Ниже демонстрируется использование оператора отрицания NOT:

USE SampleDb;

SELECT Id, FirstName
    FROM Employee
    WHERE NOT DepartamentNumber = 'd2';

В этом примере происходит выборка табельных номеров и имен сотрудников, не принадлежащих к отделу d2. В данном случае логический оператор NOT можно заменить логическим оператором сравнения <> (не равно).

Операторы IN и BETWEEN

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

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE Id IN (10102, 25348, 28559);

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

Оператор IN равнозначен последовательности условий, соединенных операторами OR. (Число операторов OR на один меньше, чем количество выражений в списке оператора IN.)

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

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE NOT Id IN (10102, 25348, 28559);

Результат выполнения этого запроса:

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

USE SampleDb;

SELECT ProjectName, Budget
    FROM Project
    WHERE Budget BETWEEN 95000 AND 120000;

В этом примере происходит выборка наименований проектов и бюджетов всех проектов с бюджетом, находящимся в диапазоне от $95 000 и до $120 000 включительно.

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

Оператор BETWEEN логически эквивалентен двум отдельным сравнениям, соединенным логическим оператором AND. Поэтому запрос, приведенный в примере выше, эквивалентен запросу:

USE SampleDb;

SELECT ProjectName, Budget
    FROM Project
    WHERE Budget >= 95000 AND Budget <= 120000;

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

В примере ниже показаны две разные формы запроса SELECT, которые дают одинаковые результаты:

USE SampleDb;

SELECT ProjectName, Budget
    FROM Project
    WHERE Budget NOT BETWEEN 99000 AND 150000;
    
-- Аналог предыдущей конструкции
SELECT ProjectName, Budget
    FROM Project
    WHERE Budget < 99000 OR Budget > 150000;

В этом примере происходит выборка всех проектов с бюджетом меньшим, чем $99 000 и большим, чем $150 000. Формулировка требования запроса: "Выбрать наименования всех проектов с бюджетом меньшим, чем $99 000 и имена всех проектов с бюджетом большим, чем $150 000" может навести на мысль, что во втором запросе SELECT требуется применить логический оператор AND. Но логический смысл запроса требует применения оператора OR, т.к. использование оператора AND не даст никаких результатов вообще. Это потому, что не может бюджет быть одновременно и меньшим, чем $99 000 и большим, чем $150 000. Но это и является ответом, почему используется оператор OR, а не AND, поскольку мы выбираем все проекты, бюджет которых меньше $99 000 или больше $150 000.

Запросы, связанные со значением NULL

Параметр NULL в инструкции CREATE TABLE указывает, что соответствующий столбец может содержать специальное значение NULL (которое обычно представляет неизвестное или неприменимое значение). Значения NULL отличаются от всех других значений базы данных. Предложение WHERE инструкции SELECT обычно возвращает строки, удовлетворяющие указанным в нем условиям сравнения. Но здесь возникает вопрос, как будут оцениваться в этих сравнениях значения NULL?

Все сравнения со значением NULL возвращают false, даже если им предшествует оператор NOT. Для выборки строк, содержащих значения NULL, в языке Transact-SQL применяется оператор IS NULL. Указание в предложении WHERE строк, содержащих (или не содержащих) значение NULL, имеет следующую общую форму:

column_name IS [NOT] NULL

Использование оператора IS NULL демонстрируется в примере ниже:

USE SampleDb;

SELECT EmpId, ProjectNumber
    FROM Works_on
    WHERE ProjectNumber = 'p2' AND job IS NULL;

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

USE SampleDb;

SELECT EmpId, ProjectNumber
    FROM Works_on
    WHERE job <> NULL;

Выполнение этого запроса не возвращает никаких строк. Условие "column IS NOT NULL" эквивалентно условию "NOT (column IS NULL)". Системная функция ISNULL позволяет отображать указанное значение вместо значения NULL:

USE SampleDb;

SELECT EmpId, ProjectNumber, ISNULL(job, N'Работа не определена') AS task
    FROM Works_on
    WHERE ProjectNumber = 'p1';

Результат выполнения этого запроса:

В примере выше для столбца должностей Job в результате запроса используется заголовок 'task'.

Оператор LIKE

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

column [NOT] LIKE 'pattern'

Параметр 'pattern' может быть строковой константой, или константой даты, или выражением (включая столбцы таблицы), и должен быть совместимым с типом данных соответствующего столбца. Для указанного столбца сравнение значения строки и шаблона возвращает true, если значение совпадает с выражением шаблона.

Определенные применяемые в шаблоне символы, называющиеся подстановочными символами (wildcard characters), имеют специальное значение. Рассмотрим два из этих символов:

% (знак процента) - обозначает последовательность любых символов любой длины;
_ (символ подчеркивания) - обозначает любой один символ.

Использование подстановочных символов % и _ показано в примере ниже:

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName LIKE '_а%';

В этом примере происходит выборка имен, фамилий и табельных номеров сотрудников, у которых второй буквой имени является буква "а". Результат выполнения этого запроса:

Кроме знака процентов и символа подчеркивания, поддерживает другие специальные символы, применяемые с оператором LIKE. Использование этих символов ([, ] и ^) демонстрируется в примерах ниже:

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName LIKE '[В-И]%';

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

Использование оператора LIKE для поиска в диапазоне

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

Символ ^ обозначает отрицание диапазона или списка символов. Но такое значение этот символ имеет только тогда, когда находится внутри квадратных скобок, как показано в примере ниже. В данном примере запроса осуществляется выборка имен и фамилий тех сотрудников, чьи имена начинаются с буквы отличной от В-И, П-Я:

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName LIKE '[^В-И]%'
        AND FirstName LIKE '[^П-Я]%';

В примере ниже демонстрируется использование оператора LIKE совместно с отрицанием NOT:

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName NOT LIKE '[А-И]%';

Здесь выбираются все столбцы сотрудников, чьи имена начинаются на буквы, отличные от букв в диапазоне 'А-И'.

Любой подстановочный символ (%, _, [, ] или ^), заключенный в квадратные скобки, остается обычным символом и представляет сам себя. Такая же возможность существует при использовании параметра ESCAPE. Поэтому оба варианта применения инструкции SELECT, показанные в примере ниже, эквивалентны:

USE SampleDb;

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName LIKE '%[_]%';

SELECT Id, FirstName, LastName
    FROM Employee
    WHERE FirstName LIKE '%!_%' ESCAPE '!';

Результат выполнения этих двух инструкций SELECT будет одинаковым. В этом примере обе инструкции SELECT рассматривают символ подчеркивания в значениях столбца FirstName, как таковой, а не как подстановочный. В первой инструкции SELECT это достигается заключением символа подчеркивания в квадратные скобки. А во второй инструкции SELECT этот же эффект достигается за счет применения символа перехода (escape character), каковым в данном случае является символ восклицательного знака. Символ перехода переопределяет значение символа подчеркивания, делая его из подстановочного символа обычным. (Результат выполнения этих инструкций содержит ноль строк, потому что ни одно имя сотрудника не содержит символов подчеркивания.)

Стандарт SQL поддерживает только подстановочные символы %, _ и оператор ESCAPE. Поэтому если требуется представить подстановочный символ как обычный символ, то вместо квадратных скобок рекомендуется применять оператор ESCAPE.