You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > 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 |

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

# 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;`

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;`

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

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

`expression`

`IS NULL`

`expression`

`IS NOT NULL`

**Note: **Do not write

because `expression`

= NULL`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.

`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_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)`

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.

**Note: **Multi-column subquery results are not yet supported:

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

/* 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);`

not enough values on right-hand side of row-to-row comparison`SELECT 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,(4,2));`

--------- succeeded