Retrieval using the IN keyword
Using the SELECT command with 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
.
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)
-
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)