SQL in SeaTable¶

You can use SQL to query data in SeaTable. If some tables in a base are archived, archived rows are also queried, as well as rows that are not archived yet.

Supported SQL Syntax¶

Currently only select, insert, update, and delete statements are supported. (insert, update, and delete statements require version 2.7 or later)

The synatx of select statement is:

SELECT [DISTINCT] fields FROM table_name [WhereClause] [GroupByClause] [HavingClause] [OrderByClause] [Limit Option]

Since version 4.3, basic JOIN query is supported, for example:

SELECT ... FROM table1, table2 WHERE table1.column1 = table2.column2 AND ...

The JOIN query has the following restrictions:

• Only INNER JOIN is supported, LEFT JOIN, RIGHT JOIN, and FULL JOIN are not supported.
• Tables in the FROM clause should be unique (no duplicate tables).
• Each table in the FROM clause should be associated with at least one join condition.
• Join conditions should be placed in the WHERE clause, and connected with ANDs.
• Join conditions can only use equality operator on columns, e.g. table1.column1 = table2.column2.
• Columns in join conditions must be indexed, unless the table is not archived.

Notes:

• Most SQL syntax can be used in where clause, including arithmetic expressions, comparison operators, [NOT] LIKE, IN, BETWEEN ... AND ..., AND, OR, NOT, IS [NOT] TRUE, IS [NOT] NULL.
• Arithmetic expressions only support numbers.
• LIKE only supports strings. The key word ILIKE can be used instead of LIKE to make the match case-insensitive.
• BETWEEN ... AND ... only supports numbers and time.
• Time constants should be strings in ISO format (e.g. "2020-09-08 00:11:23"). Since 2.8 version, strings in RFC 3339 format are supported (such as "2020-12-31T23:59:60Z").
• GROUP BY uses strict syntax. The selected fields must appear in group by list, except for aggregation functions (COUNT, SUM, MAX, MIN, AVG) and formulas (see extended syntax section below).
• HAVING filters rows resulting from the group by clause. Only fields referred in the "GROUP BY" clause or aggregation functions (such as "SUM") can be used in having clause. Other syntax is the same as specified for the where clause.
• Fields in "order by" list must be a column or an expression in the selected fields. For example, select a from table order by b is invalid; while select a from table order by b and select abs(a), b from table order by abs(a) are valid.
• Limit options are in MySQL format. The general syntax is LIMIT ... OFFSET .... You may obmit LIMIT or OFFSET.
• Field alias with AS syntax is supported. For example, select table.a as a from table returns rows whose first column is keyed by "a". There are two notes:
• Field alias can be referred in group by, having and order by clauses. E.g., select t.registration as r, count(*) as c from t group by r having c > 100 is valid.
• Field alias cannot be referred in where clause. E.g., select t.registration as r, count(*) from t group by r where r > "2020-01-01" will report syntax error.

Each returned row is a JSON map. The keys of the maps are the column keys, NOT column names. To use column names as keys, the convert_keys parameter (available since version 2.4) in query request should be TRUE. For JOIN query, the keys of row maps match the "id" fields (not the "key" or the "name"). Those column fields (e.g. id, key, name) are returned under metadata array in query response.

The synatx of insert, update, and delete statements are:

INSERT INTO table_name [column_list] VALUES value_list [, ...]

UPDATE table_name SET column_name = value [, ...] [WhereClause]

DELETE FROM table_name [WhereClause]

• column_list is a list of column names surrounded by parentheses. If omitted, it defaults to all updatable columns.
• value_list is a list of values surrounded by parentheses. Values must be in the same order as the column list, for example: (1, "2", 3.0).
• Multivalued columns, such as multiple-select column type, requires values to be surrounded by parentheses, for example: (1, "2", 3.0, ("foo", "bar")).
• Values of single-select and multiple-select column types must be option names, not option keys.
• WhereClause is optional. If omitted, all rows in the table are included.
• INSERT statement only supports bases that have been archived. The rows will be inserted into big-data storage. It'll return error if the base is not archived yet. If you want to insert rows into such bases, please use API for adding rows (e.g. Python API).
• UPDATE and DELETE statements allows updating/deleting rows in both normal and big-data storage.

Note: these column types are not allowed to insert or update:

• built-in columns, such as _id, _ctime.
• image, file, formula, link, link-formula, geolocation, auto-number, button

