You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > SQL Comparison Operators

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

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

Note: 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.

Note: 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);`
not enough values on right-hand side of row-to-row comparison```
SELECT 'succeeded' FROM DUAL WHERE (1,(2,3)) < (3,(4,2)); ```
---------
succeeded
```