The CockroachDB Select Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial we’ll be demonstrating how to use CockroachDB SELECT statements. Databases hold huge amounts of information but with select statements we can pick exactly which data we want to work with right now. Please continue reading and we’ll show you a few different ways to specify which data you need using the SELECT command.

Prerequisites

  • You must have CockroachDB installed on your computer.
  • Must be familiar in SQL queries.

CockroachDB SELECT

The SELECT statement is used to retrieve records from your tables.

The following is the basic syntax of the SELECT statement.

1
SELECT * FROM TABLE_NAME;

After the SELECT statement you specify the name of the columns you want to get or if you want to get all the all the columns, specify the asterisk (*). Then after the FROM keyword specify the table name from where you want to get the records.

CockroachDB SELECT examples

Before we begin the examples we’ll first create a table then insert records to it so we have data to work with. Here we create a table called fruits with an id and a field named fruit.

1
2
3
4
CREATE TABLE fruits (
    id INTEGER PRIMARY KEY,
    fruit VARCHAR (50)
);

Next we insert some data into that table:

1
2
3
4
5
INSERT INTO fruits (id, fruit)
VALUES ('1', 'apple'),
('2', 'strawberry'),
('3', 'grape'),
('4', 'banana');

Now that we have a table populated with data we can proceed with our examples. Here are some of the SELECT statement you may use. The first example is the most common and basic way to select records where you select all records in a table.

1
2
3
4
5
6
7
8
SELECT * FROM fruits;
  id |   fruit
-----+-------------
   1 | apple
   2 | strawberry
   3 | grape
   4 | banana
(4 ROWS)

CockroachDB SELECT FOR UPDATE

If you’d like to modify or update records stored on a CockroachDB table using a transaction you can use the FOR UPDATE clause.

The first step is to start the transaction using the BEGIN; keyword, then select all of the records, and finally use a conditional (with the WHERE keyword) to select a particular record like in the following example:

1
2
3
BEGIN;
SELECT * FROM fruits
WHERE id = 2 FOR UPDATE;

After you’ve primed the transaction and selected a record, you can update the record and commit the changes with the following:

1
2
UPDATE fruits SET fruit='strawberries' WHERE id = 2;
COMMIT;

CockroachDB SELECT tutorial screenshot showing a CockroachDB select for update example

After the transaction commits successfully the resulting table should look like the following:

1
2
3
4
5
6
  id |   fruit
+----+------------+
   2 | strawberry
(1 ROW)

TIME: 5.287ms

CockroachDB SELECT one column

You may also specify which column data you want from the table. To do that, specify the name of the columns after the SELECT statement. ( If you specify more than one column they should be separated by a comma eg.. SELECT id, fruit FROM fruits.

For this example, we will get only the fruit column.

1
2
3
4
5
6
7
8
SELECT fruit FROM fruits;
    fruit
--------------
  apple
  strawberry
  grape
  banana
(4 ROWS)

CockroachDB SELECT with WHERE

By using the WHERE clause, you can filter the records that you will get.

For this example, we will get the list of all the records from that table where the id is larger than two.

1
2
3
4
5
6
SELECT * FROM fruits WHERE id > 2;
  id | fruit
-----+---------
   3 | grape
   4 | banana
(2 ROWS)

CockroachDB SELECT with COUNT

By using the COUNT, you can get the number of rows in your table.

For this example, we will count the number of rows from the table fruits.

1
2
3
4
5
SELECT COUNT(*) FROM fruits;
  COUNT
---------
      4
(1 ROW)

CockroachDB SELECT with LIMIT example

By using the LIMIT clause, you can put a limit on how many rows from the table will be returned.

For this example, we will get two rows from the table fruits.

1
2
3
4
5
6
SELECT * FROM fruits LIMIT 2;
  id |   fruit
-----+-------------
   1 | apple
   2 | strawberry
(2 ROWS)

CockroachDB ORDER BY examples

By using the ORDER BY clause, you can sort the the values from the table in ascending or in descending.

For this example, we will sort our records by the column fruit in ascending order.

1
2
3
4
5
6
7
8
SELECT * FROM fruits ORDER BY fruit ASC;
  id |   fruit
-----+-------------
   1 | apple
   4 | banana
   3 | grape
   2 | strawberry
(4 ROWS)

CockroachDB ORDER BY desc

You can use the DESC keyword to have the records return in descending order instead:

1
SELECT * FROM fruits ORDER BY fruit DESC;

Conclusion

We hope you’ve found this article on CockroachDB SELECT queries informative and you can apply it to your application. We showed a few different ways of using the SELECT query including specifying which fields, limiting results, and ordering.

If you don’t have a database to try this demo out, we recommend setting up a free trial instance in ObjectRocket. It’s easy to get setup and connected to so you can concentrate on coding. Thanks for joining us.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.