---
title: "list_has_all"
slug: "dev-database-reference-sql-functions-lists-list-has-all"
updated: 2026-10-30T18:19:06Z
published: 2026-10-30T18:19:06Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-list-has-all"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# list_has_all

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) › Search a list.* Returns true if every element of the second list is also in the first list. `array_has_all` and the operator `l1 @> l2` are the same; `l2 <@ l1` asks the same question with the lists the other way round.

## Syntax

```
list_has_all(list, sublist)
array_has_all(list, sublist)
list @> sublist
sublist <@ list
```

| Argument | Type | Description |
| --- | --- | --- |
| `list` | a list of any element type | The list to search. |
| `sublist` | a list of a compatible element type | The elements that must all be in `list`. |

**Returns:** `BOOLEAN`.

**Aliases:** `array_has_all`, `@&gt;`; `&lt;@` with the arguments swapped.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_lists_list_has_all`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of four job candidates, each with a list of skills. Candidate 2 has a NULL skill, candidate 3 has none, and the last row has no values:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_lists_list_has_all"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.candidates"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, skills VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['sql', 'python', 'go']), "
    "(2, ['sql', NULL]), "
    "(3, []), "
    "(4, NULL)")
```

```
[]
[]
[(4,)]
```

## What it does

`list_has_all(list, sublist)` is true when each element of `sublist` is somewhere in `list`, like a set test:

- **Order and repeats don't matter.** `list_has_all([1, 2, 3], [3, 1])` and `list_has_all([1, 2], [1, 1, 1])` are true.
- **An empty `sublist` is always contained,** even in an empty list: `list_has_all([], [])` is true, `list_has_all([], [1])` is false.
- **Elements compare as in [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains):** by value for numbers (`[1.0]` is in `[1, 2]`), case-sensitive for strings, whole elements for nested lists.
- **NULL elements in `sublist` don't have to be found:** `list_has_all([1, NULL], [NULL])` is true, and so is `[&apos;sql&apos;, NULL] &lt;@ [&apos;sql&apos;, &apos;python&apos;, &apos;go&apos;, &apos;rust&apos;]` (candidate 2 in the [examples](/develop/docs/dev-database-reference-sql-functions-lists-list-has-all#examples)), although the second list has no NULL. [list_has_any](/develop/docs/dev-database-reference-sql-functions-lists-list-has-any) treats `[NULL]` differently (false).
- **`@&gt;` and `&lt;@`** are operator forms. `list @&gt; sublist` reads "list contains sublist"; `sublist &lt;@ list` reads "sublist is contained in list".

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A NULL `list`: `list_has_all(NULL, [1])` | `NULL` |
| A NULL `sublist`: `list_has_all([1, 2], NULL)` | `NULL` |
| `list_has_all([1, NULL], [NULL])` | `true` |
| `list_has_all([1, 2], [])` | `true` |
| `list_has_all([], [])` | `true` |
| `list_has_all([], [1])` | `false` |
| `list_has_all([1, 2, 3], [3, 1])` | `true` |
| `list_has_all([1, 2], [1, 1, 1])` | `true` |
| `list_has_all([1, 2], [2, 4])` | `false` |
| `list_has_all([1, 2], [1.0])` | `true` |
| `list_has_all([[1], [2]], [[2]])` | `true` |
| `[1, 2, 3] @&gt; [3, 1]`, `[1] &lt;@ [1, 2]`, `[] &lt;@ [1]` | `true` |
| `list_has_all([1, 2], [&apos;a&apos;])` (lists of different types) | Error: `&apos;list_has_all&apos; cannot compare lists of different types: &apos;INTEGER&apos; and &apos;VARCHAR&apos;` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-list-has-all#set-up-the-examples):

```
run("SELECT list_has_all(NULL, [1]), list_has_all([1, 2], NULL), list_has_all([1, NULL], [NULL]), "
    "list_has_all([1, 2], []), list_has_all([], []), list_has_all([], [1])")
run("SELECT list_has_all([1, 2, 3], [3, 1]), list_has_all([1, 2], [1, 1, 1]), list_has_all([1, 2], [2, 4]), "
    "list_has_all([1, 2], [1.0]), list_has_all([[1], [2]], [[2]])")
run("SELECT [1, 2, 3] @> [3, 1], [1] <@ [1, 2], [] <@ [1]")
run("SELECT list_has_all([1, 2], ['a'])")
```

