SQL Comparison Operators

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).

NuoDB supports the comparison operators described in the following table:

Operator Description

<

less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

=

equal

<> or !=

not equal

BETWEEN / NOT BETWEEN

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

a BETWEEN x AND y

is equivalent to:

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

is equivalent to:

a < x OR a > y

Examples

# Select rows from the hockey table, choosing players where the value for
# the player's number contains a value greater than or equal to 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

IS NULL / IS NOT NULL

To check whether a value is or is not null, use the constructs:

expression IS NULL
expression IS NOT NULL
Do not write expression = NULL because NULL is not "equal 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.

Example

USE TEST
CREATE TABLE tst_table (tst_col STRING);
INSERT INTO tst_table VALUES ('row1'),('row2'),(NULL);
SELECT count(*) FROM tst_table WHERE tst_col IS NULL;

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

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

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

SELECT count(*) FROM tst_table WHERE tst_col = NULL;

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

Row Value Comparison

(row_value_list) operator (row_value_list)

Description

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) is the same as x < 1 or y < 1;

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

The equals case works slightly differently from the others. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of the row comparison is unknown (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.

Multi-column subquery results are not yet supported:

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

Examples

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

 ---------
 succeeded

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

 ---------
 succeeded

/* This is the same query, decomposed into scalar comparisons */
SELECT '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 */
SELECT 'succeeded' FROM DUAL WHERE (1 < 3) AND (2 < null);
 /* no rows returned */
SELECT 'succeeded' FROM DUAL WHERE (1 < 3) AND (2 > null);
/* no rows returned */
SELECT '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) */
SELECT 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,4);
Error 42000: Number of values in comparison operator operands do not match
SQL: SELECT 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,4);

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

 ---------
 succeeded