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

  Example expression equivalent to BETWEEN:

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

  Example expression equivalent to NOT BETWEEN:

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

• 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.

  SELECT 3.0 IS NULL; -- false

• IS DISTINCT FROM and IS NOT DISTINCT FROM

  This is a special usage. In HetuEngine SQL statements, NULL indicates an unknown value. All comparisons related to NULL also produce NULL results. 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)

  The following truth table demonstrates how IS DISTINCT FROM and IS NOT DISTINCT FROM process common data and NULL values.

  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 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. ANY and SOME have the same meaning. You can replace ANY with SOME when using the expressions in the following table.

  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