Таблицы истинности операторов 3VL

X	NOT X		AND	TRUE	FALSE	UNKNOWN
TRUE	FALSE		TRUE	TRUE	FALSE	UNKNOWN
FALSE	TRUE		FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN		UNKNOWN	UNKNOWN	FALSE	UNKNOWN

 

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

 

IS	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	FALSE
FALSE	FALSE	TRUE	FALSE
UNKNOWN	FALSE	FALSE	TRUE

Рассмотрим теперь элементарные предикаты. Многие из них сравнивают множества значений, а потому используют так называемые конструкторы значений строк – заключённые в круглые скобки списки выражений для вычисления значений. Однако для простоты мы здесь будем излагать определения в духе стандарта SQL1, допускающего сравнения только отдельных значений. Кроме того, мы обсудим здесь не все перечисленные выше предикаты, а только важнейшие из них. Полные определения предикатов SQL2 можно найти в [4].

Предикаты сравнения сравнивают два значения[12].

предикат_comparision::=

выражение оператор_сравнения выражение

Здесь выражение есть любое допустимое выражение для вычисления значения.

оператор_сравнения::= = | < | > | <= | >= | <>

Выражения должны производить сравнимые, т.е. принадлежащие совместимым типам данных, значения. Если хотя бы одно из них приняло значение NULL, то любой предикат сравнения примет значение UNKNOWN.

Например, предикат X <> Y примет значение TRUE для X и Y, равных ‘Томск’ и ‘Яя’ соответственно. Однако сравнение NULL <> NULL даст результат UNKNOWN, несмотря на то, что никакие два NULL -значения не совпадают.

Предикат BETWEEN проверяет, попадает ли значение в заданную область.

предикат_between::=

выражение [NOT]BETWEEN выражение AND выражение

Предикат

exp1 BETWEEN exp2 AND exp3

равносилен предикату

exp1 >= exp2 AND exp1 <= exp3.

Значения предиката в зависимости от значений проверяемого выражения и границ диапазона нетрудно найти по таблицам истинности 3VL.

Примеры:

ПРЕДИКАТ	ЗНАЧЕНИЕ
5 BETWEEN 3 AND 8	TRUE
5 BETWEEN 6 AND NULL	FALSE
5 BETWEEN NULL AND 8	UNKNOWN

Предикат IN проверяет, принадлежит ли значение заданному множеству значений.

предикат_in::= выражение [NOT] IN (выражение., ..)

| подзапрос

В этой синтаксической форме подзапрос производит таблицу с единственным столбцом[13].

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

exp IN (exp1, exp2,…, expN);

exp = exp1 OR exp = exp2 OR … OR exp = expN.

Примеры:

ПРЕДИКАТ	ЗНАЧЕНИЕ
5 IN (3, 5, 2)	TRUE
5 IN (3, 7, 2)	FALSE
5 IN (3, NULL, 2)	UNKNOWN
5 IN (NULL, 5, NULL)	TRUE

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

предикат_like::=

выражение_для_вычисления_строки [NOT]LIKE

выражение_для_вычисления_строки [ESCAPE символ ]

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

В простейшем случае предикат LIKE аналогичен предикату сравнения для символьных строк, но не совпадает с ним полностью. Предикат сравнения автоматически дополняет более короткую строку концевыми пробелами до выравнивания длин сравниваемых строк. Предикат LIKE этого не делает. Поэтому, например, значения первого и второго из нижеприведённых предикатов – TRUE, а значение третьего – FALSE.

‘Томск’ = ‘Томск ’;

‘Томск’ LIKE ‘Томск’;

‘Томск ’ LIKE ‘Томск’.

В общем случае образец может содержать знаки шаблона (трафаретные символы):

· символ подчёркивания ‘_’ заменяет любой один символ в проверяемом значении;

· символ процента ‘%’ заменяет подстроку символов любой (в том числе и нулевой) длины.

При использовании знаков шаблона предикат отыскивает в проверяемом значении подстроки, явно указанные в шаблоне. Например, следующие предикаты принимают значение TRUE:

‘Томск’ LIKE ‘_омск’; ‘Омск’ LIKE ‘%омск’; ‘Атомск’ LIKE ‘%омск’.

