Inner Join in cassandra CQL

Question

How do I write subqueries/nested queries in cassandra. Is this facility is provided in CQL?

Example I tried:

cqlsh:testdb> select itemname from item where itemid = (select itemid from orders where customerid=1);

It just throws the following error -

Bad Request: line 1:87 no viable alternative at input ';'

Answer 1

Because of its distributed nature, Cassandra has no support for RDBMS style joins. You have a few options for when you want something like a join.

One option perform separate queries and then have your application join the data itself. This makes sense if the data is relatively small and you only have to perform a small number of queries. Based on the example you gave above, this would probably be a good solution for you.

For more complicated joins, the usual strategy is to denormalize the data and store a materialized view of the join. The advantage to this is that fetching this data will be much faster than having to build it join in your application every time you need it. The cost is now you have multiple places where you are storing the same data and you will need to keep it all in sync. You can either update all your views when new data comes into the system or you can have a periodic batch job that rebuilds thems.

You might find this article useful: Do You Really Need SQL to Do It All in Cassandra? Its a bit old but its principles still apply.