A deep look at the CQL WHERE clause

The CQL WHERE clause differs from SQL due to the distributed nature of Cassandra data and its aim to prevent inefficient queries. In Cassandra, two types of columns have a special role: partition key columns and clustering columns. Together, they define your row primary key. Partition key columns are used to spread data evenly around the cluster, while clustering key columns cluster the data of a partition for efficient retrieval. The WHERE clause supports different sets of restrictions depending on the type of query (SELECT, UPDATE, or DELETE) and the column being restricted. Cassandra requires that all partition key columns be restricted in SELECT queries unless secondary indexes are used. Clustering columns support various operators in single-column and multi-column restrictions. Secondary index queries allow for additional filtering on non-indexed columns using =, >, >=, <=, <, CONTAINS, or CONTAINS KEY restrictions. UPDATE and DELETE statements require all primary key columns to be restricted, with only specific operators allowed.