Comparison Functions and Operators

Comparison Operators

OperatorDescription
<Less than
>Greater than
<=Less than or equal to
>=Greater than or equal to
=Equal
<>Not equal
!=Not equal (non-standard but popular syntax)

Range Operator: BETWEEN

The BETWEEN operator tests if a value is within a specified range. It uses the syntax value BETWEEN min AND max:

SELECT 3 BETWEEN 2 AND 6;

The statement shown above is equivalent to the following statement:

SELECT 3 >= 2 AND 3 <= 6;

To test if a value does not fall within the specified range use NOT BETWEEN:

SELECT 3 NOT BETWEEN 2 AND 6;

The statement shown above is equivalent to the following statement:

SELECT 3 < 2 OR 3 > 6;

A NULL in a BETWEEN or NOT BETWEEN statement is evaluated using the standard NULL evaluation rules applied to the equivalent expression above:

SELECT NULL BETWEEN 2 AND 4; -- null

SELECT 2 BETWEEN NULL AND 6; -- null

SELECT 2 BETWEEN 1 AND NULL; -- false

SELECT 8 BETWEEN NULL AND 6; -- false

The BETWEEN and NOT BETWEEN operators can also be used to evaluate any orderable type. For example, a VARCHAR:

SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true

Not that the value, min, and max parameters to BETWEEN and NOT BETWEEN must be the same type. For example, openLooKeng will produce an error if you ask it if John is between 2.3 and 35.2.

IS NULL and IS NOT NULL

The IS NULL and IS NOT NULL operators test whether a value is null (undefined). Both operators work for all data types.

Using NULL with IS NULL evaluates to true:

select NULL IS NULL; -- true

But any other constant does not:

SELECT 3.0 IS NULL; -- false

IS DISTINCT FROM and IS NOT DISTINCT FROM

In SQL a NULL value signifies an unknown value, so any comparison involving a NULL will produce NULL. The IS DISTINCT FROM and IS NOT DISTINCT FROM operators treat NULL as a known value and both operators guarantee either a true or false outcome even in the presence of NULL input:

SELECT NULL IS DISTINCT FROM NULL; -- false

SELECT NULL IS NOT DISTINCT FROM NULL; -- true

In the example shown above, a NULL value is not considered distinct from NULL. When you are comparing values which may include NULL use these operators to guarantee either a TRUE or FALSE result.

The following truth table demonstrate the handling of NULL in IS DISTINCT FROM and IS NOT DISTINCT FROM:

aba = ba <> ba DISTINCT ba NOT DISTINCT b
11TRUEFALSEFALSETRUE
12FALSETRUETRUEFALSE
1NULLNULLNULLTRUEFALSE
NULLNULLNULLNULLFALSETRUE

GREATEST and LEAST

These functions are not in the SQL standard, but are a common extension. Like most other functions in openLooKeng, they return null if any argument is null. Note that in some other databases, such as PostgreSQL, they only return null if all arguments are null.

The following types are supported: DOUBLE, BIGINT, VARCHAR, TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATE

greatest(value1, value2, ..., valueN) -> [same as input]

Returns the largest of the provided values.

least(value1, value2, ..., valueN) -> [same as input]

Returns the smallest of the provided values.

Quantified Comparison Predicates: ALL, ANY and SOME

The ALL, ANY and SOME quantifiers can be used together with comparison operators in the following way:

expression operator quantifier ( subquery )

For 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

Here are the meanings of some quantifier and comparison operator combinations:

ExpressionMeaning
A = ALL (...)Evaluates to true when A is equal to all values.
A <> ALL (...)Evaluates to true when A doesn’t match any value.
A < ALL (...)Evaluates to true when A is smaller than the smallest value.
A = ANY (...)Evaluates to true when A is equal to any of the values. This form is equivalent to A IN (...).
A <> ANY (...)Evaluates to true when A doesn’t match one or more values.
A < ANY (...)Evaluates to true when A is smaller than the biggest value.

有奖捉虫

“有虫”文档片段

0/500

存在的问题

文档存在风险与错误

● 拼写,格式,无效链接等错误;

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 版本号不匹配:文档版本或内容描述和实际软件不一致;

● 对重要数据或系统存在风险的操作,缺少安全提示;

● 排版不美观,影响阅读;

内容描述不清晰

● 描述存在歧义;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

内容获取有困难

● 很难通过搜索引擎,openLooKeng官网,相关博客找到所需内容;

示例代码有错误

● 命令、命令参数等错误;

● 命令无法执行或无法完成对应功能;

内容有缺失

● 关键步骤错误或缺失,无法指导用户完成任务,比如安装、配置、部署等;

● 逻辑不清晰,该分类、分项、分步骤的没有给出

● 图形、表格、文字等晦涩难懂

● 缺少必要的前提条件、注意事项等;

● 描述存在歧义

0/500

您对文档的总体满意度

非常不满意
非常满意

请问是什么原因让您参与到这个问题中

您的邮箱

创Issue赢奖品
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。
有奖捉虫