How to Query Data with SELECT in Cassandra (CQL)

Introduction to CQL SELECT

Cassandra Query Language (CQL) uses SELECT statements that look similar to SQL but have important restrictions. Unlike relational databases, Cassandra requires you to query by partition key for efficient data retrieval. Understanding these constraints is essential for writing performant queries.

Basic SELECT Syntax

The simplest SELECT retrieves all columns from a table. However, in production you should always include a WHERE clause with the partition key to avoid full-cluster scans.

-- Select all rows (use with caution, only for small tables)
SELECT * FROM users;

-- Select specific columns
SELECT user_id, name, email FROM users;

-- Always prefer querying by partition key
SELECT * FROM users WHERE user_id = 550e8400-e29b-41d4-a716-446655440000;

Filtering with WHERE

In Cassandra, the WHERE clause must include the partition key. You can optionally add clustering columns in the order they were defined. This is the most critical difference from SQL: you cannot filter on arbitrary columns without indexes.

-- Query by partition key (required)
SELECT * FROM orders
WHERE customer_id = 'cust-123';

-- Query by partition key + clustering column
SELECT * FROM orders
WHERE customer_id = 'cust-123'
  AND order_date >= '2025-01-01'
  AND order_date <= '2025-12-31';

-- Limit results
SELECT * FROM orders
WHERE customer_id = 'cust-123'
LIMIT 10;

Using ALLOW FILTERING

When you need to filter by non-key columns, CQL requires the ALLOW FILTERING directive. Use this sparingly as it can trigger full table scans and severely degrade performance.

-- Filter on non-key column (expensive operation)
SELECT * FROM users
WHERE status = 'active'
ALLOW FILTERING;

-- Better approach: query by partition key first
SELECT * FROM users_by_status
WHERE status = 'active';

Selecting Distinct Partition Keys

Use SELECT DISTINCT to retrieve unique partition key values without reading full rows.

-- Get all unique customer IDs
SELECT DISTINCT customer_id FROM orders;

Key Takeaways

• Always include the partition key in your WHERE clause for efficient queries.
• Clustering columns can be added to the WHERE clause in their defined order.
• Avoid ALLOW FILTERING in production; instead, design tables around your query patterns.
• Use LIMIT to control the number of returned rows.