Skip to main content
Version: 3.12
Features and pricing

ScalarDB SQL Grammar

DDL​

CREATE NAMESPACE​

Before creating tables, namespaces must be created since a table belongs to one namespace. The CREATE NAMESPACE command creates a namespace.

Grammar​

CREATE NAMESPACE [IF NOT EXISTS] <namespace name> [WITH creation_options]

creation_options: <option name>=<option value> [AND <option name>=<option value>] ...

For details about creation_options, see Creation options.

Examples​

Examples of CREATE NAMESPACE are as follows:

-- Create a namespace "ns"
CREATE NAMESPACE ns;

-- Create a namespace only if it does not already exist
CREATE NAMESPACE IF NOT EXISTS ns;

-- Create a namespace with options
CREATE NAMESPACE ns WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';

Examples of building statement objects for CREATE NAMESPACE are as follows:

// Create a namespace "ns"
CreateNamespaceStatement statement1 = StatementBuilder.createNamespace("ns").build();

// Create a namespace only if it does not already exist
CreateNamespaceStatement statement2 =
    StatementBuilder.createNamespace("ns").ifNotExists().build();

// Create a namespace with options
CreateNamespaceStatement statement3 =
    StatementBuilder.createNamespace("ns")
        .withOption("option1", "value1")
        .withOption("option2", "value2")
        .withOption("option3", "value3")
        .build();

CREATE TABLE​

The CREATE TABLE command creates a table.

For details about the ScalarDB data model, see ScalarDB Design Document.

Grammar​

Create a table with a primary key that is composed of a single column:

CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
  <primary key column name> data_type PRIMARY KEY,
  <column name> data_type,
  ...
) [WITH creation_options]

data_type: BOOLEAN | INT | BIGINT | FLOAT | DOUBLE | TEXT | BLOB
creation_options: <option name>=<option value> [AND <option name>=<option value>] ...

Create a table with a primary key that is composed of one partition key column and multiple clustering key columns:

CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
  <partition key column name> data_type,
  <clustering key column name> data_type,
  ...,
  <column name> data_type,
  ...,
  PRIMARY KEY (<partition key column name>, <clustering key column name> [, <clustering key column name>] ...)
) [WITH [clustering_order_definition [AND creation_options]] | creation_options]

clustering_order_definition: CLUSTERING ORDER BY (<clustering key column name> [clustering_order] [, <clustering key column name> [clustering_order]] ...)
clustering_order: ASC | DESC

If you omit clustering_order, the default clustering order ASC is used.

Create a table with a primary key that is composed of multiple partition key columns and multiple clustering key columns:

CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
  <partition key column name> data_type,
  ...,
  <clustering key column name> data_type,
  ...,
  <column name1> data_type,
  <column name2> data_type,
  ...,
  PRIMARY KEY ((<partition key column name> [, <partition key column name>] ...), <clustering key column name> [, <clustering key column name>] ...)
) [WITH [clustering_order_definition [AND creation_options]] | creation_options]

Examples​

Examples of CREATE TABLE are as follows:

-- Create a table with a primary key ("c1") and creation options
CREATE TABLE ns.tbl (
  c1 INT PRIMARY KEY,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN
) WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';

-- Create a table with a partition key ("c1") and a clustering key ("c2" and "c3") with clustering order definition only if it does not already exist
CREATE TABLE IF NOT EXISTS tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);

-- Create a table with a partition key ("c1", "c2") and a clustering key ("c3" and "c4") with clustering order definition and creation options
CREATE TABLE ns.tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY ((c1, c2), c3, c4)
) WITH CLUSTERING ORDER BY (c3 DESC, c4 ASC) AND 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';

Examples of building statement objects for CREATE TABLE are as follows:

// Create a table with a primary key ("c1") and creation options
CreateTableStatement statement1 =
    StatementBuilder.createTable("ns", "tbl")
        .withPartitionKey("c1", DataType.INT)
        .withColumn("c2", DataType.TEXT)
        .withColumn("c3", DataType.FLOAT)
        .withColumn("c4", DataType.BIGINT)
        .withColumn("c5", DataType.BOOLEAN)
        .withOption("option1", "value1")
        .withOption("option2", "value2")
        .withOption("option3", "value3")
        .build();

// Create a table with a partition key ("c1") and a clustering key ("c2" and "c3") with clustering order definition
CreateTableStatement statement2 =
    StatementBuilder.createTable("tbl")
        .ifNotExists()
        .withPartitionKey("c1", DataType.INT)
        .withClusteringKey("c2", DataType.TEXT)
        .withClusteringKey("c3", DataType.FLOAT)
        .withColumn("c4", DataType.BIGINT)
        .withColumn("c5", DataType.BOOLEAN)
        .withClusteringOrder("c2", ClusteringOrder.DESC)
        .withClusteringOrder("c3", ClusteringOrder.ASC)
        .build();

// Create a table with a partition key ("c1", "c2") and a clustering key ("c3" and "c4") with clustering order definition and creation options
CreateTableStatement statement3 =
    StatementBuilder.createTable("ns", "tbl")
        .withPartitionKey("c1", DataType.INT)
        .withPartitionKey("c2", DataType.TEXT)
        .withClusteringKey("c3", DataType.FLOAT)
        .withClusteringKey("c4", DataType.BIGINT)
        .withColumn("c5", DataType.BOOLEAN)
        .withClusteringOrder("c3", ClusteringOrder.DESC)
        .withClusteringOrder("c4", ClusteringOrder.ASC)
        .withOption("option1", "value1")
        .withOption("option2", "value2")
        .withOption("option3", "value3")
        .build();

CREATE INDEX​

The CREATE INDEX command creates a secondary index on a table.

Grammar​

CREATE INDEX [IF NOT EXISTS] ON [<namespace name>.]<table name> (<column name>)

Examples​

Examples of CREATE INDEX are as follows:

-- Create a secondary index on a column "c4" of a table "ns.tbl"
CREATE INDEX ON ns.tbl (c4);

-- Create a secondary index only if it does not already exist
CREATE INDEX IF NOT EXISTS ON tbl (c4);

-- Create a secondary index with options
CREATE INDEX ON ns.tbl (c4) WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';

Examples of building statement objects for CREATE INDEX are as follows:

// Create a secondary index on a column "c4" of a table "ns.tbl"
CreateIndexStatement statement1 =
    StatementBuilder.createIndex().onTable("ns", "tbl").column("c4").build();

// Create a secondary index only if it does not already exist
CreateIndexStatement statement2 =
    StatementBuilder.createIndex().ifNotExists().onTable("tbl").column("c4").build();

// Create a secondary index with options
CreateIndexStatement statement3 =
    StatementBuilder.createIndex()
        .onTable("ns", "tbl")
        .column("c4")
        .withOption("option1", "value1")
        .withOption("option2", "value2")
        .withOption("option3", "value3")
        .build();

TRUNCATE TABLE​

The TRUNCATE TABLE command truncates a table.

Grammar​

TRUNCATE TABLE [<namespace name>.]<table name>

Examples​

Examples of TRUNCATE TABLE are as follows:

-- Truncate a table "ns.tbl"
TRUNCATE TABLE ns.tbl;

Examples of building statement objects for TRUNCATE TABLE are as follows:

// Truncate a table "ns.tbl"
TruncateTableStatement statement = StatementBuilder.truncateTable("ns", "tbl").build();

DROP INDEX​

The DROP INDEX command drops a secondary index.

Grammar​

DROP INDEX [IF EXISTS] ON [<namespace name>.]<table name> (<column name>)

Examples​

Examples of DROP INDEX are as follows:

-- Drop a secondary index on a column "c4" of a table "ns.tbl"
DROP INDEX ON ns.tbl (c4);

