---
schema_version: "1"
id: 701d8442f5b8c7a57f54729563f039d4c9b73d574da3027ac6fa421617082ecd
title: Comparison Operators
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/comparison.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/comparison.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 00edd9986a08a403706f6a12b6776da739110a5da158a611b31ca9176f513ea3
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# Comparison Operators

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Comparison operators compare a value against another value. Comparison operators are binary operators that return values of type `boolean`.

| Operator     | Description              |
| :----------- | :----------------------- |
| `<`          | less than                |
| `>`          | greater than             |
| `<=`         | less than or equal to    |
| `>=`         | greater than or equal to |
| `=`          | equal                    |
| `<>` or `!=` | not equal                |

## `BETWEEN`

The `BETWEEN` predicate simplifies range tests.

```sql
<a> BETWEEN <x> AND <y>
<a> NOT BETWEEN <x> AND <y>
```

`a BETWEEN x AND y` is equivalent to `a >= x AND a <= y`. `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`.

While the normal comparison operators and `BETWEEN` ignore NULLs.

## `NULL` Expressions

These comparison functions are available in Data 360 SQL.

```sql
<a> IS DISTINCT FROM <b>
<a> IS NOT DISTINCT FROM <b>
<expression> IS NULL
<expression> IS NOT NULL
<expression> ISNULL -- nonstandard syntax
<expression> NOTNULL -- nonstandard syntax
<boolean_expression> IS TRUE
<boolean_expression> IS NOT TRUE -- is false or unknown
<boolean_expression> IS FALSE
<boolean_expression> IS NOT FALSE -- is true or unknown
<boolean_expression> IS UNKNOWN
<boolean_expression> IS NOT UNKNOWN -- is true or false
```

Ordinary comparison operators yield null (signifying "unknown") if either input is null. For example, `7 = NULL` returns null. If this behavior doesn't fit your use case, use the `IS NOT DISTINCT FROM` predicates.

- `a IS DISTINCT FROM b`
- `a IS NOT DISTINCT FROM b`.

For non-null inputs, `IS DISTINCT FROM` is the same as the `<>` operator. However, if both inputs are null it returns false, and if one input is null it returns true. `IS NOT DISTINCT FROM` is identical to `=` for non-null inputs, but returns true if both inputs are null, and false if one input is null. These predicates act as though null is a normal data value, rather than an unknown.

To check whether a value is or isn't null, use the predicates:

If either input is null, ordinary comparison operators yield null, signifying “unknown”. For example, `7 = NULL` returns null. If this behavior doesn’t fit your use case, use the `IS NOT DISTINCT FROM` predicates.

- `expression IS NOT NULL`
- `expression ISNULL`
- `expression NOTNULL`.

For non-null inputs, `IS DISTINCT FROM` is the same as the `<>` operator. However, if both inputs are null, it returns false. If one input is null, it returns true. `IS NOT DISTINCT FROM` is identical to `=` for non-null inputs, but it returns true if both inputs are null and false if one input is null. These predicates act as though null is a normal data value rather than an unknown.

To check whether a value is or isn’t null, use the predicates.

- `IS NOT TRUE`
- `IS FALSE`
- `IS NOT FALSE`
- `IS UNKNOWN`
- `IS NOT UNKNOWN`.

You can also test boolean values by using the predicates.

## `IN` and `NOT IN`

`IN` and `NOT IN` compare an expression against multiple values. They use the syntax

```sql
<expression> IN ( <value>, ... )
<expression> NOT IN ( <value>, ... )
```

These tests always return true or false, even if the operand is null. A null input is treated as the logical value “unknown”. Notice that `IS UNKNOWN` and `IS NOT UNKNOWN` are effectively the same as `IS NULL` and `IS NOT NULL`, respectively, except that the input expression is of Boolean type.

The right-hand side is a parenthesized list of scalar expressions. The value from the left-hand side is compared against each of the expressions from the right-hand side.

`IN` and `NOT IN` compare an expression against multiple values. They use this syntax.

For `NOT IN` the result is true if the left-hand expression's result is unequal to all of the right-hand expressions. This is a shorthand notation for `expression <> value1 AND expression <> value2 AND ...`

If the left-hand expression returns null, or if there are no equal right-hand values and at least one right-hand expression returns null, the result of the `IN`/`NOT IN` construct is null.

`IN` and `NOT IN` are syntactically related to the [subquery forms](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/subquery-comparison.md#in) but don't involve subqueries.