SQL比较运算符

Comparison operators are supported for all supported data types. All comparison operators are binary operators that return values of type boolean; expressions like 1 < 2 < 3 are not valid (because there is no < operator to compare a Boolean value with 3).

NOODB支持下表中描述的比较运算符:

操作员 描述

<

少于

>

比...更棒

小于或等于

>=

大于或等于

=

平等的

<> or !=

不平等

之间/不之间

In addition to the comparison operators, the special BETWEEN construct is available:

a BETWEEN x AND y

相当于:

a >= x AND a <=  y

Notice that BETWEEN treats the endpoint values as included in the range. NOT BETWEEN does the opposite comparison:

a NOT BETWEEN x AND y

相当于:

a < x OR a > y

例子

# Select rows from the hockey table, choosing players where the value for
# the player's number contains a value 大于或等于 10 and less than or
# equal to 20.

SELECT * FROM hockey WHERE number BETWEEN 10 AND 20 order by id;

 ID  NUMBER        NAME       POSITION   TEAM
 --- ------- ---------------- --------- ------
  3    11    GREGORY CAMPBELL  Forward  Bruins
  4    18    NATHAN HORTON     Forward  Bruins
  7    17    MILAN LUCIC       Forward  Bruins
 10    20    DANIEL PAILLE     Forward  Bruins
 13    19    TYLER SEGUIN      Forward  Bruins

# Select rows from the hockey table, choosing players where the value for
# the player's number contains a value less than 10 or greater than 20 (but
# not equal to 10 and not equal to 20).

SELECT * FROM hockey WHERE number NOT BETWEEN 10 AND 20 order by id;

 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
  2    48    CHRIS BOURQUE      Forward  Bruins
  5    23    CHRIS KELLY        Forward  Bruins
  6    46    DAVID KREJCI       Forward  Bruins
  8    64    LANE MACDERMID     Forward  Bruins
  9    63    BRAD MARCHAND      Forward  Bruins
 11    49    RICH PEVERLEY      Forward  Bruins
 12    91    MARC SAVARD        Forward  Bruins
 14    22    SHAWN THORNTON     Forward  Bruins
 15    55    JOHNNY BOYCHUK     Defense  Bruins
 16    33    ZDENO CHARA        Defense  Bruins
 17    21    ANDREW FERENCE     Defense  Bruins
 18    27    DOUGIE HAMILTON    Defense  Bruins
 19    45    AARON JOHNSON      Defense  Bruins
 20    54    ADAM MCQUAID       Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins
 22    35    ANTON KHUDOBIN     Goalie   Bruins
 23    40    TUUKKA RASK        Goalie   Bruins
 24     1    MAX SUMMIT         Fan      Bruins

null /不是null

要检查值是否为空,请使用构造:

expression IS NULL
expression IS NOT NULL
Do not write expression = NULL because NULL is not "平等的 to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.

例子

用 TEST
CREATE TABLE tst_table (tst_col STRING);
 INTO tst_table VALUES ('row1'),('row2'),(NULL);
选择 count(*) FROM tst_table WHERE tst_col IS NULL;

 [COUNT]
 --------
    1

选择 count(*) FROM tst_table WHERE tst_col IS NOT NULL;

 [COUNT]
 --------
    2

选择 count(*) FROM tst_table WHERE tst_col = NULL;

 [COUNT]
 --------
    0

行价值比较

(row_value_list) operator (row_value_list)

描述

The two row_value_list lists must have the same number of fields. Each side is evaluated and they are compared column-wise. Row comparisons are allowed when the operator is =,<>, <, , > or >=.

(x,y) < (1,1) 是相同的 x < 1 or y < 1;

<>, >= and also resolve columns as OR evaluations.

等于案例与其他案例略有不同。如果所有相应的成员都不为零,则认为两行相等;如果任何相应的成员是非零空和不等的,则行不等;否则,行比较的结果是未知的(null)。

For the <, , > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null); otherwise comparison of this pair of elements determines the result. For example, (1,NULL,5) < (3,4,1) yields true because the second and subsequent pair of elements are not considered.

尚不支持多列子查询结果:

选择 'succeeded' FROM DUAL WHERE (1,2) < (选择 3,4 FROM DUAL);

例子

/* In this example, only the first comparison, 1 < 3, is evaluated because NULL comparisons stop predicate evaluation */
选择 'succeeded' FROM DUAL WHERE (1, NULL, 5) <  (3, 4, 1);

 ---------
 succeeded

/* predicate evaluation is stopped by NULL comparison */
选择 'succeeded' FROM DUAL WHERE (NULL, 2) < (3, 4);
/* no rows returned */
选择 'succeeded' FROM DUAL WHERE (1, 2) < (3, NULL);

 ---------
 succeeded

/* This is the same query, decomposed into scalar comparisons */
选择 'succeeded' FROM DUAL WHERE (1 < 3) OR (2 < null);

 ---------
 succeeded

/* The same and a slightly altered query fails when AND is used to illustrate that both (2 < null) AND (2 > null) are both not true */
选择 'succeeded' FROM DUAL WHERE (1 < 3) AND (2 < null);
 /* no rows returned */
选择 'succeeded' FROM DUAL WHERE (1 < 3) AND (2 > null);
/* no rows returned */
选择 'succeeded' FROM DUAL WHERE (1,2) < (3,4);

 ---------
 succeeded

/* Rows can be nested, but both sides must agree on the number of elements (and sub-elements) */
选择 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,4);
Error 42000: Number of values in comparison operator operands do not match
SQL: 选择 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,4);

选择 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,(4,2));

 ---------
 succeeded