Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

TABLE

Syntax

TABLE( [DISTINCT] array)

Arguments

array: A PSQL array of scalar values.

Return Type

A set of table-type rows.

Remarks

It returns a single column of output named table_col, with one row for each array entry.
The TABLE() function can be used only in the FROM clause of a query.
The array argument to the TABLE() function must contain only scalar values of valid column data types.
Array of RECORDS and multi-dimensional arrays are not supported as arguments to the TABLE() function.
PSQL arrays are not supported as a table data type or array literals in SingleStore queries.
When used as a reference to the second (or subsequent) table in the FROM clause (with an argument that is correlated), if the array value passed to TABLE() has N entries, the output will have N * number_of_values_from_the_table_on_the_left rows. The values from the table to the left will be repeated for each array entry.
The TABLE function supports DISTINCT. DISTINCT will de-duplicate the array argument and will generate a table with unique values only.

Examples

Example 1

SQL

SELECT * FROM TABLE([1,2,3]);

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

SQL

SELECT * FROM TABLE(["hello", "world"]);

+-----------+
| table_col |
+-----------+
| hello     |
| world     |
+-----------+
2 rows in set (0.04 sec)

SQL

SELECT * FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

Example 2

In the FROM clause, if the table on the left has 2 rows and the array passed to TABLE() has 2 entries, the output will have 4 rows (2*2). For example,

SQL

CREATE TABLE num (x int);
INSERT INTO num VALUES (1),(2);

SQL

SELECT * FROM num, TABLE([1,2]);

+------+-----------+
| x    | table_col |
+------+-----------+
|    1 | 1         |
|    1 | 2         |
|    2 | 1         |
|    2 | 2         |
+------+-----------+

Example 3

The following example demonstrates the usage of TABLE() function through UDFs.

SQL

CREATE TABLE square
  (num INT PRIMARY KEY);

INSERT INTO square VALUES (1),(2),(3),(4),(5);

DELIMITER //

CREATE OR REPLACE FUNCTION to_array (x INT)
  RETURNS ARRAY(INT) AS
  DECLARE
    arr ARRAY(INT) = CREATE_ARRAY(x+1);
  BEGIN
    FOR i IN 0..x LOOP
      arr[i] = i * i;
    END LOOP;
  RETURN arr;
END //

DELIMITER ;

SELECT num, table_col AS "SQUARE"
FROM square INNER JOIN TABLE(to_array(6))
  ON table_col = num*num ORDER BY num;

+-----+--------+
| num | SQUARE |
+-----+--------+
|   1 |      1 |
|   2 |      4 |
|   3 |      9 |
|   4 |     16 |
|   5 |     25 |
+-----+--------+

Example 4

The TABLE function can be used to expand a field in a table.

SQL

CREATE TABLE empRole(Name varchar(20), Role JSON);
INSERT INTO empRole VALUES
  ('Adam', '["Team Lead","Security Engineer"]'),
  ('Mary','["Product Manager"]');

SELECT * FROM empRole;

+-------+-----------------------------------+
| Name  | Role                              |
+-------+-----------------------------------+
| Adam  | ["Team Lead","Security Engineer"] |
| Mary  | ["Product Manager"]               |
+------+------------------------------------+

SQL

SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));

+------+---------------------+
| Name | Title               |
+------+---------------------+
| Adam | "Team Lead"         |
| Adam | "Security Engineer" |
| Mary | "Product Manager"   |
+------+---------------------+

Example 5

SELECT * 
FROM TABLE(DISTINCT [1,1,2,3]);

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+