-- Drop a secondary index only if it exists
DROP INDEX IF EXISTS ON tbl (c4);

Examples of building statement objects for DROP INDEX are as follows:

// Drop a secondary index on a column "c4" of a table "ns.tbl"
DropIndexStatement statement1 =
    StatementBuilder.dropIndex().onTable("ns", "tbl").column("c4").build();

// Drop a secondary index only if it exists
DropIndexStatement statement2 =
    StatementBuilder.dropIndex().ifExists().onTable("ns", "tbl").column("c4").build();

DROP TABLE​

The DROP TABLE command drops a table.

Grammar​

DROP TABLE [IF EXISTS] [<namespace name>.]<table name>

Examples​

Examples of DROP TABLE are as follows:

-- Drop a table "ns.tbl"
DROP TABLE ns.tbl;

-- Drop a table only if it exists
DROP TABLE IF EXISTS tbl;

Examples of building statement objects for DROP TABLE are as follows:

// Drop a table "ns.tbl"
DropTableStatement statement1 = StatementBuilder.dropTable("ns", "tbl").build();

// Drop a table only if it exists
DropTableStatement statement2 = StatementBuilder.dropTable("ns", "tbl").ifExists().build();

DROP NAMESPACE​

The DROP NAMESPACE command drops a namespace.

Grammar​

DROP NAMESPACE [IF EXISTS] <namespace name> [CASCADE]

Examples​

Examples of DROP NAMESPACE are as follows:

-- Drop a namespace "ns"
DROP NAMESPACE ns;

-- Drop a namespace only if it exists
DROP NAMESPACE IF EXISTS ns;

-- Drop a namespace with cascade
DROP NAMESPACE ns CASCADE;

Examples of building statement objects for DROP NAMESPACE are as follows:

// Drop a namespace "ns"
DropNamespaceStatement statement1 = StatementBuilder.dropNamespace("ns").build();

// Drop a namespace only if it exists
DropNamespaceStatement statement2 = StatementBuilder.dropNamespace("ns").ifExists().build();

// Drop a namespace with cascade
DropNamespaceStatement statement3 = StatementBuilder.dropNamespace("ns").cascade().build();

CREATE COORDINATOR TABLES​

The CREATE COORDINATOR TABLES command creates coordinator tables.

Grammar​

CREATE COORDINATOR TABLES [IF NOT EXIST] [WITH creation_options]

creation_options: <option name>=<option value> [AND <option name>=<option value>] ...

Examples​

Examples of CREATE COORDINATOR TABLES are as follows:

-- Create coordinator tables
CREATE COORDINATOR TABLES;

-- Create coordinator tables only if they do not already exist
CREATE COORDINATOR TABLES IF NOT EXIST;

-- Create coordinator tables with options
CREATE COORDINATOR TABLES WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';

Examples of building statement objects for CREATE COORDINATOR TABLES are as follows:

// Create coordinator tables
CreateCoordinatorTablesStatement statement1 =
    StatementBuilder.createCoordinatorTables().build();

// Create coordinator tables only if they do not already exist
CreateCoordinatorTablesStatement statement2 =
    StatementBuilder.createCoordinatorTables().ifNotExist().build();

// Create coordinator tables with options
CreateCoordinatorTablesStatement statement3 =
    StatementBuilder.createCoordinatorTables()
        .withOption("option1", "value1")
        .withOption("option2", "value2")
        .withOption("option3", "value3")
        .build();

TRUNCATE COORDINATOR TABLES​

The TRUNCATE COORDINATOR TABLES command truncates coordinator tables.

Grammar​

TRUNCATE COORDINATOR TABLES

Examples​

An example of building statement objects for TRUNCATE COORDINATOR TABLES is as follows:

// Truncate coordinator tables
TruncateCoordinatorTablesStatement statement =
    StatementBuilder.truncateCoordinatorTables().build();

DROP COORDINATOR TABLES​

The DROP COORDINATOR TABLES command drops coordinator tables.

Grammar​

DROP COORDINATOR TABLES [IF EXIST]

Examples​

Examples of DROP COORDINATOR TABLES are as follows:

-- Drop coordinator tables
DROP COORDINATOR TABLES;

-- Drop coordinator tables if they exist
DROP COORDINATOR TABLES IF EXIST;

Examples of building statement objects for DROP COORDINATOR TABLES are as follows:

// Drop coordinator tables
DropCoordinatorTablesStatement statement1 = StatementBuilder.dropCoordinatorTables().build();

// Drop coordinator tables if they exist
DropCoordinatorTablesStatement statement2 =
    StatementBuilder.dropCoordinatorTables().ifExist().build();

ALTER TABLE​

The ALTER TABLE command alters a table (ex. adding a column).

Grammar​

ALTER TABLE [<namespace name>.]<table name> ADD [COLUMN] <column name> data_type

data_type: BOOLEAN | INT | BIGINT | FLOAT | DOUBLE | TEXT | BLOB

Examples​

Examples of ALTER TABLE are as follows:

-- Add a new column "new_col" to "ns.tbl"
ALTER TABLE ns.tbl ADD COLUMN new_col INT;

Examples of building statement objects for ALTER TABLE are as follows:

// Add a new column "new_col" to "ns.tbl"
AlterTableAddColumnStatement statement =
    StatementBuilder.alterTable("ns", "tbl").addColumn("new_col", DataType.INT).build();

DML​

SELECT​

The SELECT command retrieves records from the database. By default, the SELECT command requires the WHERE clause to filter records by primary key or an indexed column value. If you want to retrieve all records without specifying the WHERE clause, see SELECT (with cross-partition scan). For JDBC databases only, you can use the SELECT command with arbitrary conditions and orderings. For details, see SELECT (with cross-partition scan filtering and ordering).

Grammar​

SELECT projection [, projection] ...
  FROM [<namespace name>.]<table name> [AS <alias>] [join_specification [join_specification] ...]
  WHERE predicate [AND predicate] ...
  [ORDER BY <clustering key identifier> [order] [, <clustering key identifier> [order]] ...]
  [LIMIT <limit>]

projection: * | identifier [AS <alias>]
join_specification: [INNER] JOIN [<namespace name>.]<table name> [AS <alias>] ON join_predicate [AND join_predicate] ... | {LEFT|RIGHT} [OUTER] JOIN [<namespace name>.]<table name> [AS <alias>] ON join_predicate [AND join_predicate] ...
join_predicate: <identifier> = <identifier>
predicate: <identifier> operator <column value> | <identifier> BETWEEN <column value> AND <column value>
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
operator: = | > | >= | < | <=
order: ASC | DESC
Note​
  • You need to specify primary key columns or an indexed column in the WHERE clause.
  • In the WHERE clause, you can use only the equal to (=) operator for partition key columns, and you can use the operators above for clustering key columns.
  • You can also use the BETWEEN operator for clustering key columns.
  • If you specify the WHERE clause to an indexed column, you can only use the equal to (=) operator for the index column.
  • If you specify the WHERE clause to an indexed column, you cannot specify ORDER BY.
  • You can specify <column value> and <limit> to a bind marker (positional ? and named :<name>).
  • If you omit order, the default order ASC will be used.
  • For [INNER] JOIN and LEFT [OUTER] JOIN:
    • The join_predicates must include either all primary key columns or a secondary index column from the right table.
    • The WHERE predicates and the ORDER BY clause can only include columns from the table specified in the FROM clause.
  • For RIGHT [OUTER] JOIN:
    • It must be specified as the first join_specification.
    • The join_predicates must contain all primary key columns or a secondary index column from the left table.
    • The WHERE predicates and the ORDER BY clause can only specify columns from the table specified in the RIGHT OUTER JOIN clause.

For details about retrieving data from a database in ScalarDB, see Get operation and Scan operation.

