You are viewing an older version of this section. View current production version.
Comparison Operators and Functions
The following comparison operators and functions are supported.
Name | Description |
---|---|
BETWEEN ... AND ... |
Check if a value is within a range |
NOT BETWEEN ... AND ... |
Check if a value is not within a range |
= |
Equal operator |
<=> |
NULL-safe equal operator |
> |
Greater than operator |
>= |
Greater than or equal operator |
< |
Less than operator |
<= |
Less than or equal operator |
IN() |
Check whether a value is in a comma-separated list of values |
NOT IN() |
Check whether a value is not in a comma-separated list of values |
IS |
Compare a value to a boolean |
IS NOT |
Compare a value to the complement of a boolean |
IS NULL |
Check if a value is NULL |
IS NOT NULL |
Check if a value is not NULL |
ISNULL() |
Test whether the argument is NULL |
LIKE |
Check if a value matches a simple pattern |
NOT LIKE |
Check if a value does not match a simple pattern |
!=, <> |
Not equal operator |
strcmp() |
Compare two strings and return -1, 0, or 1 |
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 |
+------+------+
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 |
+------+-------------+------------+