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. 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
    ---------+---------------------------------+---------------------------------+---------------
         100 | 2015-05-09 00:00:00.000000+0000 | 2015-05-31 00:00:00.000000+0000 | Giro d'Italia
         100 | 2014-05-08 00:00:00.000000+0000 | 2014-05-30 00:00:00.000000+0000 | Giro d'Italia
         100 | 2013-05-07 00:00:00.000000+0000 | 2014-05-29 00:00:00.000000+0000 | Giro d'Italia
         102 | 2015-06-13 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse
         102 | 2014-06-12 00:00:00.000000+0000 | 2014-06-20 00:00:00.000000+0000 | Tour de Suisse
         102 | 2013-06-11 00:00:00.000000+0000 | 2013-06-19 00:00:00.000000+0000 | Tour de Suisse
    
    (6 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)