Однако предикат ‘омск’ LIKE ‘_омск’ имеет значение FALSE.

Если поисковая подстрока включает символы ‘_’ или ‘%’ как литералы, то действие их как знаков шаблона следует отменить. Для этого нужно определить управляющий символ с помощью предложения ESCAPE и включить его в поисковую строку перед тем символом шаблона, который должен трактоваться как литерал.

Пример. Предикат X LIKE ‘__ |% % ’ ESCAPE ‘ | ’ приметзначение TRUE на любой последовательности символов X, начинающейся двумя любыми символами, имеющей в третьей позиции символ ‘%’, в четвёртой – пробел, а далее – всё, что угодно, в том числе и ничего. Символ ‘%’ в первом вхождении рассматривается как литерал, поскольку перед ним помещён управляющий символ ‘|’, а во втором – как знак шаблона.

Предикат LIKE принимает истинностные значения в соответствии со следующими правилами.

· Если проверяемое значение или образец, или управляющий символ есть NULL, то значение предиката UNKNOWN.

· В противном случае, если проверяемое значение и образец эквивалентны в смысле определения предиката сравнения, то значение предиката TRUE.

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

· Если не выполнено ни одно из перечисленных условий, то значение предиката FALSE.

В нашем примере «истинными» будут признаны строки:

'65% белков’;'30% жиров'; '05% углеводов',

а «ложными» – строки:

'содержит 65% белков’; '30%жиров'; '5% углеводов'.

Предикат NULL предназначен для обнаружения и обработки неопределённых значений. Значения NULL невозможно локализовать с помощью предиката сравнения, так как любое сравнение с участием NULL даёт результат UNKNOWN. Поэтому значение предиката X = NULL не зависит от значения X. Если необходимо найти неопределённое значение столбца, то следует использовать конструкцию

предикат_null::= выражение IS [NOT] NULL

Предикат X IS NULL примет значение TRUE, если значение Xне определено и FALSE – в противном случае. Значение UNKNOWN невозможно.

Предикат QUANTIFIRED COMPARISION является специальным типом предиката (квантифицированного) сравнения.

предикат_quantifired_comparision::=

выражение оператор_сравнения

{ALL | ANY | SOME} подзапрос

Здесь подзапрос производит таблицу с единственным столбцом.[14] Ключевые слова (кванторы) ANY и SOME – синонимы. Для SOME справедливо то и только то, что будет сказано далее об ANY.

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

exp θ ALL подзапрос;

exp θ val1 AND exp θ val2 AND … AND exp θ valN.

exp θ ANY (подзапрос);

exp θ val1 OR exp θ val2 OR … OR exp θ valN.

Здесь valJесть J-тая строка таблицы, произведённой подзапросом (значение столбца), а символом θ обозначен оператор сравнения.

Если подзапрос производит пустую таблицу, то предикат с квантором ALL примет значение TRUE, а с квантором ANY – значение FALSE независимо от использованного оператора сравнения.

Примеры. В предикатах, приведённых ниже, в скобках указаны не подзапросы, а их результаты. Мы используем это отклонение от синтаксиса для пояснения смысла конструкции.

ПРЕДИКАТ	ЗНАЧЕНИЕ
5 >= ALL (3, 5, 2)	TRUE
5 > ALL (3, 5, 2)	FALSE
5 >= ALL (3, NULL, 2)	UNKNOWN
5 > ANY (3, 5, 2)	TRUE
5 < ANY (3, 5, 2)	FALSE
5 >= ANY (3, NULL, 2)	TRUE
5 >= ANY (6, NULL, 9)	UNKNOWN

Предикат EXISTS предназначен для проверки наличия строк в таблице, произведенной запросом.

предикат_exists::= EXISTS подзапрос [15]

Возможны лишь два значения этого предиката:

· TRUE, если таблица, произведённая подзапросом, содержит хотя бы одну строку;

· FALSE в противном случае.

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

Мы познакомились здесь не со всеми перечисленными в начале раздела предикатами, а только с важнейшими из них. Приведённые определения соответствуют стандарту SQL1. Стандарт SQL2 включает их как часть. Полные определения предикатов SQL2 можно найти в [4].

Продолжим теперь обсуждение предложений оператора SELECT.

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