Examples​

Let's say you have the following table and index:

CREATE TABLE ns.tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);

CREATE INDEX ON ns.tbl (c4);

Examples of SELECT are as follows:

-- With a full primary key
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;

-- With a partial primary key
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa';

-- With projections and a partition key and clustering key boundaries
SELECT c1, c2, c3, c5 FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 >= 1.23 AND c3 < 4.56;

-- With projections and a partition key and clustering key boundaries and orders and limit
SELECT c1 AS a, c2 AS b, c3 AS c, c5 FROM ns.tbl WHERE c1 = 10 AND c2 > 'aaa' AND c2 <= 'ddd' ORDER BY c2 ASC, c3 DESC LIMIT 10;

-- With an indexed column
SELECT * FROM ns.tbl WHERE c4 = 100;

-- With projections and an indexed column and limit
SELECT c1, c2, c3, c4 FROM ns.tbl WHERE c4 = 100 LIMIT 10;

-- With positional bind markers
SELECT * FROM ns.tbl WHERE c1 = ? AND c2 > ? AND c2 <= ? ORDER BY c2 ASC, c3 DESC LIMIT ?;

Examples of building statement objects for SELECT are as follows:

// With a full primary key
SelectStatement statement1 =
    StatementBuilder.select()
        .from("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
        .and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
        .build();

// With a partial primary key
SelectStatement statement2 =
    StatementBuilder.select()
        .from("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
        .build();

// With projections and a partition key and clustering key boundaries
SelectStatement statement3 =
    StatementBuilder.select("c1", "c2", "c3", "c5")
        .from("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
        .and(Predicate.column("c3").isGreaterThanOrEqualTo(Value.of(1.23F)))
        .and(Predicate.column("c3").isLessThan(Value.of(4.56F)))
        .build();

// With projections and a partition key and clustering key boundaries and orders and limit
SelectStatement statement4 =
   StatementBuilder.select(
             Projection.column("c1").as("a"),
             Projection.column("c2").as("b"),
             Projection.column("c3").as("c"),
             Projection.column("c5").as("d"))
        .from("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isGreaterThan(Value.of("aaa")))
        .and(Predicate.column("c2").isLessThanOrEqualTo(Value.of("ddd")))
        .orderBy(Ordering.column("c2").asc(), Ordering.column("c3").desc())
        .limit(10)
        .build();

// With an indexed column
SelectStatement statement5 =
    StatementBuilder.select()
        .from("ns", "tbl")
        .where(Predicate.column("c4").isEqualTo(Value.of(100)))
        .build();

// With projections and an indexed column and limit
SelectStatement statement6 =
    StatementBuilder.select("c1", "c2", "c3", "c4")
        .from("ns", "tbl")
        .where(Predicate.column("c4").isEqualTo(Value.of(100)))
        .limit(10)
        .build();

// With positional bind markers
SelectStatement statement7 =
    StatementBuilder.select()
        .from("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
        .and(Predicate.column("c2").isGreaterThan(BindMarker.positional()))
        .and(Predicate.column("c2").isLessThanOrEqualTo(BindMarker.positional()))
        .orderBy(Ordering.column("c2").asc(), Ordering.column("c3").desc())
        .limit(BindMarker.positional())
        .build();

Examples of SELECT with JOIN are as follows:

-- For INNER JOIN and LEFT OUTER JOIN:
SELECT * FROM tbl1 as t1
  INNER JOIN tbl2 as t2 on t1.col1=t2.id1 and t1.col2=t2.id2 -- This part must have all primary key columns or a secondary index column of `tbl2`.
  WHERE t1.pkey=1 -- Only columns of `tbl1` can be specified here.
  ORDER BY t1.ckey DESC; -- Only columns of `tbl1` can be specified here.

SELECT * FROM tbl1 as t1
  INNER JOIN tbl2 as t2 on t1.col1=t2.id -- This part must have all primary key columns or a secondary index column of `tbl2`.
  LEFT OUTER JOIN tbl3 as t3 on t1.col2=t3.id -- This part must have all primary key columns or a secondary index column of `tbl3`.
  WHERE t1.pkey=1 -- Only columns of `tbl1` can be specified here.
  ORDER BY t1.ckey DESC; -- Only columns of `tbl1` can be specified here.

-- For RIGHT OUTER JOIN:
SELECT * FROM tbl1 as t1
  RIGHT OUTER JOIN tbl2 as t2 on t1.id=t2.col -- Acceptable as the first join. And this part must have all primary key columns or a secondary index column of `tbl1`. 
  LEFT OUTER JOIN tbl3 as t3 on t1.col2=t3.id
  WHERE t2.pkey=1 -- Only columns of `tbl2` can be specified here.
  ORDER BY t2.ckey DESC; -- Only columns of `tbl2` can be specified here.

SELECT * FROM tbl1 as t1
  RIGHT OUTER JOIN tbl2 as t2 on t1.id1=t2.col1 and t1.id2=t2.col2 -- This part must have all primary key columns or a secondary index column of `tbl1`.
  WHERE t2.pkey=1 -- Only columns of `tbl2` can be specified here.
  ORDER BY t2.ckey DESC; -- Only columns of `tbl2` can be specified here.

Examples of building statement objects for SELECT with JOIN are as follows:

// For INNER JOIN and LEFT OUTER JOIN:
SelectStatement statement1 =
   StatementBuilder.select()
        .from("tbl1", "t1")
        .innerJoin("tbl2", "t2")
        .on(JoinPredicate.column("t1", "col1").isEqualTo("t2", "id1"))
        .and(JoinPredicate.column("t1", "col2").isEqualTo("t2", "id2")) // This part must have all primary key columns or a secondary index column of `tbl2`.
        .where(Predicate.column("t1", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl1` can be specified here.
        .orderBy(Ordering.column("t1", "ckey").desc()) // Only columns of `tbl1` can be specified here.
        .build();

SelectStatement statement2 =
    StatementBuilder.select()
        .from("tbl1", "t1")
        .innerJoin("tbl2", "t2")
        .on(JoinPredicate.column("t1", "col1").isEqualTo("t2", "id")) // This part must have all primary key columns or a secondary index column of `tbl2`.
        .leftOuterJoin("tbl3", "t3")
        .on(JoinPredicate.column("t1", "col2").isEqualTo("t3", "id")) // This part must have all primary key columns or a secondary index column of `tbl3`.
        .where(Predicate.column("t1", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl1` can be specified here.
        .orderBy(Ordering.column("t1", "ckey").desc()) // Only columns of `tbl1` can be specified here.
        .build();

// For RIGHT OUTER JOIN:
SelectStatement statement3 =
    StatementBuilder.select()
        .from("tbl1", "t1")
        .rightOuterJoin("tbl2", "t2")
        .on(JoinPredicate.column("t1", "id").isEqualTo("t2", "col")) // Acceptable as the first join. And this part must have all primary key columns or a secondary index column of `tbl1`.
        .leftOuterJoin("tbl3", "t3")
        .on(JoinPredicate.column("t1", "col2").isEqualTo("t3", "id"))
        .where(Predicate.column("t2", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl2` can be specified here.
        .orderBy(Ordering.column("t2", "ckey").desc()) // Only columns of `tbl2` can be specified here.
        .build();

SelectStatement statement4 =
    StatementBuilder.select()
        .from("tbl1", "t1")
        .rightOuterJoin("tbl2", "t2")
        .on(JoinPredicate.column("t1", "id1").isEqualTo("t2", "col1"))
        .and(JoinPredicate.column("t1", "id2").isEqualTo("t2", "col2")) // This part must have all primary key columns or a secondary index column of `tbl1`.
        .where(Predicate.column("t2", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl2` can be specified here.
        .orderBy(Ordering.column("t2", "ckey").desc()) // Only columns of `tbl2` can be specified here.
        .build();

SELECT (with cross-partition scan)​

By enabling the cross-partition scan option, the SELECT command can retrieve all records across partitions without specifying the WHERE clause. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

warning

For non-JDBC databases, we do not recommend enabling cross-partition scan with the SERIALIAZABLE isolation level because transactions could be executed at a lower isolation level (that is, SNAPSHOT). When using non-JDBC databases, use cross-partition scan at your own risk only if consistency does not matter for your transactions.

Grammar​

SELECT projection [, projection] ...
  FROM [<namespace name>.]<table name> [AS <alias>] [join_specification [join_specification] ...]
  [LIMIT <limit>]

projection: * | identifier [AS <alias>]
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
Note​
  • You cannot specify ORDER BY if you omit the WHERE clause.
  • For JDBC databases, by enabling cross-partition scan with filtering and ordering as described in SELECT (with cross-partition scan filtering and ordering), you can specify arbitrary conditions and orderings, including the case with ORDER BY without the WHERE clause.

For details about the join clause and related examples, see SELECT.

For details about scanning data from a database in ScalarDB, see Scan operation.

Examples​

If you have the following table, for example:

CREATE TABLE ns.tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);

Examples of SELECT with cross-partition scan are as follows:

-- Without the WHERE clause to retrieve all the records of a table
SELECT * FROM ns.tbl;

-- Without the WHERE clause and with projections and a limit
SELECT c1, c2, c3, c5 FROM ns.tbl LIMIT 10;

Examples of building statement objects for SELECT are as follows:

// Without the WHERE clause to retrieve all the records of a table
SelectStatement statement1 = StatementBuilder.select().from("ns", "tbl").build();

// Without the WHERE clause and with projections and a limit
SelectStatement statement2 =
    StatementBuilder.select("c1", "c2", "c3", "c5").from("ns", "tbl").limit(10).build();

SELECT (with cross-partition scan filtering and ordering)​

By enabling the cross-partition scan option with filtering and ordering, the SELECT command can flexibly retrieve records across partitions with arbitrary conditions and orderings. Currently, the options are valid only for JDBC databases. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

Grammar​

SELECT projection [, projection] ...
  FROM [<namespace name>.]<table name> [AS <alias>] [join_specification [join_specification] ...]
  [WHERE andPredicates [OR andPredicates ...] | orPredicates [AND orPredicates ...]]
  [ORDER BY <clustering key identifier> [order] [, <clustering key identifier> [order]] ...]
  [LIMIT <limit>]

projection: * | identifier [AS <alias>]
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
andPredicates: predicate | (predicate [AND predicate ...])
orPredicates: predicate | (predicate [OR predicate ...])
predicate: <identifier> operator <column value> | <identifier> BETWEEN <column value> AND <column value> | <identifier> [NOT] LIKE <pattern> [ESCAPE <escape character>] | <identifier> IS [NOT] NULL
operator: = | <> | != | > | >= | < | <=
order: ASC | DESC
Note​

WHERE clause:

  • You can use arbitrary predicates for any columns in the WHERE clause.
  • In the WHERE clause, predicates must be an OR-wise of andPredicates (known as disjunctive normal form) or an AND-wise of orPredicates (known as conjunctive normal form).
  • When connecting multiple andPredicates or orPredicates, which have more than one predicate, you need to put parentheses around andPredicates and orPredicates.
  • You can specify <column value> and <limit> to a bind marker (positional ? and named :<name>).

LIKE predicate:

  • _ in <pattern> matches any single character.
  • % in <pattern> matches any sequence of zero or more characters.
  • \ in <pattern> works as the escape character by default.
  • The escape character can be disabled by specifying an empty escape character.

ORDER BY clause:

  • You can specify order for any columns in the ORDER BY clause.
  • If you omit order, the default order ASC will be used.

For details about the join clause and related examples, see SELECT.

For details about scanning data from a database in ScalarDB, see Scan operation.

Examples​

If you have the following table, for example:

CREATE TABLE ns.user (
  id INT,
  name TEXT,
  age INT,
  height FLOAT,
  PRIMARY KEY (id)
)

Examples of SELECT with cross-partition scan filtering and ordering are as follows:

-- Without the WHERE clause to retrieve all the records of a table
SELECT * FROM ns.user;

-- With AND predicates for non-primary key columns
SELECT * FROM ns.user WHERE age > 10 AND height > 140;

-- With OR predicates for non-primary key columns
SELECT * FROM ns.user WHERE age > 10 OR height > 140;

-- With OR-wise of AND predicates
SELECT * FROM ns.user WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);

-- With AND-wise of OR predicates
SELECT * FROM ns.user WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);

-- With LIKE predicates
SELECT * FROM ns.user WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';

-- With LIKE predicates with an escape character
SELECT * FROM ns.user WHERE name LIKE '+%Alice' ESCAPE '+';

-- With IS NULL predicates
SELECT * FROM ns.user WHERE name IS NOT NULL AND age IS NULL;

-- With projections
SELECT name, age, height FROM ns.user WHERE (age < 10 OR age > 65) AND age <> 0;

-- With limit
SELECT name, age, height FROM ns.user WHERE age < 10 OR age > 65 LIMIT 10;

-- With orderings
SELECT * FROM ns.user WHERE age < 10 ORDER BY height DESC;

-- With orderings without the WHERE clause
SELECT * FROM ns.user ORDER BY height;

-- With positional bind markers
SELECT * FROM ns.user WHERE age < ? ORDER BY age ASC, height DESC LIMIT ?;

Examples of building statement objects for SELECT are as follows:

// Without the WHERE clause to retrieve all the records of a table
SelectStatement statement1 = StatementBuilder.select().from("ns", "user").build();

// With AND predicates for non-primary key columns
SelectStatement statement2 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR predicates for non-primary key columns
SelectStatement statement3 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR-wise of AND predicates
SelectStatement statement4 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
                .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
                .build())
        .or(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
                .and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
                .build())
        .build();

// With AND-wise of OR predicates
SelectStatement statement5 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(
            OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
                .or(Predicate.column("age").isGreaterThan(Value.of(65)))
                .build())
        .and(
            OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
                .or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
                .build())
        .build();

// With LIKE predicates
SelectStatement statement6 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("name").isLike(Value.of("A%")))
        .or(Predicate.column("name").isNotLike(Value.of("B_b")))
        .build();

// With LIKE predicates with an escape character
SelectStatement statement7 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
        .build();

// With IS NULL predicates
SelectStatement statement8 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("name").isNotNull())
        .and(Predicate.column("age").isNull())
        .build();

// With projections
SelectStatement statement9 =
    StatementBuilder.select("name", "age", "height")
        .from("ns", "user")
        .where(
            OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
                .or(Predicate.column("age").isGreaterThan(Value.of(65)))
                .build())
        .and(Predicate.column("height").isNotEqualTo(Value.of(0)))
        .build();

// With limit
SelectStatement statement10 =
    StatementBuilder.select("name", "age", "height")
        .from("ns", "user")
        .where(Predicate.column("age").isLessThan(Value.of(10)))
        .or(Predicate.column("age").isGreaterThan(Value.of(65)))
        .limit(10)
        .build();

// With orderings
SelectStatement statement11 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("age").isLessThan(Value.of(10)))
        .orderBy(Ordering.column("height").desc())
        .build();

// With orderings without the WHERE clause
SelectStatement statement12 =
    StatementBuilder.select()
        .from("ns", "user")
        .orderBy(Ordering.column("height").desc())
        .build();

// With positional bind markers
SelectStatement statement13 =
    StatementBuilder.select()
        .from("ns", "user")
        .where(Predicate.column("age").isLessThan(BindMarker.positional()))
        .orderBy(Ordering.column("age").asc(), Ordering.column("height").desc())
        .limit(BindMarker.positional())
        .build();

INSERT​

The INSERT command inserts new records into the database. If any of the target records already exist, a transaction conflict error will be thrown.

This command returns the following column:

  • updateCount: INT - the number of inserted records
note

If you read the target records by a SELECT command before an INSERT command in a transaction, and if the target records already exist, the INSERT command will update the target records instead of inserting new records.

Grammar​

INSERT INTO [<namespace name>.]<table name> [(<column name> [, <column name>] ...)]
  VALUES (<column value> [, <column value>] ...) [, (<column value> [, <column value>] ...)] ...

Note that you must specify a full primary key in INSERT. And you can specify <column value> to a bind marker (positional ? and named :<name>).

Examples​

Examples of INSERT are as follows:

-- Insert a record without specifying column names
INSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true);

-- Insert a record with column names
INSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100);

