SELECT statement [YCQL]

Synopsis

Use the SELECT statement to retrieve (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

select

order_expression

Grammar

ebnf

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

order_expression ::= ( { 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.
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.
ALLOW FILTERING is provided for syntax compatibility with Cassandra. You can always filter on all columns.
Reads default to QUORUM and read from the tablet-leader.
To read from followers use ONE consistency level.
To benefit from local reads, in addition to specifying the consistency level of ONE, set the region also in the client driver to indicate where the request is coming from, and it should match the --placement_region argument for the yb-tservers in that region.

`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 must evaluate to boolean values.
The where_expression can specify conditions on any columns including partition, clustering, and regular columns.
The where_expression has a restricted list of operators.
- 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 and regular columns.
- Only IN operator can be used for conditions on tuples of clustering columns.

`IF` clause

The if_expression must evaluate to boolean values.
The if_expression supports any combinations of all available boolean and logical operators.
The if_expression can only specify conditions for non-primary-key columns although it can used on a key column of a secondary index.
While WHERE condition is used to generate efficient query plan, the IF condition is not. ALL rows that satisfy WHERE condition will be read from the database before the IF condition is used to filter unwanted data. In the following example, although the two queries yield the same result set, SELECT with WHERE clause will use INDEX-SCAN while SELECT with IF clause will use FULL-SCAN.

cql

SELECT * FROM a_table WHERE key = 'my_key';
SELECT * FROM a_table IF key = 'my_key';

{{< note title="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.{{< /note >}}

Examples

Select all rows from a table

sql

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

sql

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

sql

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

sql

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

sql

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

sql

ycqlsh:example> SELECT * FROM employees;

output

 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

sql

ycqlsh:example> SELECT * FROM employees LIMIT 2;

output

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

Select with offset

sql

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

output

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

Select distinct values

sql

ycqlsh:example> SELECT DISTINCT dept_name FROM employees;

output

 dept_name
------------
 Accounting
  Marketing

Select with a condition on the partitioning column

sql

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

output

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

Select with condition on the clustering column

sql

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

output

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

Select with condition on a regular column, using WHERE clause

sql

ycqlsh:example> SELECT * FROM employees WHERE employee_name = 'John';

output

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

Select with condition on a regular column, using IF clause

sql

ycqlsh:example> SELECT * FROM employees WHERE department_id = 1 IF employee_name != 'John';

output

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

Select with `ORDER BY` clause

sql

ycqlsh: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);

sql

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

sql

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

sql

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

sql

ycqlsh: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.

sql

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

output

 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.

sql

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

output

 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.

sql

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

output

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

Synopsis

Syntax

Diagram

select

order_expression

Grammar

Semantics

ORDER BY clause

WHERE clause

IF clause

Examples

Select all rows from a table

Select with limit

Select with offset

Select distinct values

Select with a condition on the partitioning column

Select with condition on the clustering column

Select with condition on a regular column, using WHERE clause

Select with condition on a regular column, using IF clause

Select with ORDER BY clause

See also

`ORDER BY` clause

`WHERE` clause

`IF` clause

Select with `ORDER BY` clause