SQL Statements

CockroachDB supports standard SQL statements for data definition, manipulation, and control. This page provides a comprehensive reference organized by statement category.

Statement Categories

SQL statements are classified into four main categories:

DDL (Data Definition Language) - Schema and structure management
DML (Data Manipulation Language) - Data operations
DCL (Data Control Language) - Permissions and access control
TCL (Transaction Control Language) - Transaction management

Data Definition Language (DDL)

DDL statements create, modify, and drop database objects.

Database Operations

CREATE DATABASE
ALTER DATABASE
DROP DATABASE

-- Basic database creation
CREATE DATABASE my_app;

-- With encoding and collation
CREATE DATABASE international 
  ENCODING = 'UTF8' 
  LC_COLLATE = 'en_US.UTF-8';

-- Conditional creation
CREATE DATABASE IF NOT EXISTS my_app;

-- Rename database
ALTER DATABASE my_app RENAME TO my_application;

-- Change owner
ALTER DATABASE my_application OWNER TO admin_user;

-- Set survival goal (multi-region)
ALTER DATABASE my_app SURVIVE ZONE FAILURE;

-- Drop database
DROP DATABASE my_app;

-- Drop with cascade
DROP DATABASE my_app CASCADE;

-- Conditional drop
DROP DATABASE IF EXISTS my_app CASCADE;

Schema Operations

CREATE SCHEMA analytics;

CREATE SCHEMA IF NOT EXISTS reporting;

CREATE SCHEMA private AUTHORIZATION data_owner;

Table Operations

CREATE TABLE
ALTER TABLE
DROP TABLE

-- Basic table with primary key
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with composite primary key
CREATE TABLE order_items (
  order_id INT,
  item_id INT,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, item_id)
);

-- Table with hash-sharded primary key
CREATE TABLE events (
  event_id UUID,
  event_type TEXT,
  occurred_at TIMESTAMP,
  PRIMARY KEY (event_id) USING HASH WITH (bucket_count = 8)
);

-- Temporary table
CREATE TEMP TABLE session_data (
  session_id UUID PRIMARY KEY,
  data JSONB
);

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Change column type
ALTER TABLE users ALTER COLUMN email TYPE TEXT;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT email_check 
  CHECK (email LIKE '%@%');

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT email_check;

-- Set default
ALTER TABLE users ALTER COLUMN created_at 
  SET DEFAULT CURRENT_TIMESTAMP;

-- Rename table
ALTER TABLE users RENAME TO app_users;

DROP TABLE users;

DROP TABLE IF EXISTS temp_data;

-- Drop multiple tables
DROP TABLE users, sessions, logs CASCADE;

Index Operations

-- Standard index
CREATE INDEX ON users (email);

-- Named index
CREATE INDEX users_email_idx ON users (email);

-- Composite index
CREATE INDEX users_name_email_idx ON users (last_name, first_name, email);

-- Unique index
CREATE UNIQUE INDEX ON users (username);

-- Partial index
CREATE INDEX active_users_idx ON users (email) 
  WHERE status = 'active';

-- Inverted index (for JSONB)
CREATE INVERTED INDEX ON logs (metadata);

-- Concurrent index creation
CREATE INDEX CONCURRENTLY ON large_table (column_name);

Sequence Operations

-- Create sequence
CREATE SEQUENCE customer_id_seq START WITH 1000 INCREMENT BY 1;

-- Use in table
CREATE TABLE customers (
  id INT PRIMARY KEY DEFAULT nextval('customer_id_seq'),
  name TEXT NOT NULL
);

-- Alter sequence
ALTER SEQUENCE customer_id_seq RESTART WITH 2000;
ALTER SEQUENCE customer_id_seq INCREMENT BY 10;

-- Drop sequence
DROP SEQUENCE customer_id_seq;

View Operations

CREATE VIEW active_users AS
  SELECT id, username, email 
  FROM users 
  WHERE status = 'active';

CREATE OR REPLACE VIEW user_summary AS
  SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.username;

Data Manipulation Language (DML)

DML statements query and modify data.

SELECT Statement

Basic SELECT
WHERE Clause
Joins
Aggregation
Ordering & Limiting

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email FROM users;

-- With aliases
SELECT 
  id AS user_id,
  username AS user_name,
  email AS contact_email
FROM users;

-- Basic filtering
SELECT * FROM users WHERE status = 'active';

-- Multiple conditions
SELECT * FROM users 
WHERE status = 'active' AND created_at > '2025-01-01';

-- IN clause
SELECT * FROM users 
WHERE id IN (1, 2, 3, 5, 8);

-- Pattern matching
SELECT * FROM users 
WHERE email LIKE '%@example.com';

-- Range query
SELECT * FROM orders 
WHERE total BETWEEN 100 AND 1000;