-- With positional bind markers
INSERT INTO tbl VALUES (?, ?, ?, ?, ?);

-- Insert multiple records
INSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100), (20, 'bbb', 4.56, 200);

Examples of building statement objects for INSERT are as follows:

// Insert a record without specifying column names.
InsertStatement statement1 = StatementBuilder.insertInto("ns", "tbl")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .build();

// Insert a record with column names.
InsertStatement statement2 = StatementBuilder.insertInto("ns", "tbl")
    .columns("c1", "c2", "c3", "c4", "c5")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .build();

// With positional bind markers
InsertStatement statement3 =
    StatementBuilder.insertInto("tbl")
        .columns("c1", "c2", "c3", "c4", "c5")
        .values(
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional())
        .build();

// Insert multiple records.
InsertStatement statement4 = StatementBuilder.insertInto("ns", "tbl")
    .columns("c1", "c2", "c3", "c4", "c5")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .values(Value.ofInt(20), Value.ofText("bbb"), Value.of(2.46F), Value.of(200L), Value.of(false))
    .build();

UPSERT​

The UPSERT command inserts new records into the database if they don't exist or updates the target records if they already exist.

This command returns the following column:

  • updateCount: INT - the number of inserted or updated records

Grammar​

UPSERT INTO [<namespace name>.]<table name> [(<column name> [, <column name>] ...)]
  VALUES (<column value> [, <column value>] ...) [, (<column value> [, <column value>] ...)] ...