Предложение

WHERE предикат

реализует операцию селекции или ограничения по условию [2, с. 74]. Операндом является F -таблица, построенная предложением FROM.

Выполняя селекцию, исполняющая система на каждой строке F -таблицы вычисляет значение предиката – условия отбора. Если на текущей строке предикат принял значение FALSE или UNKNOWN, то строка удаляется из рабочего буфера. В результате в буфере остаётся W -таблица, содержащая те и только те строки операнда, на которых предикат принял значение TRUE.

Пример 1. Получить всю информацию о поставщиках, имеющих статус выше 90.

SELECT *

FROM S

WHERE St > 90;

В результате обработки предложения FROM в рабочем буфере будет создана неименованная таблица, эквивалентная таблице S (см. приложение А).

Snum	Snam	St	Ci
S8	Владимир		Томск
S2	Николай		Асино
S5	Константин		Яя
S4	Петр		Рио-де-Жанейро
S3	Григорий		Яя
S9	Егор		Яя
S7	Сергей		Асино
S1	Иван		Томск
S6	Иван		Лесото

В процессе обработки предложения будут удалены строки, содержащие в столбце St значения, меньшие или равные 90. Вот результат запроса:

Snum	Snam	St	Ci
S5	Константин		Яя
S9	Иван		Лесото
S9	Егор		Яя
S1	Иван		Томск

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

SELECT Jnum, Jnam

FROM J

WHERE Ci = ‘Томск’;

Jnum	Jnam
J3	дисплей
J6	клавиатура

Пример 3. Получить значения номеров поставщиков, поставляющих деталь P1 для изделия J1.

SELECT DISTINCT Snum

FROM SPJ

WHERE Jnum = ‘J1’ AND Pnum = ‘P1’;

SNUM

S8

Пример 4. Получить сведения о поставках, выполненных поставщиками S3, S5, S4, S2.

SELECT *

FROM SPJ

WHERE Snum BETWEEN ‘S2’ AND ‘S5’;

Snum	Pnum	Jnum	Qt
S2	P3	J7
S2	P9	J4
S3	P4	J8
S3	P9	J9
S4	P4	J1
S5	P3	J8

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

Соединения таблиц

Соединения таблиц в SQL2 могут быть реализованы двумя способами:

· путём использования встроенной операции соединения в предложении FROM;

· путём явного указания условий соединения в предикате предложения WHERE.

Здесь мы обсудим встроенные операции соединения, используемые в предложении FROM [16].

соединение::= перекрёстное_соединение

| естественное_соединение

| соединение_по_именам_столбцов

| соединение_по_условию

| UNION_соединение

Замечание. Любая из перечисленных структур замещает имя таблицы в предложении FROM.

Перекрёстное соединение реализует операцию расширенного прямого произведения РА [2, с. 74].

перекрёстное_соединение::=

таблица_А CROSS JOIN таблица_В

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

Пример. Пусть в нашей учебной БД существуют следующие реализации таблиц P и J:

Таблица P (Детали) Таблица J (Изделия)

Pnum	Pnam	We	Co	Ci		Jnum	Jnam	Ci
P1	корпус		белый	Томск		J1	процессор	Асино
P3	кнопка		красный	Томск		J6	клавиатура	Томск
P5	панель		серый	Асино		J7	мышь	NULL
P2	разъём		чёрный	Яя

P CROSS JOIN J (Перекрёстное соединение)

Pnum	Pnam	We	Co	P.Ci	Jnum	Jnam	J.Ci
P1	корпус		белый	Томск	J1	процессор	Асино
P1	корпус		белый	Томск	J6	клавиатура	Томск
P1	корпус		белый	Томск	J7	мышь	NULL
P3	кнопка		красный	Томск	J1	процессор	Асино
P3	кнопка		красный	Томск	J6	клавиатура	Томск
P3	кнопка		красный	Томск	J7	мышь	NULL
P5	панель		серый	Асино	J1	процессор	Асино
P5	панель		серый	Асино	J6	клавиатура	Томск
P5	панель		серый	Асино	J7	мышь	NULL
P2	разъём		чёрный	Яя	J1	процессор	Асино
P2	разъём		чёрный	Яя	J6	клавиатура	Томск
P2	разъём		чёрный	Яя	J7	мышь	NULL

