S-JIS[2024-08-18/2024-11-13]

Tsurugi SQLの式

TsurugiのSQLで使える式（演算子）のメモ。

算術式
比較式
真偽式 [/2024-11-13]
cast [/2024-09-05]
case [2024-08-31]

GitHub
- Available SQL features in Tsurugi
- テーブル定義で使用可能なデータ型と値の範囲
- SQL実行計画表現におけるスカラー式とデータ型 …異なるデータ型を合わせる規則（変換・昇格）
SQLのデータ型（およびリテラル）
SQL関数

算術式

計算に使う演算子、および（算術式ではないが）文字列に使う演算子。

演算子	説明	例
`+`	正符号	`+1`
`-`	負符号	`-1`
`+`	加算	`1 + 2`
`-`	減算	`2 - 2`
`*`	乗算	`3 * 2`
`/`	除算	`6 / 2`
`%`	余算	`6 % 2`
`\|\|`	文字列結合	`'abc' \|\| 'def'`

nullと演算すると、結果はnullになる。

tgsql> select 1 + null from test;
[@#0: INT8]
[null]
(1 row)

tgsql> select 'abc' || null from test;
[@#0: CHARACTER]
[null]
(1 row)

比較式

値を比較してbooleanを返す演算子。

演算子	ver	説明	例
`=`	1.0.0-BETA1	等しい（eq）	`v = 1`
`<>`	1.0.0-BETA1	等しくない（ne）	`v <> 1`
`<`	1.0.0-BETA1	より小さい（lt）	`v < 1`
`>`	1.0.0-BETA1	より大きい（gt）	`v > 1`
`<=`	1.0.0-BETA1	以下（le）	`v <= 1`
`>=`	1.0.0-BETA1	以上（ge）	`v >= 1`
`between`	1.0.0-BETA6	指定された範囲に入っているかどうか	`v between 1 and 3`
`in`	1.0.0-BETA6	値リストに含まれているかどうか	`v in (1, 2, 3)`

nullと比較すると、結果はunknownになる。

unknownはwhereやhavingで判定に使用するとfalse扱いだが、Tsurugiでの値としてはnullである。

tgsql> select * from test where 1 = null;
[c: INT4]
(0 rows)

tgsql> select 1 = null, 1 <> null from test;
[@#0: BOOLEAN, @#1: BOOLEAN]
[null, null]
(1 row)

※「null = null」や「null <> null」もunknownになる。nullかどうかを判定したい場合はis null・is not nullを使用する。

between

betweenは、指定された範囲に入っているかどうかを判定する。（Tsurugi 1.0.0-BETA6以降）

値 [not] between [symmetric | asymmetric] 開始値 and 終了値

概ね、「開始値 <= 値 and 値 <= 終了値」と同じ。

値・開始値・終了値のいずれかがnullの場合、betweenの結果はunknownになる。

基本的には、betweenの終了値には開始値以上の値を指定する。

symmetricを指定すると、開始値と終了値の大小関係とは無関係に判定する。
すなわち、「(開始値 <= 値 and 値 <= 終了値) or (開始値 >= 値 and 値 >= 終了値)」と同等。

symmetric/asymmetricを省略した場合はasymmetricと同じ。（開始値≦終了値である必要がある）

tgsql> select 2 between            1 and 3,
     |        2 between asymmetric 1 and 3,
     |        2 between symmetric  1 and 3
     | from test;
[@#0: BOOLEAN, @#1: BOOLEAN, @#2: BOOLEAN]
[true, true, true]
(1 row)

tgsql> select 2 between            3 and 1,
     |        2 between asymmetric 3 and 1,
     |        2 between symmetric  3 and 1
     | from test;
[@#0: BOOLEAN, @#1: BOOLEAN, @#2: BOOLEAN]
[false, false, true]
(1 row)

in

inは、指定された値一覧に入っているかどうかを判定する。

使途	ver	構文	例	備考
値リスト	1.0.0-BETA6	値 [not] in (値1, 値2, …)	`v in (1, 2, 3)`	値がnullあるいは値リストにnullが含まれているとunknownになる。
複数列リスト	未実装		`(v0, v1) in ((1, 2), (3, 4))`
サブクエリー	未実装		`v in (select c from test)`

真偽式

booleanを扱う演算子。

演算子	ver	説明	構文	例	備考
`not`		否定	not 真偽値	`not (v = 1)`	trueの場合はfalse、falseの場合はtrue、unknownの場合はunknown。
`and`		かつ	真偽値1 and 真偽値2	`(v0 = 1) and (v1 = 2)`	両方ともtrueならtrue、どちらかがfalseならfalse。それ以外はunknwon。[/2024-11-13]
`or`		または	真偽値1 or 真偽値2	`(v = 1) or (v = 2)`	どちらかがtrueならtrue、両方ともfalseならfalse。それ以外はunknwon。[/2024-11-13]
`is null`		nullかどうか	値 is [not] null	`v is null`
`is true`	1.0.0-BETA6	trueかどうか	値 is [not] true	`v is true`
`is false`	1.0.0-BETA6	falseかどうか	値 is [not] false	`v is false`
`is unknown`	1.0.0-BETA6	unknownかどうか	値 is [not] unknown	`v is unknown`	unknownはTsurugiではnullで表されるので、動作上はis nullと同じ。

is系演算子は、必ずtrue/falseのいずれかを返す。（nullを指定してもunknownを返さない）

Tsurugi 1.0.0以前は、and/orに対するunknownの扱いが間違っていて、片方がunknwonならunknwonになっていた。[2024-11-13]

select true    and true, true    and false, true    and unknown,
       false   and true, false   and false, false   and unknown,
       unknown and true, unknown and false, unknown and unknown
from test
limit 1;

↓

[true, false, null, false, false, null,  null, null,  null]	← Tsurugi 1.0.0（誤）
[true, false, null, false, false, false, null, false, null]	← Tsurugi 1.1.0（正）

※null（unknown）はfalse扱いなので、andでは特に困らないかも

select true    or true, true    or false, true    or unknown,
       false   or true, false   or false, false   or unknown,
       unknown or true, unknown or false, unknown or unknown
from test
limit 1;

↓

[true, true, null, true, false, null, null, null, null]	← Tsurugi 1.0.0（誤）
[true, true, true, true, false, null, true, null, null]	← Tsurugi 1.1.0（正）

cast

castは、値を他のデータ型に変換する。
ただし、現時点では、まだ変換できないデータ型も多い。（文字列から他のデータ型への変換はおおよそ可能）

cast(値 as データ型)

Tsurugi 1.0.0-BETA5から、（PostgreSQLのような）「::」を使ったキャストも出来る。[2024-09-04]

値::データ型

例

select cast(1 as varchar(10)) from test;

select 1::varchar(10) from test;

decimalへのキャスト

decimalへキャストする場合、値がdecimalに収まり切らない（最大値を超える）と、最大値になる。[2024-09-05]

例えば100以上の値をdecimal(3,1)（整数部が2桁）にキャストすると、decimal(3,1)の最大値である99.9になる。

tgsql> select cast(123 as decimal(3,1)), cast(-123 as decimal(3,1)) from test;
[@#0: DECIMAL, @#1: DECIMAL]
[99.9, -99.9]
(1 row)

※テーブルのカラムがdecimal(3,1)のとき、insert文やupdate文で100以上の値を入れようとするとエラーになる。最大値に張り付くのはキャストの仕様である。

case

case式は、条件に応じた値を返す。[2024-08-31]

case 値
  when 比較値1 then 返却値1
  when 比較値2 then 返却値2
  …
 [else 返却値]
end

case
  when 条件1 then 返却値1
  when 条件2 then 返却値2
  …
 [else 返却値]
end

elseが無い場合、どの条件にも合致しなかったらnullを返す。

例

create table test (n int);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);

select
  n,
  case n % 3
    when 0 then 'Fizz'
    else cast(n as varchar)
  end fizz,
  case n % 5
    when 0 then 'Buzz'
    else cast(n as varchar)
  end buzz
from test
order by n;

↓

[n: INT4, fizz: CHARACTER, buzz: CHARACTER]
[1, 1, 1]
[2, 2, 2]
[3, Fizz, 3]
[4, 4, 4]
[5, 5, Buzz]
[6, Fizz, 6]
…
[15, Fizz, Buzz]
[16, 16, 16]

select
  n,
  case
    when n % 15 = 0 then 'FizzBuzz'
    when n %  3 = 0 then 'Fizz'
    when n %  5 = 0 then 'Buzz'
    else cast(n as varchar)
  end fizzbuzz
from test
order by n;

↓

[n: INT4, fizzbuzz: CHARACTER]
[1, 1]
[2, 2]
[3, Fizz]
[4, 4]
[5, Buzz]
[6, Fizz]
…
[15, FizzBuzz]
[16, 16]

Tsurugi SQLへ戻る / Tsurugiへ戻る / 技術メモへ戻る

メールの送信先：ひしだま