全部产品
阿里云办公

比较运算符

更新时间:2017-06-07 13:26:11

OceanBase比较的策略是,先将操作数转换为相同的类型,然后进行比较。所有比较运算符的返回类型为Bool或者NULL。比较结果为真则返回“1”,为假则返回“0”,不确定则返回“NULL”。

数值比较

比较运算符用于比较两个数值的大小。OceanBase支持的比较运算符如下表所示。

表达式 含义 举例
= 等于。 SELECT 1=0, 1=1, 1=NULL;
>= 大于等于。 SELECT 1>=0, 1>=1, 1>=2, 1>=NULL;
> 大于。 SELECT 1>0, 1>1, 1>2, 1>NULL;
<= 小于等于。 SELECT 1<=0, 1<=1, 1<=2, 1<=NULL;
< 小于。 SELECT 1<0, 1<1, 1<2, 1<NULL;
!=或<> 不等于。 SELECT 1!=0, 1!=1, 1<>0, 1<>1, 1!=NULL, 1<>NULL;

数值比较运算符的类型转换规则如下表所示。

INT FLOAT DOUBLE TIMESTAMP VARCHAR BOOL NULL
INT INT FLOAT DOUBLE Error ?INT Error NULL
FLOAT FLOAT DOUBLE Error ?FLOAT Error NULL
DOUBLE DOUBLE Error ?DOUBLE Error NULL
TIMESTAMP TIMESTAMP ?TIMESTAMP Error NULL
VARCHAR VARCHAR ?BOOL NULL
BOOL BOOL NULL
NULL NULL

注意:

  • “?”开头的类型表示会在执行的时候尝试转换到指定类型,如果转换失败则报错。
  • BOOL类型比较时,False小于True。

[NOT] BETWEEN … AND …

判断是否存在或者不存在于指定范围。

Oceanbase>SELECT 2 BETWEEN 1 AND 2, 3 NOT BETWEEN 1 AND 2, 1 BETWEEN null AND 0, 1 NOT BETWEEN null AND 0\G
*************************** 1. row ***************************
       2 BETWEEN 1 AND 2: 1
   3 NOT BETWEEN 1 AND 2: 1
    1 BETWEEN null AND 0: 0
1 NOT BETWEEN null AND 0: 1
1 row in set (0.01 sec)

示例

有两张表,emp雇员信息表(员工姓名、工资等信息)和salgrade工资等级表(工资范围和工资等级);现要查询员工姓名、工资以及工资等级信息。

Oceanbase>select * from emp;  //雇员信息表
+--------+----------+
| ename  | sal      |
+--------+----------+
| Jerry  | 25000.00 |
| Larry  | 40000.00 |
| Maggie | 46000.00 |
| Micky  | 15000.00 |
+--------+----------+
4 rows in set (0.00 sec)
Oceanbase>select * from salgrade; //工资等级表
+-------+----------+----------+
| grade | losal    | hisal    |
+-------+----------+----------+
|     1 | 10000.00 | 20000.00 |
|     2 | 20001.00 | 30000.00 |
|     3 | 30001.00 | 40000.00 |
|     4 | 40001.00 | 50000.00 |
|     5 | 50001.00 | 90000.00 |
+-------+----------+----------+
5 rows in set (0.00 sec)
Oceanbase>select a1.ename, a1.sal, a2.grade from emp a1, salgrade a2 where a1.sal between a2.losal and a2.hisal;  //通过between...and...设定查询条件
+--------+----------+-------+
| ename  | sal      | grade |
+--------+----------+-------+
| Micky  | 15000.00 |     1 |
| Jerry  | 25000.00 |     2 |
| Larry  | 40000.00 |     3 |
| Maggie | 46000.00 |     4 |
+--------+----------+-------+
4 rows in set (0.00 sec)

[NOT] IN

判断是否存在于指定集合。

Oceanbase>SELECT 2 IN (1, 2), 3 IN (1, 2)\G;
*************************** 1. row ***************************
2 IN (1, 2): 1
3 IN (1, 2): 0
1 row in set (0.00 sec)

IS [NOT] NULL | TRUE | FALSE | UNKNOWN

