<a id="select-statement"></a>

# SELECT

Querying data from data is done using a `SELECT` statement:

```cql
select_statement: SELECT [ JSON ] [ DISTINCT ] ( `select_clause` | '*' )
                : [ FROM `table_name` ]
                : [ WHERE `where_clause` ]
                : [ GROUP BY `group_by_clause` ]
                : [ ORDER BY `ordering_clause` ]
                : [ ORDER BY ( `vector_column_name` ANN OF `vector` | ANN '(' `vector_column_name` ',' `vector` ')' ) LIMIT `integer` ]
                : [ WHERE BM25 '(' `column_name` ',' `term` ')' '>' 0 ORDER BY BM25 '(' `column_name` ',' `term` ')' LIMIT `integer` ]
                : [ ORDER BY `fusion_function` '(' `search` ( ',' `search` )+ ')' LIMIT `integer` ]
                : [ PER PARTITION LIMIT (`integer` | `bind_marker`) ]
                : [ LIMIT (`integer` | `bind_marker`) ]
                : [ ALLOW FILTERING ]
                : [ BYPASS CACHE ]
                : [ USING TIMEOUT `timeout` ]
select_clause: `selector` [ AS `identifier` ] ( ',' `selector` [ AS `identifier` ] )*
selector: ( `column_name`
        : | CAST '(' `selector` AS `cql_type` ')'
        : | `function_name` '(' [ `selector` ( ',' `selector` )* ] ')'
        : | COUNT '(' '*' ')'
        : | literal
        : | bind_marker
        : )
        : ( '.' `field_name` | '[' `term` ']' )*
where_clause: `relation` ( AND `relation` )*
group_by_clause: `column_name` (',' `column_name` )*
relation: `column_name` `operator` `term`
        : '(' `column_name` ( ',' `column_name` )* ')' `operator` `tuple_literal`
        : TOKEN '(' `column_name` ( ',' `column_name` )* ')' `operator` `term`
        : `column_name` IS [ NOT ] NULL
operator: '=' | '<' | '>' | '<=' | '>=' | IN | NOT IN | CONTAINS | CONTAINS KEY
ordering_clause: `column_name` [ ASC | DESC ] ( ',' `column_name` [ ASC | DESC ] )*
timeout: `duration`
literal: number | 'string' | boolean | NULL | tuple_literal | list_literal | map_literal
bind_marker: '?' | ':' `identifier`
```

For instance:

```cql
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207);
SELECT name AS user_name, occupation AS user_occupation FROM users;

SELECT time, value
FROM events
WHERE event_type = 'myEvent'
  AND time > '2011-02-03'
  AND time <= '2012-01-01'

SELECT COUNT (*) AS user_count FROM users;

SELECT * FROM users WHERE event_type = 'myEvent' USING TIMEOUT 50ms;
```

The `SELECT` statement reads one or more columns for one or more rows in a table. It returns a result-set of the rows
matching the request, where each row contains the values for the selection corresponding to the query. Additionally,
functions, including aggregation ones, can be applied to the result.