note

You must specify a full primary key in UPSERT. In addition, you can specify <column value> to a bind marker (positional ? and named :<name>).

Examples​

Examples of UPSERT are as follows:

-- Upsert a record without specifying column names.
UPSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true);

-- Upsert a record with column names.
UPSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100);

-- With positional bind markers
UPSERT INTO tbl VALUES (?, ?, ?, ?, ?);

-- Upsert multiple records.
UPSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100), (20, 'bbb', 4.56, 200);

Examples of building statement objects for UPSERT are as follows:

// Upsert a record without specifying column names.
UpsertStatement statement1 = StatementBuilder.upsertInto("ns", "tbl")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .build();

// Upsert a record with column names.
UpsertStatement statement2 = StatementBuilder.upsertInto("ns", "tbl")
    .columns("c1", "c2", "c3", "c4", "c5")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .build();

// With positional bind markers
UpsertStatement statement3 =
    StatementBuilder.upsertInto("tbl")
        .columns("c1", "c2", "c3", "c4", "c5")
        .values(
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional(),
            BindMarker.positional())
        .build();

// Upsert multiple records.
UpsertStatement statement4 = StatementBuilder.upsertInto("ns", "tbl")
    .columns("c1", "c2", "c3", "c4", "c5")
    .values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
    .values(Value.ofInt(20), Value.ofText("bbb"), Value.of(2.46F), Value.of(200L), Value.of(false))
    .build();

UPDATE​

The UPDATE command updates existing records in the database. By default, the UPDATE command requires the WHERE clause to filter records by primary key. If you want to update all records without specifying the WHERE clause, see UPDATE (with cross-partition scan). For JDBC databases only, you can use the UPDATE command with arbitrary conditions. For details, see UPDATE (with cross-partition scan filtering).

This command returns the following column:

  • updateCount: INT - the number of updated records

Grammar​

UPDATE [<namespace name>.]<table name> [AS <alias>]
  SET <column identifier> = <column value> [, <column identifier> = <column value>] ...
  WHERE <primary key identifier> = <column value> [AND <primary key identifier> = <column value>] ...]

identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>

Note that you must specify a full primary key in UPDATE. And you can specify <column value> to a bind marker (positional ? and named :<name>).

Examples​

Examples of UPDATE are as follows:

-- Update a record
UPDATE ns.tbl SET c4 = 200, c5 = false WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;

-- With positional bind markers
UPDATE ns.tbl SET c4 = ?, c5 = ? WHERE c1 = ? AND c2 = ? AND c3 = ?;

Examples of building statement objects for UPDATE are as follows:

// Update a record
UpdateStatement statement1 =
    StatementBuilder.update("ns", "tbl")
        .set(
            Assignment.column("c4").value(Value.of(200L)),
            Assignment.column("c5").value(Value.of(false)))
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
        .and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
        .build();

// With positional bind markers
UpdateStatement statement2 =
    StatementBuilder.update("tbl")
        .set(
            Assignment.column("c4").value(BindMarker.positional()),
            Assignment.column("c5").value(BindMarker.positional()))
        .where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
        .and(Predicate.column("c2").isEqualTo(BindMarker.positional()))
        .and(Predicate.column("c3").isEqualTo(BindMarker.positional()))
        .build();

UPDATE (with cross-partition scan)​

By enabling the cross-partition scan option, the UPDATE command can update all records across partitions without specifying the WHERE clause. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

warning

For non-JDBC databases, we do not recommend enabling cross-partition scan with the SERIALIAZABLE isolation level because transactions could be executed at a lower isolation level (that is, SNAPSHOT). When using non-JDBC databases, use cross-partition scan at your own risk only if consistency does not matter for your transactions.

Grammar​

UPDATE [<namespace name>.]<table name> [AS <alias>]
  SET <column identifier> = <column value> [, <column identifier> = <column value>] ...

identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
Note​

Examples​

If you have the following table, for example:

CREATE TABLE ns.tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);

Examples of UPDATE with cross-partition scan are as follows:

-- Without the WHERE clause to update all the records of a table
UPDATE ns.tbl SET c4 = 200, c5 = false;

Examples of building statement objects for UPDATE are as follows:

// Without the WHERE clause to update all the records of a table
UpdateStatement statement =
    StatementBuilder.update("ns", "tbl")
        .set(
            Assignment.column("c4").value(Value.of(200L)),
            Assignment.column("c5").value(Value.of(false)))
        .build();

UPDATE (with cross-partition scan filtering)​

By enabling the cross-partition scan option with filtering, the UPDATE command can flexibly update records across partitions with arbitrary conditions. Currently, the option is valid only for JDBC databases. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

Grammar​

UPDATE [<namespace name>.]<table name> [AS <alias>]
  SET <column identifier> = <column value> [, <column identifier> = <column value>] ...
  [WHERE andPredicates [OR andPredicates ...] | orPredicates [AND orPredicates ...]]

identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
andPredicates: predicate | (predicate [AND predicate ...])
orPredicates: predicate | (predicate [OR predicate ...])
predicate: <identifier> operator <column value> | <identifier> BETWEEN <column value> AND <column value> | <identifier> [NOT] LIKE <pattern> [ESCAPE <escape character>] | <identifier> IS [NOT] NULL
operator: = | <> | != | > | >= | < | <=
Note​

