Retrieval using the IN keyword
The IN
keyword can define a set of clustering columns to fetch together, supporting a "multi-get" of CQL rows.
A single clustering column can be defined if all preceding columns are defined for either equality or group inclusion.
Alternatively, several clustering columns can be defined to collect several rows, as long as all preceding columns are queried for equality or group inclusion.
The defined clustering columns can also be queried for inequality.
Note that using both IN
and ORDER BY
require turning off paging with the PAGING OFF
command in cqlsh
.
ALLOW FILTERING
is allowed for use in conjunction with IN
to retrieve regular or static columns.
Procedure
-
Turn off paging.
PAGING OFF;
-
To retrieve results, use the SELECT statement. Retrieve and sort results in descending order.
SELECT * FROM cycling.calendar WHERE race_id IN (100, 101, 102) ORDER BY race_start_date DESC;
race_id | race_start_date | race_end_date | race_name ---------+---------------------------------+---------------------------------+----------------------- 102 | 2015-06-13 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse 101 | 2015-06-07 00:00:00.000000+0000 | 2015-06-14 00:00:00.000000+0000 | Criterium du Dauphine 100 | 2015-05-09 00:00:00.000000+0000 | 2015-05-31 00:00:00.000000+0000 | Giro d'Italia 102 | 2014-06-12 00:00:00.000000+0000 | 2014-06-20 00:00:00.000000+0000 | Tour de Suisse 101 | 2014-06-06 00:00:00.000000+0000 | 2014-06-13 00:00:00.000000+0000 | Criterium du Dauphine 100 | 2014-05-08 00:00:00.000000+0000 | 2014-05-30 00:00:00.000000+0000 | Giro d'Italia 102 | 2013-06-11 00:00:00.000000+0000 | 2013-06-19 00:00:00.000000+0000 | Tour de Suisse 101 | 2013-06-05 00:00:00.000000+0000 | 2013-06-12 00:00:00.000000+0000 | Criterium du Dauphine 100 | 2013-05-07 00:00:00.000000+0000 | 2014-05-29 00:00:00.000000+0000 | Giro d'Italia (9 rows)
-
To retrieve results from a regular column with ALLOW FILTERING, use the SELECT statement.
SELECT * FROM cycling.calendar WHERE race_name IN ('Giro d''Italia','Tour de Suisse') ALLOW FILTERING;
race_id | race_start_date | race_end_date | race_name ---------+---------------------------------+---------------------------------+---------------- 102 | 2015-06-13 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse (1 rows)
-
Alternatively, retrieve and sort results in ascending order.
SELECT * FROM cycling.calendar WHERE race_id IN (100, 101, 102) ORDER BY race_start_date ASC;
race_id | race_start_date | race_end_date | race_name ---------+---------------------------------+---------------------------------+----------------------- 100 | 2013-05-07 00:00:00.000000+0000 | 2014-05-29 00:00:00.000000+0000 | Giro d'Italia 101 | 2013-06-05 00:00:00.000000+0000 | 2013-06-12 00:00:00.000000+0000 | Criterium du Dauphine 102 | 2013-06-11 00:00:00.000000+0000 | 2013-06-19 00:00:00.000000+0000 | Tour de Suisse 100 | 2014-05-08 00:00:00.000000+0000 | 2014-05-30 00:00:00.000000+0000 | Giro d'Italia 101 | 2014-06-06 00:00:00.000000+0000 | 2014-06-13 00:00:00.000000+0000 | Criterium du Dauphine 102 | 2014-06-12 00:00:00.000000+0000 | 2014-06-20 00:00:00.000000+0000 | Tour de Suisse 100 | 2015-05-09 00:00:00.000000+0000 | 2015-05-31 00:00:00.000000+0000 | Giro d'Italia 101 | 2015-06-07 00:00:00.000000+0000 | 2015-06-14 00:00:00.000000+0000 | Criterium du Dauphine 102 | 2015-06-13 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse (9 rows)
-
Retrieve rows using multiple clustering columns. This example searches the partition key
race_ids
for several races, but the partition key can also be composed as an equality for one value.SELECT * FROM cycling.calendar WHERE race_id IN (100, 101, 102) AND (race_start_date, race_end_date) IN (('2015-05-09', '2015-05-31'), ('2015-05-06', '2015-05-31'));
race_id | race_start_date | race_end_date | race_name ---------+---------------------------------+---------------------------------+--------------- 100 | 2015-05-09 00:00:00.000000+0000 | 2015-05-31 00:00:00.000000+0000 | Giro d'Italia (1 rows)
-
Retrieve several rows using multiple clustering columns and inequality.
SELECT * FROM cycling.calendar WHERE race_id IN (100, 101, 102) AND (race_start_date, race_end_date) >= ('2015-05-09', '2015-05-24');
race_id | race_start_date | race_end_date | race_name ---------+---------------------------------+---------------------------------+----------------------- 100 | 2015-05-09 00:00:00.000000+0000 | 2015-05-31 00:00:00.000000+0000 | Giro d'Italia 101 | 2015-06-07 00:00:00.000000+0000 | 2015-06-14 00:00:00.000000+0000 | Criterium du Dauphine 102 | 2015-06-13 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse (3 rows)