判断是否为NULL、真、假或未知。如果执行成功,结果是TRUE或者FALSE,不会出现NULL。

左参数必须是BOOL类型,或者是NULL,否则报错。

Oceanbase>SELECT 0 IS NULL,
->     NULL IS NULL, NULL IS TRUE,
->     (0>1) IS FALSE,
->     NULL IS UNKNOWN,
->     0 IS NOT NULL,
->     NULL IS NOT NULL,
->     NULL IS NOT TRUE,
->     (0>1) IS NOT FALSE,
->     NULL IS NOT UNKNOWN\G;
*************************** 1. row ***************************
          0 IS NULL: 0
       NULL IS NULL: 1
       NULL IS TRUE: 0
     (0>1) IS FALSE: 1
    NULL IS UNKNOWN: 1
      0 IS NOT NULL: 1
   NULL IS NOT NULL: 0
   NULL IS NOT TRUE: 1
 (0>1) IS NOT FALSE: 0
NULL IS NOT UNKNOWN: 0
1 row in set (0.00 sec)

向量比较运算符

向量比较运算符对两个向量(ROW)进行比较,支持“<”、“ >”、“=”、“<=”、“>=”、 “!=”、“<=>”、“in”和“not in”等操作符。这几个操作符都是二元操作符,被比较的两个向量的维度要求相同。

“<=>”表示NULL-safe equal,这个操作符和=操作符执行相同的比较操作,不过在两个操作码均为NULL时,其所得值为1而不为NULL,而当一个操作码为NULL时,其所得值为0而不为NULL。

表达式(1,2)和ROW(1,2)有时被称为行构造符。两者是等同的,在其它的语境中,也是合法的。例如,以下两个语句在语义上是等同的(但是目前只有第二个语句可以被优化):

SELECT * FROM t1 WHERE (column1, column2) = (1, 1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

向量比较操作符和普通操作符相比主要有以下不同:

  • 如果两个操作数的第i个标量的比较就决定了比较结果,则不再继续比较后续的数值。

向量比较操作符需要注意以下几点:

  • 多元向量比较,关键字ROW可以省略。例如,ROW(1,2,3) < ROW(1,3,5)等价于(1,2,3) < (1,3,4)。

  • in/not in操作一定是向量操作,表示左参数(不)在右集合内,集合用“()”括起。例如, 1 in (2, 3, 1)。

  • in/not in操作需要左右操作数对应位置的标量都是可以比较的,否则返回错误。例如,ROW(1,2) in (ROW(1,2), ROW(2,3), ROW(3,4))成功,ROW(1,2) in (ROW(2,1), ROW(2,3), ROW(1,3,4))失败。

Oceanbase>SELECT ROW(1,2) < ROW(1, 3),
    -> ROW(1,2,10) < ROW(1, 3, 0),
    -> ROW(1,null) < ROW(1,0),
    -> ROW(null, 1) < ROW(null, 2),
    -> ROW(1,2) in (ROW(1,2), ROW(2,3), ROW(3,4), ROW(4,5)),
    -> 1 in (1,2,3),
    -> 1 not in (2,3,4),
    -> ROW(1,2) not in (ROW(2,1),ROW(2,3), ROW(3,4)),
    -> NULL = NULL,
    -> NULL <=> NULL,
    -> NULL <=> 1,
    -> 1 <=> 0 \G;
*************************** 1. row ***************************
                                ROW(1,2) < ROW(1, 3): 1
                          ROW(1,2,10) < ROW(1, 3, 0): 1
                              ROW(1,null) < ROW(1,0): NULL
                         ROW(null, 1) < ROW(null, 2): NULL
ROW(1,2) in (ROW(1,2), ROW(2,3), ROW(3,4), ROW(4,5)): 1
                                        1 in (1,2,3): 1
                                    1 not in (2,3,4): 1
       ROW(1,2) not in (ROW(2,1),ROW(2,3), ROW(3,4)): 1
                                         NULL = NULL: NULL
                                       NULL <=> NULL: 1
                                          NULL <=> 1: 0
                                             1 <=> 0: 0
1 row in set (0.00 sec)