Comparison Functions and Operators

Updated on 2024-12-13 GMT+08:00

View PDF

Comparison

Operation	Description
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
=	Equal to
<>	Not equal to
!=	Not equal to

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 typical 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

Parent topic: HetuEngine SQL Functions and Operators

Previous topic: Logical Operators

Next topic: Condition Expression