SELECT

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

Synopsis

The SELECT statement retrieves (part of) rows of specified columns that meet a given condition from a table. It specifies the columns to be retrieved, the name of the table, and the condition each selected row must satisfy.

Syntax

Diagram

Grammar

select ::= SELECT [ DISTINCT ] { '*' | column_name [ ',' column_name ... ] } 
               FROM table_name
               [ WHERE where_expression ]
               [ ORDER BY order_expression ]
               [ LIMIT limit_expression ]
               [ OFFSET offset_expression ]
order_expression ::= '(' ( column_name [ ASC | DESC ] ) [ ',' ( column_name [ ASC | DESC ] )  ... ] ')'

Where

• table_name and column_name are identifiers (table_name may be qualified with a keyspace name).
• limit_expression is an integer literal (or a bind variable marker for prepared statements).
• Restrictions for where_expression are discussed in the Semantics section below.
• See Expressions for more information on syntax rules.

Semantics

• An error is raised if the specified table_name does not exist.
• SELECT DISTINCT can only be used for partition columns or static columns.
• * means all columns of the table will be retrieved.
• LIMIT clause sets the maximum number of results (rows) to be returned.
• OFFSET clause sets the number of rows to be skipped before returning results.

ORDER BY clause

• The ORDER BY clause sets the order for the returned results.
• Only clustering columns are allowed in the order_expression.
• For a given column, DESC means descending order and ASC or omitted means ascending order.
• Currently, only two overall orderings are allowed, the clustering order from the CREATE TABLE statement (forward scan) or its opposite (reverse scan).

WHERE Clause

• The where_expression and if_expression must evaluate to boolean values.
• The where_expression can only use AND and comparison operators. Other operators are not yet supported.
• The where_expression can specify conditions any column.
  • Only =, IN and NOT IN operators can be used for conditions on partition columns.
  • Only operators =, <, <=, >, >=, IN and NOT IN can be used for conditions on clustering columns.
  • All logical and boolean operators can be used for conditions on regular columns.

Note

While the where clause allows a wide range of operators, the exact conditions used in the where clause have significant performance considerations (especially for large datasets).Some best practices are:

• Use equality conditions on all partition columns (to fix the value of the partition key).
• Use comparison operators on the clustering columns (tighter restrictions are more valuable for left-most clustering columns).
• Generally, the closer a column is to the beginning of the primary key, the higher the performance gain for setting tighter restrictions on it.

Ideally, these performance considerations should be taken into account when creating the table schema.

Examples

Select all rows from a table

You can do this as shown below.

cqlsh:example> CREATE TABLE employees(department_id INT, 
                                      employee_id INT, 
                                      dept_name TEXT STATIC,
                                      employee_name TEXT, 
                                      PRIMARY KEY(department_id, employee_id));

cqlsh:example> INSERT INTO employees(department_id, employee_id, dept_name, employee_name) 
                   VALUES (1, 1, 'Accounting', 'John');

cqlsh:example> INSERT INTO employees(department_id, employee_id, dept_name, employee_name) 
                   VALUES (1, 2, 'Accounting', 'Jane');

cqlsh:example> INSERT INTO employees(department_id, employee_id, dept_name, employee_name) 
                   VALUES (1, 3, 'Accounting', 'John');

cqlsh:example> INSERT INTO employees(department_id, employee_id, dept_name, employee_name) 
                   VALUES (2, 1, 'Marketing', 'Joe');

cqlsh:example> SELECT * FROM employees;

 department_id | employee_id | dept_name  | employee_name
---------------+-------------+------------+---------------
             1 |           1 | Accounting |          John
             1 |           2 | Accounting |          Jane
             1 |           3 | Accounting |          John
             2 |           1 |  Marketing |           Joe

Select with limit

You can do this as shown below.

cqlsh:example> SELECT * FROM employees LIMIT 2;

 department_id | employee_id | dept_name  | employee_name
---------------+-------------+------------+---------------
             1 |           1 | Accounting |          John
             1 |           2 | Accounting |          Jane

