Ограничение строк возвращаемых результатом
Одним из краеугольных принципов в реляционной теории является выборка. Эта операция реализована ключевым словом WHERE, иногда называемом предикатом. Условия которые ограничивают результат запроса могут быть сложными и работать как со столбцами так и с выражениями. Только строки которые удовлетворяют условиям будут возвращены в результате запроса. Условия проверяют строки используя операторы сравнения в сочетании со столбцами и выражениями. Операторы булевой алгебры позволяют использовать комбинацию условий для ограничения возвращаемых строк.
Предикат WHERE
Предикат WHERE расширяет команду SELECT предоставляя возможность ограничить строки результата наложением одного или нескольких условий. Запрос к таблице используя только предикаты SELECT и FROM приводит к тому, что все строки хранящиеся в таблице будут возвращены в результате выполнения запроса. Использование ключевого слова DISTINCT позволяет убрать дубликаты и результат выполнения запроса ограничивается в какой-то мере. Но что если нам нужна из таблицы только определённая информация, например, только те данные которые содержат определённое значение в конкретном столбец? Как нам получить данные только тех стран которые находятся в Европе из таблицы COUNTRIES? Или как получить только сотрудников работающих торговыми представителями? Все эти запросы можно выполнить используя предикат WHERE для указания какие конкретные строки мы хотим получить. Синтаксис команды SELECT с предикатом WHERE выглядит следующим образом
SELECT *|{[DISTINCT] column|expression [alias],…}
FROM table
[WHERE condition(s)];
Предикат WHERE всегда находится после предиката FROM. Квадратные скобки указывает на то что использование WHERE необязательно. Одно или несколько условий могут применяться для ограничения результата. Условия определяется оператором сравнения и двумя операндами. Операндами могут быть значения столбцов, литераты или выражения. Оператор равенства (equality) наиболее часто используемые оператор для ограничения результата. Пример использования предиката WHERE
select country_name
from countries
where region_id=3;
Этот пример проецирует столбец COUNTRY_NAME из таблицы COUNTRIES. Вместо выбора всех строк из таблицы предикат WHERE ограничивает результат только теми строками у которые значение столбца REGION_ID равно трём.
Условия для числовых данных
Условия должны быть сформулированы в зависимости от типа данных. Условия для численных значений можно устанавливать несколькими способами. Рассмотрим столбец SALARY в таблице EMPLOYEES. Тип данных столбца NUMBER(8,2). Ограничить строки по значению этого столбца можно следующим способом
select last_name, salary from employees where salary = 10000;
Этот запрос на фамилию и запрлату сотрудников кто зарабатывает больше 10000 выполнится успешно так как типы данных обоих операндов совпадают и совместимы. Столбец с численным типом данных также можно сравнить с другим численным столбцом той же строки в условии WHERE
select last_name, salary from employees where salary = department_id;
В этом примере условие WHERE никогда не выполнится так как значение зарплаты находится в диапазоне от 2100 до 99999.99 а значение номера отдела – от 10 до 110. Так как значения не пересекаются, значит строк удовлетворяющих условию нет и запрос возвращает пустой результат.
Условие WHERE также можно использовать для сравнения столбцов и выражений или сравнения выражения с выражением
select last_name, salary from employees where salary = department_id*100;
select last_name, salary from employees where salary/10 = department_id*10;
В первом примере сравнивается значение столбца SALARY со значением DEPARTAMENT_ID умноженным на 100 для каждой строки. Во втором примере сравнивается два выражения. Обратте внимания что алгебраически два примера асболютно идентичны и поэтому у двух запросов одинаковый результат
Условия для символьных данных
Условия для выборки строк основанные на символьных данных определяются с помощью заключения литералов в условии в одинарные кавычки. Столбец JOB_ID в таблице EMPLOYEES имеет тип данных VARCHAR(20). Предположим что вам нужен список фамилий тех сотрудников, которые работают сейчас в должности торгового представителя. Значение должности торгового представителя равно SA_REP. Следующий запрос можно использовать для этой цели
select last_name from employees where job_id=’SA_REP’;
Если вы попробуете выполнить запрос без заключения значения литерала в кавычки вы получите ошибку. Надо помнить что данные чувствительны к регистру, т.е. следующие условия WHERE не эквивалентны
Условие 1: where job_id=SA_REP
Условие 2: where job_id=’Sa_Rep’
Условие 3: where job_id=’sa_rep’
Условие один приведёт к возникновению ошибки ORA-00904: «SA_REP»: invalid identifier так как значение литерала не заключено в кавычки и воспринимается как название столбца. Условия два и три синтаксически валидны, но не эквивалентны. Более того, ни одно из условий не вернёт никаких данных, так как в таблице нет строки ни со значением Sa_Rep ни со значением sa_rep.
Условия для символьных данных не ограничены сравнением значений столбца со значением литералов. Вы можете использовать другие столбцы символьного типа или выражения. Символьные выражения могут быть любой частью оператора условия. Выражения можно формировать используя конкатенацию литерала с одним или несколькими столбцами. Слелующие примеры используют эти возможности
Условие 1: where ‘A ‘||last_name||first_name = ‘A King’
Условие 2: where first_name||’ ‘||last_name = last_name||»||first_name
Условие 3: where ‘SA_REP’||’King’ = job_id||last_name
Условие 4: where job_id||last_name =’SA_REP’||’King’
Условия над датами
Столбцы с типом данных DATE используются для хранения данных о дате и времени. Литералы даты должны заключаться в одинарные кавычки так же как символьные данные. Когда используется в условии WHERE столбец с типом данных даты – его можно сравнивать с другим столбцом даты, литералом или выражением. Литералы конвертируются к типу данных DATE используя формат по умолчанию DD-MON-RR. Если литерал встречается в выражении использующем столбец с типом данных даты, он автоматически конвертируется в дату используя формат по умолчанию. DD обозначает день, MON – первые три буквы месяца и RR – две последние цифры года (если RR – между 50 и 99 это значит что используется предыдущий век, иначе используется текущий век). Также можно указать все четыре цифры года. Рассмотрим следующие условия
Условие 1: where start_date = end_date;
Условие 2: where start_date = ’01-JAN-2001′;
Условие 3: where start_date = ’01-JAN-01′;
Условие 4: where start_date = ’01-JAN-99′;
Первое условия проверяет равенство двух столбцов с типом данных DATE. Результатом будут строки в которых значение START_DATE и END_DATE одинаковое. Важно помнить, что даты равны только тогда, когда равны всех их части, включая день, месяц, год, часы, минуты и секунды. В главе 10 мы обсудим тип данных дата подробнее. Пока что не обращаем внимание на часы, минуты и секунды. Во втором условии START_DATE сравнивается с литералом ’01-JAN-2001’. Указаны все четыре цифры года. Такое условие валидно. Третий запрос эквивалентен воторому, так как литерал ’01-JAN-01’ преобразуется в значение ’01-JAN-2001’. Так происходит потому что RR значение меньше 50 и используется текущий (21) век. В четвёртом условии значение 01-JAN-99 будет преобразовано в 1999 так как 99 входит в диапазон от 50 до 99 и используется предыдущий век.
Также поддерживаются арифметические действия над датами такие как добавление и вычитание. Выражение вида END_DATE – START_DAE вернёт число – количество дней между датой начала и датой завершения, а выражением STAR_DATE+30 вернёт дату которая будет через 30 дней после даты начала.
Exam tip
Условия сравнивают два значения используя оператор сравнения. Необходимо знать тип данных операндов, так как может быть необходимо заключить значение в кавычки.
Операторы сравнения
Оператор равенства обычно используется для отображения концепции ограничения данных используя предикат WHERE. Но ещё доступны и некоторые другие операторы. Операторы неравенства такие как «меньше» или «больше или равно» могут использоваться для создания нестрогих равенств. Оператор BETWEEN используется для определения входит ли значение столбца в диапазон между двумя значениями. Оператор IN проверяет вхождение значения в заданный набор значений (равенство хотя бы одному значению из заданного набора). Оператор LIKE – это инструмент, позволяющий сравнивать символьные значения на основании специальных шаблонов. И последний оператор сравнения это оператор IS NULL, который позволяет проверять строки на значение NULL в столбце. Эти операторы можно комбинировать в секции WHERE.
Равенство и неравенство
Ограничение строк возвращаемых запросом требует создания подходящего условия WHERE. Если ограничения слишком строгие, то могут быть получены всего несколько или вообще ни одной строки. Если условия построено слишком размытым – то можно получить больше строк чем необходимо. Рассмотрев разные операторы вы сможете строить гибкие условия для получения именно тех строк что вам необходимы. Как работает проверка на равенство обычно интуитивно понятно. Такие условия создаются используя оператор равенства (=). Рассмотрим запрос
select last_name, salary from employees where job_id=’SA_REP’;
Столбец JOB_ID каждой строки таблицы EMPLOYEE проверяется на равенство символьному литералу SA_REP. Для символьной инфомрации, понятие равенства означает что они должны быть посимвольно идентичны учитывая регистр. Когда находится такое равенство, значения необходимых столбцов из этой строки помещаются в результат запроса. Обратите внимание на то, что хотя столбец JOB_ID используется в условии – нет никакой обязательности проецировать этот столбец в результат запроса.
Условия неравенства расширяют возможности предиката WHERE. Проверку на вхождение в диапазон значений или в набор предопределённых значений можно сделать используя операторы равенства и неравенства, но обычно предпочтительнее использовать операторы BETWEEN и LIKE. Операторы неравенства описаны в таблице 9-3.
Операторы неравенства позволяют выполнять запросы для диапазонов. У вас может возникнуть задача вернуть строки где значение столбца больше чем другое значение. Следующий пример выбирает фамилию и зарплату сотрудников у которых зарплата больше 5000:
select last_name, salary from employees where salary > 5000;
Сложный оператор неравенства (который состоит больше чем из одного символа) используется в следующих условиях
Условие 1: where salary <= 3000;
Условие 2: where salary <> department_id;
В первом условии выбираются строки в которых значение зарплаты меньше или равно 3000. В условии номер два показана одна из форма как получить оператор «не равно». При использовании этого условия будут отобраны строки в которых значение зарплаты не равно номеру департамента.
Неравество чисел интуитивно понятно. Сравнение строк и дат не так очевидно. Строки сравниваются следующим образом: оба операнда преобразуются в числовые значения. На основании кодировки и настроек NLS, каждому символу назначается числовое значение и значения суммируются. Именно эти значения в итоге сравниваются. Рассмотрим запрос
select last_name from employees where last_name<‘King’
Строковый-литера Kink преобразуется в число. Предположим что используется US7 ASCII кодировка с настройками локали NLS = AMERICAN, тогда значение литерала будет равно K + i + n + g = (75+105+110+103=393). Для каждой строки из таблицы EMPLOYEES значение поля LAST_NAME преобразуется в численное значение. Если полученное значение меньше 393 – то строка будет возвращена в результате запроса.
Сравнение на неравенство со значениями даты работает примерно как сравнение строковых значений. Oracle хранит значения типа данных дата во встроенном числовом формате, и эти значения используется при сравнении. Рассмотрим пример
select last_name from employees where hire_date < ’01-JAN-2010′
Этот запрос вернёт фамилии сотрудников значение HIRE_DATE которых ранее первого января 2010 года.
Сравнение диапазона. Оператор BETWEEN
Оператор BETWEEN проверяет входит ли значение столбца или выражение в диапазон установленный двумя граничными значениями. Значение должно быть не меньше чем нижняя граница и не больше чем верхняя граница одновременно – тогда сравнение расценивается как верное.
Предположим что вам нужны фамилии сотрудников, зарплата у которых находится между значениями 3400 и 4000. Используя оператор BETWEEN запрос будет выглядеть так
select last_name from employees where salary between 3400 and 4000;
Условие в операторе BETWEEN можео переписать используя два оператора неравенства
select last_name from employees where salary >=3400 and salary <=4000;
Но быстрее и проще использовать оператор BETWEEN
Проверка вхождения в группу. Оператор IN
Оператор IN проверяет является ли проверяемый элемент членом набора значений литералов. Набор определяется перечислением значений через запятую в обрамляющих скобках. Если литералы являются символьными данными или данными типа дата, то значения должны быть в одинарных кавычках. Вы можете использовать сколько угодно значений. Рассмотрим пример
select last_name from employees where salary in (1000,4000,6000);
Значение SALARY у каждой строки сравнивается со всеми значениями указанными в скобках. Если значение равно 1000, 4000 или 6000 то строка будет включена в результат. Следующие запросы используют IN для символьных данных и данных типа дата
select last_name from employees where last_name in (‘Watson’,’Garbharran’,’Ramklass’);
select last_name from employees where hire_date in (’01-JAN-2008′,’01-DEC-2009′);
Сравнение с шаблоном. Оператор LIKE
Оператор LIKE создан для символьных данных и он предоставляет гибкий механизм для поиска символов или строк. LIKE использует два специальных символа для составления шаблона: символ процента (%) и нижнее подчеркивание (_). Знак процента используется для указания нуля или более любых символов, а нижнее подчеркивание заменяет один любой символ. Вы можете использовать следующий пример для поиска сотрудников чьё имя начинается с буквы A
select first_name from employees where first_name like ‘A%’;
Значение столбца FIRST_NAME сравнивается со значением в кавычках как со значением литерала. В дополнении к этому символ процента вместе с оператором LIKE рассматриваются как «значение начинается с символа A, и затем может быть ноль или более любых символов». Специальные символы могут использоваться в любой части шаблона: в начале, в середине или в конце. Также можно использовать просто специальный символ
where first_name like ‘%’
В этом случае все строки в которых значение не NULL будут включены в результат запроса. Использование специальных символов не обязательно при использовании оператора LIKE. В таком случае LIKE будет рассматриваться как оператор равенства, т.е. следующие условия эквивалентны
where last_name like ‘King’;
where last_name = ‘King’;
Символ нижнего подчеркивания означает «обязательно один любой символ». Для поиска сотрудников, чья фамилия состоит из четырёх символов, начинается на K, имеет любой второй символ и заканчивается на ng можно выполнить следующий запрос
select last_name from employees where last_name like ‘K_ng’;
на рисунке 9-6 показано что оба специальных символа могут использоваться отдельно, вместе, или даже несколько раз в одном шаблоне в одном условии. Первый запрос выбирает записи где COUNTRY_NAME начинается с символа I, после которого может быть один или больше символов, один из которых обязательно a. Второй запрос ищет страны, в имени которых пятым символом должен быть символ i. Длина после пятого символа не важна, и первыми четырьмя символами могут быть любые символы.
А что если вам нужно найти литерал, который содержит специальный символ? Специальные символы как данные можно экранировать используя идентификатор ESCAPE. В следующем примере выполняется поиск JOB_ID первыми тремя символами которых должны быть “SA_”
select job_id from jobs where job_id like ‘SA\_%’ escape ‘\’;
Обычно символом экранирования является обратный слеш, но это не обязательно условие. Следующий пример абсолюнто эквивалентен предыдущему, но использовается символ доллара вместо слеша
select job_id from jobs where job_id like ‘SA$_%’ escape ‘$’;
Знак процента обрабатывается точно также.
Сравнение значения NULL с помощью оператора IS NULL
Значение NULL неизбежно встречается в базах данных. И иногда втречается задача поиска только тех записей, в которых значение поля равно NULL. Оператор IS NULL возвращает только те строки где в значении столбца именно значение NULL. Проверка значения на NULL невозможна используя оператор равенства.
Рассмотрим запрос которые выбирает фамилии сотрудников, у которых значение COMMISION_PCT – NULL
select last_name from employees where commission_pct is null;
Булевы операторы
Булевы или логические операторы позволяют использовать несколько условий в предикате WHERE команды SELECT. Это позволяет более гибко извлекать необходимые данные из БД. К примеру нам могут быть интерестны сотрудники фамилия которых начинается на J и значение COMMISSION_PCT которых больше чем десят процентов. То есть первое условие, это ограничить фамилию сотрудников использяу шаблон “J%”. И второе условие, это проверить значение COMMISION_PCT и выбрать только те записи где значение больше 10. Эти два условия можно объединить, используя логическое «И», и применить последовательно в секции WHERE. Результат выполняющий все или некоторые, или не выполняющий определённые условия может быть получен путём использования операторов AND, OR и NOT соответственно.
Оператор AND
Оператор AND (логическое «И») объединяет условия в одно условие и только те данные которые выполняют все условия будут включаться в результат. Если два условия указаны в WHERE и используется оператор AND, то все строки будут проверять последовательно на выполнение обоих условий. Если данные не удовлетворяют условиям или удовлетворяют только одно условие – то данные не будут влючены в результат. Сотрудники, у которых имя начинается на J и комиссия больше 10 процентов могут быть получены следующим запросом
select first_name, last_name, commission_pct, hire_date from employees where first_name like ‘J%’ and commission_pct > 0.1
Обратите внимание что у нас два условия, но зарезервированное слово WHERE используется только один раз. Оператор AND объединяет два условия. Для добавления дополнительных обязательных условий, просто добавьте их, и убедитесь, что они разделены оператором AND. Вы можете определять любое количество условий. Помните, что чем больше вы условий добавляете, тем более строгой будет выборка и тем менее значений будет возвращено.
Оператор OR
Оператор OR (логическое ИЛИ) разделяет два условия таким образом, что для попадания в результат данные должны удовлетворять минимум одному условию. Если у вас в секции WHERE указаны два условия, разделённые оператором OR то если выполняется либо одно, либо оба условия строка будет возвращена. Если не удовлетворяется ни одно из условий – строка исключается из выдачи. Запрос для поиска сотрудников с фамилией, начинающейся с символа “B” или с комиссией больше 35% выглядит так
select first_name, last_name, commission_pct, hire_date from employees where first_name like ‘B%’ or commission_pct > 0.35;
Условия разделены оператором OR. Все сотрудники фамилия которых начинается с “B” буду в результате запроса, несмотря на их значение комиссии, даже если значение COMISSION_PCT это NULL. Плюс все строки в которых значение комиссии больше 35 % будут в результате (вне зависимости от первого символа их фамилии). Дополнительные условия можно добавлять используя оператор OR. Чем больше условий OR вы добавляете тем менее строгой становится выборка и тем больше строк может быть возрващено результатом.
Оператор NOT
Оператор NOT (логическое отрицание) отменяет операторы условия. То есть данные должны удовлетворять логически обратному условию для попадания в результат. Булевы операторы тогда записываются как показано в таблице 9-4
Оператор NOT меняет сравнение вне зависимости от оператора сравнения. Будь то оператор равенства, неравенства, вхождения в диапазон или группу и т.п.
Правила приоритета
Арифметические операторы, символьные операторы, операторы сравнения и логические выражения рассматривались в контексте предиката WHERE. Но как эти операторы работают друг с другом? Иерархия приоритетов показана в таблице 9-5. Операторы одинакового уровня приоритета выполняются слева направо если они встречаются вместе в выражении. Когда оператор NOT изменяет оператор LIKE, IS NULL или IN, то приоритет оператора остаётся таким же как и без NOT.
Рассмотрим запрос в котором используются разные операторы
select
last_name,salary,department_id,job_id,commission_pct
from
employees
where
(last_name like ‘%a%’ and salary > department_id * 200
or
job_id in (‘MK_REP’,’MK_MAN’)) and commission_pct is not null;
Столбцы LAST_NAME, SALARY, DEPARTMENT_ID, JOB_ID и COMISSION_PCT проецируются из таблицы EMPLOYEES основываясь на двух условиях. Вначале проверяется условие что в фамилии есть символ “a” и сравнивается значение с номером департамента умноженным на двести. Умножение выполняется перед сравнением, так как приоритет умножения выше чем сравнения на неравенство. Затем выбираются строки где значение JOB_ID или MK_MAN или MK_REP и значение COMISSION_PCT не NULL. Для строки, чтобы быть включенной в результат этого запроса достаточно удовлетворять либо первому либо второму условию. Изменение порядка операторов меняет смысл запроса. Рассмотрим пример
select
last_name,salary,department_id,job_id,commission_pct
from
employees
where
last_name like ‘%a%’ and salary > department_id * 100 and commission_pct is not null
or
job_id = ‘MK_MAN’;
В этом запросе два составных условия. Первое условие проверяет фамилию на наличие символа “a” плюс значение зарплаты должно быть больше чем номер департамента умноженный на сто и значение комисси должно быть указано. Второе условие требует чтобы значение JOB_ID было MK_MAN. Строка включается в результат если выполняются либо оба, либо хотя бы одно условие.
Exam tip
Булевы операторы OR и AND позволяют использовать несколько условий в предикате WHERE в то время как оператор NOT меняет оператор сравнения и может быть использован несколько раз в одном условии. Операторы равенства, неравенства, BETWEEN, IN и LIKE сравнивают два операнда в одном условии. Только один оператор сравнения используется в одном условии.