ここではMySQLにおける比較演算子の使い方を紹介しています。
比較演算子の一覧
MySQLで利用できる比較演算子は次の通りです。
| 演算子 | 内容 |
| = | 等しい |
| <=> | 等しい（NULL対応） |
| > | 小さい |
| < | 大きい |
| <= | 以下 |
| >= | 以上 |
| <> | 等しくない |
| != |
等しくない |
さらにNULL値との比較や範囲の指定では以下の演算子を使うことが出来ます。
| 演算子 | 内容 |
| IS NULL | NULLと等しい |
| IS NOT NULL | NULLと等しくない |
| IS boolean_value | boolean_valueと等しい |
| IS NOT boolean_value | boolean_valueと等しくない |
| BETWEEN min AND max | min以上max以下の範囲内 |
| NOT BETWEEN min AND max | min以上max以下の範囲外 |
| IN (value,…) | 列挙されたvalueのいずれかの値と等しい |
| NOT IN (value,…) | 列挙されたvalueのいずれの値とも等しくない |
比較演算子を使ったSQLサンプル
等しいかどうかの比較
mysql> SELECT 1=1,1<=>1;
+-----+-------+
| 1=1 | 1<=>1 |
+-----+-------+
| 1 | 1 |
+-----+-------+
1 row in set (0.00 sec)
mysql> SELECT 1=2,1<=>2;
+-----+-------+
| 1=2 | 1<=>2 |
+-----+-------+
| 0 | 0 |
+-----+-------+
1 row in set (0.00 sec)大小の比較
mysql> SELECT 3 > 1,1 < 3;
+-------+-------+
| 3 > 1 | 1 < 3 |
+-------+-------+
| 1 | 1 |
+-------+-------+
1 row in set (0.00 sec)
mysql> SELECT 1 > 3,3 < 1;
+-------+-------+
| 1 > 3 | 3 < 1 |
+-------+-------+
| 0 | 0 |
+-------+-------+
1 row in set (0.00 sec)以上以下の比較
mysql> SELECT 3 >= 3,3 <= 3;
+--------+--------+
| 3 >= 3 | 3 <= 3 |
+--------+--------+
| 1 | 1 |
+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT 3 >= 4,4 <= 3;
+--------+--------+
| 3 >= 4 | 4 <= 3 |
+--------+--------+
| 0 | 0 |
+--------+--------+
1 row in set (0.00 sec)等しくない比較
mysql> SELECT 1 <> 2, 1 != 2;
+--------+--------+
| 1 <> 2 | 1 != 2 |
+--------+--------+
| 1 | 1 |
+--------+--------+
1 row in set (0.00 sec)
mysql> SELECT 1 <> 1, 1 != 1;
+--------+--------+
| 1 <> 1 | 1 != 1 |
+--------+--------+
| 0 | 0 |
+--------+--------+
1 row in set (0.00 sec)NULLとの比較
mysql> SELECT col1,col2,col2 IS NULL,col2 IS NOT NULL FROM tab;
+-------+------+--------------+------------------+
| col1 | col2 | col2 IS NULL | col2 IS NOT NULL |
+-------+------+--------------+------------------+
| 00001 | 1 | 0 | 1 |
| 00002 | 2 | 0 | 1 |
| 00003 | NULL | 1 | 0 |
+-------+------+--------------+------------------+
3 rows in set (0.01 sec)booleanとの比較
mysql> SELECT col1,col2,col2 IS TRUE,col2 IS NOT FALSE FROM tab;
+-------+------+--------------+-------------------+
| col1 | col2 | col2 IS TRUE | col2 IS NOT FALSE |
+-------+------+--------------+-------------------+
| 00001 | 1 | 1 | 1 |
| 00002 | 0 | 0 | 0 |
| 00003 | 1 | 1 | 1 |
+-------+------+--------------+-------------------+
3 rows in set (0.00 sec)範囲の比較（BETWEEN）
mysql> SELECT col1,col2,col2 BETWEEN 20 AND 40,col2 NOT BETWEEN 20 AND 40 FROM tab;
+-------+------+------------------------+----------------------------+
| col1 | col2 | col2 BETWEEN 20 AND 40 | col2 NOT BETWEEN 20 AND 40 |
+-------+------+------------------------+----------------------------+
| 00001 | 10 | 0 | 1 |
| 00002 | 20 | 1 | 0 |
| 00003 | 30 | 1 | 0 |
| 00004 | 40 | 1 | 0 |
| 00005 | 50 | 0 | 1 |
+-------+------+------------------------+----------------------------+
5 rows in set (0.00 sec)列挙値・リストとの比較（IN）
mysql> SELECT col1,col2,col2 IN (20,30,40),col2 NOT IN (20,30,40) FROM tab;
+-------+------+--------------------+------------------------+
| col1 | col2 | col2 IN (20,30,40) | col2 NOT IN (20,30,40) |
+-------+------+--------------------+------------------------+
| 00001 | 10 | 0 | 1 |
| 00002 | 20 | 1 | 0 |
| 00003 | 30 | 1 | 0 |
| 00004 | 40 | 1 | 0 |
| 00005 | 50 | 0 | 1 |
+-------+------+--------------------+------------------------+
5 rows in set (0.01 sec)