Операторы и выражения в SQLite

При написании SQL-запросов синтаксис некоторых операторов или выражений может отличаться в разных базах данных. В SQLite тоже есть некоторые особенности, о которых я напишу в этой статье. Для получения информации использовался первоисточник: https://www.sqlite.org/lang_expr.html

Операторы

SQLite понимает следующие операторы в порядке от наивысшего к низшему приоритету:

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IS   IS NOT   IN   LIKE   GLOB   MATCH   REGEXP
AND   
OR
NOT

В SQLite операторы сравнения двух значений могут быть записаны в 2 вариантах. Равенство может быть записано как = так и ==. А неравенство может быть записано != и <>.

Оператор || - означает конкатенацию (объединение) строк. При этом, если одно из значений число, оно будет преобразовано в текст и объединено с другим значением. Например, select 1||1 вернет 11.

Оператор % - означает определение остатка от деления одного значения на другое. Например, select 8%3& вернет 2.

Операторы IS и IS NOT - означают то же самое, что и операторы = и != за исключением случаев, когда одно или оба значения NULL. В этом случае если оба значения NULL, тогда IS вернет 1 (истина), а IS NOT вернет 0 (ложь). Если одно из значений NULL а другое отличное от NULL, тогда IS вернет 0 (ложь), а IS NOT вернет 1 (истина). Эти операторы используются для сравнения значений с NULL.

Оператор LIKE - осуществляет сравнение по шаблону. Значение справа от оператора LIKE должно содержать шаблон, а значение слева - строку, которая будет сравниваться. Символ процента (%) в шаблоне означает совпадение с любым количеством (в т.ч. и ноль) символов. Символ нижнее подчеркивание (_) - означает один любой символ. Замечание: оператор LIKE различает верхний и нижний регистр только для ASCII символов. Это означает, что 'z' LIKE 'Z' - истина, а 'я' LIKE 'Я' - ложь. Оператор LIKE можно сделать чувствительным к регистру использовав PRAGMA case_sensitive_like

Оператор GLOB - аналогичен оператору LIKE, но использует синтаксис как функция glob в Unix.

Оператор BETWEEN логически эквивалентен двум парным сравнениям. Выражение "x BETWEEN y AND z" будет эквивалентно "x>=y AND x<=z" за исключением только того, что значение x будет вычисляться только один раз.

Выражение CASE

Выражение (оператор) CASE имеет тот же смысл как IF THEN ELSE в языках программирования. Обычно используется для получение разных значений из разных полей базы в зависимости от условия, либо для разной сортировки в зависимости от условия. Выражение, записанное между CASE и WHEN называется "базовым". Есть два варианта использования CASE с базовым значением и без него.

Если CASE используется без базового значения, тогда будут вычисляться все выражения после каждого WHEN. Сравнение происходит в порядке слева направо. Если ни одно сравнение WHEN не дело истину (true), тогда будет возвращено значение после ELSE. Пример:

SELECT CASE WHEN 1=2 THEN 'a' WHEN 1=1 THEN 'b' ELSE 'c' END  -  вернет значение "b".

Если CASE используется с базовым значением, тогда базовое значение будет вычислено один раз и результат будет сравниваться с каждым выражением после WHEN. Если ни одно из значений после WHEN не совпало с базовым и есть оператор ELSE, будет возращено значение после ELSE. Если оператора ELSE нет и ни одно из значений WHEN не совпало с базовым, будет возвращено NULL. Пример:

SELECT 1 CASE WHEN 2 THEN 'a' WHEN 1 THEN 'b' ELSE 'c' END  -  вернет значение "b".

Если базовое значение будет NULL, тогда результат всегда будет значением после ELSE, если оно есть или NULL, если нет.

Еще несколько примеров оператора условия CASE можете посмотреть здесь.

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

Операторы IN и NOT IN сравнивают значение слева от оператора со списком значений справа от него. В случае, если в качестве списка значений используется подзапрос, тогда он должен возвращать столько же колонок, что и имеет выражение слева. Если в правой части написано название таблицы, тогда это будет эквивалентно select * from table (table - название таблицы).

Оператор EXISTS

Оператор EXISTS всегда возвращает одно из двух значений 0 (ложь) или 1 (истина). Если в правом выражении используется подзапрос, возвращающий одно или более значений, тогда оператор EXISTS вернет 1 (истина). Если подзапрос в выражении справа не возвращает ни одной строки, тогда EXISTS вернет 0 (ложь).

Количество возвращаемых подзапросом столбцов не имеет значения. Так же не имеет значения есть ли в возвращаемых столбцах NULL значения.

Выражение CAST

Выражение CAST(X AS Y) позволяет преобразовать (конвертировать) данные X в данные другого типа Y. Если значение X равно NULL, результат тоже будет NULL. Преобразование возможно в следующие типы данных: