WHERE: Filtering by Key

The WHERE clause keeps only the rows you want.

Syntax

SELECT columns FROM table_name
WHERE condition;

Example: one employee by id

SELECT * FROM employees WHERE id = 5;

Output:

 id | age | department | gender | name
----+-----+------------+--------+------
  5 |  26 |         IT | female | Emma

(1 rows)

id is the primary key of the employees table. Cassandra hashes the value 5 and goes straight to the node that stores this row. It does not look at any other rows. This is the kind of query Cassandra is built for. It stays fast even when the table has billions of rows.

If no row has that id, you simply get no rows back:

SELECT * FROM employees WHERE id = 99;

Output:

 id | age | department | gender | name
----+-----+------------+--------+------


(0 rows)

WHERE on a column that is not a key

Now try the same thing with a normal column:

SELECT * FROM employees WHERE department = 'IT';

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

Cassandra refuses to run the query. department is not part of the primary key, so Cassandra does not know where the IT employees are stored. To answer, it would have to read every row on every node. Cassandra gives you an error so that you never do this by accident.

This is the biggest difference from SQL in this course: in Cassandra, WHERE is made for key columns. This rule is how Cassandra stays fast with very large tables. In the next lessons you will see what you can do instead when you need "all IT employees".

In the exercise, find one employee by id.