Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Reference/ Expressions/ Simple Expressions
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Simple Expressions

Logical Expressions

Logical Operators lists the operators and calculation rules of logical expressions.

Comparative Expressions

Comparison Operators lists the common comparative operators.

In addition to comparative operators, you can also use the following sentence structure:

  • BETWEEN operator

    a BETWEEN x AND y is equivalent to a >= x AND a <= y.

    a NOT BETWEEN x AND y is equivalent to a < x OR a > y.

  • To check whether a value is null, use:

    expression IS NULL

    expression IS NOT NULL

    or an equivalent (non-standard) sentence structure:

    expression ISNULL

    expression NOTNULL

    • Do not write expression=NULL or expression<>(!=)NULL, because NULL represents an unknown value, and these expressions cannot determine whether two unknown values are equal.
    • Only the comparative expressions IS NULL and IS NOT NULL support data of XML type.
  • is distinct from/is not distinct from
    • is distinct from

      If the data types and values of A and B are different, the value is true.

      If the data types and values of A and B are the same, the value is false.

      Empty values are considered the same.

    • is not distinct from

      If the data types and values of A and B are different, the value is false.

      If the data types and values of A and B are the same, the value is true.

      Empty values are considered the same.

  • <=> NULL-safe equal operator

    The comparison of NULL values is added on the basis of the comparison of '='. If neither the left nor right value of the operator is NULL, the result is the same as that of '='.

    If the data types and values of A and B are different, the value is false.

    If the data types and values of A and B are the same, the value is true.

    Empty values are considered the same.

    • The usage of the <=> operator is the same as that of IS NOT DISTINCT FROM.
    • This operator is valid only when the database is compatible with the MySQL type (that is, sql_compatibility is set to 'MYSQL'). Other types do not support this operator.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
gaussdb=# SELECT 2 BETWEEN 1 AND 3 AS RESULT;
 result 
----------
 t
(1 row)

gaussdb=# SELECT 2 >= 1 AND 2 <= 3 AS RESULT;
 result 
----------
 t
(1 row)

gaussdb=# SELECT 2 NOT BETWEEN 1 AND 3 AS RESULT;
 result 
----------
 f
(1 row)

gaussdb=# SELECT 2 < 1 OR 2 > 3 AS RESULT;
 result 
----------
 f
(1 row)

gaussdb=# SELECT 2+2 IS NULL AS RESULT;
 result 
----------
 f
(1 row)

gaussdb=# SELECT 2+2 IS NOT NULL AS RESULT;
 result 
----------
 t
(1 row)

gaussdb=# SELECT 2+2 ISNULL AS RESULT;
 result 
----------
 f
(1 row)

gaussdb=# SELECT 2+2 NOTNULL AS RESULT;
 result 
----------
 t
(1 row)

gaussdb=# SELECT 2+2 IS DISTINCT FROM NULL AS RESULT;
 result 
----------
 t
(1 row)

gaussdb=# SELECT 2+2 IS NOT DISTINCT FROM NULL AS RESULT;
 result  
----------
 f
(1 row)

gaussdb=# SELECT 1 <=> 1 AS RESULT;
 result
--------
 t
(1 row)

gaussdb=# SELECT NULL <=> 1 AS RESULT;
 result
--------
 f
(1 row)

gaussdb=# SELECT NULL <=> NULL AS RESULT;
 result
--------
 t
(1 row)
Parent topic: Expressions