Quick Overview of the List Operators in DuckDB

by

DuckDB provides us with a bunch of functions and operators that we can use when working with lists. These include operators for doing things like concatenating lists to performing calculations on the lists. Most of these operators are aliases for a corresponding function, so we can choose which one to use in those cases.

This article provides an overview of the six list operators available in DuckDB, along with basic examples to demonstrate their usage.

The Operators

  • &&: Checks for overlapping elements within two lists.
  • @>: Checks whether all elements in the list to the right of the operator are in the list to its left.
  • <@: Checks whether all elements in the list to the left of the operator are in the list to its right.
  • ||: Concatenates two lists.
  • <=>: Computes the cosine distance between two lists.
  • <->: Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.

Below are examples of each operator.

The && Operator

The && operator is an alias for the list_has_any() function, which checks for overlapping elements within two lists. It returns true if any elements exist is both lists.

Example:

SELECT ['Duck', 'Zebra', 'Elephant'] && ['Cat', 'Duck', 'Ant'];

Output:

true

In this example, Duck is in both lists and so the result is true.

Here’s what happens when there’s no overlap between the lists:

SELECT ['Duck', 'Zebra', 'Elephant'] && ['Cat', 'Dog', 'Ant'];

Output:

false

This time it’s false, as expected.

The @> Operator

The @> operator is an alias for the list_has_all() function, which checks whether all elements in the list to the right of the operator are in the list to its left.

Example:

SELECT ['a', 'b', 'c', 'd' ] @> ['a', 'b', 'c'];

Output:

true

In this case all elements in the list on the right are in list on the left.

Here’s what happens if we switch the lists around:

SELECT ['a', 'b', 'c' ] @> ['a', 'b', 'c', 'd'];

Output:

false

This time it’s false, because it’s not true that all elements in the right list are in the left list.

The <@ Operator

The <@ operator is similar to the @> operator, except that it works in the opposite direction. It checks whether all elements in the list to the left of the operator are in the list to its right.

So here’s what happens if we use the same example from above, but we switch @> for <@:

SELECT ['a', 'b', 'c', 'd' ] <@ ['a', 'b', 'c'];

Output:

false

We get the opposite outcome, as expected.

Here’s what happens when we switch the lists around:

SELECT ['a', 'b', 'c' ] <@ ['a', 'b', 'c', 'd'];

Output:

true

This time it’s true.

The || Operator

The || operator concatenates two lists. It’s similar to the list_concat() function, but is different in the way it handles NULL inputs. With the || operator, any NULL input is returned as NULL. With list_concat() on the other hand, NULL values are skipped.

Here’s an example of using the || operator:

SELECT ['Tea', 'Coffee', null ] || ['Water', 'Juice'];

Output:

[Tea, Coffee, NULL, Water, Juice]

The two lists are concatenated as expected. The NULL value is preserved.

Here it is compared to list_concat():

SELECT 
    ['Tea', 'Coffee', null ] || ['Water', 'Juice'] AS "||_operator",
    list_concat(['Tea', 'Coffee' ], ['Water', 'Juice']) AS "list_concat";

Output:

+-----------------------------------+-----------------------------+
|            ||_operator            |         list_concat         |
+-----------------------------------+-----------------------------+
| [Tea, Coffee, NULL, Water, Juice] | [Tea, Coffee, Water, Juice] |
+-----------------------------------+-----------------------------+

Similar result, except for the NULL value. The || operator kept the NULL value whereas list_concat() didn’t.

The <=> Operator

The <=> operator computes the cosine distance between two lists. It’s an alias for list_cosine_distance().

Example:

SELECT [10, 25, 31] <=> [9, 30, 27];

Output:

0.01234282686747401

One thing to be mindful of is that the list dimensions need to be equal. Here’s what happens when they’re not:

SELECT [10, 25, 31] <=> [9, 30, 27, 2];

Output:

Invalid Input Error:
<=>: list dimensions must be equal, got left length '3' and right length '4'

The <-> Operator

The <-> operator calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length. This operator is an alias for the list_distance() function.

Example:

SELECT [10, 25, 31] <-> [10, 25, 37];

Output:

6.0

Here it is with various other list values:

SELECT 
    [10, 25, 31] <-> [10, 24, 37] AS a,
    [10, 25, 31] <-> [4, 180, 2] AS b,
    [1, 1, 1] <-> [2, 2, 2] AS c,
    [1, 1, 1] <-> [1, 2, 1] AS d;

Output:

+-------------------+--------------------+--------------------+-----+
|         a         |         b          |         c          |  d  |
+-------------------+--------------------+--------------------+-----+
| 6.082762530298219 | 157.80367549585148 | 1.7320508075688772 | 1.0 |
+-------------------+--------------------+--------------------+-----+

As with the <=> operator, if the list dimensions aren’t equal, we’ll get an error:

SELECT [10, 25, 31] <-> [10, 24, 37, 5];

Output:

Invalid Input Error:
<->: list dimensions must be equal, got left length '3' and right length '4'