The following comparison operators and functions are supported.

BETWEEN, NOT BETWEEN

BETWEEN and NOT BETWEEN check if values are in or not in a range that includes the two endpoints. For example:

CREATE TABLE r(t TEXT);
INSERT r VALUES("blue"), ("orange"), ("yellow");
SELECT * FROM r WHERE t BETWEEN "blue" AND "pink";
****
+--------+
| t      |
+--------+
| blue   |
| orange |
+--------+

NULL Handling

Unless otherwise noted, NULL handling is as defined in the SQL standard; the result of a comparison with NULL is NULL, which, when used to qualify rows, is treated as false.

IS NULL, IS NOT NULL

IS NULL and IS NOT NULL are postfix operators; the argument appears on the left. They return true if the argument is NULL or NOT NULL, respectively. For example:

CREATE TABLE s(b BOOLEAN, t TEXT);
INSERT s VALUES(true, "blue"), (false, "red"), (true, NULL);
SELECT * FROM s WHERE t IS NULL;
****
+------+------+
| b    | t    |
+------+------+
|    1 | NULL |
+------+------+

SELECT 'x' IS NULL, NULL IS NULL, NULL IS NOT NULL;
****
+-------------+--------------+------------------+
| 'x' IS NULL | NULL IS NULL | NULL IS NOT NULL |
+-------------+--------------+------------------+
|           0 |            1 |                0 |
+-------------+--------------+------------------+

ISNULL()

The ISNULL() function behaves the same as IS NULL except it uses the standard function syntax where the single argument appears on the right, e.g. ISNULL(expr) returns true if expr is NULL and false otherwise.

NULL-safe equal operator: <=>

The NULL-safe equal operator, <=> returns true if both arguments are NULL. Otherwise, it is the same as the = operator. For example:

SELECT * FROM s AS s1, s AS s2 WHERE s1.t <=> s2.t;
****
+------+------+------+------+
| b    | t    | b    | t    |
+------+------+------+------+
|    1 | blue |    1 | blue |
|    1 | NULL |    1 | NULL |
|    0 | red  |    0 | red  |
+------+------+------+------+

LIKE, NOT LIKE

The LIKE and NOT LIKE operators are useful for matching patterns containing simple wildcards. The % character appearing in a pattern matches any string and the _ character matches a single character. For example:

SELECT * FROM s WHERE t LIKE 'r_d';
****
+------+------+
| b    | t    |
+------+------+
|    0 | red  |
+------+------+

SELECT * FROM s WHERE t NOT LIKE 'r%';
****
+------+------+
| b    | t    |
+------+------+
|    1 | blue |
+------+------+

You can use the backslash character \ as an escape character with a LIKE operator so that SingleStore DB interprets the wildcard character as a literal character. For example:

SELECT "foo_" LIKE "foo\_";
****
+---------------------+
| "foo_" LIKE "foo\_" |
+---------------------+
|                   1 |
+---------------------+

IN(), NOT IN()

The IN() and NOT IN() functions filter the results after checking if the values are in or not in the list of parameters specified. The maximum number of parameters that can be specified within the IN() and NOT IN() functions is 1048576 (this limit applies to the number of parameters, not the size of parameters). The following example demonstrates the use of IN() and NOT IN() functions.

SELECT * FROM hrRec;
****
+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Adam      | Gale      | Brooklyn |     40 |
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+

SELECT * FROM hrRec WHERE City IN('DC','New York');
****
+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+

SELECT * FROM hrRec WHERE City NOT IN('DC','Brooklyn');
****
+-----------+----------+----------+--------+
| FirstName | LastName | City     | Tenure |
+-----------+----------+----------+--------+
| Samantha  | Beck     | New York |     44 |
| Ron       | Fletcher | New York |     23 |
+-----------+----------+----------+--------+

strcmp()

The strcmp(v1, v2) function takes two string values. It returns 0 if they are equal, -1 if v1 is less than v2, and 1 if v2 is greater than v1. It returns NULL if either input is NULL. For example:

SELECT r1.t, r2.t, strcmp(r1.t, r2.t) FROM r AS r1, r AS r2;
****
+--------+--------+--------------------+
| t      | t      | strcmp(r1.t, r2.t) |
+--------+--------+--------------------+
| blue   | blue   |                  0 |
| blue   | orange |                 -1 |
| blue   | yellow |                 -1 |
| orange | blue   |                  1 |
| orange | orange |                  0 |
| orange | yellow |                 -1 |
| yellow | blue   |                  1 |
| yellow | orange |                  1 |
| yellow | yellow |                  0 |
+--------+--------+--------------------+

Where operators can be used

Comparison operators and functions can appear as conditions, such as part of the WHERE clause or join ON conditions. In addition, they can appear in the SELECT list or other locations where expressions can appear. In the latter case, they return 0 if false, 1 if true, and NULL if an input is NULL. For example:

SELECT t, t LIKE 'r%', t != 'red' FROM s WHERE t = 'red';
****
+------+-------------+------------+
| t    | t LIKE 'r%' | t != 'red' |
+------+-------------+------------+
| red  |           1 |          0 |
+------+-------------+------------+

Using Comparison Operators with Date and Time Functions

The following examples demonstrate how to use different comparison operators with date and time functions.

Consider a table Emp with the following values:

SELECT * FROM Emp;
****
+-------+------------+
| Name  | DOJ        |
+-------+------------+
| Jim   | 2019-01-05 |
| Rob   | 2020-01-04 |
| Peter | 2021-02-12 |
| Jack  | 2019-03-20 |
| Mary  | 2020-07-03 |
+-------+------------+

To filter employees who joined in the month of January:

SELECT * FROM Emp WHERE MONTH(DOJ) = 01;
****
+------+------------+
| Name | DOJ        |
+------+------------+
| Jim  | 2019-01-05 |
| Rob  | 2020-01-04 |
+------+------------+

To filter employees who joined through the current date, assuming that the CURRENT_DATE is 2021-02-12:

SELECT * FROM Emp WHERE DOJ < CURRENT_DATE;
+------+------------+
| Name | DOJ        |
+------+------------+
| Jim  | 2019-01-05 |
| Rob  | 2020-01-04 |
| Jack | 2019-03-20 |
| Mary | 2020-07-03 |
+------+------------+

To filter employees who joined in a specific date range:

SELECT * FROM Emp WHERE DOJ BETWEEN DATE('2019-01-01') AND DATE('2020-01-30');
****
+------+------------+
| Name | DOJ        |
+------+------------+
| Jim  | 2019-01-05 |
| Rob  | 2020-01-04 |
| Jack | 2019-03-20 |
+------+------------+

Was this Article Helpful?

Thanks For Your Feedback!