Никакого семантического значения этот набор данных не имеет. Как правило, он используется для построения других таблиц. Заметим, что предложения FROM A, B и FROM A CROSS JOIN B эквивалентны, т.е. производят временные таблицы, совпадающие с точностью до порядка строк.

Операция естественного соединения в SQL может быть реализована в четырёх разновидностях.

естественное_соединение::=

таблица_А [NATURAL] [ тип_соединения ] JOIN таблица_В

тип_соединения::= INNER | {{LEFT | RIGHT | FULL} [OUTER]}

Ключевое слово NATURAL смысловой нагрузки не несёт и может быть опущено. Соединяемые таблицы должны иметь одноимённые столбцы одного типа и одинаковой длины.

Естественное соединение может быть внутренним (INNER) или внешним (OUTER). По умолчанию используется INNER.

Операция INNER JOIN производит аналог естественного соединения РА. Производная таблица содержит все столбцы операндов. Одноимённые столбцы включаются однократно. Строками производной таблицы являются объединения таких строк операндов, в которых значения всех одноимённых столбцов совпадают.

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

P INNER JOIN J (Внутреннее естественное соединение)

Pnum	Pnam	We	Co	Ci	Jnum	Jnam
P5	панель		серый	Асино	J1	процессор
P3	кнопка		красный	Томск	J6	клавиатура
P1	корпус		белый	Томск	J6	клавиатура

В этой таблице отсутствуют сведения о детали P2 и изделии J7, так как описывающие их строки не имеют пар по столбцу Ci в таблицах J и P соответственно.

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

Пример. Пусть необходимо получить полные сведения обо всех деталях и об изделиях, размещенных в тех же городах. Как мы видели, результат операции INNER JOIN не содержит всех ожидаемых данных. Нужная таблица может быть построена с помощью операции внешнего (OUTER) соединения, например, левого (LEFT).

P LEFT OUTER JOIN J (Левое внешнее соединение)

Pnum	Pnam	We	Co	Ci	Jnum	Jnam
P1	корпус		белый	Томск	J6	клавиатура
P2	разъём		чёрный	Яя	NULL	NULL
P3	кнопка		красный	Томск	J6	клавиатура
P5	панель		серый	Асино	J1	процессор

Аналогично этому, все сведения об изделиях и о деталях, размещённых в тех же городах, можно получить с помощью операции правого (RIGHT) внешнего соединения.

P RIGHT OUTER JOIN J (Правое внешнее соединение)

Pnum	Pnam	We	Co	Ci	Jnum	Jnam
P5	панель		серый	Асино	J1	процессор
P3	кнопка		красный	Томск	J6	клавиатура
P1	корпус		белый	Томск	J6	клавиатура
NULL	NULL	NULL	NULL	NULL	J7	мышь

Легко убедиться в том, что один и тот же результат можно получить как с помощью правого, так и с помощью левого внешнего соединения. Например, выражения J RIGHT JOIN PиP LEFT JOIN Jопределяют эквивалентные таблицы.

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

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

P FULL OUTER JOIN J (Полное внешнее соединение)

Pnum	Pnam	We	Co	Ci	Jnum	Jnam
NULL	NULL	NULL	NULL	NULL	J7	мышь
P1	корпус		белый	Томск	J6	клавиатура
P2	разъём		чёрный	Яя	NULL	NULL
P3	кнопка		красный	Томск	J6	клавиатура
P5	панель		серый	Асино	J1	процессор

Приведём алгоритмическое определение операции полного внешнего соединения.

1. Вычислить внутреннее соединение.

2. Для каждой строки левой таблицы, если она не представлена во внутреннем соединении, вычислить строку соединения, заполнив столбцы, соответствующие левой таблице, значениями текущей строки, а столбцы, соответствующие правой таблице, NULL -значениями.

3. Для каждой строки правой таблицы, если она не представлена во внутреннем соединении, вычислить строку соединения, заполнив столбцы, соответствующие правой таблице, значениями текущей строки, а столбцы, соответствующие левой таблице, NULL -значениями.

4. Добавить результаты шагов 2 и 3 к результату шага 4.