```
[(None, None, True, True, True, False)]
[(True, True, False, True, True)]
[(True, True, True)]
InternalError: Binder Error: 'list_has_all' cannot compare lists of different types: 'INTEGER' and 'VARCHAR'
```

## Examples

Which candidates have both `sql` and `python`, with the function and with the operator. Candidate 3 has no skills, so the answer is false; candidate 4's NULL list gives NULL:

```
run(f"SELECT id, list_has_all(skills, ['sql', 'python']), skills @> ['sql', 'python'] FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, True, True), (2, False, False), (3, False, False), (4, None, None)]
```

The other way round: the candidates whose skills are all on an approved list. An empty list of skills passes, and candidate 2's NULL skill doesn't stop it from passing:

```
run(f"SELECT id FROM {TABLE} WHERE skills <@ ['sql', 'python', 'go', 'rust'] ORDER BY id LIMIT 10")
```

```
[(1,), (2,), (3,)]
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). The scan reads list columns whole, and every condition on a list, `list_has_all()`, `@&gt;` and `&lt;@` included, runs in the Query Engine:

- **In the `SELECT` list,** the scan sends the list column and the Query Engine compares it for each row it returns.
- **In `WHERE`,** the scan sends the list for every row, and a `FILTER` step in the Query Engine compares it and keeps the rows that match. No rows are skipped in storage.

The plan of a `WHERE` with `@&gt;` shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE skills @> ['sql', 'python']")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (skills @> ['sql', 'python│
│            '])            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           skills          │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Pair it with a pushed-down condition.** A comparison of a plain column with a constant, such as `id &gt; 1`, is evaluated in VAST DataBase, so the rows it rules out never reach the `FILTER` step.
- **Keep values you filter on often outside the list.** If most queries require one particular skill, store it in its own column when you write the rows, so that condition can be pushed down and `list_has_all` runs on fewer rows.

```
run(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_has_all(skills, ['sql']) ORDER BY id LIMIT 10")
```

```
[(2,)]
```

## Limitations and errors

- **Both arguments must be lists.** A scalar first argument fails with `No function matches the given name and argument types`, for example `&apos;list_has_all(INTEGER_LITERAL, INTEGER[])&apos;`; so does a scalar second argument (`&apos;list_has_all(INTEGER[], INTEGER_LITERAL)&apos;`). To test one value, use [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains).
- **The element types must be compatible.** Lists of numbers and of strings fail with `cannot compare lists of different types`; the operator forms name the operator, such as `&apos;@&gt;&apos; cannot compare lists of different types`.

```
run("SELECT list_has_all([1, 2], 1)")
run("SELECT [1] @> ['a']")
```

```
InternalError: Binder Error: No function matches the given name and argument types 'list_has_all(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: '@>' cannot compare lists of different types: 'INTEGER' and 'VARCHAR'
```

## Similar functions

| Function | Answers | Result for `[3, 1, NULL, 3, 10]` |
| --- | --- | --- |
| `list_has_all(l, [3, 10])`, `l @&gt; [3, 10]` | are all of these elements? | `true` |
| `[3] &lt;@ l` | are all elements of `[3]` in `l`? | `true` |
| [list_has_any](/develop/docs/dev-database-reference-sql-functions-lists-list-has-any)`(l, [3, 99])`, `l &amp;&amp; [3, 99]` | is any of these an element? | `true` |
| [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains)`(l, 3)` | is this one value an element? | `true` |

```
run("SELECT list_has_all(l, [3, 10]), l @> [3, 10], [3] <@ l, list_has_any(l, [3, 99]), l && [3, 99], "
    "list_contains(l, 3) FROM (SELECT [3, 1, NULL, 3, 10] AS l)")
```

```
[(True, True, True, True, True, True)]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions.
- [List overlap and containment](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#list-overlap-and-containment): the `&amp;&amp;`, `@&gt;` and `&lt;@` operators.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.