WHERE clause:

  • You can use arbitrary predicates for any columns in the WHERE clause.
  • In the WHERE clause, predicates must be an OR-wise of andPredicates (known as disjunctive normal form) or an AND-wise of orPredicates (known as conjunctive normal form).
  • When connecting multiple andPredicates or orPredicates, which have more than one predicate, you need to put parentheses around andPredicates and orPredicates.
  • You can specify <column value> to a bind marker (positional ? and named :<name>).

LIKE predicate:

  • _ in <pattern> matches any single character.
  • % in <pattern> matches any sequence of zero or more characters.
  • \ in <pattern> works as the escape character by default.
  • The escape character can be disabled by specifying an empty escape character.

Examples​

If you have the following table, for example:

CREATE TABLE ns.user (
  id INT,
  name TEXT,
  age INT,
  height FLOAT,
  PRIMARY KEY (id)
)

Examples of UPDATE with cross-partition scan filtering are as follows:

-- Without the WHERE clause to update all the records of a table
UPDATE ns.user SET age = 31;

-- With AND predicates for non-primary key columns
UPDATE ns.user SET age = 31 WHERE age > 10 AND height > 140;

-- With OR predicates for non-primary key columns
UPDATE ns.user SET age = 31 WHERE age > 10 OR height > 140;

-- With OR-wise of AND predicates
UPDATE ns.user SET age = 31 WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);

-- With AND-wise of OR predicates
UPDATE ns.user SET age = 31 WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);

-- With LIKE predicates
UPDATE ns.user SET age = 31 WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';

-- With LIKE predicates with an escape character
UPDATE ns.user SET age = 31 WHERE name LIKE '+%Alice' ESCAPE '+';

-- With IS NULL predicates
UPDATE ns.user SET age = 31 WHERE name IS NOT NULL AND age IS NULL;

-- With positional bind markers
UPDATE ns.user SET age = ? WHERE age < ?;

Examples of building statement objects for UPDATE are as follows:

// Without the WHERE clause to update all the records of a table
UpdateStatement statement1 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .build();

// With AND predicates for non-primary key columns
UpdateStatement statement2 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR predicates for non-primary key columns
UpdateStatement statement3 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR-wise of AND predicates
UpdateStatement statement4 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
                .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
                .build())
        .or(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
                .and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
                .build())
        .build();

// With AND-wise of OR predicates
UpdateStatement statement5 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(
            OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
                .or(Predicate.column("age").isGreaterThan(Value.of(65)))
                .build())
        .and(
            OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
                .or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
                .build())
        .build();

// With LIKE predicates
UpdateStatement statement6 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(Predicate.column("name").isLike(Value.of("A%")))
        .or(Predicate.column("name").isNotLike(Value.of("B_b")))
        .build();

// With LIKE predicates with an escape character
UpdateStatement statement7 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
        .build();

// With IS NULL predicates
UpdateStatement statement8 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(Value.of(30)))
        .where(Predicate.column("name").isNotNull())
        .and(Predicate.column("age").isNull())
        .build();

// With positional bind markers
UpdateStatement statement9 =
    StatementBuilder.update("ns", "user")
        .set(Assignment.column("age").value(BindMarker.positional()))
        .where(Predicate.column("age").isLessThan(BindMarker.positional()))
        .build();

DELETE​

The DELETE command deletes records in the database. By default, the DELETE command requires the WHERE clause to filter records by primary key. If you want to delete all records without specifying the WHERE clause, see DELETE (with cross-partition scan). For JDBC databases only, you can use the DELETE command with arbitrary conditions. For details, see DELETE (with cross-partition scan filtering).

This command returns the following column:

  • updateCount: INT - the number of deleted records

Grammar​

DELETE FROM [<namespace name>.]<table name> [AS <alias>]
  WHERE <primary key identifier> = <column value> [AND <primary key identifier> = <column value>] ...]

identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>

Note that you must specify a full primary key in DELETE. And you can specify <column value> to a bind marker (positional ? and named :<name>).

Examples​

Examples of DELETE are as follows:

-- Delete a record
DELETE FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;

-- With positional bind markers
DELETE FROM tbl WHERE c1 = ? AND c2 = ? AND c3 = ?;

Examples of building statement objects for DELETE are as follows:

// Delete a record
DeleteStatement statement1 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("c1").isEqualTo(Value.of(10)))
        .and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
        .and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
        .build();

// With positional bind markers
DeleteStatement statement2 =
    StatementBuilder.deleteFrom("tbl")
        .where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
        .and(Predicate.column("c2").isEqualTo(BindMarker.positional()))
        .and(Predicate.column("c3").isEqualTo(BindMarker.positional()))
        .build();

DELETE (with cross-partition scan)​

By enabling the cross-partition scan option, the DELETE command can delete all records across partitions without specifying the WHERE clause. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

warning

For non-JDBC databases, we do not recommend enabling cross-partition scan with the SERIALIAZABLE isolation level because transactions could be executed at a lower isolation level (that is, SNAPSHOT). When using non-JDBC databases, use cross-partition scan at your own risk only if consistency does not matter for your transactions.

Grammar​

DELETE FROM [<namespace name>.]<table name> [AS <alias>]
Note​

Examples​

If you have the following table, for example:

CREATE TABLE ns.tbl (
  c1 INT,
  c2 TEXT,
  c3 FLOAT,
  c4 BIGINT,
  c5 BOOLEAN,
  PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);

Examples of DELETE with cross-partition scan are as follows:

-- Without the WHERE clause to delete all the records of a table
DELETE FROM ns.tbl;

Examples of building statement objects for DELETE are as follows:

// Without the WHERE clause to delete all the records of a table
DeleteStatement statement = StatementBuilder.deleteFrom("ns", "tbl").build();

DELETE (with cross-partition scan filtering)​

By enabling the cross-partition scan option with filtering, the DELETE command can flexibly delete records across partitions with arbitrary conditions. Currently, the option is valid only for JDBC databases. For details about configurations, see Cross-partition scan configurations and ScalarDB SQL Configurations.

Grammar​

DELETE FROM [<namespace name>.]<table name> [AS <alias>]
  [WHERE andPredicates [OR andPredicates ...] | orPredicates [AND orPredicates ...]]

identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
andPredicates: predicate | (predicate [AND predicate ...])
orPredicates: predicate | (predicate [OR predicate ...])
predicate: <identifier> operator <column value> | <identifier> BETWEEN <column value> AND <column value> | <identifier> [NOT] LIKE <pattern> [ESCAPE <escape character>] | <identifier> IS [NOT] NULL
operator: = | <> | != | > | >= | < | <=
Note​

WHERE clause:

  • You can use arbitrary predicates for any columns in the WHERE clause.
  • In the WHERE clause, predicates must be an OR-wise of andPredicates (known as disjunctive normal form) or an AND-wise of orPredicates (known as conjunctive normal form).
  • When connecting multiple andPredicates or orPredicates, which have more than one predicate, you need to put parentheses around andPredicates and orPredicates.
  • You can specify <column value> to a bind marker (positional ? and named :<name>).

LIKE predicate:

  • _ in <pattern> matches any single character.
  • % in <pattern> matches any sequence of zero or more characters.
  • \ in <pattern> works as the escape character by default.
  • The escape character can be disabled by specifying an empty escape character.

Examples​

If you have the following table, for example:

CREATE TABLE ns.user (
  id INT,
  name TEXT,
  age INT,
  height FLOAT,
  PRIMARY KEY (id)
)

Examples of DELETE with cross-partition scan filtering are as follows:

-- Without the WHERE clause to delete all the records of a table
DELETE FROM ns.user;

-- With AND predicates for non-primary key columns
DELETE FROM ns.user WHERE age > 10 AND height > 140;

-- With OR predicates for non-primary key columns
DELETE FROM ns.user WHERE age > 10 OR height > 140;