5. Определения левого и правого внешних соединений можно получить из вышеприведённого, опустив шаг 3 или 2 соответственно.

UNION-соединение можно трактовать как реляционную разность [2, с. 73] полного внешнего и внутреннего соединений. Синтаксис операции следующий:

UNION_соединение::= таблица_А UNION JOIN таблица_В

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

P UNION JOIN J (UNION-соединение)

Pnum	Pnam	We	Co	Ci	Jnum	Jnam
NULL	NULL	NULL	NULL	NULL	J7	мышь
P2	разъём		чёрный	Яя	NULL	NULL

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

Пример. Пусть в нашей БД существует следующая таблица, хранящая сведения о поставках деталей для изделий:

Таблица SPJ (Поставки)

Snum	Pnum	Jnum	Qt
S1	P1	J6
S1	P2	J6

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

P INNER JOIN J INNER JOIN SPJ

Pnum	Pnam	We	Co	Ci	Jnum	Jn	Snum	Qt
P1	корпус		белый	Томск	J6	клавиатура	S1

Результат не содержит всех ожидаемых сведений. Утрачены данные о поставке детали P2. Это произошло потому, что соединения выполнялись по столбцам Pnum, Jnum, Ci. На самом деле соединение по столбцу Ci не нужно.

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

соединение_по_именам_столбцов::=

таблица_А [ тип_соединения ] JOIN таблица_В

USING(имя_столбца., ..)

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

Пример. В условиях предыдущего примера правильное выражение соединения может быть таким:

( P INNER JOIN SPJ )INNER JOIN J USING ( Jnum )

или таким:

P INNER JOIN ( SPJ INNER JOIN J )USING ( Pnum )

Эти выражения произведут таблицу, эквивалентную показаной ниже.

Pnum	Pnam	We	Co	P.Ci	Snum	Jnum	Qt	Jn	J.Ci
P1	корпус		белый	Томск	S1	J6		клавиатура	Томск
P2	разъём		чёрный	Яя	S1	J6		клавиатура	Томск

Операция соединения по условию использует следующий синтаксис:

соединение_по_условию::=

таблица_А [ тип_соединения ] JOIN таблица_В

ON предикат

предикат – логическое выражение, содержащее ссылки на столбцы соединяемых таблиц и принимающее значения TRUE, FALSE или UNKNOWN (см. п. 6.2.6). Значение предиката вычисляется на значениях ссылочных столбцов в строке расширенного прямого произведения таблиц (строке-кандидате соединения).

Соединяемые таблицы должны иметь сравнимые столбцы. Производимая таблица включает все столбцы обоих операндов, а состав её строк зависит от типа соединения.

Внутреннее (INNER) соединение можно рассматривать как реализацию операции соединения по условию РА. Оно производит только такие конкатенации строк операндов, на которых предикат принимает значение TRUE.

Пример. Для того чтобы получить сведения о деталях и изделиях, размещённых в различных городах, следует использовать операцию внутреннего соединения по условию P.Ci <> J.Ci:

P INNER JOIN J ON P.Ci <> J.Ci

Pnum	Pnam	We	Co	P.Ci	Jnum	Jn	J.Ci
P1	корпус		белый	Томск	J1	процессор	Асино
P2	разъём		чёрный	Яя	J1	процессор	Асино
P3	кнопка		красный	Томск	J1	процессор	Асино
P2	разъём		чёрный	Яя	J6	клавиатура	Томск
P5	панель		серый	Асино	J6	клавиатура	Томск

Определения внешних соединений по условию аналогичны определениям естественных внешних соединений. Сформулируйте их самостоятельно.

Читатель легко убедится в том, что в условиях рассматриваемого примера соединение P LEFT JOIN J ON P.Ci <> J.Ci совпадает с внутренним. Соединение P RIGHT JOIN J ON P.Ci <> J.Ci будет содержать дополнительную строку – сведения об изделии J7.

Понятие соединения в SQL2 шире аналогичного понятия в классической реляционной алгебре. Определяя внешние соединения и соединения USING и UNION, авторы стандарта руководствовались потребностями практического применения языка. Действительно, как мы увидим далее, использование встроенных операторов соединения позволяет значительно упростить логику запросов на выборку данных.