Cassandra WHERE Clause

The WHERE clause in CQL filters the rows that a SELECT, UPDATE, or DELETE statement targets. Cassandra's WHERE clause looks like SQL's, but it has strict rules tied to the primary key structure. Knowing those rules prevents accidental full-table scans and keeps queries fast.

WHERE and the Primary Key Rule

Cassandra requires the partition key in every WHERE clause for an efficient query. Providing the partition key tells Cassandra exactly which node holds the data so it does not have to ask every node in the cluster.

Table: orders_by_customer
PRIMARY KEY (customer_id, order_date, order_id)
              ────────────  ──────────  ────────
              Partition     Clustering  Clustering
              Key           Column 1    Column 2

Correct: Full partition key provided

SELECT * FROM orders_by_customer
WHERE customer_id = [uuid];
-- Routes to one node — fast

Incorrect: Partition key missing

SELECT * FROM orders_by_customer
WHERE order_date = '2024-05-01';
-- Requires ALLOW FILTERING — scans every node

Filtering on Clustering Columns

After you provide the partition key, you can optionally filter on clustering columns. You must follow the left-to-right rule: filter on the first clustering column before the second, and so on.

-- Valid: filter on first clustering column
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date = '2024-05-01';

-- Valid: filter on both clustering columns
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date   = '2024-05-01'
  AND order_id     = [order-uuid];

-- Invalid: skips order_date (first clustering column)
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_id = [order-uuid];
-- CQL error or requires ALLOW FILTERING

Range Operators on Clustering Columns

Clustering columns support range filters: >, <, >=, and <=. You can use ranges only on the last clustering column in the WHERE clause.

-- Orders from Q1 2024:
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date >= '2024-01-01'
  AND order_date <  '2024-04-01';

-- Orders in May on a specific day:
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date = '2024-05-01'
  AND order_id > [some-threshold-uuid];

IN Operator

IN lets you filter on a list of specific values. For the partition key, IN sends parallel sub-queries to each relevant node.

-- Multiple customers in one query:
SELECT * FROM customers
WHERE customer_id IN (uuid-A, uuid-B, uuid-C);

-- Multiple order dates for one customer:
SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date IN ('2024-01-10', '2024-03-20');

Keep IN lists short — large IN lists cause coordinator memory pressure and slower responses.

Token Function in WHERE

The token() function lets you filter by the raw token value of the partition key. This is useful for manual pagination or for splitting a full-table scan across worker processes.

-- Retrieve customers in a specific token range:
SELECT * FROM customers
WHERE token(customer_id) >= token([uuid-start])
  AND token(customer_id) <  token([uuid-end]);

Ring token range split for parallel processing:

Worker 1: token range [-9223372036854775808 to -4611686018427387904]
Worker 2: token range [-4611686018427387904 to 0]
Worker 3: token range [0                   to  4611686018427387904]
Worker 4: token range [4611686018427387904 to  9223372036854775807]

ALLOW FILTERING

ALLOW FILTERING lets you run a query that filters on a non-primary-key column. Cassandra fetches all rows that match the partition key (or scans the whole table if no partition key is provided) and then applies the filter in memory.

SELECT * FROM customers
WHERE email = 'alice@example.com'
ALLOW FILTERING;

Use ALLOW FILTERING only when the result set is guaranteed to be small — for example, during development or on a table that contains only a few hundred rows. On large tables it reads millions of rows only to discard most of them.

WHERE in UPDATE and DELETE

UPDATE and DELETE also require the full partition key in the WHERE clause. Cassandra does not allow UPDATE or DELETE without specifying which rows to target.

-- Update: must specify full primary key
UPDATE customers
SET loyalty_points = 1000
WHERE customer_id = [uuid];

-- Delete: must specify partition key at minimum
DELETE FROM orders_by_customer
WHERE customer_id = [uuid];

WHERE Clause Summary Table

Filter Type                   Allowed?  Notes
──────────────────────────────────────────────────────────────────────
Partition key equality        Yes       Most efficient; routes to one node
Partition key IN list         Yes       Parallel sub-queries per key
Clustering column equality    Yes       Must follow left-to-right order
Clustering column range       Yes       Only on last filtered cluster. col
Non-primary-key column        No*       Requires ALLOW FILTERING
Secondary index column        Yes       With caveats — see indexes topic

Summary

The WHERE clause in CQL requires the partition key for efficient, targeted queries. Clustering columns can be filtered in their defined order, and the last-filtered clustering column supports range operators. The IN operator allows querying multiple partition key values at once but should be used sparingly. Reserve ALLOW FILTERING for small, controlled datasets and use secondary indexes or redesigned tables for frequent non-key filtering.

Previous lesson

Back to course

Next lesson