Comparison Functions and Operators

Comparison

• Scope comparison: between

  between is applicable to values in a specified range, for example, value BETWEEN min AND max.

  Not between is used when the value is not in a specified range.

  The null value cannot be used in the between operation. The execution results of the following two operations are Null:

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

  In HetuEngine, the value, min, and max parameters must be of the same data type in BETWEEN and NOT BETWEEN.

  Wrong usage: 'John' between 2.3 and 35.2

• IS NULL and IS NOT NULL

  They are used to determine whether a value is empty. All data types can be used for this determination.

• IS DISTINCT FROM and IS NOT DISTINCT FROM

  This is a special usage. In HetuEngine SQL statements, null indicates an unknown value. If an expression contains null, the result is also null. IS DISTINCT FROM and IS NOT DISTINCT FROM can take a null value as a known value and return true or false (even if the expression contains a null value).

  Example:

  --Create a table.
  create table dis_tab(col int);
  --Insert data.
  insert into dis_tab values (2),(3),(5),(null);
  --Query:
  select col from dis_tab where col is distinct from null;
   col
  ----
   2 
   3 
   5
  (3 rows)

• GREATEST and LEAST

  The two functions are not standard SQL functions. They are common extensions. The parameter cannot contain null values.

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

    Returns the provided maximum value.

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

    Returns the provided minimum value.

• Batch comparison: ALL, ANY, and SOME

  Quantifiers ALL, ANY, and SOME can be used together with comparison operators in the following ways:

  expression operator quantifier ( subquery )

  The meanings of some combinations of quantifiers and comparison operators are as follows:

  Expression	Definition
  A = ALL (...)	Returns true when A is equal to all values.
  A <> ALL (...)	Returns true when A is not equal to any value.
  A < ALL (...)	Returns true when A is less than the minimum value.
  A = ANY (...)	Returns true when A is the same as any value, which is equivalent to A IN (...).
  A <> ANY (...)	Returns true when A is different from any value.
  A < ANY (...)	Returns true when A is less than the maximum value.

  Example:

  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