-- With OR-wise of AND predicates
DELETE FROM ns.user WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);

-- With AND-wise of OR predicates
DELETE FROM ns.user WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);

-- With LIKE predicates
DELETE FROM ns.user WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';

-- With LIKE predicates with an escape character
DELETE FROM ns.user WHERE name LIKE '+%Alice' ESCAPE '+';

-- With IS NULL predicates
DELETE FROM ns.user WHERE name IS NOT NULL AND age IS NULL;

-- With positional bind markers
DELETE FROM ns.user WHERE age < ?;

Examples of building statement objects for DELETE are as follows:

// Without the WHERE clause to delete all the records of a table
DeleteStatement statement1 = StatementBuilder.deleteFrom("ns", "tbl").build();

// With AND predicates for non-primary key columns
DeleteStatement statement2 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR predicates for non-primary key columns
DeleteStatement statement3 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("age").isGreaterThan(Value.of(10)))
        .or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
        .build();

// With OR-wise of AND predicates
DeleteStatement statement4 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
                .and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
                .build())
        .or(
            AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
                .and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
                .build())
        .build();

// With AND-wise of OR predicates
DeleteStatement statement5 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(
            OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
                .or(Predicate.column("age").isGreaterThan(Value.of(65)))
                .build())
        .and(
            OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
                .or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
                .build())
        .build();

// With LIKE predicates
DeleteStatement statement6 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("name").isLike(Value.of("A%")))
        .or(Predicate.column("name").isNotLike(Value.of("B_b")))
        .build();

// With LIKE predicates with an escape character
DeleteStatement statement7 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
        .build();

// With IS NULL predicates
DeleteStatement statement8 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("name").isNotNull())
        .and(Predicate.column("age").isNull())
        .build();

// With positional bind markers
DeleteStatement statement9 =
    StatementBuilder.deleteFrom("ns", "tbl")
        .where(Predicate.column("age").isLessThan(BindMarker.positional()))
        .build();

DCL​

CREATE USER​

The CREATE USER command creates a new user with the specified username, password, and user attributes.

Grammar​

CREATE USER <username> [WITH] {PASSWORD <password> | SUPERUSER | NO_SUPERUSER} ...

If you don't specify a password for a user, the user is created without password.

If you specify the SUPERUSER attribute for a user, the user becomes a superuser. If you specify the NO_SUPERUSER attribute for a user, the user becomes a normal user. If you don't specify either SUPERUSER or NO_SUPERUSER for a user, the user becomes a normal user.

Examples​

Examples of CREATE USER are as follows:

-- Create a user with a password as a superuser.
CREATE USER user1 WITH PASSWORD 'password1' SUPERUSER;

-- Create a user with a password.
CREATE USER user2 WITH PASSWORD 'password2';

-- Create a user without a password.
CREATE USER user3;

Examples of building statement objects for CREATE USER are as follows:

// Create a user with a password and the `SUPERUSER` attribute.
CreateUserStatement statement1 =
    StatementBuilder.createUser("user1").with("password", UserOption.SUPERUSER).build();

// Create a user with a password.
CreateUserStatement statement2 = StatementBuilder.createUser("user1").with("password").build();

// Create a user without a password.
CreateUserStatement statement3 = StatementBuilder.createUser("user1").build();

ALTER USER​

The ALTER USER command changes the password and user attributes of the specified user.

Grammar​

ALTER USER <username> [WITH] {PASSWORD <password> | SUPERUSER | NO_SUPERUSER} ...

If you specify the SUPERUSER attribute for a user, the user becomes a superuser. If you specify the NO_SUPERUSER attribute for a user, the user becomes a normal user.

Examples​

Examples of ALTER USER are as follows:

-- Change the password of a user.
ALTER USER user1 WITH PASSWORD 'password1';

-- Change a user to a superuser.
ALTER USER user1 WITH SUPERUSER;

Examples of building statement objects for ALTER USER are as follows:

// Change the password of a user.
AlterUserStatement statement1 = StatementBuilder.alterUser("user1").with("password2").build();

// Change a user to a superuser.
AlterUserStatement statement2 =
        StatementBuilder.alterUser("user1").with(UserOption.SUPERUSER).build();

DROP USER​

The DROP USER command deletes the specified user.

Grammar​

DROP USER <username>

Examples​

An example of DROP USER is as follows:

-- Delete a user.
DROP USER user1;

An example of building statement objects for DROP USER is as follows:

// Delete a user.
DropUserStatement statement = StatementBuilder.dropUser("user1").build();

GRANT​

The GRANT command grants privileges to the specified user.

Grammar​

GRANT {privilege [, privilege] ... | ALL [PRIVILEGES]} ON [TABLE] <table name> [, <table name>] ... TO <username> [, <username>] ... [WITH GRANT OPTION]
GRANT {privilege [, privilege] ... | ALL [PRIVILEGES]} ON NAMESPACE <namespace name> [, <namespace name>] ... TO <username> [, <username>] ... [WITH GRANT OPTION]

privilege: SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | TRUNCATE | ALTER

If you specify the WITH GRANT OPTION option, the user can grant privileges to other users.

Examples​

Examples of GRANT are as follows:

-- Grant the SELECT privilege on a table to a user.
GRANT SELECT ON ns.tbl TO user1;

-- Grant the SELECT privilege on tables to users.
GRANT SELECT ON ns.tbl1, ns.tbl2 TO user1, user2;

-- Grant the SELECT privilege on all tables in a namespace to a user.
GRANT SELECT ON NAMESPACE ns TO user1;

-- Grant all privileges and GRANT OPTION on a table to a user.
GRANT ALL ON ns.tbl TO user1 WITH GRANT OPTION;

-- Grant all privileges and GRANT OPTION on all tables in a namespace to a user.
GRANT ALL ON NAMESPACE ns TO user1 WITH GRANT OPTION;

Examples of building statement objects for GRANT are as follows:

// Grant the SELECT privilege on a table to a user.
GrantStatement statement1 =
    StatementBuilder.grant(Privilege.SELECT).on("ns", "tbl").to("user1").build();

// Grant the SELECT privilege on tables to users.
GrantStatement statement2 =
    StatementBuilder.grant(Privilege.SELECT)
        .on("ns", "tbl1", "ns", "tbl2")
        .to("user1", "user2")
        .build();

// Grant the SELECT privilege on all tables in a namespace to a user.
GrantStatement statement3 =
    StatementBuilder.grant(Privilege.SELECT).onNamespace("ns").to("user1").build();

// Grant all privileges and GRANT OPTION on a table to a user.
GrantStatement statement4 =
    StatementBuilder.grant(Privilege.values())
        .on("ns", "tbl")
        .to("user1")
        .withGrantOption()
        .build();

// Grant all privileges and GRANT OPTION on all tables in a namespace to a user.
GrantStatement statement5 =
    StatementBuilder.grant(Privilege.values())
        .onNamespace("ns")
        .to("user1")
        .withGrantOption()
        .build();

REVOKE​

The REVOKE command revokes privileges from the specified user.

Grammar​

REVOKE {privilege [, privilege] ... | ALL [PRIVILEGES]} [, GRANT OPTION] ON [TABLE] <table name> [, <table name>] ... FROM <username> [, <username>] ...
REVOKE GRANT OPTION ON [TABLE] <table name> [, <table name>] ... FROM <username> [, <username>] ...
REVOKE {privilege [, privilege] ... | ALL [PRIVILEGES]} [, GRANT OPTION] ON NAMESPACE <namespace name> [, <namespace name>] ... FROM <username> [, <username>] ...
REVOKE GRANT OPTION ON NAMESPACE <namespace name> [, <namespace name>] ... FROM <username> [, <username>] ...

privilege: SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | TRUNCATE | ALTER