Select with offset

You can do this as shown below.

cqlsh:example> SELECT * FROM employees LIMIT 2 OFFSET 1;

 department_id | employee_id | dept_name  | employee_name
---------------+-------------+------------+---------------
             1 |           2 | Accounting |          Jane
             1 |           3 | Accounting |          John

Select distinct values

You can do this as shown below.

cqlsh:example> SELECT DISTINCT dept_name FROM employees;

 dept_name

Accounting Marketing

Select with a condition on the partitioning column

You can do this as shown below.

cqlsh:example> SELECT * FROM employees WHERE department_id = 2;

 department_id | employee_id | dept_name | employee_name
---------------+-------------+-----------+---------------
             2 |           1 | Marketing |           Joe

Select with condition on the clustering column

You can do this as shown below.

cqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_id <= 2;

 department_id | employee_id | dept_name  | employee_name
---------------+-------------+------------+---------------
             1 |           1 | Accounting |          John
             1 |           2 | Accounting |          Jane

Select with condition on a regular column

You can do this as shown below.

cqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_name = 'John';

 department_id | employee_id | dept_name  | employee_name
---------------+-------------+------------+---------------
             1 |           1 | Accounting |          John
             1 |           3 | Accounting |          John

Select with ORDER BY clause

cqlsh:example> CREATE TABLE sensor_data(device_id INT,
                                        sensor_id INT,
                                        ts TIMESTAMP,
                                        value TEXT,
                                        PRIMARY KEY((device_id), sensor_id, ts)) WITH CLUSTERING ORDER BY (sensor_id ASC, ts DESC);

cqlsh:example> INSERT INTO sensor_data(device_id, sensor_id, ts, value)
                   VALUES (1, 1, '2018-1-1 12:30:30 UTC', 'a');

cqlsh:example> INSERT INTO sensor_data(device_id, sensor_id, ts, value)
                   VALUES (1, 1, '2018-1-1 12:30:31 UTC', 'b');

cqlsh:example> INSERT INTO sensor_data(device_id, sensor_id, ts, value)
                   VALUES (1, 2, '2018-1-1 12:30:30 UTC', 'x');

cqlsh:example> INSERT INTO sensor_data(device_id, sensor_id, ts, value)
                   VALUES (1, 2, '2018-1-1 12:30:31 UTC', 'y');

Reverse scan, opposite of the table’s clustering order.

cqlsh:example> SELECT * FROM sensor_data WHERE device_id = 1 ORDER BY sensor_id DESC, ts ASC;

 device_id | sensor_id | ts                              | value
-----------+-----------+---------------------------------+-------
         1 |         2 | 2018-01-01 12:30:30.000000+0000 |     x
         1 |         2 | 2018-01-01 12:30:31.000000+0000 |     y
         1 |         1 | 2018-01-01 12:30:30.000000+0000 |     a
         1 |         1 | 2018-01-01 12:30:31.000000+0000 |     b

Forward scan, same as a SELECT without an ORDER BY clause.

cqlsh:example> SELECT * FROM sensor_data WHERE device_id = 1 ORDER BY sensor_id ASC, ts DESC;

 device_id | sensor_id | ts                              | value
-----------+-----------+---------------------------------+-------
         1 |         1 | 2018-01-01 12:30:31.000000+0000 |     b
         1 |         1 | 2018-01-01 12:30:30.000000+0000 |     a
         1 |         2 | 2018-01-01 12:30:31.000000+0000 |     y
         1 |         2 | 2018-01-01 12:30:30.000000+0000 |     x

Other orderings are not allowed.

cqlsh:example> SELECT * FROM sensor_data WHERE device_id = 1 ORDER BY sensor_id ASC, ts ASC;

InvalidRequest: Unsupported order by relation
SELECT * FROM sensor_data WHERE device_id = 1 ORDER BY sensor_id ASC, ts ASC; 
                                                        ^^^^^^^^^^^^^^^^^^^^^                    

See Also

CREATE TABLEINSERTUPDATEDELETEExpressionOther CQL Statements