比较函数和运算符

比较操作

• 范围比较：between

  between适用于值在一个特定的范围内，如：value BETWEEN min AND max

  Not between适用于值不在某个特定范围内。

  null值不能出现在between操作中，如下两种执行结果都是Null：

  SELECT NULL BETWEEN 2 AND 4; -- null
  SELECT 2 BETWEEN NULL AND 6; -- null

  HetuEngine中，value，min和max 三个参数在between和not between中必须是同一数据类型。

  错误示例：'John' between 2.3 and 35.2

  BETWEEN等价写法示例：

  SELECT 3 BETWEEN 2 AND 6; -- true
  SELECT 3 >= 2 AND 3 <= 6; -- true

  NOT BETWEEN等价写法示例：

  SELECT 3 NOT BETWEEN 2 AND 6; -- false
  SELECT 3 < 2 OR 3 > 6; -- false

• IS NULL和IS NOT NULL

  用于判断值是否为空，所有数据类型都可以用于此判断。

  SELECT 3.0 IS NULL; -- false

• IS DISTINCT FROM和IS NOT DISTINCT FROM

  特有用法。在HetuEngine的SQL中，NULL代表未知值，所有与NULL有关的比较，产生的结果也是NULL。IS DISTINCT FROM和IS NOT DISTINCT FROM可以把null值当成某个已知值，从而使结果返回true或者false（即使表达式中有Null值）。

  示例：

  --建表
  create table dis_tab(col int);
  --插入数据
  insert into dis_tab values (2),(3),(5),(null);
  --查询
  select col from dis_tab where col is distinct from null;
   col
  ----
   2 
   3 
   5
  (3 rows)

  如以下真值表，演示了IS DISTINCT FROM和IS NOT DISTINCT FROM对正常数据和NULL值的处理结果：

  a	b	a = b	a <> b	a DISTINCT b	a NOT DISTINCT b
  1	1	TRUE	FALSE	FALSE	TRUE
  1	2	FALSE	TRUE	TRUE	FALSE
  1	NULL	NULL	NULL	TRUE	FALSE
  NULL	NULL	NULL	NULL	FALSE	TRUE

• GREATEST和LEAST

  这两个函数不是SQL标准函数，是常用的扩展。参数中不能有Null值。

  • greatest(value1, value2, ..., valueN)

    返回提供的最大值。

  • least(value1, value2, ..., valueN) → [same as input]

    返回提供的最小值。

• 批量比较判断：ALL，ANY和SOME

  量词ALL，ANY和SOME可以参考以下方式，结合比较操作符一起使用：

  expression operator quantifier ( subquery )

  以下是一些量词和比较运算符组合的含义，ANY和SOME具有相同的含义，表中的ANY换为SOME也同样：

  表达式	含义
  A = ALL (...)	当A与所有值相等时返回true
  A <> ALL (...)	当A与任何值都不相等时返回true。
  A < ALL (...)	当A小于最小值时返回true。
  A = ANY (...)	当A与任意一个值相同时返回true。等价于A IN (...)。
  A <> ANY (...)	当A与任意一个值都不相同时返回true。
  A < ANY (...)	当A小于最大值时返回true。

  例如：

  SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true
  SELECT 21 < ALL (VALUES 19, 20, 21); -- false
  SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43);-- true