# SQL commands supported by GridDB
Supported SQL commands are in the table as follows.
Command | Overview |
---|---|
CREATE DATABASE | Create a database. |
CREATE TABLE | Create a table. |
CREATE INDEX | Create an index. |
CREATE VIEW | Create a view. |
CREATE USER | Create a general user. |
DROP DATABASE | Delete a database. |
DROP TABLE | Delete a table. |
DROP INDEX | Delete an index. |
DROP VIEW | Delete a view. |
DROP USER | Delete a general user. |
ALTER TABLE | Change the structure of a table. |
GRANT | Assign database access rights to a general user. |
REVOKE | Revoke database access rights from a general user. |
SET PASSWORD | Change the password of a general user. |
SELECT | Select data. |
INSERT | Insert rows into a table. |
DELETE | Delete rows from a table. |
UPDATE | Update rows in a table. |
Comment | Add a comment. |
Hints | Control an execution plan. |
An explanation for each category of SQL command is given in this chapter.
# Data definition language (DDL)
# CREATE DATABASE
Create a database.
Syntax
CREATE DATABASE database_name; |
Specifications
- Can be executed by an administrator user only.
- Databases with the same name as "public", "information_schema" cannot be created as these are reserved for internal use in GridDB.
- Nothing will be changed if a database with the same name already exists.
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the rules of a database name.
# CREATE TABLE
# Creating a table
Create a table.
Syntax
- Table (collection)
CREATE TABLE [IF NOT EXISTS] table name (column definition [, column definition ...] [, PRIMARY KEY (column name [, ...])]) [WITH (property key = property value)]; |
- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column_name TIMESTAMP PRIMARY KEY [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value [, property_key=property_value ...])]; |
column definition
- column_name data_type [ column_constraint ]
column_constraint
- PRIMARY KEY (only the 1st column can be specified)
- NULL
- NOT NULL
Specifications
See "GridDB Features Reference (GridDB_FeaturesReference) for the rule of a table name and a column name.
If "IF NOT EXISTS" is specified, the specified table can be created only if another table with the same name does not exist.
The column name and data type name need to be specified in column definition. See Data types used in data storage for the data types that can be specified.
Composite primary key can be set to a table (collection) by setting the primary key after describing the column definition. The composite primary key must be set to the columns which are continuous from the first column and can be set up to 16 columns. It cannot be set together with the PRIMARY KEY as a column constraint, cannot be set to a time series table (time series container).
See "GridDB Features Reference" (GridDB_FeaturesReference) for details of time series table (Time series container).
Options related to data affinity can be specified in the format "WITH (property key = property value, ...)".
Function Item Property key Property value type Data affinity (Character string indicating similarity between containers) data_affinity STRING For time series tables, options about expiry release can be specified by the format " WITH (property_key=property_value, ...)".
Function | Item | Property key | Property value type | Required or optional when setting expiry release |
---|---|---|---|---|
Expiry release function | Type | expiration_type | STRING (Any of the followings. ROW: Row expiry release) | Required |
Elapsed time | expiration_time | INTEGER | Required | |
Elapsed time unit | expiration_time_unit | STRING (Any of the followings. DAY / HOUR / MINUTE / SECOND / MILLISECOND ) | Optional (default: DAY). | |
Division count | expiration_division_count | INTEGER | Optional (default: 8). |
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the details of each item.
Examples
Creating a table
CREATE TABLE myTable ( key INTEGER PRIMARY KEY, value1 DOUBLE NOT NULL, value2 DOUBLE NOT NULL );
# Creating a partitioned table
Creating a partitioned table
See "GridDB Features Reference" (GridDB_FeaturesReference) for details of each partitioning function.
(1) Creating a hash partitioned table
Syntax
- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY(column name [, ...])] ) [WITH (property_key=property_value)] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division count; |
- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...]) USING TIMESERIES [WITH property_key=property_value, ...)]] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division count; |
Specifications
- Create a hash partitioned table usng the column name of the partitioning key and the value of division count.
- Specify the value from 1 to 1024 for "division_count".
- The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference).
- The column specified as partitioning key cannot be updated.
- Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.
- For time series tables, options about expiry release can be specified by the format " WITH (property_key=property_value, ...)". The options that can be specified are same as normal table.
Examples
Creating a hash partitioned table
CREATE TABLE myHashPartition ( id INTEGER PRIMARY KEY, value STRING ) PARTITION BY HASH (id) PARTITIONS 128;
(2) Creating an interval partitioned table
Syntax
- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY(column name [, ...])]) [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]); |
- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] USING TIMESERIES)]) [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]); |
Specifications
Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for "column_name_of_partitioning_key".
The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference).
The column specified as partitioning key cannot be updated.
The following values can be specified as the "interval_value".
Partitioning key type Possible interval value BYTE from 1 to 27-1 SHORT from 1 to 215-1 INTEGER from 1 to 231-1 LONG from 1000 to 263-1 TIMESTAMP 1 or more If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.
The interval unit cannot be specified for any types other than TIMESTAMP.
Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.
The options about expiry release can be specified by the format "WITH (property_key=property_value, ...)". The options that can be specified are same as those of regular tables, except for the expiration_type which has the following specifications.
Function | Item | Property key | Property value type | Required or optional when setting expiry release |
---|---|---|---|---|
Expiry release function | Type | expiration_type | STRING (Any of the followings. If omitted, PARTITION. PARTITION: Partition expiry release ROW: Row expiry release) | Optional |
- The row expiry release can only be specified for timeseries table (timeseries container).
- The partition expiry release can only be specified for followings:
- Timeseries table (timeseries container)
- Table (collection) whose partitioning key is TIMESTAMP type.
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the details of each item.
Examples
Creating an interval partitioned table.
CREATE TABLE myIntervalPartition ( date TIMESTAMP PRIMARY KEY, value STRING ) PARTITION BY RANGE (date) EVERY (30, DAY);
Creating an interval partitioned table (timeseries table) using the partition expiry release function.
CREATE TABLE myIntervalPartition2 ( date TIMESTAMP PRIMARY KEY, value STRING ) USING TIMESERIES WITH ( expiration_type='PARTITION', expiration_time=90, expiration_time_unit='DAY' ) PARTITION BY RANGE (date) EVERY (30, DAY);
(3) Creating an interval hash partitioned table
Syntax
- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] [, PRIMARY KEY(column name [, ...])] ) [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count; |
- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] ) USING TIMESERIES [WITH (property_key=property_value, ...)] PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ]) SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count; |
Specifications
Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for "column_name_of_interval_partitioning_key".
The following values can be specified as the "interval_value".
Partitioning key type Possible interval value BYTE from 1 to 27-1 SHORT from 1 to 215-1 INTEGER from 1 to 231-1 LONG from 1000 * division_count to -263-1 TIMESTAMP 1 or more - If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.
- The interval unit cannot be specified for any types other than TIMESTAMP.
Specify the value from 1 to 1024 for "division_count".
The partitioning key requires the primary key. To set a key other than the primary key, the restriction in the configuration file need to be removed. For details, refer to the cluster definition file settings in GridDB Features Reference (GridDB_FeaturesReference).
The column specified as partitioning key cannot be updated.
Options related to data affinity can be specified in the format "WITH (property key = property value, ...)". The options that can be specified are same as normal table.
The options about expiry release can be specified by the format "WITH (property_key=property_value, ...)". The options that can be specified are same as those of regular tables, except for the expiration_type which has the following specifications.
Function | Item | Property key | Property value type | Required or optional when setting expiry release |
---|---|---|---|---|
Expiry release function | Type | expiration_type | STRING (Any of the followings. If omitted, PARTITION. PARTITION: Partition expiry release ROW: Row expiry release) | Optional |
Examples
Creating an interval-hash partitioned table
CREATE TABLE myIntervalHashPartition ( date TIMESTAMP, value STRING, PRIMARY KEY (date, value) ) PARTITION BY RANGE (date) EVERY (60, DAY) SUBPARTITION BY HASH (value) SUBPARTITIONS 64;
Creating an interval-hash partitioned table (timeseries table) using the partition expiry release function.
CREATE TABLE myIntervalHashPartition2 ( date TIMESTAMP PRIMARY KEY, value STRING ) USING TIMESERIES WITH ( expiration_type='PARTITION', expiration_time=90, expiration_time_unit='DAY' ) PARTITION BY RANGE (date) EVERY (60, DAY) SUBPARTITION BY HASH (date) SUBPARTITIONS 64;
# CREATE INDEX
Create an index.
Syntax
CREATE INDEX [IF NOT EXISTS] index_name ON table_name ( column_name_to_be_indexed ); |
Specifications
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the rules of an index name.
- For a table, an index with the same name as an existing index in the table cannot be created.
- If a transaction under execution exists in a table subject to processing, the system will wait for these to be completed before creating the data.
- An index cannot be created on a column of BLOB type and ARRAY type.
- Up to 16 columns can be indexed.
- Time series table does not allow a primary key in a composite index.
# CREATE VIEW
Create a view.
Syntax
CREATE [FORCE] VIEW view_name AS SELECT statement; |
Specifications
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the rules of a view name.
- Whether the result from the SELECT statement is available or not is checked. If the result is not available, a view cannot be created.
- When FORCE is specified, the result from the SELECT statement is not checked, while a syntactic check is done.
- The SELECT statement can include other view names. If other view names in the SELECT statement cause circular reference, the view cannot be created even if FORCE is specified.
# CREATE USER
Create a general user.
Syntax
CREATE USER user_name IDENTIFIED BY 'password_string' ; |
Specifications
- See "GridDB Features Reference" (GridDB_FeaturesReference) for the rules of a user name.
- Can be executed by an administrator user only.
- A user with the same name as an administrator user (admin and system) registered during installation cannot be created.
- Only ASCII characters can be used in the password string. The password is case-sensitive.
# DROP DATABASE
Delete a database.
Syntax
DROP DATABASE database_name; |
Specifications
- Can be executed by an administrator user only.
- The database with the following names are reserved for internal use and thus cannot be deleted: "public", "information_schema", and any names starting with "gs#".
- A database containing tables created by a user cannot be deleted.
# DROP TABLE
Delete a table.
Syntax
DROP TABLE [IF EXISTS] table_name; |
Specifications
- If "IF EXISTS" is specified, nothing will change if no table with the specified name exists.
- If there is an active transaction involving the table, the table will be deleted only after the transaction is completed.
# DROP INDEX
Delete the specified index.
Syntax
DROP INDEX [IF EXISTS] index_name ON table_name; |
Specifications
- If "IF EXISTS" is specified, nothing will change if no index with the specified name exists.
- If there is an active transaction involving the table, the table will be deleted only after the transaction is completed.
- The unnamed index creating through NoSQL I/F can not be deleted by "DROP INDEX".
# DROP VIEW
Delete a view.
Syntax
DROP VIEW [IF EXISTS] view name ; |
Specifications
- If "IF EXISTS" is specified, nothing will be changed if a view with the specified name does not exist.
# DROP USER
Delete a general user.
Syntax
DROP USER user_name; |
Specifications
- Can be executed by an administrator user only.
# ALTER TABLE
Change the structure of a table.
# Adding columns to a table
Add columns to the end of the table.
Syntax
ALTER TABLE table_name ADD [COLUMN] column definition [,ADD [COLUMN] column definition ...]; |
column definition
- column_name data_type [ column_constraint ]
column_constraint
- NULL
- NOT NULL
Specifications
- The added column is located in the end of the table. If multiple columns are specified, they are located in their order.
- PRIMARY KEY can not be specified to the column constraint.
- If the same name column exists, an error occurs.
Examples
Adding multiple columns to the table
ALTER TABLE myTable1 ADD COLUMN col111 STRING NOT NULL, ADD COLUMN col112 INTEGER;
# Deleting data partitions
Delete data partitions created by table partitioning.
Syntax
ALTER TABLE table_name DROP PARTITION FOR ( value_included_in_the_data_partition ); |
Specifications
- Data partitions can be deleted only for interval and interval-hash partitioning.
- Specify the value included in the data partition to be deleted.
- Data in the range of the once deleted data partition (from the lower limit value to the upper limit value of the data partition) cannot be registered.
- The lower limit value of a data partition can be checked by metatable. In many cases, the upper limit of a data partition is the lower limit value plus division width value.
- For interval-hash partitioned tables, there are multiple data partitions which have the same lower limit value, and the maximum number of those partitions is equal to the hash division count. Those data partitions are deleted simultaneously. Deleted partitions are checked by metatable.
See Metatables for the details on the metatable.
Examples
Interval partitioned table
Check the lower limit value of the interval partitioned table "myIntervalPartition1" (partitioning key type: TIMESTAMP, interval: 30 DAY)
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions" WHERE TABLE_NAME='myIntervalPartition1' ORDER BY PARTITION_BOUNDARY_VALUE; PARTITION_BOUNDARY_VALUE ----------------------------------- 2017-01-10T13:00:00.000Z 2017-02-09T13:00:00.000Z 2017-03-11T13:00:00.000Z :
Delete unnecessary data partitions
ALTER TABLE myIntervalPartition1 DROP PARTITION FOR ('2017-01-10T13:00:00Z');
Interval hash partitioned table
Check the lower limit value of each data partitions on the interval hash partitioned table "myIntervalHashPartition" (partitioning key type: TIMESTAMP, interval value: 90 DAY, division count 3)
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions" WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE; PARTITION_BOUNDARY_VALUE ----------------------------------- 2016-08-01T10:00:00.000Z The data of the same lower limit is hashed and 2016-08-01T10:00:00.000Z is divided into three data partitions. 2016-08-01T10:00:00.000Z 2016-10-30T10:00:00.000Z 2016-10-30T10:00:00.000Z 2016-10-30T10:00:00.000Z 2017-01-29T10:00:00.000Z :
Delete unnecessary data partitions
ALTER TABLE myIntervalHashPartition DROP PARTITION FOR ('2016-09-15T10:00:00Z');
Data partitions that have same boundary value will be deleted
SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions" WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE; PARTITION_BOUNDARY_VALUE ----------------------------------- 2016-10-30T10:00:00.000Z For the section (lower limit '2016-08-01T10: 00: 00Z') including '2016-09-15T10: 00: 00Z' 2016-10-30T10:00:00.000Z three data partitions are deleted. 2016-10-30T10:00:00.000Z 2017-01-29T10:00:00.000Z :
# Data control language (DCL)
# GRANT
Assign database access rights to a general user.
Syntax
GRANT {SELECT |
Specifications
- Can be executed by an administrator user only.
- SELECT indicates reference authority and ALL indicates reference authority and update authority.
# REVOKE
Revoke database access rights from a general user.
Syntax
REVOKE {SELECT |
Specifications
- Can be executed by an administrator user only.
- SELECT indicates reference authority and ALL indicates reference authority and update authority.
# SET PASSWORD
Change the password of a general user.
Syntax
SET PASSWORD [FOR user_name ] = 'password_string'; |
Specifications
- An administrator user can change the passwords of all general users.
- A general user can change its own password only.
# Data management language (DML)
# SELECT
Select data. Made up of a variety of Clauses such as FROM, WHERE, etc.
Syntax
SELECT [{ALL |
# INSERT
Register rows in a table. INSERT only registers rows, while INSERT OR REPLACE and REPLACE overwrite the existing data, when the data with the same primary key as that of the existing data is given. REPLACE is an alias of INSERT OR REPLACE and they are the same in their functions.
Syntax
{INSERT |
Specifications
- If a SELECT statement is specified instead of VALUES, the execution result will be registered.
INSERT INTO myTable1 VALUES(1, 100);
REPLACE INTO myTable1 VALUES(1, 200);
INSERT INTO myTable1 SELECT * FROM myTable2;
# DELETE
Delete rows from a table.
Syntax
DELETE FROM table_name [ WHERE clause ]; |
# UPDATE
Update the rows existing in a table.
Syntax
UPDATE table_name SET column_name_1 = expression_1 [, column_name_2 = expression_2 ...] [ WHERE clause ]; |
Specifications
The value of the PRIMARY KEY column can not be updated.
For a partitioned table, a column , set as a partitioning key, can not be updated to a different value using the UPDATE statement. In such a case, INSERT after DELETE.
- Example:
CREATE TABLE tab (a INTEGER, b STRING) PARTITION BY HASH a PARTITIONS 5; -- NG UPDATE tab SET a = a * 2; [240016:SQL_COMPILE_PARTITIONING_KEY_NOT_UPDATABLE] Partitioning column='a' is not updatable -- OK UPDATE tab SET b = 'XXX';
- Example:
A column name specified with SET cannot be qualified with a table name.
- Example:
CREATE TABLE myTable1 (key INTEGER, value INTEGER); -- NG UPDATE myTable1 SET myTable1.value = 999 WHERE myTable1.key = 8; -- OK UPDATE myTable1 SET value = 999 WHERE myTable1.key = 8;
- Example:
Subqueries cannot be used for update values, while it can be used for conditional statements such as WHERE.
- Example:
CREATE TABLE myTable1 (key INTEGER, value INTEGER); -- NG UPDATE myTable1 SET value = (SELECT 999) WHERE key = 8; -- OK UPDATE myTable1 SET value = 999 WHERE key = (SELECT 8);
- Example:
# Clauses
# FROM
Specify the table name, view name, and subquery on which to execute data operations.
Syntax
FROM table_name_1 [, table_name_2 ... ] |
FROM (sub_query) [AS] Alias [, ...] |
Specifications
- A sub query must be enclosed with () and requires an alias.
Example:
SELECT a.ID, b.ID FROM mytable a, (SELECT ID FROM mytable2) b;
ID ID
---+-----
1 100
1 200
2 100
2 200
:
# GROUP BY
Among the results of the clauses specified earlier, rows having the same value in the specified column will be grouped together.
Syntax
GROUP BY column_name_1 [, column_name_2 ...] |
# HAVING
Perform filtering using the search condition on data grouped by the GROUP BY clause. GROUP BY clause cannot be omitted.
Syntax
HAVING search_conditions |
# ORDER BY
Sort search results.
ORDER BY column_name_1 [{ASC |
# WHERE
Apply a search condition on the result of the preceding FROM clause.
Syntax
WHERE search_conditions |
Specifications
- Search conditions can be described using an expression, a function, a subquery, etc.
# LIMIT/OFFSET
Extract the specified number of data from the specified location.
Syntax
LIMIT value_1 [OFFSET value_2 ] |
Specifications
- Value_1 represents the number of data to extract while value_2 represents the position of the data to extract.
# JOIN
Join a table.
Syntax
Type of join | Syntax |
---|---|
Inner join | Table 1 [INNER] JOIN table 2 [ON condition |
Left outer join | Table 1 LEFT [OUTER] JOIN table 2 [ON type |
Cross join | Table 1 CROSS JOIN table 2 [ON condition |
- Inner join returns records that have matching values in both tables in the specified row.
- Left outer join returns records that have matching values in both tables in the specified row, as well as the records only exist in the table 1.
- A cross join is equivalent to an inner join (INNER JOIN).
Specify join conditions with ON or USING.
Example:
name: employees
id first_name department_id
----+------------+----------------
0 John 0
1 William 1
2 Richard 0
3 Mary 4
4 Lisa 3
5 James 1
name: departments
id department
----+------------
0 Sales
1 Development
2 Research
3 Marketing
○Inner join
SELECT * FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;
id first_name department_id department_id department
------+-----------+--------------+--------------+-----------
0 John 0 0 Sales
1 William 1 1 Development
2 Richard 0 0 Sales
4 Lisa 3 3 Marketing
5 James 1 1 Development
○Left outer join
SELECT * FROM employees e LEFT JOIN departments d ON e.department_id=d.department_id;
id first_name department_id department_id department
------+-----------+--------------+--------------+-----------
0 John 0 0 Sales
1 William 1 1 Development
2 Richard 0 0 Sales
3 Mary 4 (NULL) (NULL)
4 Lisa 3 3 Marketing
5 James 1 1 Development
Natural join (NATURAL JOIN) joins tables that have matching values in the rows under the same name.
Type of join | Syntax |
---|---|
Inner join | Table 1 NATURAL [INNER] JOIN table 2 |
Left outer join | Table 1 NATURAL LEFT [OUTER] JOIN table 2 |
Cross join | Table 1 NATURAL CROSS JOIN table 2 |
SELECT * FROM employees NATURAL INNER JOIN departments;
department_id id first_name department
---------------+-----+--------------+--------------
0 0 John Sales
1 1 William Development
0 2 Richard Sales
3 4 Lisa Marketing
1 5 James Development
# UNION/INTERSECT/EXCEPT
Calculate on a set of two query results.
Syntax
Inquiry 1 UNION inquiry 2 | Returns all the results of two queries. (duplication is not included) |
Query 1 UNION ALL query 2 | Returns all the results of two queries. (duplication is included) |
Query 1 INTERSECT query 2 | Returns the results common to the results of two queries. |
Query 1 EXCEPT query 2 | Returns the difference of two queries (result included in the query 1, not in the query 2). |
# OVER
Split and sort query results. Use with a WINDOW function.
Syntax
Function OVER ([PARTITION BY expression 1] [ORDER BY expression 2]) |
Specifications
- Can be used in the SELECT clause.
- The corresponding functions are:
- ROW_NUMBER()
- Split the query result with PARTITION BY clause. Sort rows by ORDER BY clause.
- Multiple use of the WINDOW function/OVER clause in the same SELECT clause, and simultaneous use of the WINDOW function/OVER clause and the MEDIAN function are not allowed.
- The following expressions cannot be specified in the PARTITION BY
clause.
- Expression containing OVER clause
- Expression containing aggregate function
- Expression containing column aliases
- Subquery
- The following expressions cannot be specified in the ORDER BY
clause.
- Expression containing OVER clause
- Expression containing aggregate function
- Expression containing column aliases
- Subquery
# Operator
This section explains the operators used in SQL statements.
# List of Operators
The list of operators is as follows.
Class | Operator | Description |
---|---|---|
Arithmetic | + | Add |
- | Substract | |
* | Multiply | |
/ | Divide | |
% | Modulo | |
Character | ||
Compare | =, == | Compare whether both sides are equal. |
!=, <> | Compare whether both sides are not equal. | |
> | Compare whether the left side is larger than the right side. | |
>= | Compare whether the left side is larger than or equal to the right one. | |
< | Compare whether the left side is smaller than the right side. | |
<= | Compare whether the left side is smaller than or equal to the right side. | |
IS | Compare whether both sides are equal. Return true, when both sides are NULLs. Return false, when either side is NULL. | |
IS NOT | Compare whether both sides are not equal. Return false, when both sides are NULLs. Return true, when either side is NULL. | |
ISNULL | Determine whether the left side is NULL. | |
NOTNULL | Determine whether the left side is not NULL. | |
LIKE | Search the character string on the right. | |
GLOB | Search the character string on the right. | |
BETWEEN | Extract values of the specified range. | |
IN | Return whether the specified value is included in the set of values. | |
Bit | & | A & B :Bitwise AND of A and B |
~ | ~A : Bitwise NOT of A | |
<< | A << B :Shift A to the left by B bit. | |
>> | A >> B :Shift A to the right by B bit. | |
Logic | AND | Return true, when both sides are true. Return false, when either side is false. Otherwise return NULL. |
OR | Return true, when the expression on either side is true. Return false, when the expressions on both sides are false. Otherwise return NULL. | |
NOT | Return false, when the expression on the right is true. Return true, when the expression on the right is false. |
# LIKE
Search the character string on the right.
# Syntax
str [NOT] LIKE pattern_str [ESCAPE escape_str ] |
# Specifications
- See LIKE function.
# GLOB
# Syntax
Search the character string on the right.
str GLOB pattern_str |
Specifications
- See GLOB function.
# BETWEEN
Extract values of the specified range.
Syntax
expression_1 [NOT] BETWEEN expression_2 AND expression_3 |
Specifications
Return true if the following conditions are met
expression_2 <= expression_1 <= expression_3
Return true if the following conditions are not met when NOT is specified.
# IN
Return whether the specified value is included in the set of values.
Syntax
expression_1 [NOT] IN ( expression_2 [, expression_3 ...] ) |
Specifications
- Return true when the value of expression_1 is included in the result of expression_N.
- IN can be used in a sub query.
# Functions
This section explains the functions used in SQL statements.
# List of Functions
The following functions are available for SQL statements.
Class | Function name | Description |
---|---|---|
Aggregation | AVG | Return the average value. |
COUNT | Return the number of rows. | |
MAX | Return the maximum. | |
MIN | Return the minimum. | |
SUM | Return a sum of values. | |
TOTAL | Return a sum of values. | |
GROUP_CONCAT | Connect values. | |
STDDEV_SAMP | Returns the sample standard deviation | |
STDDEV | Returns the sample standard deviation | |
STDDEV0 | Returns the sample standard deviation | |
STDDEV_POP | Returns the population standard deviation | |
VAR_SAMP | Returns the sample variance | |
VARIANCE | Returns the sample variance | |
VARIANCE0 | Returns the sample variance | |
VAR_POP | Returns the population variance | |
MEDIAN | Returns the median | |
Arithmetic | ABS | Return an absolute value. |
ROUND | Round off. | |
RANDOM | Return a random number. | |
MAX | Return the maximum. | |
MIN | Return the minimum. | |
LOG | Return the logarithm. | |
SQRT | Return the square root. | |
TRUNC | Round down numbers. | |
HEX_TO_DEC | Converts a hexadecimal string to a decimal number | |
Character | LENGTH | Return the length of a character string. |
LOWER | Convert a character string to a lowercase. | |
UPPER | Convert a character string to an uppercase. | |
SUBSTR | Cut out part of a character string. | |
REPLACE | Replace a character string. | |
INSTR | Return the position of a specified character string in a character string. | |
LIKE | Search a character string. | |
GLOB | Search a character string. | |
TRIM | Remove a specified character(s) from the both ends of a character string. | |
LTRIM | Remove a specified character(s) from the left end of a character string. | |
RTRIM | Remove a specified character(s) from the right end of a character string. | |
QUOTE | Enclose a character string with single quotes. | |
UNICODE | Return the Unicode code point of a character. | |
CHAR | A Unicode code point is converted to characters and connected. | |
PRINTF | Return the converted character string | |
TRANSLATE | Replace a character string. | |
Time | NOW | Return the present time. |
TIMESTAMP | Convert the string representation of time to TIMESTAMP type. | |
TIMESTAMP_ADD | Add a duration to a time. | |
TIMESTAMP_DIFF | Return the difference of times. | |
TO_TIMESTAMP_MS | Add lapsed time to the time point '1970-01-01T00:00:00.000Z'. | |
TO_EPOCH_MS | Return the lapsed time from the time point '1970-01-01T00:00:00.000Z'. | |
EXTRACT | Take out the value of the specific field from time. | |
STRFTIME | Return a character string with the time converted. | |
MAKE_TIMESTAMP | Generate time. | |
TIMESTAMP_TRUNC | Truncate time. | |
WINDOW | ROW_NUMBER | Assign a unique sequential value to the resulting Row |
Other | COALESCE | Return the first argument that is not NULL. |
IFNULL | Return the first argument that is not NULL. | |
NULLIF | Return NULL when two arguments are the same, return the first argument when the arguments are different. | |
RANDOMBLOB | Return a BLOB type value (random number). | |
ZEROBLOB | Return a BLOB type value (0x00). | |
HEX | Convert a BLOB type value to a hexadecimal type. | |
TYPEOF | Return the data type of a value. |
These functions are described using the data in the following table as an example.
table: employees
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
0 John Smith 43 Sales 15.5
1 William Jones 59 Development 23.2
2 Richard Brown (NULL) Sales 7.0
3 Mary Taylor 31 Research (NULL)
4 Lisa (NULL) 29 (NULL) 4.9
5 James Smith 43 Development 10.3
table: departments
id department
----+------------
0 Sales
1 Development
2 Research
[Notice]
- NULL value is expressed as (NULL).
# Aggregate functions
Functions to aggregate values DISTINCT or ALL can be specified as the argument of an aggregate function.
Format | function( [DISTINCT | ALL] argument) |
Point | Meaning |
---|---|
DISTINCT | Rows of duplicate values are excluded and aggregated. |
ALL | All the rows including the duplicate values are aggregated. |
When no argument is specified, the resut will be the same as ALL is specified.
[Notice]
- An aggregate function can be used only for a SELECT phrase.
- If there are no rows to be calculated, the result of COUNT is 0. Other aggregate functions result in NULL.
# AVG
Format | AVG( [DISTINCT | ALL] n) |
Return the average value of n.
- Specify a numeric value as the argument n.
- Rows with n of NULL value are excluded from the calculation.
- The result is of a DOUBLE type.
Example:
SELECT AVG(age) FROM employees;
Result: 41.0
SELECT AVG(DISTINCT age) FROM employees;
Result: 40.5
SELECT department, AVG(age) avg FROM employees GROUP BY department;
Result:
department avg
------------+-----
Development 51.0
Research 31.0
Sales 43.0
(NULL) 29.0
# COUNT
Format | COUNT( [DISTINCT | ALL] x) |
Return the number of rows.
- Rows with x of NULL value are excluded from the calculation. They are not included in the number of rows.
- The result is of a LONG type.
Example:
SELECT COUNT(*) FROM employees;
Result: 6
// Count the rows ignoring the ones with NULL value.
SELECT COUNT(department) FROM employees;
Result: 5
SELECT COUNT(DISTINCT department) FROM employees;
Result: 3
# MAX
Format | MAX( [DISTINCT | ALL] x) |
Return the maximum.
- Specify the value of arbitrary types as the argument x.
- For the argument of character string type, the character string started with the largest character code is returned.
- For the argument of TIMESTAMP type, return the newest time.
- Rows with x of NULL value are excluded from the calculation.
- The type of the result is the same as that of the argument x.
Example:
SELECT MAX(age) FROM employees;
Result: 59
SELECT MAX(first_name) FROM employees;
Result: William
# MIN
format | MIN( [DISTINCT | ALL] x) |
Return the minimum.
- Specify the value of arbitrary types as the argument x.
- For the argument of character string type, the character string started with the smallest character code is returned.
- For the argument of TIMESTAMP type, return the oldest time.
- Rows with x of NULL value are excluded from the calculation.
- The type of the result is the same as that of the argument x.
Example:
SELECT MIN(age) FROM employees;
Result: 29
SELECT MIN(first_name) FROM employees;
Result: James
# SUM/TOTAL
Format | SUM( [DISTINCT | ALL] n) |
Format | TOTAL( [DISTINCT | ALL] n) |
Return a sum of values.
Specify a numeric value as the argument n.
Rows with n of NULL value are excluded from the calculation.
The difference between SUM and TOTAL is as follows.
- When n includes integer type values only, SUM returns a value of integer (LONG) type, while TOTAL returns a value of floating point number (DOUBLE).
- When n includes a floating point number type value, both of them return a value of floating point number (DOUBLE).
- When n includes NULL only, SUM returns NULL, while TOTAL returns 0.
Example:
SELECT SUM(age) FROM employees;
Result: 205
SELECT TOTAL(age) FROM employees;
Result: 205.0
SELECT department, SUM(age) sum FROM employees GROUP BY department;
Result:
department sum
------------+-----
Development 102
Research 31
Sales 43
(NULL) 29
# GROUP_CONCAT
Format | GROUP_CONCAT( [DISTINCT | ALL] x [, separator] ) |
Return the character string in which the values of x are concatenated. Specify the separator to be concatenated as "separator". When not specified, ", " is used.
- Specify the value of arbitrary types as the argument x.
- A TIMESTAMP type argument is converted to the string representation of time 'YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)'(see TIMESTAMP function) and connected.
- Rows with x of NULL value are excluded from the calculation.
- The result is of a STRING type.
Example:
// Concatenate the name last_name with '/'
SELECT GROUP_CONCAT(last_name, '/') from employees;
Result: Smith/Jones/Brown/Taylor/Smith
// Concatenate the name "first_name" for each department "department"
SELECT department, GROUP_CONCAT(first_name) group_concat from employees GROUP BY(department);
Result:
department group_concat
-------------+--------------
Development William,James
Research Mary
Sales John,Richard
(NULL) Lisa
SELECT GROUP_CONCAT(age, ' + ') FROM employees;
Result: 43 + 59 + 31 + 29 + 43
# STDDEV_SAMP
Format | STDDEV_SAMP( [DISTINCT | ALL] x) |
Returns the sample standard deviation.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- If x is 1, returns NULL.
- The result is of a DOUBLE type.
Example:
SELECT department, STDDEV_SAMP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) (NULL)
# STDDEV/STDDEV0
Format | STDDEV( [DISTINCT | ALL] x) |
Format | STDDEV0( [DISTINCT | ALL] x) |
Returns the sample standard deviation. STDDEV is an alias of the STDDEV_SAMP function.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- The result is of a DOUBLE type.
- The differences between STDDEV and STDDEV0 are as follows:
- STDDEV returns NULL if x is 1,
- while STDDEV0 returns 0 when x is 1.
Example:
SELECT department, STDDEV(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) (NULL)
SELECT department, STDDEV0(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 9.121677477306465
Research (NULL)
Sales 6.010407640085654
(NULL) 0.0
SELECT STDDEV(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
enrollment_period_stddev
--------------------------
(NULL)
SELECT STDDEV0(enrollment_period) enrollment_period_stddev from employees WHERE age >= 55;
Result:
enrollment_period_stddev
--------------------------
0.0
# STDDEV_POP
Format | STDDEV_POP( [DISTINCT | ALL] x) |
Returns the population standard deviation.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- The result is of a DOUBLE type.
Example:
SELECT department, STDDEV_POP(enrollment_period) enrollment_period_stddev from employees GROUP BY department;
Result:
department enrollment_period_stddev
-------------+--------------------------
Development 6.450000000000002
Research (NULL)
Sales 4.25
(NULL) 0.0
# VAR_SAMP
Format | VAR_SAMP( [DISTINCT | ALL] x) |
Returns the sample variance.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- If x is 1, returns NULL.
- The result is of a DOUBLE type.
Example:
SELECT department, VAR_SAMP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) (NULL)
# VARIANCE/VARIANCE0
Format | VARIANCE([DISTINCT | ALL] x) |
Format | VARIANCE0([DISTINCT | ALL] x) |
Returns the sample variance. VARIANCE is an alias of the VAR_SAMP function.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- The result is of a DOUBLE type.
- The differences between VARIANCE and VARIANCE0 are as follows:
- VARIANCE returns NULL if x is 1,
- while VARIANCE0 returns 0 if x is 1.
Example:
SELECT department, VARIANCE(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) (NULL)
SELECT department, VARIANCE0(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 83.20500000000004
Research (NULL)
Sales 36.125
(NULL) 0.0
SELECT VARIANCE(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
enrollment_period_variance
----------------------------
(NULL)
SELECT VARIANCE0(enrollment_period) enrollment_period_variance from employees WHERE age >= 55;
Result:
enrollment_period_variance
----------------------------
0.0
# VAR_POP
Format | VAR_POP( [DISTINCT | ALL] x) |
Returns the population variance.
- Specify a numeric value for the argument x.
- Expressions cannot contain aggregate functions or WINDOW functions/OVER clauses.
- Rows with x of NULL value are excluded from the calculation.
- The result is of a DOUBLE type.
Example:
SELECT department, VAR_POP(enrollment_period) enrollment_period_variance from employees GROUP BY department;
Result:
department enrollment_period_variance
-------------+----------------------------
Development 41.60250000000002
Research (NULL)
Sales 18.0625
(NULL) 0.0
# MEDIAN
Format | MEDIAN(n) |
Returns the median of n. If the number of rows to be calculated is even, returns the average value of the two rows near the center.
- Specify a numeric value as the argument n.
- Subqueries cannot be specified.
- Rows with n of NULL value are excluded from the calculation.
- The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.
- Multiple use of the WINDOW function/OVER clause in the same SELECT clause, and simultaneous use of the WINDOW function/OVER clause and the MEDIAN function are not allowed.
Example:
SELECT MEDIAN(age) FROM employees;
Result: 43
SELECT department, MEDIAN(age) mn FROM employees GROUP BY department ORDER BY mn DESC;
Result:
department mn
------------+-----
Development 51
Sales 43
Research 31
(NULL) 29
# Mathematical functions
# ABS
Format | ABS(n) |
Return the absolute value of n. For a positive number, the value as it is is returned and for a negative number, the value multiplied by -1 is returned.
- Specify a numeric value as the argument n.
- Return NULL, when the result value is NULL.
- Cause an overflow error, when the value is an integer of -263.
- The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.
Example:
SELECT first_name, ABS(age) abs FROM employees;
Result:
first_name abs
------------+-------
John 43
William 59
Richard (NULL)
Mary 31
Lisa 29
James 43
# ROUND
Format | ROUND(n [, m]) |
Round off. Returns the value of n rounded to m decimal places.
- Specify a row of a numeric type as the argument n.
- Specify an integer greater than or equal to 0 as the argument m. When no value is specified for m, the default value 0 is specified.
- Return NULL, when the result value is NULL.
- The type of the result is a LONG type when n includes only integers, a DOUBLE type when n includes a floating point number.
Example:
SELECT first_name, ROUND(enrollment_period, 0) round FROM employees;
Result:
first_name round
------------+-------
John 16.0
William 23.0
Richard 7.0
Mary (NULL)
Lisa 5.0
James 10.0
# RANDOM
Format | RANDOM() |
Return a random number. A random number is an integer of the range from -263 to 263-1.
- The result is of a LONG type.
Example:
SELECT first_name, RANDOM() random FROM employees;
Result:
first_name random
------------+----------------------
John -3382931580741820003
William -7362300487836647182
Richard 8834368641333737477
Mary -8544493602797564288
Lisa -7727163797274657674
James 6751560427268247384
# MAX/MIN
Format | MAX(x1, x2 [,...]) |
Return the greatest value among the values xN.
Format | MIN(x1, x2 [,...]) |
Return the smallest value among the values xN.
Example:
SELECT first_name, age, enrollment_period, MAX(age, enrollment_period) max FROM employees;
Result:
first_name age enrollment_period max
------------+-------+------------------+--------
John 43 15.5 43.0
William 59 23.2 59.0
Richard (NULL) 7.0 (NULL)
Mary 31 (NULL) (NULL)
Lisa 29 4.9 29.0
James 43 10.3 43.0
# LOG
Format | LOG(n, m) |
Returns the logarithm of m with base n.
- For the argument n, specify a numeric value greater than 0 and other than 1.
- For the argument m, specify a numeric value greater than 0.
- Return NULL, when the result value is NULL.
- The result is of a DOUBLE type.
Example:
SELECT LOG(2, 8);
Result: 3.0
SELECT LOG(0.5, 2.0);
Result: -1.0
# SQRT
Format | SQRT(n) |
Returns the positive square root of n.
- Specify a numeric value of 0 or greater as the argument n.
- Return NULL, when the result value is NULL.
- The result is of a DOUBLE type.
Example:
SELECT SQRT(4);
Result:2.0
SELECT SQRT(16.0);
Result:4.0
# TRUNC
Format | TRUNC(n [,m]) |
In the case of m>=0, return the value of n, rounded down to the nearest m digits.
In the case of m< 0, return the value of n, rounded down to the nearest -m digits.
- Specify a numeric value as the argument n.
- Specify an integer as the argument m. When no value is specified for m, the default value 0 is specified. A value greater than 309 or less than -308 cannot be specified.
- Return NULL, when the result value is NULL.
- The result type is LONG if an integer is specified for the argument n and DOUBLE if a decimal is specified.
Example:
SELECT TRUNC(123.4567);
Result: 123.0
SELECT TRUNC(123.4567, 2);
Result: 123.45
SELECT TRUNC(123.4567, -1);
Result: 120.0
SELECT TRUNC(123.4567, -3);
Result: 0.0
SELECT TRUNC(1234567, -2);
Result: 1234500
# HEX_TO_DEC
Format | HEX_TO_DEC(str) |
Converts hexadecimal string str to decimal number type.
- Specify a character string type value (0-9, a-f, A-F) that can be converted to hexadecimal for the argument str.
- Return NULL, when the result value is NULL.
- The result is of a LONG type.
Example:
SELECT HEX_TO_DEC('FF');
Result: 255
SELECT HEX_TO_DEC('10');
Result: 16
# Character functions
# LENGTH
Format | LENGTH(str) |
Return the length of the character string str.
- Specify character string type values for the argument str.
- Unicode code point of a character string is used.
- Return NULL, when the result value is NULL.
- The result is of a LONG type.
- A BLOB type can also be specified for an argument.
Example:
SELECT last_name, LENGTH(last_name) length FROM employees;
Result:
last_name length
------------+----------------------
Smith 5
Jones 5
Brown 5
Taylor 6
(NULL) (NULL)
Smith 5
# LOWER
Format | LOWER(str) |
Convert all the alphabet of the character string str to lowercases.
- Specify character string type values for the argument str.
- Return NULL, when the result value is NULL.
- The result is of a character string type.
- Unicode characters other than ASCII alphabetic characters are not converted.
Example:
SELECT last_name, LOWER(last_name) lower FROM employees;
Result:
last_name lower
------------+----------------------
Smith smith
Jones jones
Brown brown
Taylor taylor
(NULL) (NULL)
Smith smith
# UPPER
Format | UPPER(str) |
Convert all the alphabet of the character string str to uppercases.
- Specify character string type values for the argument str.
- Return NULL, when the result value is NULL.
- The result is of a character string type.
- Unicode characters, such as Cyrille characters, other than ASCII alphabetic characters are not converted.
Example:
SELECT last_name, UPPER(last_name) upper FROM employees;
Result:
last_name upper
------------+----------------------
Smith SMITH
Jones JONES
Brown BROWN
Taylor TAYLOR
(NULL) (NULL)
Smith SMITH
# SUBSTR
Format | SUBSTR(str, index [, length]) |
Cut out a part of a character string. from the character on the starting position, indicated by "index" up to the length specified by "length".
- Specify character string type values for the argument str.
- Specify an integer, 1 or larger, as the argument index. The starting position at the beginning of a character string is 1.
- When the argument length is not specified, the character strings up to the end of str is cut out.
- Return NULL, when the str value is NULL.
- The result is of a character string type.
- A BLOB type can also be specified for an argument.
Example:
SELECT SUBSTR('abcdefg', 3);
Result:cdefg
SELECT SUBSTR('abcdefg', 3, 2);
Result:cd
# REPLACE
Format | REPLACE(str, search_str, replacement_str) |
Replace a character string. In the character string str, replace all the parts matching the character string search_str with replacement_str.
- Specify character string type values for the argument search_str, replacement_str.
- Return NULL, when the str value is NULL.
- The result is of a character string type.
Example:
SELECT REPLACE('abcdefabc', 'abc', '123');
Result:123def123
# INSTR
Format | INSTR(str, search_str [, offset] [, occurrence]) |
Search for character string search_str in the character string str, and return its starting position. Return 0, when not found. Return 0, when not found.
- Specify a string type or BLOB type value for the arguments str and search_str. The values of the same data type must be specified for str and search_str. For the offset and occurrence arguments, specify a LONG value.
- For string type, it is calculated in Unicode code point unit, and for BLOB type, it is calculated in byte unit.
- offset indicates the position where the search starts: for a positive value, the search starts from the front; for a negative value, the search starts from the rear end; when 0 is specified, 0 is returned meaning no match.
- occurrence indicates the number of matches: the search is repeated the specified number of times and the last matched position is returned. when 0 is specified, 0 is returned meaning no match.
- Return NULL, when either of the value of the arguments is NULL.
- The result is of a LONG type.
Example:
SELECT INSTR('abcdef', 'cd');
Result:3
SELECT INSTR('abcdef', 'gh');
Result:0
SELECT INSTR('abcabcabcde', 'ab', 2, 2);
Result: 7
SELECT INSTR('abcabcabcde', 'ab', -1, 2);
Result: 4
# LIKE
------ | ----------------------------------------------- |
Format | LIKE(pattern_str, str [, escape_str]) |
Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following two wild cards are available for a match pattern.
Wild card | Meaning |
---|---|
_ | Any one character |
% | Any character with zero or more character strings |
Specify the escape character escape_str when searching for the character _ or % in str containing the wildcard character _ or %. If a escape character is specified before the wild card character, it will no longer be interpreted as a wild card.
- Specify character string type values for the argument str, pattern_str ,escape_str.
- Return NULL, when either of the value of the arguments is NULL.
- Uppercase and lowercase characters are not distinguished.
- The result is of a BOOL type.
Example:
SELECT last_name, LIKE('%mi%', last_name) like_name FROM employees;
Result:
last_name like_name
------------+----------------------
Smith true
Jones false
Brown false
Taylor false
(NULL) (NULL)
Smith true
SELECT LIKE('%C%E%', 'ABC%DEF');
Result:true
SELECT LIKE('%C@%E%', 'ABC%DEF', '@');
Result:false
SELECT LIKE('%C@%D%', 'ABC%DEF', '@');
Result:true
# GLOB
Format | GLOB(pattern_str, str) |
Search the character string on the right. Return true, when the character string str matches the match pattern pattern_str. Return false, when no match was found. The following wild cards are available for a match pattern.
Wild card | Meaning |
---|---|
? | Any one character |
* | Any character with zero or more character strings |
[abc] | Match any of the letters a, b or c |
[a-e] | Match any of the letters from a to e |
- Specify character string type values for the argument str, pattern_str.
- Return NULL, when either of the value of the arguments is NULL.
- Uppercase and lowercase characters are distinguished.
- The result is of a BOOL type.
Example:
SELECT GLOB('*[BA]AB?D', 'AABCD');
Result:true
# TRIM
Format | TRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from both ends of the character string str.
- Specify character string type values for the argument str and trim_str.
- Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from both ends of str.
- The result is of a character string type.
Example:
SELECT TRIM(' ABC ');
Result: ABC (no space at both ends)
SELECT TRIM('ABCAA', 'BA');
Result: C
# LTRIM
Format | LTRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from the left end of the character string str.
- Specify character string type values for the argument str and trim_str.
- Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from the left end of str.
- The result is of a character string type.
Example:
SELECT TRIM(' ABC ');
Result: ABC (no space at the left end)
SELECT TRIM('ABCAA', 'BA');
Result: BCAA
# RTRIM
Format | RTRIM(str [, trim_str]) |
Delete all the characters of character string trim_str from the right end of the character string str.
- Specify character string type values for the argument str and trim_str.
- Delete all the characters contained in the argument trim_str. When no value is specified, spaces are deleted from the right end of str.
- The result is of a character string type.
Example:
SELECT RTRIM(' ABC ');
Result: ABC (no space at the right end)
SELECT RTRIM('ABCAA', 'A');
Result: ABC
# QUOTE
Format | QUOTE(x) |
Returns a character string containing the value of x enclosed in single quotes.
- For the argument x, specify a value of a character string type, a numeric type, a TIMESTAMP type, and a BLOB type value.
- For the string type, single quotes contained in the string are escaped into two single quotes ''.
- For the numeric type, a numeric value is returns as it is. It is not enclosed in single quotes.
- A TIMESTAMP type argument is converted to the string representation of time 'YYYY-MM-DDThh:mm:ss.SSS(Z|±hh:mm)' (see TIMESTAMP function). It is not enclosed in single quotes.
- For the BLOB type, return the character string X'BLOB type value'.
- The result is of a character string type.
Example:
SELECT QUOTE(last_name) last_name, QUOTE(age) age FROM employees;
Result:
last_name age
------------+-------
'Smith' 43
'Jones' 59
'Brown' (NULL)
'Taylor' 31
(NULL) 29
'Smith' 43
SELECT QUOTE(RANDOMBLOB(4));
Result: X'A45EA28D'
// The value of column "value" is a character string "Today's news."
SELECT value, QUOTE(value) FROM testcontainer;
Result:
value QUOTE(value)
---------------+-------------------
Today's news 'Today''s news'
# UNICODE
Format | UNICODE(str) |
Returns the UNICODE code point of the first character of the string str.
- Specify character string type values for the argument str.
- The result is of a LONG type.
Example:
SELECT last_name, UNICODE(last_name) unicode FROM employees;
Result:
last_name unicode
------------+----------------------
Smith 83
Jones 74
Brown 66
Taylor 84
(NULL) (NULL)
Smith 83
# CHAR
Format | CHAR(x1 [, x2, ... , xn]) |
Returns a concatenated character string of characters with Unicode code point value xn.
- Specify a Unicode code point value for an argument xn.
- The result is of a STRING type.
Example:
SELECT CHAR(83, 84, 85);
Result: STU
# PRINTF
Format | PRINTF(format [, x1, x2, ..., xn]) |
Return the converted character string according to the specified format "format". A format equivalent to the printf function of the standard C libraries can be used. There are two other formats as below.
Format | Description |
---|---|
%q | A single quote in a character string is escaped to two single quotes ''. |
%Q | A single quote in a character string is escaped to two single quotes ''. Enclose both ends of the character string by single quotes. |
Example:
SELECT enrollment_period, PRINTF('%.2f', enrollment_period) printf FROM employees;
Result:
enrollment_period printf
------------------+-----------
15.5 15.50
23.2 23.20
7.0 7.00
(NULL) 0.00
4.9 4.90
10.3 10.30
# TRANSLATE
Format | TRANSLATE(str, search_str, replacement_str) |
Replace a character string. Among the character string str, the characters matched the character string search_str is replaced by the characters of character string replacement_str in the same position as search_str. When replacement_str is shorter than search_str, thus having no characters to substitute in the part longer than replacement_str, the characters to be replaced will be deleted.
- Specify character string type values for the argument search_str, replacement_str.
- Return NULL, when the result value is NULL.
- The result is of a character string type.
Example:
SELECT TRANSLATE('abcde', 'ace', '123');
Result:1b2d3
SELECT TRANSLATE('abcdeca', 'ace', '123');
Result: 1b2d321
SELECT TRANSLATE('abcde', 'ac', '123');
Result: 1b2de
SELECT TRANSLATE('abcde', 'ace', '12');
Result: 1b2d
SELECT TRANSLATE('abcde', 'AB', '123');
Result: abcde
SELECT TRANSLATE('abcde', 'abc', '');
Result: de
# Time functions
# NOW
Format | NOW() |
Returns the current time value.
- If the time zone is specified at the time of connection, the offset calculated value is returned.
- The result is of a TIMESTAMP type.
Example:
SELECT NOW();
Result: 2019-09-17T04:07:31.825Z
SELECT NOW();
Result: 2019-09-17T13:09:20.918+09:00
# TIMESTAMP
Format | TIMESTAMP(timestamp_string [, timezone]) |
Convert the string representation of time to TIMESTAMP type.
In the argument timestamp_string, specify a character string in the following format as a character string representation of time.
- YYYY-MM-DDThh:mm:ssZ
- YYYY-MM-DDThh:mm:ss.SSSZ
- YYYY-MM-DD
- hh:mm:ss
Notation Item The range of value YYYY Year (A.D.) 1970- MM Month 1 to 12 DD Day 1 to 31 hh Time (24-hour notation) 0 to 23 mm Minute 0 to 59 ss Second 0 to 59 SSS Millisecond 0 to 999 Z Time zone Z For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm), not required when time zone information is included in timestamp_string. An error is returned if the specified values are inconsistent.
If the time zone is specified at the time of connection, the offset calculated value is returned.
The result is of a TIMESTAMP type.
Use CAST for the inverse conversion of TIMESTAMP function (conversion from TIMESTAMP type to string type).
- CAST(timestamp AS STRING)
Example:
// Search for a row with the value of column date (TIMESTAMP type) newer than time '2018-12-01T10: 30: 00Z'
SELECT * FROM timeseries WHERE date > TIMESTAMP('2018-12-01T10:30:00Z');
# TIMESTAMP_ADD
Format | TIMESTAMP_ADD(time_unit, timestamp, duration [, timezone]) |
The value obtained by adding the period "duration" (unit: time_umit) to time period "timestamp" is returned.
- Specify a TIMESTAMP type value for the argument timestamp.
- Specify an integer for an argument duration. Subtract from the time point, when a negative number is specified.
- Specify one of the following identifiers for the argument time_unit:
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
- If the calculated day of the month does not exist as a result of adding a year or a month, the day is rounded to the last day of the month. For example, if one month is added to May 31, the result will be rounded to June 30 because June 31 does not exist.
- If the time zone is specified at the time of connection, the offset calculated value is returned.
- The result is of a TIMESTAMP type.
- TIMESTAMPADD can also be used as a function alias.
Example:
Add ten days to time period '2018-12-01T11:22:33.444Z'.
SELECT TIMESTAMP_ADD(DAY, TIMESTAMP('2018-12-01T11:22:33.444Z'), 10);
Result: 2018-12-11T11:22:33.444Z
SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1);
Result: 2019-06-30T01:23:45.678Z
SELECT TIMESTAMP_ADD(MONTH, TIMESTAMP('2019-05-31T01:23:45.678Z'), 1, '-02:00');
Result: 2019-07-01T01:23:45.678Z
# TIMESTAMP_DIFF
Format | TIMESTAMP_DIFF(time_unit, timestamp1, timestamp2 [, timezone]) |
Returns the difference of timestamp1 and timestamp2 (timestamp1-timestamp2) as a value expressed in the time unit "time_unit". When a time difference is represented in time units, the decimal places are rounded off.
- Specify a TIMESTAMP type value for the argument timestamp1 and timestamp2.
- Specify one of the following identifiers for the argument time_unit: Instead of calculating the difference only in the unit specified by the identifier, the unit less than the identifier is also used in the calculation. For example, if MONTH is specified and 2019/09/30 is compared with 2019/10/02, the output will be 0 instead of 1 because 2 days of 0 months will be the difference.
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
- If the time zone is specified at the time of connection, the offset calculated value is used for the calculation of the difference.
- The result is of a LONG type.
- TIMESTAMPDIFF can also be used as a function alias.
Example:
// Time unit: Month
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 0
Time unit: Day
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 10
SELECT TIMESTAMP_DIFF(DAY, TIMESTAMP('2018-12-01T11:00:00.000Z'), TIMESTAMP('2018-12-11T10:30:15.555Z'));
Result:-9
// Time unit: Time point
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 240
// Time unit: Minute
SELECT TIMESTAMPDIFF(MINUTE, TIMESTAMP('2018-12-11T10:30:15.555Z'), TIMESTAMP('2018-12-01T10:00:00.000Z'));
Result: 14430
// Here is an example where the result changes depending on the time zone.
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), 'Z');
Result: 2
SELECT TIMESTAMP_DIFF(MONTH, MAKE_TIMESTAMP(2019, 8, 1), MAKE_TIMESTAMP(2019, 6, 30), '-01:00');
Result: 1
# TO_TIMESTAMP_MS
Format | TO_TIMESTAMP_MS(milliseconds) |
Return the time point obtained by adding the value of argument "milliseconds" as millisecond, to the time point'1970-01-01T00:00:00.000Z'.
This function is an inverse conversion of TO_EPOCH_MS function.
- Specify an integer for the argument "milliseconds".
- If the time zone is specified at the time of connection, the offset calculated value is returned.
- The result is of a TIMESTAMP type.
Example:
SELECT TO_TIMESTAMP_MS(1609459199999);
Result: 2020-12-31T23:59:59.999Z
# TO_EPOCH_MS
Format | TO_EPOCH_MS(timestamp) |
Return the lapsed time (in milliseconds) from the time '1970-01-01T00:00:00.000Z' to the time "timestamp".
This function is an inverse conversion of TO_EPOCH_MS function.
- Specify a TIMESTAMP type value for the argument timestamp.
- The result is of a LONG type.
Example:
SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999Z'));
Result: 1609459199999
SELECT TO_EPOCH_MS(TIMESTAMP('2020-12-31T23:59:59.999+09:00'));
Result: 1609426799999
# EXTRACT
Format | EXTRACT(time_field, timestamp [, timezone]) |
Retrieve the value of time field "time_field" from the time "timestamp". The time will be the value of UTC.
- Specify a TIMESTAMP type value for the argument timestamp.
- Specify one of the following identifiers for the argument time_field:
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND | DAY_OF_WEEK | DAY_OF_YEAR
- DAY_OF_WEEK is from Sunday, as 0, to Saturday, as 6.
- DAY_OF_YEAR is from January first, as 1, to December 31th, as 365 or 366.
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND | DAY_OF_WEEK | DAY_OF_YEAR
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
- If the time zone is specified at the time of connection, the offset calculated value is returned. If it is also specified in the argument timezone, the one specified in the argument will be used.
- The result is of a LONG type.
Example:
// Calculate the value of the year, the day, and the millisecond of time point '2018-12-01T10:30:02.392Z'.
// The value of the year
SELECT EXTRACT(YEAR, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 2018
SELECT EXTRACT(DAY, TIMESTAMP('2018-12-01T10:30:02.392Z'));
// The value of the day
Result: 1
// The value of the millisecond
SELECT EXTRACT(MILLISECOND, TIMESTAMP('2018-12-01T10:30:02.392Z'));
Result: 392
// Consider the time zone.
SELECT EXTRACT(HOUR, TIMESTAMP('2018-12-01T10:30:02.392Z'), '+09:00');
Result: 19
# STRFTIME
Format | STRFTIME(format, timestamp [, modifier,...]) |
Return a time converted to a string according to the specified format.
- Specify the following in the format argument to extract time information.
Format | Description |
---|---|
%Y | Extract the year in YYYY format. |
%m | Extract the month in MM format. |
%d | Extract the day in DD format. |
%H | Extract the time in hh format. |
%M | Extract the minute in mm format. |
%S | Extract the second in ss format. |
%3f | Extract millisecond in SSS format. |
%z | Extract the time zone in ± hh:mm format. |
%w | Extracts the day of the week in D format (0 to 6): from Sunday, as 0, to Saturday, as 6. |
%W | Extracts the number of the week of the year in DD format (from 00 to 53). The first Monday is considered to be in the first week, and days before that are considered to be in the 0th week. |
%j | Extract the number of days from January first in DDD format (001 to 366). |
%c | Extract the time in the format YYYY-MM-DDThh:mm:ss[.SSS](Z |
%% | Output % as a character. |
- Specify a TIMESTAMP type value for the argument timestamp.
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
- The result is of a STRING type.
Example:
SELECT STRFTIME('%c', TIMESTAMP('2019-06-19T14:15:01.123Z'));
Result: 2019-06-19T14:15:01.123Z
SELECT STRFTIME('%H:%M:%S%z', TIMESTAMP('2019-06-19T14:15:01.123Z'), '+09:00');
Result: 23:15:01+09:00
SELECT STRFTIME('%W', TIMESTAMP('2019-01-19T14:15:01.123Z'));
Result: 02
# MAKE_TIMESTAMP
Format | MAKE_TIMESTAMP(year, month, day [, timezone]) MAKE_TIMESTAMP(year, month, day, hour, min, sec [, timezone]) |
Generate and return a TIMESTAMP type value.
- If the hour, min, and sec arguments are not specified, it is assumed that all 0 have been specified.
- The argument sec can be specified in milliseconds. The value less than a millisecond is rounded, possibly causing a floating point calculation error.
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
- The result is of a TIMESTAMP type.
Example:
SELECT MAKE_TIMESTAMP(2019, 9, 19);
Result: 2019-09-19T00:00:00.000Z
SELECT MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123, '+09:00');
Result: 2019-09-19T01:30:15.123Z
# TIMESTAMP_TRUNC
Format | TIMESTAMP_TRUNC(field, timestamp [, timezone]) |
Truncates the time information.
- Specify one of the following identifiers for the argument field:
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
- Specify a TIMESTAMP type value for the argument timestamp.
- For the timezone argument, specify the time zone (Z|±hh:mm|±hhmm),
Example:
SELECT TIMESTAMP_TRUNC(HOUR, MAKE_TIMESTAMP(2019, 9, 19, 10, 30, 15.123));
Result: 2019-09-19T10:00:00.000Z
SELECT TIMESTAMP_TRUNC(DAY, MAKE_TIMESTAMP(2019, 5, 15), '-01:00');
Result: 2019-05-14T01:00:00.000Z
# WINDOW function
# ROW_NUMBER
Format | ROW_NUMBER() OVER ( [PARTITION BY expression_1] [ORDER BY expression_2 ] ) |
Assign a unique serial number to the resulting rows.
- Used with the OVER clause. See OVER clause for details.
Example:
SELECT ROW_NUMBER() OVER(PARTITION BY department ORDER BY age) no, first_name, age, department FROM employees;
Result:
no first_name age department
----+------------+--------+-------------
1 James 43 Development
2 William 59 Development
1 Mary 31 Research
1 John 43 Sales
2 Richard (NULL) Sales
1 Lisa 29 (NULL)
# Other functions
# COALESCE
Format | COALESCE(x1, x2 [,..., xn]) |
Return the value of the first argument that is not NULL in the specified argument xn.
Specify the same type value for the argument xn. There are some different types that can be specified. Refer to CASE for the allowed combination of types.
Return NULL, when all argument values are NULL.
Example:
SELECT last_name, COALESCE(last_name, 'XXX') coalesce FROM employees;
Result:
last_name coalesce
------------+----------------------
Smith Smith
Jones Jones
Brown Brown
Taylor Taylor
(NULL) XXX
Smith Smith
SELECT age, COALESCE(age, -1) coalesce FROM employees;
Result:
age coalesce
--------+-----------
43 43
59 59
(NULL) -1
31 31
29 29
43 43
# IFNULL
Format | IFNULL(x, y) |
Return the value of the first argument that is not NULL among the specified arguments x and y. The IFNULL function is equivalent to the COALESCE function with two arguments.
- Specify the value of the same type for the arguments x and y. There are some different types that can be specified. Refer to CASE for the allowed combination of types.
- Return NULL, when all argument values are NULL.
Example:
SELECT last_name, IFNULL(last_name, 'XXX') ifnull FROM employees;
Result:
last_name ifnull
------------+----------------------
Smith Smith
Jones Jones
Brown Brown
Taylor Taylor
(NULL) XXX
Smith Smith
SELECT age, IFNULL(age, -1) ifnull FROM employees;
Result:
age coalesce
--------+-----------
43 43
59 59
(NULL) -1
31 31
29 29
43 43
# NULLIF
Format | NULLIF(x, y) |
Return NULL when two arguments are the same, return the first argument when the arguments are different.
- Specify the value of the same type for the arguments x and y. There are some different types that can be specified. Refer to CASE for the allowed combination of types.
Example:
// Execute NULLIF with the value of value1 and value2.
SELECT value1, value2, NULLIF(value1, value2) nullif FROM container_sample;
Result:
value1 value2 nullif
--------+--------+--------
10 10 (NULL)
5 0 5
(NULL) 4 (NULL)
3 (NULL) 3
(NULL) (NULL) (NULL)
// Convert 0 to NULL to prevent division by zero errors in the calculation of value1 / value2
SELECT value1, value2, value1/NULLIF(value2, 0) division FROM container_sample;
Result:
value1 value2 division
--------+--------+--------
10 10 1
5 0 (NULL)
(NULL) 4 (NULL)
3 (NULL) (NULL)
(NULL) (NULL) (NULL)
# RANDOMBLOB
Format | RANDOMBLOB(size) |
Return a BLOB type value (random number).
- Specify the size (number of bytes) of a BLOB type value as an integer for the argument size .
- The result is of a BLOB type.
Example:
// Generate a 10-byte blob value (random number)
SELECT HEX(RANDOMBLOB(10));
Result: 7C8C893C8087F07883AF
# ZEROBLOB
Format | ZEROBLOB(size) |
Return a BLOB type value (0x00).
- Specify the size (number of bytes) of a BLOB type value as an integer for the argument size .
- The result is of a BLOB type.
Example:
// Generate a 10-byte blob value (0x00).
SELECT HEX(ZEROBLOB(10));
Result: 00000000000000000000
# HEX
Format | HEX(x) |
Convert a BLOB type value to a hexadecimal type. Interpret the argument x as a BLOB type value, and return the character string (uppercase) converted into the hexadecimal.
- Specify a BLOB type and a character string type for the argument x.
- For a character string type argument, return the character string in which the Unicode code point of all the characters converted to hexadecimal.
- The result is of a character string type.
Example:
SELECT HEX(RANDOMBLOB(2));
Result: E18D
SELECT first_name, HEX(first_name) hex FROM employees;
Result:
first_name hex
------------+----------------------
John 4A6F686E
William 57696C6C69616D
Richard 52696368617264
Mary 4D617279
Lisa 4C697361
James 4A616D6573
# TYPEOF
Format | TYPEOF(x) |
Return the character string indicating the data type of the value of x.
The correspondence between the data type and the string returned by the TYPEOF function is shown below.
Data types Character string which TYPEOF function returns BOOL BOOL STRING STRING BYTE BYTE SHORT SHORT INTEGER INTEGER LONG LONG FLOAT FLOAT DOUBLE DOUBLE TIMESTAMP TIMESTAMP GEOMETRY NULL BLOB BLOB ARRAY NULL The result is of a character string type.
When a NULL value is specified, 'NULL' is returned.
Example:
SELECT TYPEOF(ABS(-10)) abs, TYPEOF(RANDOMBLOB(10)) randomblob,
TYPEOF(TIMESTAMP('2018-12-01T10:30:02.392Z')) timestamp;
Result:
abs randomblob timestamp
------+------------+-----------
LONG BLOB TIMESTAMP
# Other syntaxes
# CAST
Format | CAST(x AS data_type) |
Convert the value x into the data type "data_type".
Specify the following values for argument "data_type" according to the converted data type.
Converted data type Value for data_type BOOL BOOL STRING STRING BYTE BYTE SHORT SHORT INTEGER INTEGER LONG LONG FLOAT FLOAT DOUBLE DOUBLE TIMESTAMP TIMESTAMP BLOB BLOB
# Convert to string type
Format | CAST(x AS STRING) |
Convert the argument x to a character string type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to character string type |
---|---|
BOOL | 'true' if true, 'false' if false |
STRING | Original value |
BYTE SHORT INTEGER LONG FLOAT DOUBLE | Value converted from a number to a character |
TIMESTAMP | Character string notation of time point 'YYYY-MM-DDThh:mm:ss.SSS(Z |
BLOB | A character string equivalent to the converted character string usingHEX function |
# Convert to numeric type
Format | CAST(x AS BYTE |
Convert the argument x into a numeric type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to numeric type |
---|---|
BOOL | 1 if true, 0 if false |
STRING | The value converted from the character string to numerical value |
BYTE SHORT INTEGER LONG FLOAT DOUBLE | The numerical value converted to the specified numeric type |
- An error will occur if the converted number exceeds the range of numeric values specified in data_type.
// An error occurs if exceeding BYTE type range (-128 to 127)
SELECT CAST(128 AS BYTE);
Result: error
// An error occurs if exceeding INTEGER type range (-2147483648-2147483647).
SELECT CAST('2147483648' AS INTEGER);
Result: error
- When converted from floating-point type (FLOAT, DOUBLE) to integer type (BYTE, SHORT, INTEGER, LONG), the number of significant digits in the result may be reduced.
SELECT CAST(10.5 AS INTEGER);
Result: 10
- The following character strings can be specified in the conversion from a character string type to a numeric type (case insensitive). An error will occur when character strings other than these are specified.
- The character string containing a number, a sign (". "," - ", "+"), or "E"
- "Inf" (signed data acceptable)
- "Infinity" (signed data acceptable)
- "NaN"
SELECT CAST('abc' AS INTEGER);
Result: error
SELECT CAST('-1.09E+10' AS DOUBLE);
Result: -1.09E10
# Convert to time type
Format | CAST(x AS TIMESTAMP) |
Convert the argument x to a time type. If the time zone is specified at the time of connection, that value is used for offset calculation.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to time type |
---|---|
STRING (character string notation of time 'YYYY-MM-DDThh:mm:ss.SSSZ') | Equivalent to the value converted using the TIMESTAMP function |
SELECT CAST('2018-12-01T10:30:00Z' AS TIMESTAMP);
Result: 2018-12-01T10:30:00.000Z
SELECT CAST('2018-12-01T10:30:00+09:00' AS TIMESTAMP);
Result: 2018-12-01T01:30:00.000Z
# Convert to BOOL type
Format | CAST(x AS BOOL) |
Convert the argument x to a BOOL type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to time type |
---|---|
STRING | True if 'true', false if 'false' (case insensitive) |
BYTE SHORT INTEGER LONG | False if 0, otherwise true |
# Convert to BLOB type
Format | CAST(x AS BLOB) |
Convert the argument x to a BLOB type.
The data types of the value which can be specified for x, and the converted values are as follows.
Data type of x | Value converted to BLOB type |
---|---|
STRING | The value converted from character string as hexadecimal data to BLOB type |
# CASE
Format | CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] ... [ELSE resultElse] END |
When the conditional expression conditionN is true, the value of corresponding resultN is returned. When all the conditional expressions are false or NULL, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.
Format | CASE x WHEN value1 THEN result1 [WHEN value2 THEN result2] ... [ELSE resultElse] END |
When the value of x is valueN, the value of corresponding resultN is returned. When the value of x is not equal to all values, and if ELSE is specified, the value of resultElse will be returned. When ELSE is not specified, NULL is returned.
Specify the same type value for resultN. There are some different types that can be specified.
If the arguments are of different types, only the combination of the following types can be calculated. Any other combinations will result in an error.
Type of argument Type of argument Type of argument when calculating the two arguments SHORT BYTE LONG INTEGER BYTE, SHORT LONG LONG BYTE, SHORT, INTEGER LONG FLOAT BYTE, SHORT, INTEGER, LONG DOUBLE DOUBLE BYTE, SHORT, INTEGER, LONG, FLOAT DOUBLE
Example:
// Display the employee's age (30's, 40's, 50's, other than these)
SELECT id, first_name, age,
CASE
WHEN age > 50 THEN '50s'
WHEN age > 40 THEN '40s'
WHEN age > 30 THEN '30s'
ELSE 'other'
END AS period
FROM employees;
Result:
id first_name age period
----+------------+-------+--------
0 John 43 40s
1 William 59 50s
2 Richard (NULL) other
3 Mary 31 30s
4 Lisa 29 other
5 James 43 40s
// Display a location according to their departments.
SELECT id, first_name, department,
CASE department
WHEN 'Sales' THEN 'Tokyo'
WHEN 'Development' THEN 'Osaka'
ELSE 'Nagoya'
END AS location
FROM employees;
Result:
id first_name department location
----+------------+-------------+---------
0 John Sales Tokyo
1 William Development Osaka
2 Richard Sales Tokyo
3 Mary Research Nagoya
4 Lisa (NULL) Nagoya
5 James Development Osaka
# Subquery
Subqueries can be specified in various parts of an SQL statemnt other than FROM and WHERE clauses. Some operation types for subqueries are also provided, which are explained in this section.
# IN
Return whether the specified value is included in the sub query execution result.
Syntax
| -------------------------------------- | | Expression 1 [NOT] IN ( sub_query ) |
Specifications
- Return true when the value of expression_1 is included in the result of the sub query.
- The result of a sub query must be data of one row.
Example:
// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE department IN(
SELECT department FROM departments
WHERE id = 1
);
Result:
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
1 William Jones 59 Development 23.2
5 James Smith 43 Development 10.3
# EXISTS
Return whether the execution result of the sub query exists.
Syntax
[NOT] EXISTS( sub_query ) |
Specifications
Check whether the execution result of the sub query exists. Return true if the number of execution result is 1 or more, false if it is 0.
The result is of a BOOL type.
Example:
// Display the information of the employee who belongs to the department of id=1 in the departments table from the employees table.
SELECT * FROM employees
WHERE EXISTS(
SELECT * FROM departments
WHERE employees.department=departments.department AND departments.id=1
);
Result:
id first_name last_name age department enrollment_period
----+------------+-----------+-------+-------------+-------------------
1 William Jones 59 Development 23.2
5 James Smith 43 Development 10.3
# Scalar sub query
Subquery which returns one result, which can be used for the result of a SELECT statement or for an expression.
Example:
SELECT id, first_name,
(SELECT department FROM departments WHERE department_id=employees.department_id)
FROM employees;
Result:
id first_name department
---+-----------+-------------
0 John Sales
1 William Development
2 Richard Sales
3 Mary (NULL)
4 Lisa Marketing
5 James Development
# Placeholder
A prepared statement can describe a placeholder in SQL statements. A placeholder indicates the position of the parameter to be substituted when the statement is executed. The parameter number starts from 1.
The placeholder can use several forms for compatibility with other databases. However, the parameter number will be the already assigned parameter number + 1, regardless of which format is specified.
Format | Description | Example of description |
---|---|---|
? | Format of a standard placeholder | ? |
?NNN | NNN indicates a number. | ?56 |
:AAAA | AAAA indicates a character string. | :name |
@AAAA | AAAA indicates a character string. | @name |
The placeholder must not start with $.
Example:
String sql = "SELECT * FROM users WHERE id > ? AND id != :exclude_id;";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 100); // 1: ?
pstmt.setInt(2, 253); // 2: :exclude_id
ResultSet rs = pstmt.executeQuery();
# Comment
Comments can be written in a SQL command. Format: Description at the back of -- (2 hyphens) or enclose with /* */. A new line needs to be returned at the end of the comment.
SELECT * -- comment
FROM employees;
SELECT *
/*
comment
*/
FROM employees;
# Hints
In GridDB, specifying the hints indicating the execution plan in the query makes it possible to control the execution plan without changing the SQL statement.
# Error handling
In the following cases, a syntax error occurs.
- Multiple block comments for hints are described
- The hint is described in the wrong position
- There is a syntax error in the description of the hint phrase
- Duplicate hint of the same class are specified for the same table
In the following case, a table specification error occurs:
- The table specification of the hint phrase is incorrect
[Memo]
- When a table specification error occurs, ignore the error hint phrase and execute the query using the others.
- When a syntax error and a table specification error occur at the same time, a syntax error occurs.
----