演算子
演算子とは、演算内容を指示する記号のことです。SQLの中で使用される式は、『オペランド』と『演算子（オペレータ）』を組み合わせたものです。オペランドとは、演算の対象になる値やフィールド指定などを総称したもので、このオペランドを操作する記号が、演算子です。
次の式では、10と100がオペランド、+ が演算子に当たります。
演算子一覧
| 種類 | 演算子 |
|---|---|
| 算術演算子 | +、-、*、/ |
| 比較演算子 | =、<>、!=、>、<、<=、>= |
| IS NULL / IS_NOT_NULL | |
| IN / NOT IN、 EXISTS / NOT EXISTS |
|
| BETWEEN AND | |
| LIKE | |
| 論理演算子 | AND / &&、 OR / ||、 NOT / ! |
| 日付/時刻演算子 | +、-#- |
| 集合演算子 | UNION (ALL) |
フィールドの値をオペランドにする
SQLでは、文字列や数字といったリテラル値以外に、テーブルのフィールドなどを指定することができます。たとえば、下記のクエリではgoodsというテーブル名の[price]フィールドをオペランドとして指定しています。
SELECT price * 10 FROM goods;
算術演算子
算術演算子の概要
演算子には算術演算子、比較演算子、論理演算子、ビット演算子といった種類がありますが、その中でも算術演算子は一般的にもっともよく使われる演算子です。算術演算子は、加算や減算を行うための演算子です。
SELECT構文で10に100を加算する式は下記のようになります。
SELECT 10 + 100;
出力結果
-> 110
+ や - などの演算をあらわす記号が演算子、特に上記のような計算に使う演算子を『算術演算子』と呼びます。
+, -, *, / 加減剰余
加減剰余の四則演算には、加算（ + ）、減算（ - ）、乗算（ * ）、除算（ / ）の演算子があります。
算術演算子の一覧
| 演算子 | 説明 |
|---|---|
| + | 足し算 |
| - | 引き算 |
| * | 掛け算 |
| / | 割り算 |
| % | 剰余 |
算術演算子
+演算子
足し算
+演算子はオペランドを加算します。
SELECT 2 + 10;
出力結果
-> 12
-演算子
引き算
-演算子はオペランドを減算します。
SELECT 2 - 10;
出力結果
-> -8
*演算子
掛け算
*演算子はオペランドを乗算します。
SELECT 2 * 10;
出力結果
-> 20
/演算子
割り算
/演算子はオペランドを除算します。
SELECT 2 / 10;
出力結果
-> 0.20
0で割ったり、整数で64ビットを超えた場合など、エラーの場合はNULLを返します。
SELECT 2 / 0;
出力結果
-> NULL
%演算子
剰余
%演算子はオペランドを除算した余りの値を返します。
SELECT 10 / 3;
出力結果
->1
演算子の優先順位
演算子には優先順位があり、ひとつの式に複数の演算子が含まれる場合、優先順位が高い演算子から演算されます。次の表は、優先順位の高い順に並んでいます。同じ行に並んで記載されている演算子は、優先順位が同じものです。
演算子の優先順位一覧
| 演算子 | 解説 |
|---|---|
| ! | 論理否定 |
| ^ | 階乗 |
| *、/、% | 掛け算、割り算、剰余 |
| -、+ | 足し算、引き算 |
| <<、>> | 左にビットをシフト、右にビットをシフト |
| & | ビット演算のAND |
| | | ビット演算のOR |
| =、<=>、>=、>、<=、<、<>、!=、LIKE、REGEXP、IN | 等しい、<=>、大きい・もしくは等しい、大きい、小さい・もしくは等しい、小さい、等しくない、等しくない、パターンマッチング、正規表現、複数の条件一致の設定 |
| BETWEEN、CASE | 範囲内に収まっているか、値の変換 |
| NOT | 論理否定 |
| &&、AND | 論理積、論理積 |
| ||、OR、XOR | 論理和、論理和、排他的論理和 |
グループ化演算子
演算子には結合性と優先順位があります。優先順位の高い演算子は、低い演算子よりも先に処理されます。たとえば、次のような計算式の場合、+ 演算子よりも * 演算子のほうが優先順位が高いため、「10＊5」が先に演算され、次に「10＊5」の結果に10を加算します。
SELECT 10 + 10 * 5;
出力結果
-> 60
優先順位によって演算の順番がわかりにくくなるような際は、グループ化して優先順位を明確にすることができます。式を括弧で囲むと、中の式はグループ化され、優先順位にかかわらず、グループ内の式が先に演算されます。カッコの使い方は代数学の場合と同じです。たとえば、「10
+ 5」に2を掛けるには次のように書きます。
SELECT (10 + 5) * 2;
出力結果
-> 30
計算式が複雑になるときはカッコを使って見やすくしましょう。
式の規則
- 式の中でカッコ内にある演算子は、最初に計算されます。
- 演算子の順番では、乗算、除算、剰余演算子の次に加算、減算が計算されます。
- 同一の優先順位にある演算子が複数並ぶときは、左から順に計算されます。
比較演算子
比較演算子の概要
『比較演算子』は、複数のオペランドを比較し、その結果を 0 か 1 の『論理値（ブール値）』で返す演算子です。論理値は 0 が『偽（FALSE）』 、1 が『真（TRUE）』 を表します。
- 論理値
- 論理値はTRUEかFALSEだけを扱います。値が数値の 0 か空の文字列の場合は常にFALSE、それ以外はTRUEです。
次の例では、= 演算子を使って、二つのオペランドの大きさが同じかどうかを比較しています。
# 比較が正しい場合は 1 が返されます
SELECT 50 = 50;
出力結果
-> 1
# 比較が誤っている場合は 0 が返されます SELECT 50 = 100;
出力結果
-> 0
文字列を使った比較も可能です。
SELECT 'AAA' = 'AAA';
出力結果
-> 1
SELECT 'AAA' = 'BAA';
出力結果
-> 0
文字列と数値の比較もできます。
SELECT '1' = 1;
出力結果
-> 1
SELECT '1' = 2;
出力結果
-> 0
比較の規則
- 比較対象のオペランドが、どちらか一方でもNULLの場合は、返却値もNULLです。
- 比較対象のオペランドの両方が文字列の場合、文字列として比較します。
- 比較対象のオペランドの両方が整数の場合、整数として比較します。
- [MySQLの場合] 比較対象のオペランドの一方がTIMESTAMPまたはDATETIMEで、他の引数が定数の場合は、定数は比較前にTIMESTAMPに変換されます。他の場合は全て浮動小数として比較されます。
比較演算子の一覧
| 演算子 | 説明 |
|---|---|
| = | 等しい |
| != <> |
等しくない |
| > | 大きい |
| < | 小さい |
| >= | 大きい、もしくは等しい |
| <= | 小さい、もしくは等しい |
これらの演算子は数値と文字列の両方で使えます。必要であれば、文字は自動的に数字に変換され、数字は文字に変換されます。
比較演算子
比較関数は数値と文字列の両方で利用でき、1 (TRUE)、0 (FALSE)、もしくはNULLを返します。
=演算子
等しい
=演算子は左右のオペラントが等しいかどうかを判定します。等しい場合は 1、等しくない場合は 0 を返します。
SELECT 1 = 0; -> 0 SELECT 0 = 0; -> 1 SELECT 'A' = 'A'; -> 1 SELECT 'A' = 'B'; -> 0
<>演算子 / !=演算子
等しくない
<>演算子は左右のオペラントが等しくないかどうかを判定します。等しくない場合は 1、等しい場合は 0 を返します。
SELECT 1 <> 0; -> 1 SELECT 0 <> 0; -> 0 SELECT 'A' <> 'A'; -> 0 SELECT 'A' <> 'B'; -> 1
>演算子
大きい
>演算子は左辺のオペランドが右辺のオペランドよりも大きいかどうかを判定します。大きい場合は 1、大きくない場合は 0 を返します。
SELECT 1 > 0; -> 1 SELECT 0 > 0; -> 0 SELECT -1 > 0; -> 0 SELECT 'Z' > 'A'; -> 1 SELECT 'A' > 'B'; -> 0
<演算子
小さい
<演算子は左辺のオペランドが右辺のオペランドよりも小さいかどうかを判定します。小さい場合は 1、小さくない場合は 0 を返します。
SELECT 1 < 0; -> 0 SELECT 0 < 0; -> 0 SELECT -1 < 0; -> 1 SELECT 'Z' < 'A'; -> 0 SELECT 'A' < 'B'; -> 1
>=演算子
大きい、もしくは等しい
>=演算子は左辺のオペランドが右辺のオペランドよりも大きい、もしくは等しいかどうかを判定します。大きい、もしくは等しい場合は 1、小さい場合は 0 を返します。
SELECT 1 >= 0; -> 1 SELECT 0 >= 0; -> 1 SELECT -1 >= 0; -> 0 SELECT 'Z' >= 'A'; -> 1 SELECT 'A' >= 'B'; -> 0
<=演算子
小さい、もしくは等しい
>=演算子は左辺のオペランドが右辺のオペランドよりも小さい、もしくは等しいかどうかを判定します。小さい、もしくは等しい場合は 1、大きい場合は 0 を返します。
SELECT 1 <= 0; -> 0 SELECT 0 <= 0; -> 1 SELECT -1 <= 0; -> 1 SELECT 'Z' <= 'A'; -> 0 SELECT 'A' <= 'B'; -> 1
<=>演算子
等しい(NULL安全)
<=>演算子は左右のオペランドが等しいかどうかを判定します。等しい場合は 1、等しくない場合は 0 を返します。
SELECT 1 <=> 1; -> 1 SELECT NULL <=> NULL; -> 1 SELECT 1 <=> NULL; -> 0
IS NULL演算子
IS NOT NULL演算子
NULLの判定
IS NULL演算子は、式がNULLかどうかを判定します。式がNULLであれば 1 を、そうでなければ 0 を返します。
式 IS NULL
式 IS NOT NULL
次の例では、1、0、NULLの値を判定しています。
SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; -> 0 0 1
IS NOT NULL演算子は、IS NULL演算子の反対で、式がNULLであれば 1 を、そうでなければ 0 を返します。
SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; -> 1 1 0
ISNULL演算子
NULLの判定
ISNULL演算子は、式がNULLかどうかを判定します。利用方法以外はIS NULLと同じで、式がNULLなら 1 を、そうでなければ 0 を返します。
ISNULL(式)
ISNULL演算子は引数に式を指定します。
SELECT ISNULL(1+1); -> 0 SELECT ISNULL(1/0); -> 1
※NULLの値を = を使用して比較した場合は常に偽(false)
となることに注意！
IN演算子
NOT IN演算子
複数の条件一致の設定
IN演算子は、複数の条件一致を設定することができます。
式 IN ( x1, x2, ... )
式の値が、引数x1や引数x2とマッチするかを調べ、マッチした場合は 1 を返し、そうでなければ 0 を返します。
SELECT 2 IN (1, 2, 3, 'abc'); -> 1 SELECT 5 IN (1, 2, 3, 'abc'); -> 0 SELECT 'abc' IN (1, 2, 3, 'abc'); -> 1
IN演算子はWHERE句の可読性を高くすることができます。たとえば、購入履歴から顧客IDが 3 と 2 にあたるレコードを参照するとき、比較演算子のORを使うと下記のようになります。
SELECT * FROM 購入履歴 WHERE 顧客ID = 1 OR 顧客ID = 2;
これと同じ事が IN 演算子を利用すると、より簡単な文で書くことができます。
SELECT * FROM 購入履歴
WHERE 顧客ID IN ( 1, 2 ) ;
NOT IN演算子はINの逆で、引数のリストにある値とマッチするかを調べ、マッチした場合は 0、そうでなければ 1 を返します。
SELECT 2 NOT IN (1, 2, 3, 'abc'); -> 0
EXISTS演算子
存在するか判定
EXISTS、ANY、ALL演算子は、メインクエリのWHERE句で使用され、サブクエリが返す値を論理値として評価します。EXISTS演算子はサブクエリが返す結果セットの有無を評価し、ANY演算子は結果セットのいずれかの値を評価対象とします。ALL演算子は結果セットの全ての値を評価対象とします。
EXISTS演算子は、サブクエリが返す値がある場合は 1、返す値がない場合は 0 を返します。サブクエリの返す値は複数のレコードでも、複数の列でも問題ありません。
EXISTS (サブクエリ)
次の例では、商品(goods)テーブルから商品IDが"1"のレコードをサブクエリで選択しています。商品IDが"1"のレコードが存在した場合、メインのSELECTで選択した内容が出力されます。
SELECT id_g FROM goods WHERE EXISTS (SELECT * FROM goods WHERE id_g = 1); -> +------+ -> | id_g | -> +------+ -> | 1 | -> | 2 | -> | 3 | -> | 4 | -> | 5 | -> | 6 | -> +------+
サブクエリの条件にあったレコードがなかった場合は、EXISTSは 0 を返します。WHERE句の条件が偽となったため、メインのSELECTが選択するレコードはありません。
SELECT id_g FROM goods WHERE EXISTS (SELECT * FROM goods WHERE id_g = 10);
BETWEEN演算子
範囲内に収まっているか
BETWEEN演算子は、値の範囲を調べます。
式 BETWEEN 最大値 AND 最小値
式の値が"最大値"以下で、"最小値"以上であれば 1 を返し、そうでなければ 0 を返します。
SELECT 5 BETWEEN 5 AND 10 ; -> 1 SELECT 11 BETWEEN 5 AND 10; -> 0 SELECT 'b' BETWEEN 'a' AND 'c'; -> 1
論理演算子
『論理演算子』とは、オペランドを論理値として評価し、複数のオペランドの結果を論理値で返したり、論理値を反転したりします。
算術演算子同様に、よく利用される演算子の1つで、その種類には「AとBがTRUEならば・・・」のAND ( && )演算子、「A、もしくはBのどちらかがTRUEならば・・・」のOR ( || )演算子、否定のNOT ( ! )があります。
SELECT 1 AND 1; -> 1 SELECT 1 AND 0; -> 0 SELECT 1 OR 1; -> 1 SELECT 1 OR 0; -> 1 SELECT 0 OR 0; -> 0 SELECT NOT 1; -> 0
論理演算子の一覧
| 演算子 | 名称 | 説明 |
|---|---|---|
| AND / && | 論理積 | 左右の式の両方が真を返すかどうかを判定し、論理値を返します。 全ての条件式が 0 または NULL以外であれば 1 を返します。引数に 0 やNULLといった値がひとつでも含まれる場合は 0 が返されます。 |
| OR / || | 論理和 | 左右の式のどちらか一方が真を返すかどうかを判定し、論理値を返します。 条件式のどれかが 0 または NULL 以外の値であれば 1 を返します。全ての値が 0 かNULLの場合は 0 を返します。 |
| NOT / ! | 論理否定 |
左右の式の両方が偽を返すかどうかを判定し、論理値を返します。 引数が 0 なら 1 を返し、そうでなければ 0 を返します。例外として、 NOT NULL は NULL を返します。 |
- 論理積
- && と AND は、左側の演算子が真であれば、右側の演算子も評価します。左側の演算子が偽であれば、右側の演算子を評価せずに結果を返します。
- 論理和
- || と OR は、左側の演算子が真であれば、右側の演算子を評価せずに結果を返します。左側の演算子が偽りであれば、右側の演算子も評価します。
AND演算子 / &&演算子
論理積
左右の式の両方が真を返すかどうかを判定し、論理値を返します。
全ての条件式が 0 または NULL以外であれば 1 を返します。条件式に 0 やNULLといった値がひとつでも含まれる場合は 0 が返されます。
SELECT 1 AND 1; -> 1 SELECT 0 AND 1; -> 0 SELECT 0 AND 0; -> 0 SELECT 1 AND NULL; -> 0
OR演算子 / ||演算子
論理和
左右の式のどちらか一方が真を返すかどうかを判定し、論理値を返します。
条件式のどれかが 0 または NULL 以外の値であれば 1 を返します。全ての値が 0 かNULLの場合は 0 を返します。
SELECT 1 OR 1; -> 1 SELECT 0 OR 1; -> 1 SELECT 0 OR 0; -> 0 SELECT 1 OR NULL; -> 1
NOT演算子 / !演算子
論理否定
左右の式の両方が偽を返すかどうかを判定し、論理値を返します。
論理否定のNOTは対象の値を反転します。引数が 0 なら 1 を返し、そうでなければ 0 を返します。例外として、 NOT NULLはNULLを返します。
SELECT NOT 1; -> 0 SELECT NOT 0; -> 1 SELECT NOT NULL; -> NULL
計算の結果を論理否定する場合は、カッコでグループ化しましょう。グループ化を忘れると、最初の数値だけが論理否定の対象になります。
#論理否定のためのグループ化 SELECT ! (0+1); -> 0最初の数値 0 だけが論理否定され、1 + 1 という計算式になりますSELECT ! 0 + 1; -> 2
COALESCE
COALESCEは、リストで指定した値の中にあるNULL以外の要素を返します。
SELECT COALESCE(NULL, 5); -> 5 SELECT COALESCE(NULL, NULL); -> NULL
INTERVAL(N,N1,N2,N3,...)
N < N1 なら 0 を返し、N < N2 なら1を返します。INTERVALは、左から順番に、Nより大きい数値が最初に現れる位置情報を返す関数です。位置情報はNからみて、Nの右隣が0、その次が1です。全ての引数は数値として扱われます。
SELECT INTERVAL(10, 1, 10, 100, 1000); -> 2 SELECT INTERVAL(1, 10, 100, 1000); -> 0
フロー制御関数
IFNULL(expr1,expr2)
expr1がNULLではなければexpr1を、NULLであればexpr2を返します。
IFNULL() はどの文脈で使用されたかにより数値か文字を返します。
SELECT IFNULL(1,0); -> 1 SELECT IFNULL(0,10); -> 0 SELECT IFNULL(1/0,10); -> 10 SELECT IFNULL(1/0,'yes'); -> 'yes'
IF(expr1,expr2,expr3)
expr1 が真 の場合 expr2 を返し、そうでなければ expr3 を返します。
IFNULL() はどの文脈で使用されたかにより数値か文字を返します。
SELECT IF(1>2,2,3);
-> 3
SELECT IF(1<2,'yes','no');
-> 'yes'
SELECT IF(strcmp('test','test1'),'yes','no');
-> 'no'
expr1 は INTEGER
として評価します。これは浮動小数点を使用する場合、比較演算も使用すべきであることを意味します。
SELECT IF(0.1,1,0); -> 0 SELECT IF(0.1<>0,1,0); -> 1
上の最初の例では、 IF(0.1) は 0 を返します。なぜなら 0.1 は整数値として変換され
IF(0)
になり、それをもとにしたテスト結果が返るからです。二番目の場合、比較は、元の浮動小数点値が非ゼロかどうかテストします。比較結果は整数として使用されます。
構文：
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
最初の式は value=compare-value ならばresultを返します。二つ目の式では、最初の条件[condition]が真ならば、resultを返します。もしマッチするresultの値がなければ、ELSE以下のresultが返ります。もしELSE部分がなければ、 NULLが返ります:
SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; -> "one" SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> "true" SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END; -> NULL
戻り値の型(INTEGER, DOUBLE, STRING)は最初に返される値 (最初の THEN の後の式)の型と同じです。
ビット演算子
ビット演算子は、フィールドの値や、指定した値などからビット演算を行います。
|演算子
ビット単位のOR
|演算子はビット単位のORです。いずれかのビットが 1 であれば 1 を返すビット演算です。
SELECT 1 | 1; -> 1 SELECT 1 | 0; -> 1 SELECT 0 | 0; -> 0
&演算子
ビット単位のAND
&演算子はビット単位のANDです。両方のビットが 1 のとき 1 を返すビット演算です。
SELECT 1 & 1; -> 1 SELECT 1 & 0; -> 0 SELECT 0 & 0; -> 0
<<演算子
左シフト
左にビットをシフトします。
SELECT 2 << 1; -> 4
>>演算子
右シフト
右にビットをシフトします。
SELECT 2 >> 1; -> 1
^演算子
ビットのXOR
~演算子は、両方のビットが異なるときに 1 を返すビット演算です。
SELECT 1 ^ 1; -> 0 SELECT 1 ^ 0; -> 1 SELECT 0 ^ 0; -> 0
~演算子
ビットの反転
~演算子は、ビットを反転します。
SELECT ~ 0 ; -> 1 SELECT ~ 1 ; -> 0
BIT_COUNT演算子
引数Nを2進数表記したときの１の数になっている回数を数えます。
SELECT BIT_COUNT(29); -> 4