Query Data

This page has instructions for making SQL selection queries against CockroachDB from various programming languages.

Before you begin

Before reading this page, do the following:

Create a CockroachDB Standard cluster or start a local cluster.
Install a Driver or ORM Framework.
Connect to the database.
Insert data that you now want to run queries against.

When running under the default SERIALIZABLE isolation level, your application should use a retry loop to handle transaction retry errors that can occur under contention.

Simple selects

SQL
Go
Java
Python

SELECT id, balance from accounts;

For more information about how to use the built-in SQL client, see the cockroach sql reference docs.

// 'db' is an open database connection

rows, err := db.Query("SELECT id, balance FROM accounts")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
fmt.Println("Initial balances:")
for rows.Next() {
    var id, balance int
    if err := rows.Scan(&id, &balance); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%d %d\n", id, balance)
}

For complete examples, see:

// ds is an org.postgresql.ds.PGSimpleDataSource

try (Connection connection = ds.getConnection()) {
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT id, balance FROM accounts");

    while (rs.next()) {
        int id = rs.getInt(1);
        int bal = rs.getInt(2);
        System.out.printf("ID: %10s\nBalance: %5s\n", id, bal);
    }
    rs.close();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n",
                      e.getSQLState(), e.getCause(), e.getMessage());
}

For complete examples, see:

# conn is a psycopg2 connection

with conn.cursor() as cur:
    cur.execute("SELECT id, balance FROM accounts")
    rows = cur.fetchall()
    for row in rows:
        print([str(cell) for cell in row])

For complete examples, see:

Order results

To order the results of a query, use an ORDER BY clause. For example:

SELECT * FROM bank ORDER BY balance;

  id | balance |                                               payload
-----+---------+-------------------------------------------------------------------------------------------------------
   0 |    -500 | initial-dTqnRurXztAPkykhZWvsCmeJkMwRNcJAvTlNbgUEYfagEQJaHmfPsquKZUBOGwpAjPtATpGXFJkrtQCEJODSlmQctvyh
   1 |    -499 | initial-PCLGABqTvrtRNyhAyOhQdyLfVtCmRykQJSsdwqUFABkPOMQayVEhiAwzZKHpJUiNmVaWYZnReMKfONZvRKbTETaIDccE
   2 |    -498 | initial-VNfyUJHfCmMeAUoTgoSVvnByDyvpHNPHDfVoNWdXBFQpwMOBgNVtNijyTjmecvFqyeLHlDbIBRrbCzSeiHWSLmWbhIvh
   3 |    -497 | initial-llflzsVuQYUlfwlyoaqjdwKUNgNFVgvlnINeOUUVyfxyvmOiAelxqkTBfpBBziYVHgQLLEuCazSXmURnXBlCCfsOqeji
   4 |    -496 | initial-rmGzVVucMqbYnBaccWilErbWvcatqBsWSXvrbxYUUEhmOnccXzvqcsGuMVJNBjmzKErJzEzzfCzNTmLQqhkrDUxdgqDD
(5 rows)

For reference documentation and more examples, see the ORDER BY syntax page.

Limit results

To limit the results of a query, use a LIMIT clause. For example:

SELECT * FROM bank LIMIT 5;

  id | balance |                                               payload
-----+---------+-------------------------------------------------------------------------------------------------------
   0 |       0 | initial-dTqnRurXztAPkykhZWvsCmeJkMwRNcJAvTlNbgUEYfagEQJaHmfPsquKZUBOGwpAjPtATpGXFJkrtQCEJODSlmQctvyh
   1 |       0 | initial-PCLGABqTvrtRNyhAyOhQdyLfVtCmRykQJSsdwqUFABkPOMQayVEhiAwzZKHpJUiNmVaWYZnReMKfONZvRKbTETaIDccE
   2 |       0 | initial-VNfyUJHfCmMeAUoTgoSVvnByDyvpHNPHDfVoNWdXBFQpwMOBgNVtNijyTjmecvFqyeLHlDbIBRrbCzSeiHWSLmWbhIvh
   3 |       0 | initial-llflzsVuQYUlfwlyoaqjdwKUNgNFVgvlnINeOUUVyfxyvmOiAelxqkTBfpBBziYVHgQLLEuCazSXmURnXBlCCfsOqeji
   4 |       0 | initial-rmGzVVucMqbYnBaccWilErbWvcatqBsWSXvrbxYUUEhmOnccXzvqcsGuMVJNBjmzKErJzEzzfCzNTmLQqhkrDUxdgqDD
(5 rows)

For reference documentation and more examples, see the LIMIT/OFFSET syntax page.

Joins

The syntax for a selection query with a two-way join is shown below.

SELECT
    a.col1, b.col1
FROM
    some_table AS a
    JOIN
    some_other_table AS b
    ON
    a.id = b.id
WHERE
    a.col2 > 100 AND a.col3 > now()
ORDER BY
    a.col2 DESC
LIMIT
    25;

Join performance can be a big factor in your application’s performance. For more information about how to make sure your SQL performs well, see Optimize Statement Performance.

Get Started

CockroachDB and AI

Feature Overview

Data Resilience

Connect to an Application

Self-Hosted Deployments

Schema Design

Reads and Writes

Stream Data

Cross-Cluster Replication

Multi-Region Capabilities

Optimize Performance

Integrate

Before you begin

Simple selects

Order results

Limit results

Joins

See also

​Before you begin

​Simple selects

​Order results

​Limit results

​Joins

​See also

Before you begin

Simple selects

Order results

Limit results

Joins

See also