Глава 5. ИСПОЛЬЗОВАНИЕ СПЕЦИАЛЬНЫХ ОПЕРАТОРОВ В УСЛОВИЯХ

В дополнение к реляционным и булевым операциям, обсуждённым в Главе 4, SQL использует специальные операторы: IN, BETWEEN, LIKE и IS NULL.
В этой главе вы узнаете, как их использовать и как реляционные операторы позволяют создавать более сложные и мощные предикаты. Обсуждение оператора IS NULL будет включать отсутствие данных и значение NULL, которое указывает на то, что данные отсутствуют.
Вы также узнаете о вариантах использования оператора, NOT применяющегося с этими операторами.

ОПЕРАТОР IN

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

      SELECT *
         FROM Salespeople
         WHERE city = 'Barcelona'
            OR city = 'London';

Имеется и более простой способ получить ту же информацию:

       SELECT *
          FROM Salespeople
          WHERE city IN ('Barcelona', 'London');

Вывод для этого запроса показан на Рисунке 5.2.

Как видите, IN определяет набор значений с помощью имён членов набора, заключённых в круглые скобки и разделённых запятыми. Он затем проверяет различные значения указанного поля, пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен. Когда набор содержит числовые значения, а не символы, одиночные кавычки опускаются. Давайте найдём всех заказчиков, относящихся к продавцам, имеющих значения snum = 1001, 1007, и 1004. Вывод для следующего запроса показан на Рисунке 5.3:

     SELECT *
        FROM Customers
        WHERE snum IN (1001, 1007, 1004);


           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE city = 'Barcelona'                      |
          | OR city = 'London';                           |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1001      Peel         London        0.12   |
          |   1004      Motika       London        0.11   |
          |   1007      Rifkin       Barcelona     0.15   |
          |                                               |
           ===============================================

	Рисунок 5.1 Нахождение продавцов в Барселоне и Лондоне


           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE city IN ('Barcelona', 'London';         |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1001      Peel         London        0.12   |
          |   1004      Motika       London        0.11   |
          |   1007      Rifkin       Barcelona     0.15   |
          |                                               |
           ===============================================

		Рисунок 5.2 SELECT использует IN
 
           ===============  SQL Execution Log ============
          | SELECT *                                      |
          | FROM  Customers                               |
          | WHERE snum IN ( 1001, 1007, 1004 );           |
          | ============================================= |
          |   snum     cname    city     rating    snum   |
          |  ------   --------  ------    ----   ------   |
          |   2001     Hoffman  London     100     1001   |
          |   2006     Clemens  London     100     1001   |
          |   2008     Cisneros San Jose   300     1007   |
          |   2007     Pereira  Rome       100     1004   |
            =============================================

	    Рисунок 5.3 SELECT использует IN с номерами

ОПЕРАТОР BETWEEN

Оператор BETWEEN похож на оператор IN. Но, в отличие от определения по числам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться, что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть последним по алфавитному или числовому порядку. (Обратите внимание, что, в отличие от английского языка, SQL не говорит, что "значение находится (между) BETWEEN значением и значением", а просто "значение BETWEEN значение значение". Это применимо и к оператору LIKE). Следующий пример будет извлекать из таблицы Продавцов всех продавцов с комиссионными между .10 и .12 (вывод показан на Рисунке 5.4):

          SELECT *
             FROM Salespeople
             WHERE comm BETWEEN .10 AND .12;

Для оператора BETWEEN, значение, совпадающее с любым из двух значений границы (в данном случае это .10 и .12 ), заставляет предикат быть верным.

           ===============  SQL Execution Log ============
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE comm BETWEEN .10 AND .12;               |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1001      Peel         London        0.12   |
          |   1004      Motika       London        0.11   |
          |   1003      Axelrod      New York      0.10   |
	   ===============================================

		  Рисунок 5.4 SELECT с BETWEEN

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

      SELECT *
          FROM Salespeople
          WHERE (comm BETWEEN .10, AND .12)
            AND NOT comm IN (.10, .12);

Вывод для этого запроса показан на Рисунке 5.5.

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

В основном вы используете IN и BETWEEN так же, как вы использовали реляционные операции при сравнении значений, которые берутся либо из набора (для IN), либо из диапазона (для BETWEEN).

Также, подобно реляционным операциям, BETWEEN может работать с символьными полями в терминах эквивалентов ASCII. Это означает, что вы можете использовать BETWEEN для выборки ряда значений из упорядоченных по алфавиту значений.

           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE (comm BETWEEN .10 AND .12)              |
          | AND NOT comm IN (.10 .12);                    |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1004      Motika       London        0.11   |
          |                                               |
           ===============================================

	    Рисунок 5.5 Сделать BETWEEN с невключением

Этот запрос выбирает всех заказчиков, чьи имена попали в определенный алфавитный диапазон:

            SELECT *
               FROM Customers
               WHERE cname BETWEEN 'A' AND 'G';

Вывод для этого запроса показан на Рисунке 5.6.

Обратите внимание, что Grass и Giovanni отсутствуют даже при включенном BETWEEN. Это происходит из-за того, что BETWEEN сравнивает строки неравной длины. Строка 'G' короче, чем строка Giovanni, поэтому BETWEEN выводит 'G' с пробелами. Пробелы предшествуют символам в алфавитном порядке (в большинстве реализаций), поэтому Giovanni не выбирается. То же самое происходит и с Grass. Важно помнить это, когда вы используете BETWEEN для извлечения значений из алфавитных диапазонов. Обычно вы указываете диапазон с помощью символа начала диапазона и символа конца (вместо которого можно просто поставить z).

           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Customers                               |
          | WHERE cname BETWEEN 'A' AND 'G';              |
          | ============================================= |
          |   cnum     cname    city     rating    snum   |
          |  ------   --------  ------    ----   ------   |
          |   2006     Clemens  London     100     1001   |
          |   2008     Cisneros San Jose   300     1007   |
          |                                               |
            =============================================

	Рисунок 5.6 Использование BETWEEN в алфавитных порядках

ОПЕРАТОР LIKE

LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется для поиска подстрок. Т.е. он ищет поле символа, чтобы увидеть, совпадает ли с условием часть его строки. В качестве условия он использует групповые символы-шаблоны (wildсards) - специальные символы, которые могут соответствовать чему-нибудь.

Имеются два типа шаблонов, используемых с LIKE:

символ подчёркивания ( _ ) замещает любой одиночный символ. Например, 'b_t' будет соответствовать словам 'bat' или 'bit', но не будет соответствовать 'brat'.
знак процента (%) замещает последовательность любого количества символов (включая символы нуля). Например '%p%t' будет соответствовать словам 'put', 'posit', или 'opt, но не 'spite'.

Давайте найдём всех заказчиков, чьи имена начинаются с G (вывод показан на Рисунке 5.7):

       SELECT
          FROM Customers
          WHERE cname LIKE 'G%';


           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Customers                               |
          | WHERE cname LIKE 'G';                         |
          | ============================================= |
          |   cnum     cname    city     rating    snum   |
          |  ------   --------  ------    ----   ------   |
          |   2002     Giovanni Rome       200     1003   |
          |   2004     Grass    Berlin     300     1002   |
          |                                               |
            =============================================

		Рисунок 5.7 SELECT использует LIKE с %

LIKE может быть удобен, если вы ищете имя или другое значение и если вы не помните, как они точно пишутся. Предположим, что вы не уверены, как записано по буквам имя одного из ваших продавцов - Peal или Peel. Вы можете просто использовать ту часть, которую вы знаете, и групповые символы, чтобы находить все возможные совпадения (вывод этого запроса показан на Рисунке 5.8):

       SELECT *
          FROM Salespeople
          WHERE sname LIKE 'P _ _ l %';

Группа символов подчёркивания, каждый из которых представляет один символ, добавит только два символа к уже существующим 'P' и 'l' , поэтому имя наподобие Prettel не может быть показано. Групповой символ ' % ' в конце строки необходим в большинстве реализаций, если длина поля sname больше, чем число символов в имени Peel (потому что некоторые другие значения sname длиннее, чем четыре символа). В этом варианте значение поля sname, фактически сохраняемое как имя Peel, сопровождается рядом пробелов. Следовательно, символ 'l' не будет считаться концом строки. Групповой символ ' % ' просто соответствует этим пробелам. Это не обязательно, если поле sname имеет тип VARCHAR.

           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE sname LIKE 'P__l';                   |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1001      Peel         London        0.12   |
          |                                               |
           ===============================================

 	Рисунок 5.8 SELECT использует LIKE с подчёркиванием (_)

А что вы будете делать, если вам нужно искать символ процента или символ подчёркивания в строке? В LIKE-предикате вы можете определить любой одиночный символ как символ ESC. Символ ESC используется сразу перед процентом или подчёркиванием в предикате и означает, что процент или подчёркивание будет интерпретироваться как символ, а не как групповой символ-шаблон. Например, мы могли бы найти наш sname-столбец, где присутствует подчёркивание, следующим образом:

          SELECT *
             FROM Salespeople
             WHERE sname LIKE '%/_%' ESCAPE '/';

С этими данными не будет никакого вывода, потому что мы не включили никакого подчёркивания в имя нашего продавца. Ключевое слово ESCAPE определяет '/ ' как ESC-символ. ESC-символ, используемый в LIKE-строке, сопровождается знаком процента, знаком подчёркивания или знаком ESCAPE, который будет искаться в столбце, а не обрабатываться как шаблон.

Символ ESC должен быть одиночным символом и применяется только к одиночному символу сразу после него.

В примере выше, символ процента начала и символ процента окончания обрабатываются как групповые символы; только подчёркивание представлено как сам символ.

Как упомянуто выше, символ ESC может также использоваться самостоятельно. Другими словами, если вы будете искать столбец с символом ESC, вы просто вводите его дважды. Символ ESC "берёт следующий символ буквально как символ" и, во-вторых, символ ESC самостоятелен.

Вот предыдущий пример, который пересмотрен, чтобы найти местонахождение строки '_/' в sname-столбце:

     SELECT *
        FROM Salespeople
        WHERE sname LIKE '%/_//%' ESCAPE'/';

Снова не будет никакого вывода с такими данными. Строка сравнивается с содержанием любой последовательности символов (%), сопровождаемых символом подчёркивания ( /_ ), символом ESC ( // ) и любой последовательностью символов в конце строки (%).

РАБОТА СО ЗНАЧЕНИЯМИ NULL

Часто в таблице будут записи, которые не имеют никаких значений поля, например, потому что информация не завершена, или потому что это поле просто не заполнялось. SQL учитывает такой вариант, позволяя вам вводить значение NULL (ПУСТОЙ) в поле, вместо значения. Когда значение поля равно NULL, это означает, что программа базы данных специально промаркировала это поле как не имеющее никакого значения для этой строки (записи).
Это отличается от просто назначения полю значения нуль или пробела, которые база данных будет обрабатывать так же, как и любое другое значение. Точно так же как NULL не является техническим значением, оно не имеет и типа данных. Оно может помещаться в любой тип поля. Тем не менее, NULL в SQL часто упоминается как "нуль".

Предположим, что вы получили нового заказчика, который ещё не был назначен продавцу. Чем ждать продавца, к которому его нужно назначить, вы можете ввести заказчика в базу данных теперь же, так что он не потеряется при перестановке. Вы можете ввести строку для заказчика со значением NULL в поле snum и заполнить это поле значением позже, когда продавец будет назначен.

ОПЕРАТОР IS NULL

Так как NULL указывает на отсутствие значения, вы не можете знать, каков будет результат любого сравнения с использованием NULL. Когда NULL сравнивается с любым значением, даже с другим таким же NULL, результат будет ни true, ни false, он неизвестен/undefined. Неизвестный булев вообще ведёт себя так же, как неверная строка, которая, произведя неизвестное значение в предикате, не будет выбрана запросом. Имейте в виду, что, в то время как NOT (неверное) равняется верно, NOT (неизвестное) равняется неизвестно.

Следовательно, выражение типа 'city = NULL' или 'city IN (NULL)' будет неизвестно в любом случае.

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

Найдём все записи в нашей таблице Заказчиков с NULL-значениями в столбце city:

            SELECT *
               FROM Customers
               WHERE city IS NULL;

Здесь не будет никакого вывода, потому что мы не имеем никаких значений NULL в наших типовых таблицах. Значения NULL очень важны, и мы вернёмся к ним позже.

ИСПОЛЬЗОВАНИЕ NOT СО СПЕЦИАЛЬНЫМИ ОПЕРАТОРАМИ

Операнды могут непосредственно предшествовать булеву NOT.

Это противоположно реляционным операциям, когда оператор NOT должен идти перед вводимым выражением. Например, если мы хотим устранить NULL из нашего вывода, мы будем использовать NOT, чтобы изменить на противоположное значение предиката:

          SELECT *
             FROM Customers
             WHERE city NOT NULL;

При отсутствии значений NULL (как в нашем случае), будет выведена вся таблица Заказчиков. Аналогично можно ввести следующее

           SELECT *
              FROM Customers
              WHERE NOT city IS NULL;

что также приемлемо. Мы можем также использовать NOT с IN:

         SELECT *
            FROM Salespeople
            WHERE city NOT IN ('London', 'San Jose');

А вот другой способ подобного же выражения:

       SELECT *
          FROM Salespeople
          WHERE NOT city IN ('London', ' San Jose');

Вывод для этого запроса показан на Рисунке 5.9.

Таким же способом вы можете использовать NOT BETWEEN и NOT LIKE.

           ===============  SQL Execution Log ============
          |                                               |
          | SELECT *                                      |
          | FROM  Salespeople                             |
          | WHERE сity NOT IN ('London', 'San Jose';)     |
          | ==============================================|
          |   snum      sname         city         comm   |
          | ------    ----------   -----------   -------  |
          |   1003      Rifkin       Barcelona     0.15   |
          |   1007      Axelrod      New York      0.10   |
          |                                               |
           ===============================================

		Рисунок 5.9 Использование NOT с IN

РЕЗЮМЕ

Теперь вы можете создавать предикаты в терминах связей, специально определённых в SQL. Вы можете искать значения в определённом диапазоне (BETWEEN) или в числовом наборе (IN), или вы можете искать символьные значения, которые соответствуют тексту внутри параметров (LIKE).

Вы также изучили кое-что о том, как SQL поступает при отсутствии данных (а это реально), используя NULL вместо конкретных значений. Вы можете извлекать или исключать значения NULL из вашего вывода, используя оператор IS NULL.

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

РАБОТА СО SQL

Напишите два запроса, которые могли бы вывести все заказы на 3 или 4 октября 1990.
Напишите запрос, который выберет всех заказчиков, обслуживаемых продавцами Peel или Motika. (Подсказка: из наших типовых таблиц поле snum связывает вторую таблицу с первой.)
Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы, попадающей в диапазон от A до G.
Напишите запрос, который выберет всех пользователей, чьи имена начинаются с буквы C.
Напишите запрос, который выберет все заказы, имеющие нулевые значения или NULL в поле amt (сумма).

(См. ответы в Приложении A.)