Examples​

Examples of REVOKE are as follows:

-- Revoke the SELECT privilege on a table from a user.
REVOKE SELECT ON ns.tbl FROM user1;

-- Revoke the SELECT privilege on tables from users.
REVOKE SELECT ON ns.tbl1, ns.tbl2 FROM user1, user2;

-- Revoke the SELECT privilege on all tables in a namespace from a user.
REVOKE SELECT ON NAMESPACE ns FROM user1;

-- Revoke all privileges and GRANT OPTION on a table from a user.
REVOKE ALL, GRANT OPTION ON ns.tbl FROM user1;

-- Revoke all privileges and GRANT OPTION on all tables in a namespace from a user.
REVOKE ALL, GRANT OPTION ON NAMESPACE ns FROM user1;

Examples of building statement objects for REVOKE are as follows:

// Revoke the SELECT privilege on a table from a user.
RevokeStatement statement1 =
    StatementBuilder.revoke(Privilege.SELECT).on("ns", "tbl").from("user1").build();

// Revoke the SELECT privilege on tables from users.
RevokeStatement statement2 =
    StatementBuilder.revoke(Privilege.SELECT)
        .on("ns", "tbl1", "ns", "tbl2")
        .from("user1", "user2")
        .build();

// Revoke the SELECT privilege on all tables in a namespace from a user.
RevokeStatement statement3 =
    StatementBuilder.revoke(Privilege.SELECT).onNamespace("ns").from("user1").build();

// Revoke all privileges and GRANT OPTION on a table from a user.
RevokeStatement statement4 =
    StatementBuilder.revoke(Privilege.values())
        .on("ns", "tbl")
        .from("user1")
        .build();

// Revoke all privileges and GRANT OPTION on all tables in a namespace from a user.
RevokeStatement statement5 =
    StatementBuilder.revoke(Privilege.values())
        .onNamespace("ns")
        .from("user1")
        .build();

Others​

USE​

The USE command specifies a default namespace. If a namespace name is omitted in a SQL, the default namespace is used.

Grammar​

USE <namespace name>

Examples​

An example of USE is as follows:

-- Specify a default namespace name "ns"
USE ns;

An example of building statement objects for USE is as follows:

// Specify a default namespace name "ns"
UseStatement statement = StatementBuilder.use("ns").build();

BEGIN​

The BEGIN command begins a transaction.

This command returns the following column:

  • transactionId: TEXT - a transaction ID associated with the transaction you have begun

Grammar​

BEGIN

Examples​

An example of building statement objects for BEGIN is as follows:

// Begin a transaction
BeginStatement statement = StatementBuilder.begin().build();

START TRANSACTION​

The START TRANSACTION command starts a transaction. This command is an alias of BEGIN.

This command returns the following column:

  • transactionId: TEXT - the transaction ID associated with the transaction you have started

Grammar​

START TRANSACTION

Examples​

An example of building statement objects for START TRANSACTION is as follows:

// Start a transaction.
StartTransactionStatement statement = StatementBuilder.startTransaction().build();

JOIN​

The JOIN command joins a transaction associated with the specified transaction ID.

Grammar​

JOIN <transaction ID>

Examples​

An example of JOIN is as follows:

-- Join a transaction
JOIN 'id';

An example of building statement objects for JOIN is as follows:

// Join a transaction
JoinStatement statement = StatementBuilder.join("id").build();

PREPARE​

The PREPARE command prepares the current transaction.

Grammar​

PREPARE

Examples​

An example of building statement objects for PREPARE is as follows:

// Prepare the current transaction
PrepareStatement statement = StatementBuilder.prepare().build();

VALIDATE​

The VALIDATE command validates the current transaction.

Grammar​

VALIDATE

Examples​

An example of building statement objects for VALIDATE is as follows:

// Validate the current transaction
ValidateStatement statement = StatementBuilder.validate().build();

COMMIT​

The COMMIT command commits the current transaction.

Grammar​

COMMIT

Examples​

An example of building statement objects for COMMIT is as follows:

// Commit the current transaction
CommitStatement statement = StatementBuilder.commit().build();

ROLLBACK​

The ROLLBACK command rolls back the current transaction.

Grammar​

ROLLBACK

Examples​

An example of building statement objects for ROLLBACK is as follows:

// Rollback the current transaction
RollbackStatement statement = StatementBuilder.rollback().build();

ABORT​

The ABORT command rolls back the current transaction. This command is an alias of ROLLBACK.

Grammar​

ABORT

Examples​

An example of building statement objects for ABORT is as follows:

// Abort the current transaction.
AbortStatement statement = StatementBuilder.abort().build();

SET MODE​

The SET MODE command switches the current transaction mode.

Grammar​

SET MODE transaction_mode

transaction_mode: TRANSACTION | TWO_PHASE_COMMIT_TRANSACTION

Examples​

An example of SET MODE is as follows:

-- Switch the current transaction mode to Two-phase Commit Transaction
SET MODE TWO_PHASE_COMMIT_TRANSACTION;

An example of building statement objects for SET MODE is as follows:

// Switch the current transaction mode to Two-phase Commit Transaction
SetModeStatement statement =
    StatementBuilder.setMode(TransactionMode.TWO_PHASE_COMMIT_TRANSACTION).build();

SHOW TABLES​

The SHOW TABLES command shows table names in a namespace. If a namespace name is omitted, the default namespace is used.

This command returns the following column:

  • tableName: TEXT - a table name

Grammar​

SHOW TABLES [FROM <namespace name>]

Examples​

Examples of SHOW TABLES is as follows:

-- Show table names in the default namespace
SHOW TABLES;

-- Show table names in a namespace "ns"
SHOW TABLES FROM ns;

Examples of building statement objects for SHOW TABLES is as follows:

// Show table names in the default namespace
ShowTablesStatement statement1 = StatementBuilder.showTables().build();

// Show table names in a namespace "ns"
ShowTablesStatement statement2 = StatementBuilder.showTables().from("ns").build();

DESCRIBE​

The DESCRIBE command returns column metadata for the specified table.

This command returns the following columns:

  • columnName: TEXT - a table name
  • type: TEXT - a type name
  • isPrimaryKey: BOOLEAN - whether it's a column part of primary key
  • isPartitionKey: BOOLEAN - whether it's a column part of partition key
  • isClusteringKey: BOOLEAN - whether it's a column part of clustering key
  • clusteringOrder: TEXT - a clustering order
  • isIndexed: BOOLEAN - whether it's an indexed column

Grammar​

DESCRIBE [<namespace name>.]<table name>

DESC [<namespace name>.]<table name>

Examples​

Examples of DESCRIBE is as follows:

-- Returns column metadata for "ns.tbl"
DESCRIBE ns.tbl;

-- Returns column metadata for "tbl"
DESC tbl;

Examples of building statement objects for DESCRIBE is as follows:

// Returns column metadata for "ns.tbl"
DescribeStatement statement1 = StatementBuilder.describe("ns", "tbl").build();

// Returns column metadata for "tbl"
DescribeStatement statement2 = StatementBuilder.describe("tbl").build();

SUSPEND​

The SUSPEND command suspends the ongoing transaction in the current session.

Grammar​

SUSPEND

Examples​

Examples of building statement objects for SUSPEND is as follows:

// Suspend the ongonig transaction in the current session
SuspendStatement statement = StatementBuilder.suspend().build();

RESUME​

The RESUME command resumes the transaction associated with the specified transaction ID in the current session.

Grammar​

RESUME <transaction ID>

Examples​

An example of RESUME is as follows:

-- Resume a transaction
RESUME 'id';

An example of building statement objects for RESUME is as follows:

// Resume a transaction
ResumeStatement statement = StatementBuilder.resume("id").build();