-- INNER JOIN
SELECT u.username, o.order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- Multiple joins
SELECT 
  u.username,
  o.order_id,
  oi.item_id,
  oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id;

-- Basic aggregation
SELECT 
  COUNT(*) AS total_users,
  COUNT(DISTINCT email) AS unique_emails
FROM users;

-- GROUP BY
SELECT 
  status,
  COUNT(*) AS user_count,
  AVG(age) AS avg_age
FROM users
GROUP BY status;

-- HAVING clause
SELECT 
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- ORDER BY
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple sort columns
SELECT * FROM users 
ORDER BY last_name ASC, first_name ASC;

-- LIMIT and OFFSET
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;

-- Pagination pattern
SELECT * FROM users 
WHERE id > 100 
ORDER BY id 
LIMIT 50;

INSERT Statement

INSERT INTO users (username, email) 
VALUES ('alice', '[email protected]');

-- With all columns specified
INSERT INTO users (id, username, email, created_at)
VALUES (1, 'alice', '[email protected]', CURRENT_TIMESTAMP);

UPDATE Statement

-- Basic update
UPDATE users SET status = 'inactive' WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET 
  status = 'active',
  last_login = CURRENT_TIMESTAMP
WHERE id = 1;

-- Update with subquery
UPDATE orders
SET status = 'shipped'
WHERE id IN (
  SELECT order_id FROM shipments 
  WHERE shipped_at IS NOT NULL
);

-- Update with join pattern
UPDATE orders o
SET total = (
  SELECT SUM(price * quantity)
  FROM order_items oi
  WHERE oi.order_id = o.id
);

-- RETURNING clause
UPDATE users 
SET status = 'active' 
WHERE id = 1
RETURNING id, status, updated_at;

DELETE Statement

-- Basic delete
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM sessions 
WHERE expires_at < CURRENT_TIMESTAMP;

-- Delete with subquery
DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled'
);

-- RETURNING clause
DELETE FROM users 
WHERE status = 'deleted'
RETURNING id, username;

TRUNCATE Statement

-- Fast delete all rows
TRUNCATE users;

-- Truncate multiple tables
TRUNCATE users, sessions, logs CASCADE;

TRUNCATE is faster than DELETE but cannot be rolled back in some scenarios and resets sequences.

Data Control Language (DCL)

DCL statements manage permissions and access control.

User and Role Management

CREATE ROLE analyst;

CREATE USER alice WITH PASSWORD 'secure_password';

CREATE ROLE admin WITH LOGIN PASSWORD 'admin_pass';

Transaction Control Language (TCL)

See the Transactions page for detailed transaction control documentation.

Utility Statements

SHOW Statements

SHOW DATABASES;

SHOW SCHEMAS FROM my_app;

SET Statements

-- Session variable
SET statement_timeout = '60s';

-- Search path
SET search_path = my_app, public;

-- Time zone
SET TIME ZONE 'America/New_York';

-- Application name
SET application_name = 'my_application';

EXPLAIN Statement

-- Show query plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output
EXPLAIN (VERBOSE) SELECT * FROM orders o 
  JOIN users u ON o.user_id = u.id;

-- Analyze actual execution
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

Statement Categories

Data Definition Language (DDL)

Database Operations

Schema Operations

Table Operations

Index Operations

Sequence Operations

View Operations

Data Manipulation Language (DML)

SELECT Statement

INSERT Statement

UPDATE Statement

DELETE Statement

TRUNCATE Statement

Data Control Language (DCL)

User and Role Management

Transaction Control Language (TCL)

Utility Statements

SHOW Statements

SET Statements

EXPLAIN Statement

See Also

Build docs developers (and LLMs) love

Getting Started

Architecture

SQL Reference

Administration

Operations

Performance

​Statement Categories

​Data Definition Language (DDL)

​Database Operations

​Schema Operations

​Table Operations

​Index Operations

​Sequence Operations

​View Operations

​Data Manipulation Language (DML)

​SELECT Statement

​INSERT Statement

​UPDATE Statement

​DELETE Statement

​TRUNCATE Statement

​Data Control Language (DCL)

​User and Role Management

​Transaction Control Language (TCL)

​Utility Statements

​SHOW Statements

​SET Statements

​EXPLAIN Statement

​See Also

Build docs developers (and LLMs) love

Statement Categories

Data Definition Language (DDL)

Database Operations

Schema Operations

Table Operations

Index Operations

Sequence Operations

View Operations

Data Manipulation Language (DML)

SELECT Statement

INSERT Statement

UPDATE Statement

DELETE Statement

TRUNCATE Statement

Data Control Language (DCL)

User and Role Management

Transaction Control Language (TCL)

Utility Statements

SHOW Statements

SET Statements

EXPLAIN Statement

See Also