A `SELECT` statement contains at least a [selection clause](#selection-clause) and typically the name of the table
on which the selection is on (note that CQL does **not** support joins or sub-queries, and thus a select statement only
applies to a single table). The `FROM` clause may be omitted; the statement then evaluates the selected expressions
and returns them as a single row (see [SELECT without FROM](#select-without-from)). In most cases, a select will also have a
[where clause](#where-clause) and it can optionally have additional clauses to [order](#ordering-clause) or
[limit](#limit-clause) the results. Lastly, [queries that require filtering](#allow-filtering) can be allowed
if the `ALLOW FILTERING` flag is provided.

If your `SELECT` query results in what appears to be missing data, see this [KB Article](https://docs.scylladb.com/manual/branch-2026.4/kb/cqlsh-results.md) for information.

<a id="selection-clause"></a>

## Selection clause

The `select_clause` determines which columns need to be queried and returned in the result-set, as well as any
transformation to apply to this result before returning. It consists of a comma-separated list of *selectors* or,
alternatively, of the wildcard character (`*`) to select all the columns defined in the table.

### Selectors

A `selector` can be one of the following:

- A column name of the table selected to retrieve the values for that column.
- A casting, which allows you to convert a nested selector to a (compatible) type.
- A function call, where the arguments are selector themselves.
- A call to the [COUNT function](https://docs.scylladb.com/manual/branch-2026.4/cql/functions.md#count-function), which counts all non-null results.
- A literal value: a constant, or a collection or tuple literal.
- A bind variable (? or :name).

Literals can be used as top-level selectors. Their type is inferred automatically.
The default inferred type depends on the literal kind:

- Whole numbers: the smallest of `int`, `bigint`, or `varint` that fits the
  value. For example, `1` is inferred as `int`, `10000000000` as `bigint`,
  and very large integers as `varint`. Note that `tinyint` and `smallint` are
  never inferred — use a cast (`CAST(1 AS tinyint)` or the `(tinyint)1` type hint)
  to obtain them.
- Decimal or scientific-notation numbers (e.g. `3.14`, `1e6`): always `double`.
  `float` and `decimal` are never inferred.
- Strings: `text`.
- Booleans: `boolean`.
- UUIDs: `uuid`.
- Hex literals (e.g. `0xCAFE`): `blob`.
- Durations: `duration`.

Collection and tuple literals are supported, with element types inferred recursively.
When collection elements have different sizes within the same numeric family, they are
widened to the largest type within two lossless chains:

- Integer: `tinyint` < `smallint` < `int` < `bigint` < `varint`
- Floating-point: `float` < `double`

Cross-family widening (e.g. `int` and `double`) is not supported and requires an
explicit `CAST`.

Bind markers (`?` and `:name`) cannot be used as *top-level* selectors, since their
type cannot be inferred without context; they can be used nested inside a function
call, where the parameter type provides it.

Examples:

```cql
SELECT 1, 'hello', true FROM t;            -- int, text, boolean
SELECT [1, 2, 3] FROM t;                   -- frozen<list<int>>
SELECT {'a': 1, 'b': 2} FROM t;            -- frozen<map<text, int>>
SELECT [1, 10000000000] FROM t;            -- frozen<list<bigint>>, widened from int + bigint
SELECT CAST(1 AS bigint) FROM t;           -- bigint via explicit cast
```

### Aliases

Every *top-level* selector can also be aliased (using AS). If so, the name of the corresponding column in the result
set will be that of the alias. For instance:

```cql
// Without alias
SELECT intAsBlob(4) FROM t;

//  intAsBlob(4)
// --------------
//  0x00000004

// With alias
SELECT intAsBlob(4) AS four FROM t;

//  four
// ------------
//  0x00000004
```

#### NOTE
Currently, aliases aren’t recognized anywhere else in the statement where they are used (not in the `WHERE`
clause, not in the `ORDER BY` clause, …). You must use the original column name instead.

<a id="select-writetime-ttl"></a>

### `WRITETIME` and `TTL` function

Selection supports two special functions (which aren’t allowed anywhere else): `WRITETIME` and `TTL`. Both functions
take only one argument, which must be either a column name, a subscript expression of the form `collection_column[element]`
for an individual element of a non-frozen map or set collection, or a field selection of the form `udt_column.field`
for an individual field of a non-frozen user-defined type column (so, for instance, `TTL(3)` is invalid).

Those functions let you retrieve meta-information that is stored internally for each column, namely:

- `WRITETIME` retrieves the timestamp used when writing the column. The timestamp is typically the number of *microseconds* since the [Unix epoch](https://en.wikipedia.org/wiki/Unix_time) (January 1st 1970 at 00:00:00 UTC).

You can read more about the `TIMESTAMP` retrieved by `WRITETIME` in the [UPDATE](https://docs.scylladb.com/manual/branch-2026.4/cql/dml.md#update-parameters) section.

- `TTL` retrieves the remaining time to live (in *seconds*) for the value of the column, if it set to expire, or `null` otherwise.

You can read more about TTL in the [documentation](https://docs.scylladb.com/manual/branch-2026.4/cql/time-to-live.md) and also in [this ScyllaDB University lesson](https://university.scylladb.com/courses/data-modeling/lessons/advanced-data-modeling/topic/expiring-data-with-ttl-time-to-live/).

**Using** `WRITETIME` **and** `TTL` **on map and set collection elements**

For a non-frozen map or set column, each element is stored as an independent cell and may have its own write timestamp and TTL.
You can retrieve the per-element timestamp or TTL by subscripting the column with the element:

```cql
-- Create a table with a map column and a set column
CREATE TABLE t (pk int PRIMARY KEY, m map<int, int>, s set<int>);

-- Insert elements with an explicit timestamp
INSERT INTO t (pk, m, s) VALUES (1, {1: 10, 2: 20}, {1, 2}) USING TIMESTAMP 1000000;

-- Update one element with a newer timestamp
UPDATE t USING TIMESTAMP 2000000 SET m = m + {2: 30}, s = s + {3} WHERE pk = 1;

-- WRITETIME(m[key]) returns the write timestamp of that specific map element
SELECT WRITETIME(m[1]), WRITETIME(m[2]) FROM t WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- WRITETIME(s[element]) returns the write timestamp of that specific set element
SELECT WRITETIME(s[1]), WRITETIME(s[3]) FROM t WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- TTL(m[key]) returns the remaining TTL of that specific map element
INSERT INTO t (pk, m) VALUES (1, {1: 10}) USING TTL 3600;
SELECT TTL(m[1]) FROM t WHERE pk = 1;
-- Returns the remaining TTL in seconds for element m[1]
```

Note that `WRITETIME(m)` and `TTL(m)` on an entire non-frozen map or set column are **not** supported —
since each element may have a different timestamp or TTL, there is no single meaningful value to return.
Only `WRITETIME(m[key])` and `TTL(m[key])` (for a specific map key) or `WRITETIME(s[element])`
and `TTL(s[element])` (for a specific set element) are allowed.

**Using** `WRITETIME` **and** `TTL` **on user-defined type fields**

For a non-frozen user-defined type (UDT) column, each field is stored as an independent cell and may have its own
write timestamp and TTL. You can retrieve the per-field timestamp or TTL using dot notation:

```cql
CREATE TYPE address (street text, city text);
CREATE TABLE person (pk int PRIMARY KEY, addr address);

-- Write two fields at different timestamps
UPDATE person USING TIMESTAMP 1000000 SET addr.street = '123 Main St' WHERE pk = 1;
UPDATE person USING TIMESTAMP 2000000 SET addr.city = 'Springfield' WHERE pk = 1;

-- WRITETIME(udt_column.field) returns the write timestamp of that specific UDT field
SELECT WRITETIME(addr.street), WRITETIME(addr.city) FROM person WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- TTL(udt_column.field) returns the remaining TTL of that specific UDT field
UPDATE person USING TTL 3600 SET addr.street = 'Oak Ave' WHERE pk = 1;
SELECT TTL(addr.street) FROM person WHERE pk = 1;
-- Returns the remaining TTL in seconds for addr.street
```

<a id="where-clause"></a>

## The `WHERE` clause

The `WHERE` clause specifies which rows must be queried. It is composed of relations on the columns that are part of
the `PRIMARY KEY`, and relations can be joined only with `AND` (`OR` and other logical operators are not supported).

Not all relations are allowed in a query. For instance, non-equal relations (where `IN` is considered as an equal
relation) on a partition key are not supported (see the use of the `TOKEN` method below to do non-equal queries on
the partition key). Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations
on them restricted to the relations that let you select a **contiguous** (for the ordering) set of rows. For
instance, given:

```cql
CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
)
```

The following query is allowed:

```cql
SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND blog_title='John''s Blog'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'
```

But the following query is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are
set):

```cql
// Needs a blog_title to be set to select ranges of posted_at
SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'
```

When specifying relations, the `TOKEN` function can be used on the `PARTITION KEY` column to query. In that case,
rows will be selected based on the token of their `PARTITION_KEY` rather than on the value. Note that the token of a
key depends on the partitioner in use and that, in particular, the RandomPartitioner won’t yield a meaningful order. Also
note that ordering partitioners always order token values by bytes (so even if the partition key is of type int,
`token(-1) > token(0)` in particular). For example:

```cql
SELECT * FROM posts
 WHERE token(userid) > token('tom') AND token(userid) < token('bob')
```

Moreover, the `IN` relation is only allowed on the last column of the partition key and on the last column of the full
primary key.

It is also possible to “group” `CLUSTERING COLUMNS` together in a relation using the tuple notation. For instance:

```cql
SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')
```

will request all rows that sort after the one having “John’s Blog” as `blog_title` and ‘2012-01-01’ for `posted_at`
in the clustering order. In particular, rows having a `posted_at <= '2012-01-01'` will be returned as long as their
`blog_title > 'John''s Blog'`.

The tuple notation may also be used for `IN` clauses on clustering columns:

```cql
SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'))
```

This tuple notation is different from boolean grouping. For example, the following query is not supported:

```cql
SELECT * FROM users
 WHERE (country = 'BR' AND state = 'SP')
```

because parentheses are only allowed around a single relation, so this works: `(country = 'BR') AND (state = 'SP')`, but this does not: `(country = 'BR' AND state = 'SP')`.
Similarly, an extended query of the form of:

```cql
SELECT * FROM users
 WHERE (country = 'BR' AND state = 'SP')
   OR (country = 'BR' AND state = 'RJ')
```

won’t work due to both: grouping boolean expressions and not supporting `OR`, so when possible,
rewrite such queries with `IN` on the varying column, for example
`country = 'BR' AND state IN ('SP', 'RJ')`, or run multiple queries and merge
the results client-side.

The `CONTAINS` operator may only be used on collection columns (lists, sets, and maps). In the case of maps,
`CONTAINS` applies to the map values. The `CONTAINS KEY` operator may only be used on map columns and applies to the
map keys.

<a id="group-by-clause"></a>

## Grouping results

The `GROUP BY` option lets you condense into a single row all selected rows that share the same values for a set of columns.
Using the `GROUP BY` option, it is only possible to group rows at the partition key level or at a clustering column level.
The `GROUP BY` arguments must form a prefix of the primary key.

For example, if the primary key is `(p1, p2, c1, c2)`, then the following queries are valid:

```cql
GROUP BY p1
GROUP BY p1, p2
GROUP BY p1, p2, c1
GROUP BY p1, p2, c1, c2
```

The following should be considered when using the `GROUP BY` option:

* If a primary key column is restricted by an equality restriction, it is not required to be present in the `GROUP BY` clause.
* Aggregate functions will produce a separate value for each group.
* If no `GROUP BY` clause is specified, aggregate functions will produce a single value for all the rows.
* If a column is selected without an aggregate function, in a statement with a `GROUP BY`, the first value encounter in each group will be returned.

<a id="ordering-clause"></a>

## Ordering results

The default order for a SELECT statement depends on the default clustering order of a table, which is defined when
the table is created - it is `ASC` (ascendant) by default, but can be changed using the `WITH CLUSTERING ORDER BY`
option. See [CREATE TABLE](https://docs.scylladb.com/manual/branch-2026.4/cql/ddl.md#create-table-statement).

The `ORDER BY` clause allows you to configure a non-default order of the returned result. It takes a list of column names
along with the order for the column as an argument  (`ASC` for ascendant and `DESC` for descendant, omitting the default order).

Currently, the possible orderings are limited by the [clustering order](https://docs.scylladb.com/manual/branch-2026.4/cql/ddl.md#clustering-order) defined on the table:

- If the table has been defined without any specific `CLUSTERING ORDER`, then allowed orderings are the order
  induced by the clustering columns and the reverse of that one.
- Otherwise, the orderings allowed are the order of the `CLUSTERING ORDER` option and the reversed one.

<a id="vector-queries"></a>

## Vector queries ScyllaDB Cloud

The `ORDER BY` clause can also be used with vector columns to perform the approximate nearest neighbor (ANN) search.
When using vector columns, the syntax is as follows:

```cql
order_by_vector: ORDER BY ( `vector_column_name` ANN OF `vector` | ANN '(' `vector_column_name` ',' `vector` ')' ) LIMIT `integer`
```

Where `vector_column_name` is the name of the vector column,
`vector` is the query [vector](https://docs.scylladb.com/manual/branch-2026.4/cql/types.md#vectors), and `LIMIT` is a limit on the number of results to return.
Vector queries can only be performed on a vector column that has a [vector index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-vector-index-statement) created.

The two forms are equivalent: `ORDER BY embedding ANN OF [0.1, 0.2]` and
`ORDER BY ANN(embedding, [0.1, 0.2])` produce the same query. The function form is a
ScyllaDB extension, and is consistent with how the [BM25](#fulltext-queries) scoring
function is written; the `ANN OF` form is compatible with Apache Cassandra.

The ANN ordering clause orders the results by their distance to the provided query vector,
using the distance function defined for the vector column when
[creating the vector index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-vector-index-statement).
The query vector must have the same dimension as the vector column.

Supported `LIMIT` values are integers between 1 and 1000, inclusive.
However, based on the index parameters and the dataset size and distribution,
the actual number of results returned may be less than the specified limit.

For example:

```cql
SELECT image_id FROM ImageEmbeddings
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;
```

This query returns up to 5 rows with the closest distance of `embedding` vector to the provided query vector,
in this case `[0.1, 0.2, 0.3, 0.4]`. Written with the function form, it is:

```cql
SELECT image_id FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;
```

`ANN()` may also be used as a selector. It returns a `tuple<float, int>`: the **similarity** the
index gave the row, and the row’s **rank**, its position in the index’s result counted from 1:

```cql
SELECT image_id, ANN(embedding, [0.1, 0.2, 0.3, 0.4]) AS score_and_rank
  FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;
```

`ANN_SCORE()` and `ANN_RANK()` return the two values on their own:

```cql
SELECT image_id, ANN_SCORE(embedding, [0.1, 0.2, 0.3, 0.4]) AS similarity
  FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;
```

All three describe the search the rows are ranked by, so each is only accepted in a query whose
`ORDER BY` clause has an ANN search, in either form, on the same column, and every occurrence must
use the same query vector as that search. A query using several of them still makes a single
request.

An index configured for [rescoring](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-vector-index-statement) recomputes the similarity
on the coordinator and reorders the rows by it, so the rank the Vector Store gave them no longer
applies. `ANN_RANK()` and `ANN()` are rejected in the `SELECT` clause of such a query;
`ANN_SCORE()` and the `ORDER BY` itself work as usual.

To score rows against a vector other than the one being searched for, use the
[similarity functions](https://docs.scylladb.com/manual/branch-2026.4/cql/functions.md#vector-similarity-functions) directly.

For example:

```cql
SELECT image_id, similarity_cosine(embedding, [0.9, 0.8, 0.7, 0.6])
  FROM ImageEmbeddings
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;
```

Vector queries also support filtering with `WHERE` clauses on columns that are part of the primary key.
See [WHERE](#where-clause).

For example:

```cql
SELECT image_id FROM ImageEmbeddings
  WHERE user_id = 'user123'
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;
```

#### NOTE
Vector indexes are supported in ScyllaDB Cloud only in clusters that have the Vector Search feature enabled.
Vector indexes do not support all ScyllaDB features (e.g., tracing, paging, grouping, and `SELECT DISTINCT`).
`SELECT DISTINCT` is currently accepted in queries ordered by `ANN OF` or `ANN()`, but does not remove
duplicate partition keys. More information about Vector Search is available in the
[ScyllaDB Cloud documentation](https://cloud.docs.scylladb.com/stable/vector-search/).

<a id="fulltext-queries"></a>

## Full-Text Search queries (BM25) ScyllaDB Cloud

#### NOTE
Full-Text Search is supported in ScyllaDB Cloud only in clusters that have the Vector and Text Search feature enabled.
For more information, see the [Full-Text Search documentation](https://docs.scylladb.com/manual/branch-2026.4/features/fulltext-search.md).

The `BM25()` function enables full-text search queries over text columns that
have a [full-text index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-fulltext-index-statement) created with
`USING 'fulltext_index'`. `BM25()` takes a column name and a query string
and scores rows using the BM25 ranking algorithm.

A full-text search query must contain **both** of the following clauses,
referencing the **same** column and the **same** search term:

* A `WHERE` filter of the exact form `BM25(column, 'term') > 0`.
* An `ORDER BY BM25(column, 'term')` clause that ranks the matching rows.

Neither clause is accepted on its own. A query that has only `WHERE BM25()` or
only `ORDER BY BM25()` is rejected. The exception is a [hybrid query](#hybrid-queries),
which ranks the rows by several searches at once and takes no `WHERE` clause.

**Syntax:**

```cql
fts_query: SELECT ... FROM `table_name`
         :   WHERE BM25 '(' `column_name` ',' `term` ')' '>' 0
         :   ORDER BY BM25 '(' `column_name` ',' `term` ')'
         :   LIMIT `integer`
```

In the `WHERE` clause, `>` is the only supported comparison operator, and the right-hand side must be
the literal `0`. The column must be of type `text`, `varchar`, or `ascii`
and have a `fulltext_index`. Columns of the [primary key](https://docs.scylladb.com/manual/branch-2026.4/cql/ddl.md#primary-key)
and [static columns](https://docs.scylladb.com/manual/branch-2026.4/cql/ddl.md#static-columns) cannot be indexed.

`ORDER BY BM25()` must be the only ordering in the query - it cannot be combined
with another `ORDER BY` column. To rank the rows by several searches, such as a
full-text and a vector search, fuse them into one ordering in a
[hybrid query](#hybrid-queries).

A `LIMIT` clause is mandatory; queries without `LIMIT` are rejected. The
specified value must not exceed 1000.

`SELECT DISTINCT` is not supported in queries ordered by `BM25()`. It is
currently accepted, but does not remove duplicate partition keys.

**Examples:**

Filter and rank by relevance:

```cql
SELECT * FROM articles
    WHERE BM25(body, 'distributed database') > 0
    ORDER BY BM25(body, 'distributed database')
    LIMIT 10;
```

Use bind markers for the query term:

```cql
SELECT * FROM articles
    WHERE BM25(body, ?) > 0
    ORDER BY BM25(body, ?)
    LIMIT 10;
```

Return the score and the rank of each row:

```cql
SELECT title, BM25(body, 'distributed database') AS score_and_rank FROM articles
    WHERE BM25(body, 'distributed database') > 0
    ORDER BY BM25(body, 'distributed database')
    LIMIT 10;
```

`BM25()` returns a `tuple<float, int>`: the **score** the index gave the row, and the row’s
**rank**, its position in the index’s result counted from 1. `BM25_SCORE()` and `BM25_RANK()`
return the two values on their own.

All three describe the search the rows are ranked by, so each is only accepted in a query whose
`ORDER BY` clause has a BM25 search on the same column, and every occurrence must use the same
search term as that search. `WHERE BM25(...) > 0` compares the score, and is the same as
`WHERE BM25_SCORE(...) > 0`. `BM25_RANK()` cannot be used in the `WHERE` clause.

The `BM25()` operator is not a reserved word. If a user-defined function named
`bm25` exists in a keyspace, unqualified `BM25()` becomes ambiguous; qualify
the built-in operator as `system.bm25(...)` to disambiguate it.

# Selecting an excerpt

`BM25_HIGHLIGHT(column, term)` returns an excerpt of the searched text as HTML,
with the matched terms wrapped in `<b>` and `</b>` and the text around them
escaped:

```cql
SELECT id, BM25_HIGHLIGHT(body, 'distributed database') AS excerpt FROM articles
    WHERE BM25(body, 'distributed database') > 0
    ORDER BY BM25(body, 'distributed database')
    LIMIT 10;
```

It is accepted only in the `SELECT` clause, under the same rules as `BM25()`
there. The result is of type `text` and is `null` for a row in which no
fragment could be found; the row itself is still returned. See
[Highlighting](https://docs.scylladb.com/manual/branch-2026.4/features/fulltext-search.md#fulltext-highlighting) for the details.

For the full list of query constraints and requirements, see
[Full-Text Search](https://docs.scylladb.com/manual/branch-2026.4/features/fulltext-search.md).

<a id="hybrid-queries"></a>

## Hybrid search queries ScyllaDB Cloud

A hybrid query ranks the rows by several searches at once, typically a [vector search](#vector-queries) and a [full-text search](#fulltext-queries) over the same table. Its
`ORDER BY` clause fuses the searches into one score:

```cql
hybrid_query: SELECT ... FROM `table_name`
            :   ORDER BY `fusion_function` '(' `search` ( ',' `search` )+ ')'
            :   LIMIT `integer`
search: ANN '(' `vector_column_name` ',' `vector` ')'
      : | BM25 '(' `column_name` ',' `term` ')'
```

For example:

```cql
SELECT id, title FROM articles
    ORDER BY RRF(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;
```

The searches are sent to their indexes in parallel. A row is a candidate if any of them returned
it. The candidates are read, scored by the fusion function, sorted by that score, highest first,
and only then cut to the `LIMIT`.

Each search passes the fusion function a `tuple<float, int>`: the score the index gave the row
and the row’s rank, its position in the index’s result counted from 1. Where a search did not
return the row, both are null. Scores of different searches are on different scales, so a BM25
relevance and a vector similarity cannot be meaningfully added; ranks can be compared, so fusion
usually goes by rank.

`RRF()` is reciprocal rank fusion. A row scores `1 / (60 + rank)` for each search that returned
it, summed over the searches. It takes any number of searches, at least two.

A user-defined function can fuse the searches instead. It takes one `tuple<float, int>` argument
per search and returns a `float`. For example, to weigh the vector search three times as much as
the full-text one:

```cql
CREATE FUNCTION ks.weighted(a tuple<float, int>, b tuple<float, int>)
    CALLED ON NULL INPUT RETURNS float LANGUAGE lua AS
    'local function w(hit) if hit[2] == nil then return 0 end return 1 / hit[2] end
     return 3 * w(a) + w(b)';

SELECT id, title FROM articles
    ORDER BY ks.weighted(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;
```

A search is identified by its kind and its column. Two calls on the same column are the same search
and must use the same query vector or search term; several columns of one kind are several
searches, so `RRF(BM25(title, 'database'), BM25(body, 'database'))` fuses two full-text searches.

`ANN()`, `ANN_SCORE()`, `ANN_RANK()`, `BM25()`, `BM25_SCORE()` and `BM25_RANK()` may be
selected to report what each search said about a row, provided the `ORDER BY` clause searches
their column with the same query value. They return null for a row that search did not return:

```cql
SELECT id, ANN_RANK(embedding, [0.1, 0.2, 0.3, 0.4]) AS vector_rank,
           BM25_RANK(body, 'distributed database') AS text_rank
    FROM articles
    ORDER BY RRF(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;
```

[BM25_HIGHLIGHT()](https://docs.scylladb.com/manual/branch-2026.4/features/fulltext-search.md#fulltext-highlighting) may be selected too, and is null in the same way: only
the text of the rows the full-text search returned is sent to the index for an excerpt.

Hybrid queries have the following limitations:

* A hybrid query takes no `WHERE` clause.
* A `LIMIT` clause is mandatory, and must not exceed 1000. Only the first `LIMIT` rows of each
  search’s answer are fused: a vector index with `oversampling` is asked for the `LIMIT` times
  the oversampling factor, but its answer is cut to the `LIMIT` before the fusion.
* A vector index configured for [rescoring](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-vector-index-statement) cannot take part in
  a hybrid query: the rescored order has no rank to fuse by. On such an index, `ORDER BY` accepts
  only `ANN()` or `ANN_SCORE()` called directly.
* The rank is the position in the index’s result, not in the result set. If an index returns a row
  that is no longer in the base table, the ranks after it are not renumbered.
* Paging is not supported: the whole result is returned at once, with a warning if a page size
  smaller than the `LIMIT` was requested.

<a id="pattern-queries"></a>

## Indexed LIKE queries (pattern index) ScyllaDB Cloud

#### NOTE
Pattern Search is supported in ScyllaDB Cloud only in clusters that have the Vector and Text Search feature enabled.
For more information, see the [Pattern Search documentation](https://docs.scylladb.com/manual/branch-2026.4/features/pattern-search.md).

This section covers `LIKE` on a column that has a [pattern index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-pattern-index-statement).
`LIKE` as a filter, available in every deployment, is described in [LIKE Operator](#like-operator).
The two use the same pattern syntax but otherwise behave differently, for example in result order,
paging, consistency and case sensitivity; see
[Indexed vs. Filtered LIKE](https://docs.scylladb.com/manual/branch-2026.4/features/pattern-search.md#pattern-search-indexed-vs-filtered) for a comparison.

On a column that has a pattern index, every `LIKE` restriction is served by the index instead of by
filtering. Such a query does not need `ALLOW FILTERING`; if it is given, it is ignored and the index
is still used:

```cql
pattern_query: SELECT ... FROM `table_name`
               :   WHERE `column_name` LIKE `pattern`
               :   LIMIT `integer`
```

Example:

```cql
SELECT id, code FROM parts
    WHERE code LIKE '%024%'
    LIMIT 10;
```

The rules are:

* The pattern uses the [LIKE](#like-operator) syntax. Which patterns are supported depends on the
  index node, and a pattern it does not support fails the query with an `InvalidRequest` error.
* A bind marker may stand for the pattern (`LIKE ?`). The pattern must not be null.
* `LIMIT` is mandatory and must not exceed 1000. `ORDER BY`, `PER PARTITION LIMIT`, `GROUP BY`,
  aggregation and any further `WHERE` restriction, including one on the partition key, are not
  supported.
* The query is served by the index even with `ALLOW FILTERING`. When the index node is unavailable
  or does not support the pattern, the query fails.

#### WARNING
A pattern index changes every `LIKE` on its column, from every application, for as long as the
index exists: a `LIKE` that worked by filtering before the index was created can fail or return
different rows afterwards. Dropping the index with [DROP INDEX](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#drop-index-statement) restores
filtered `LIKE`.

<a id="limit-clause"></a>

## Limiting results

The `LIMIT` option to a `SELECT` statement limits the number of rows returned by a query, while the `PER PARTITION
LIMIT` option limits the number of rows returned for a given **partition** by the query. Note that both types of limit can be
used in the same statement.

#### NOTE
The `LIMIT` and `PER PARTITION LIMIT` are applied to the output of the aggregate functions.

Examples:

The Partition Key in the following table is `client_id`, and the clustering key is `when`.
The table has seven rows, split between four clients (partition keys)

```cql
cqlsh:ks1> SELECT client_id, when FROM test;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       2 | 2020-02-12 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(7 rows)
```

You can ask the query to limit the number of rows returned from **all partition** with LIMIT, for example:

```cql
cqlsh:ks1> SELECT client_id, when FROM ks1.test LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000

(3 rows)
```

You can ask the query to limit the number of rows returned for **each** `client_id`. For example, with limit of *1* :

```cql
cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(4 rows)
```

Increasing limit to *2*, would yield:

```cql
cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 2;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(6 rows)
```

You can also mix the two limits types:

```cql
cqlsh> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1 LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000

(3 rows)
```

<a id="allow-filtering"></a>

## Allowing filtering

By default, CQL only allows select queries that don’t involve “filtering” server-side, i.e. queries where we know that
all (live) record read will be returned (maybe partly) in the result set. The reasoning is that those “non filtering”
queries have predictable performance in the sense that they will execute in a time that is proportional to the amount of
data **returned** by the query (which can be controlled through `LIMIT`).

The `ALLOW FILTERING` option lets you explicitly allow (some) queries that require filtering. Please note that a
query using `ALLOW FILTERING` may thus have unpredictable performance (for the definition above), i.e. even a query
that selects a handful of records **may** exhibit performance that depends on the total amount of data stored in the
cluster.

For instance, consider the following table holding user profiles with their year of birth (with a secondary index on
it) and country of residence:

```cql
CREATE TABLE users (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
)

CREATE INDEX ON users(birth_year);
```

Then the following queries are valid:

```cql
SELECT * FROM users;
SELECT * FROM users WHERE birth_year = 1981;
```

because in both cases, ScyllaDB guarantees that these queries’ performance will be proportional to the amount of data
returned. In particular, if no users were born in 1981, then the second query performance will not depend on the number
of user profiles stored in the database (not directly at least: due to secondary index implementation consideration, this
query may still depend on the number of nodes in the cluster, which indirectly depends on the amount of data stored.
Nevertheless, the number of nodes will always be multiple orders of magnitude lower than the number of user profiles
stored). Of course, both queries may return very large result sets in practice, but the amount of data returned can always
be controlled by adding a `LIMIT`.

However, the following query will be rejected:

```cql
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';
```

because ScyllaDB cannot guarantee that it won’t have to scan a large amount of data even if the result of those queries is
small. Typically, it will scan all the index entries for users born in 1981 even if only a handful are actually from
France. However, if you “know what you are doing”, you can force the execution of this query by using `ALLOW
FILTERING` and so the following query is valid:

```cql
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;
```

<a id="eval-order"></a>

## Evaluation order of SELECT statement clauses

This section explains the relative priority among the various clauses
of the SELECT statement.

> - All rows of the table named in the FROM clause are considered as candidates.
> - Rows are ordered in token order first, then partition key order, then clustering key order.
> - If ORDER BY is specified, then the clustering key order can be reversed.
> - The WHERE clause predicate is applied.
> - GROUP BY is then applied to create groups.
> - Aggregate functions in the SELECT clause are applied to groups, or to the entire query if GROUP BY was not specified.
> - If there are selectors that are not aggregate functions, then the first value in the group is selected.
> - If specified, PER PARTITION LIMIT is applied to each partition result.
> - If specified, LIMIT is applied to the entire query result.

#### NOTE
The server may use a different execution plan, as long as it arrives at the same result. For
example, conditions in the WHERE clause will limit the candidate row set first by looking up the
primary index or a secondary index.

<a id="select-without-from"></a>

## `SELECT` without `FROM` ScyllaDB Extension

The `FROM` clause is optional. When omitted, the `SELECT` statement evaluates the given
expressions and returns them as a single row, which is useful for evaluating functions or
computing constant expressions without needing a table. Internally, the statement runs as
an ordinary select over `system.one_row`, a built-in table with exactly one row.

`*` and `DISTINCT` are not allowed without `FROM`, and there are no columns in
scope to reference. Aggregate functions operate on the single row, so, for example,
`count(*)` returns 1.

Examples:

```cql
SELECT 1 AS one, 'hello' AS greeting;
SELECT toTimestamp(now()) AS ts;
SELECT JSON 1, now();
```

#### NOTE
`SELECT` without `FROM` is a ScyllaDB CQL extension and is not supported
by Apache Cassandra.

<a id="bypass-cache"></a>

## Bypass Cache

The `BYPASS CACHE` clause on SELECT statements informs the database that the data being read is unlikely to be read again in the near future, and also was unlikely to have been read in the near past; therefore, no attempt should be made to read it from the cache or to populate the cache with the data. This is mostly useful for range scans; these typically process large amounts of data with no temporal locality and do not benefit from the cache.
The clause is placed immediately after the optional ALLOW FILTERING clause.

`BYPASS CACHE` is a ScyllaDB CQL extension and not part of Apache Cassandra CQL.

For example:

```cql
SELECT * FROM users BYPASS CACHE;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE;
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING BYPASS CACHE;
```

<a id="using-timeout"></a>

## Using Timeout

The `USING TIMEOUT` clause allows specifying a timeout for a specific request.

For example:

```cql
SELECT * FROM users USING TIMEOUT 5s;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE USING TIMEOUT 200ms;
```

`USING TIMEOUT` is a ScyllaDB CQL extension and not part of Apache Cassandra CQL.

<a id="like-operator"></a>

## LIKE Operator

The `LIKE` operation on `SELECT` statements informs ScyllaDB that you are looking for a pattern match. The expression ‘column LIKE pattern’ yields true only if the entire column value matches the pattern.

Without an index, `LIKE` is a filter: ScyllaDB reads the rows and checks each value against the
pattern, so the query requires [ALLOW FILTERING](#allow-filtering). This section describes that
filtered `LIKE`. On a column with a [pattern index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-pattern-index-statement), `LIKE`
is served by the index instead, even with `ALLOW FILTERING`, and follows different rules; see
[Indexed LIKE queries](#pattern-queries) and
[Indexed vs. Filtered LIKE](https://docs.scylladb.com/manual/branch-2026.4/features/pattern-search.md#pattern-search-indexed-vs-filtered).

The search pattern is a string of characters with two wildcards, as shown:

* `_` matches any single character
* `%` matches any substring (including an empty string)
* `\` escapes the next pattern character, so it matches verbatim
* any other pattern character matches itself
* an empty pattern matches empty text fields

#### NOTE
Only string types (ascii, text, and varchar) are valid for matching

A filtered `LIKE` is always **case sensitive**. The entire column value must match the pattern.
For example, consider the search pattern ‘M%n’ - this will match `Martin`, but will not match `Moonbeam` because the `m` at the end isn’t matched. In addition, `moon` is not matched because `M` is not the same as `m`. Both the pattern and the column value are assumed to be UTF-8 encoded.

To match regardless of letter case, use a pattern index created with `'case_sensitive': 'false'`.

A query can find all values containing some text fragment by matching to an appropriate `LIKE` pattern.

**Differences Between ScyllaDB and Cassandra LIKE Operators**

* In Apache Cassandra, you must create a SASI index to use LIKE. ScyllaDB supports LIKE as a regular filter,
  and can also serve it from a [pattern index](https://docs.scylladb.com/manual/branch-2026.4/cql/secondary-indexes.md#create-pattern-index-statement).
* Consequently, a filtered ScyllaDB LIKE will be less performant than Apache Cassandra LIKE for some workloads.
* ScyllaDB treats underscore (_) as a wildcard; Cassandra doesn’t.
* ScyllaDB treats percent (%) as a wildcard anywhere in the pattern; Cassandra only at the beginning/end
* ScyllaDB interprets backslash (\\) as an escape character; Cassandra doesn’t.
* Cassandra allows case-insensitive LIKE through an option of the SASI index. A filtered ScyllaDB LIKE is always
  case-sensitive (see [#4911](https://github.com/scylladb/scylla/issues/4911)); case-insensitive matching needs a
  pattern index created with `'case_sensitive': 'false'`.
* ScyllaDB allows empty LIKE pattern; Cassandra doesn’t.

**Example A**

In this example, `LIKE` specifies that the match is looking for a word that starts with the letter `S`. The `%` after the letter `S` matches any text to the end of the field.

```none
SELECT * FROM pet_owners WHERE firstname LIKE ‘S%’ ALLOW FILTERING;
╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│1         │Adams                │Steven          │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│25        │Harris               │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│98        │Smith                │Sara            │
├──────────┼─────────────────────┼────────────────┤
│115       │Williams             │Susan           │
├──────────┼─────────────────────┼────────────────┤
│130       │Young                │Stuart          │
╰──────────┴─────────────────────┴────────────────╯
```

**Example B**

In this example, you are searching for all pet owners whose last name contains the characters ‘erg’.

```none
SELECT * FROM pet_owners WHERE lastname LIKE ‘%erg%’ ALLOW FILTERING;

╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│11        │Berger               │David           │
├──────────┼─────────────────────┼────────────────┤
│18        │Gerg                 │Lawrence        │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│91        │Schulberg            │Barry           │
├──────────┼─────────────────────┼────────────────┤
│110       │Weinberg             │Stuart          │
╰──────────┴─────────────────────┴────────────────╯
```

Note that this query does not return:

```none
╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
╰──────────┴─────────────────────┴────────────────╯
```

As it is case sensitive.

**Example C**

This table contains some commonly used `LIKE` filters and the matches you can expect the filter to return.

| Filter   | Matches                                |
|----------|----------------------------------------|
| %abe%    | Babel, aberration, cabernet, scarabees |
| \_0%     | 10%, 20%, 50%                          |
| a%t      | asphalt, adapt, at                     |

<a id="is-null-operator"></a>

## IS NULL and IS NOT NULL Operators

The `IS NULL` and `IS NOT NULL` operators allow you to filter rows based on whether a column value is present or absent.

- `IS NULL` returns rows where the column has no value (is null).
- `IS NOT NULL` returns rows where the column has a value.

Whether these operators require `ALLOW FILTERING` depends on the column:

- On a **partition key column**, `IS NOT NULL` never requires `ALLOW FILTERING`. Such a column can never be
  null, so the restriction matches every row and is dropped entirely. Being dropped, it also doesn’t count as
  restricting the partition key for the rules below. `IS NULL` on a partition key column matches no row, but it
  is still a restriction, so it requires `ALLOW FILTERING`.
- On a **clustering key column**, both operators are ordinary restrictions on that column and neither is dropped,
  because a clustering key column *can* read as null - see below. They therefore need the partition key fully
  restricted by `=` or `IN` (a `token()` range is not enough) and every preceding clustering key column
  restricted; otherwise they require `ALLOW FILTERING`.
- On any **other column** - regular or static - both operators require `ALLOW FILTERING`, because every row’s
  value has to be inspected. This holds even when the column is indexed: the index is not consulted for a null
  check.

A clustering key column reads as null in one case: a partition that holds a static row but no clustering rows is
returned as a single row whose clustering key columns are all null. So on a clustering key column `IS NULL`
selects exactly those static-only rows, and `IS NOT NULL` selects exactly the clustering rows. In a table with
no static columns there are no such rows, which makes `IS NULL` match nothing and `IS NOT NULL` match
everything - but the two are still ordinary restrictions there, subject to the same rules.

```cql
CREATE TABLE events (p int, c int, v int, PRIMARY KEY (p, c));

-- no ALLOW FILTERING needed: p is a partition key column, so IS NOT NULL is
-- dropped and this is a full scan
SELECT * FROM events WHERE p IS NOT NULL;

-- no ALLOW FILTERING needed: the partition key is fixed, so IS [NOT] NULL on
-- the clustering key is an ordinary clustering key restriction
SELECT * FROM events WHERE p = 1 AND c IS NOT NULL;
SELECT * FROM events WHERE p = 1 AND c IS NULL;

-- ALLOW FILTERING needed: no partition key restriction to anchor the
-- clustering key restriction
SELECT * FROM events WHERE c IS NOT NULL ALLOW FILTERING;
SELECT * FROM events WHERE c IS NULL ALLOW FILTERING;

-- ALLOW FILTERING needed: v is a regular column
SELECT * FROM events WHERE p = 1 AND v IS NULL ALLOW FILTERING;
```

Like other Scylla filtering predicates, they can also be combined with additional restrictions on the same column.
The result follows ordinary conjunction semantics: contradictory predicates such as `x IS NULL AND x = 3` match
no rows, while compatible predicates such as `x IS NOT NULL AND x = 3` work normally.

#### NOTE
In a materialized view definition `IS NOT NULL` on a base table primary key column is a no-op,
because a view row is only ever built from a base clustering row, and such a row has a value for every one
of the base table’s primary key columns. This is why a view definition may name those columns with
`IS NOT NULL` freely. On a regular base table column
promoted into the view’s primary key it is *not* a no-op - see **Usage in Materialized Views** below. For
the `SELECT` rules, which differ between partition key and clustering key columns, see the list above
rather than this note.

Unlike the other filtering operators, `IS NULL` and `IS NOT NULL` may also be used on non-frozen collection
columns, because they only test whether the column has a value rather than compare it against one. Note that a
non-frozen collection with no elements is indistinguishable from a missing one, so an empty collection is
considered null.

**Example**

```cql
CREATE TABLE users (id int PRIMARY KEY, name text, email text);
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users (id, name) VALUES (2, 'Bob');

SELECT * FROM users WHERE email IS NULL ALLOW FILTERING;

 id | email | name
----+-------+------
  2 |  null |  Bob

SELECT * FROM users WHERE email IS NOT NULL ALLOW FILTERING;

 id | email             | name
----+-------------------+-------
  1 | alice@example.com | Alice
```

**Usage in Materialized Views**

The `IS NOT NULL` restriction is required in materialized view definitions for all columns that are part of the
view’s primary key, as primary key columns cannot be null. See [Materialized Views](https://docs.scylladb.com/manual/branch-2026.4/cql/mv.md#materialized-views) for
more details. This usage does not require `ALLOW FILTERING`. On a base table primary key column the restriction
is a no-op, because a view row is only ever built from a base clustering row, and such a row has a value for every
one of the base table’s primary key columns. On a regular base table column promoted into the view’s primary key it
is not a no-op: it is what excludes the base rows where that column is null from the view.
`IS NULL` is not supported in materialized view definitions, and neither operator can be used in a view
definition on a column that is not part of the view’s primary key - views do not support filtering on non-key
columns.

[Apache Cassandra Query Language (CQL) Reference](https://docs.scylladb.com/manual/branch-2026.4/cql/index.md)

Copyright

© 2016, The Apache Software Foundation.

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.

<!-- Licensed to the Apache Software Foundation (ASF) under one -->
<!-- or more contributor license agreements.  See the NOTICE file -->
<!-- distributed with this work for additional information -->
<!-- regarding copyright ownership.  The ASF licenses this file -->
<!-- to you under the Apache License, Version 2.0 (the -->
<!-- "License"); you may not use this file except in compliance -->
<!-- with the License.  You may obtain a copy of the License at -->
<!-- http://www.apache.org/licenses/LICENSE-2.0 -->
<!-- Unless required by applicable law or agreed to in writing, software -->
<!-- distributed under the License is distributed on an "AS IS" BASIS, -->
<!-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -->
<!-- See the License for the specific language governing permissions and -->
<!-- limitations under the License. -->