Data Types¶

Below is mapping from SeaTable column types to SQL column types.

List Types¶

In SeaTable, two categories of column types are list types:

• Built-in list types: including multiple selection, image, collaborator, and link to other records.
• Return values for the following link formulas: formula columns whose formula is {link.column} or lookup; link formula columns whose formula is lookup, findmin or findmax.

When referring a column with list type in where conditions, the following rules apply, depending on the type for the list elements. (If an operator is not listed below, it's unsupported.)

When a list column is returned in a selected field, only the first 10 elements are returned.

When used in group by or order by clauses, the elements for each list will first be sorted in ascending order, then the lists will be sorted by the rules below:

• Compare the elements one by one, list with smaller element is sorted before list with larger element.
• If all elements compared in step 1 are equal, shorter list is sorted before longer list.
• Otherwise the tow lists are equal.

If a list column is passed as parameter to a formula, and the parameter expects a scalar value, the first element will be used. And if the element is a linked record, the value of its display column will be used.

When applying aggregate functions (min, max, sum, avg) to a list column, if there is only 1 element in the list, use that element; otherwise this row will not be aggregated.

NULL Values¶

NULL value is distinct from 0. It represents a missing value. The following values are treated as NULL:

• Empty cells in a table is treated as NULL.
• Values which cannot be converted to the column type will be treated as NULL.
• Empty strings ("") will be treated as NULL too. This is different from standard SQL.
• Lists are treated as NULL based on the rules described in the "List Types" section.
• Functions or formula columns that return error will be treated as NULL.

In the Where clause:

• Arithmetic operations (+, -, * etc.) on NULL values will return NULL.
• !=, NOT LIKE, NOT IN, NOT BETWEEN, HAS NONE OF, IS NOT TRUE, and IS NULL operations will return true when the value is NULL.
• AND, OR, NOT treat NULL values as false.
• Aggregate functions (min, max, sum, avg) will ignore NULL values.

In formulas, NULL values will be converted to 0 or an empty strings.

Extended Syntax¶

Use Formulas in SQL Query¶

You may use a formula syntax that's almost the same as SeaTable's formulas in SQL queries. There are a few special notes:

• Link formulas are not supported. e.g. {link.age} is invalid.
• Reference to columns should not be enclosed by curly brackets ("{}"). Don't write select abs({column}) from talbe;. Wirte select abs(column) from table;. This is consistent with standard SQL syntax.
• You can use back quote ("``") to enclose column references, when column name contains space or "-". E.g. select abs(column-a) from table;
• You may not use column alias in formulas. E.g. select abs(t.column) from table as t; is invalid.
• formulas can be use in group by and order by clauses.

A few extended formulas are supported:

• STARTOFWEEK(date, weekStart): returns the first day of the week where "date" is in. "weekstart" can be used to choose "sunday" or "monday" as the first day of a week.
• Quarter(date): Returns the quater of the date. Return value is 1, 2, 3 or 4.
• ISODate(date): Returns ISO format string for the date. E.g. "2020-09-08".
• ISOMonth(date): Returns ISO format string for the month where "date" is in. E.g. "07".

The above formulas can be used for group by week, quater, date and month. E.g. select sum(sale) from SalesRecord group by ISODate(SalesTime); will return the total sales amount for each day.

For more details, please refer to [./function.md].

Extended List Operators¶

Some column types in SeaTable have list values. The SeaTable UI supports a few special filters for such types. They are HAS ANY OF, HAS ALL OF, HAS NONE OF, IS EXACTLY. You may also use the same syntax to filter such columns with SQL.

For example, if column "city" is of type multi-select, and we want to find out all rows that contains "New York" or "Paris" in the "city" column, you can query: select * from table where city has any of ("New York", "Paris");. The list of string constant are enclosed with brackets, just like the syntax for IN.

Indexes¶

To improve query performance, SeaTable will automatically create indexes for the rows stored in big data storage engine. Currently, text, number, date, single-select, multiple-select, collaborators, creator, create date, modifier, modification date columns are indexed.

When you add or delete a column in a table, the index for this column is not added/removed immediately. Indexes creation and deletion are triggered in two cases:

1. When you archive the table for the next time, indexes are created for new columns and indexes for remvoed columns are removed.
2. Users may manage indexes from "index management" UI. You can open it from the "Big data management" menu in the base.