Comparison Functions and Operators¶

This page documents comparison behavior available in Polars SQL.

Comparison Operators¶

Range Operator: BETWEEN¶

The BETWEEN operator tests if a value is within a specified range. It uses the syntax value BETWEEN min AND max:

SELECT 3 BETWEEN 2 AND 6;

The statement shown above is equivalent to the following statement:

SELECT 3 >= 2 AND 3 <= 6;

To test if a value does not fall within the specified range use NOT BETWEEN:

SELECT 3 NOT BETWEEN 2 AND 6;

The statement shown above is equivalent to the following statement:

SELECT 3 < 2 OR 3 > 6;

The presence of NULL in a BETWEEN or NOT BETWEEN statement will result in the statement evaluating to NULL:

SELECT NULL BETWEEN 2 AND 4; -- null

SELECT 2 BETWEEN NULL AND 6; -- null

The BETWEEN and NOT BETWEEN operators can also be used to evaluate string arguments:

SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true

The value, min, and max parameters to BETWEEN and NOT BETWEEN must be mutually comparable after Polars SQL type coercion.

IS NULL and IS NOT NULL¶

The IS NULL and IS NOT NULL operators test whether a value is null (undefined). Both operators work for all data types.

Using NULL with IS NULL evaluates to true:

select NULL IS NULL; -- true

But any other constant does not:

SELECT 3.0 IS NULL; -- false

IS DISTINCT FROM and IS NOT DISTINCT FROM¶

In SQL a NULL value signifies an unknown value, so any comparison involving a NULL will produce NULL. The IS DISTINCT FROM and IS NOT DISTINCT FROM operators treat NULL as a known value and both operators guarantee either a true or false outcome even in the presence of NULL input:

SELECT NULL IS DISTINCT FROM NULL; -- false

SELECT NULL IS NOT DISTINCT FROM NULL; -- true

In the example shown above, a NULL value is not considered distinct from NULL. When you are comparing values which may include NULL use these operators to guarantee either a TRUE or FALSE result.

The following truth table demonstrate the handling of NULL in IS DISTINCT FROM and IS NOT DISTINCT FROM:

LIKE¶

The LIKE operator is used to match a specified character pattern in a string. Patterns can contain regular characters as well as wildcards. Wildcard characters can be escaped using the single character specified for the ESCAPE parameter. Matching is case sensitive, and the pattern must match the whole string.

Syntax:

expression LIKE pattern [ ESCAPE 'escape_character' ]

if pattern or escape_character is null, the expression evaluates to null.

Examples:

 SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
 WHERE name LIKE '%b%'
 --returns 'abc' and  'bcd'

 SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
 WHERE name LIKE '_b%'
 --returns 'abc'

 SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
 WHERE name LIKE 'b%'
 --returns 'bcd'

 SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS t (name)
 WHERE name LIKE 'B%'
 --returns nothing

 SELECT * FROM (VALUES ('a_c'), ('_cd'), ('cde')) AS t (name)
 WHERE name LIKE '%#_%' ESCAPE '#'
 --returns 'a_c' and  '_cd'

 SELECT * FROM (VALUES ('a%c'), ('%cd'), ('cde')) AS t (name)
 WHERE name LIKE '%#%%' ESCAPE '#'
 --returns 'a%c' and  '%cd'

SELECT 'ab' || chr(10) || 'c' LIKE 'ab'  --chr(10) is a newline character
 --returns 'false'

IN¶

The IN comparison operator in SQL is used to compare a value with a list of literal values that have been specified. The IN operator returns TRUE if the value matches any of the literal values in the list. The IN operator can be used to fetch records according to multiple scalar values specified in a WHERE clause. A subquery or list of values must be specified in parentheses. Subqueries used with IN must return exactly one column.

WHERE column [NOT] IN ('value1','value2');
WHERE column [NOT] IN ( subquery )

Examples:

SELECT * FROM region WHERE name IN ('AMERICA', 'EUROPE');

SELECT * FROM region WHERE name IN ('NULL', 'AMERICA', 'EUROPE');

IN as OR¶

The OR operator is used to filter the results of a query based on more than one condition. It returns a record if any of the conditions separated by OR is TRUE. The values in the clause are used for multiple comparisons that are combined as a logical OR. The preceding query is equivalent to the following query:

Example:

SELECT * FROM region WHERE name = 'AMERICA' OR name = 'EUROPE';

NOT IN¶

The NOT IN comparison operator in SQL is used to exclude the rows that match any value in a list or subquery.

You can negate the comparisons by adding NOT, and get all other regions except the values in list:

Example:

SELECT * FROM region WHERE name NOT IN ('AMERICA', 'EUROPE');

When using a subquery to determine the values to use in the comparison, the subquery must return a single column and one or more rows.

Example:

SELECT id, name FROM region WHERE name IN (SELECT name FROM region WHERE id IN (3,4));