更新时间:2024-03-06 GMT+08:00

简单表达式

逻辑表达式

逻辑表达式的操作符和运算规则,请参见逻辑操作符

比较表达式

常用的比较操作符,请参见比较操作符

除比较操作符外,还可以使用以下句式结构:

  • BETWEEN操作符

    操作符BETWEEN...AND会选取介于两个值之间的数据范围。这些值可以是数值、文本或日期。

    a BETWEEN x AND y表达式等效于a >= x AND a <= y表达式

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT 2 BETWEEN 1 AND 3 AS RESULT;
     result 
    ----------
     t
    (1 row)
    
    SELECT 2 >= 1 AND 2 <= 3 AS RESULT;
     result 
    ----------
     t
    (1 row)
    

    a NOT BETWEEN x AND y等效于a < x OR a > y

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT 2 NOT BETWEEN 1 AND 3 AS RESULT;
     result 
    ----------
     f
    (1 row)
    
    SELECT 2 < 1 OR 2 > 3 AS RESULT;
     result 
    ----------
     f
    (1 row)
    
  • 检查一个值是不是null,可使用:

    expression IS NULL

    expression IS NOT NULL

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT 2+2 IS NULL AS RESULT;
     result 
    ----------
     f
    (1 row)
    
    SELECT 2+2 IS NOT NULL AS RESULT;
     result 
    ----------
     t
    (1 row)
    

    或者与之等价的句式结构,但不是标准的:

    expression ISNULL

    expression NOTNULL

    不要写expression=NULL或expression<>(!=)NULL,因为NULL代表一个未知的值,不能通过该表达式判断两个未知值是否相等。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT 2+2 ISNULL AS RESULT;
     result 
    ----------
     f
    (1 row)
    
    SELECT 2+2 NOTNULL AS RESULT;
     result 
    ----------
     t
    (1 row)
    
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SELECT 2+2 IS DISTINCT FROM NULL AS RESULT;
     result 
    ----------
     t
    (1 row)
    
    SELECT 2+2 IS NOT DISTINCT FROM NULL AS RESULT;
     result  
    ----------
     f
    (1 row)