# select {#select}

Access data in a table. The select statement always returns a table.

**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

\(1\) Select column\(s\) from a table

```
select column1, column2, ... from t
select * from t //select all columns
```

where, *column1*, *column2*, ... are the column names of table t.

\(2\) Create a new column

```
select expression [as colName] from t
```

where *expression* can be an expression on a column of table t, or a constant. When *colName* is not specified:

-   If *expression* is an expression, combine the expression and the applied column name as the new column name.

-   If *expression* is a constant, use the constant value as the new column name. Note: When you select only one constant, it returns a table with one column and one row.


\(3\) Generate a column with null values only

```
select NULL [as colName] from t
```

The generated column is of VOID type. It is used as a placeholder. It does not support any calculations or operations \(including adding, inserting, or modifying\). If you append a table containing a null column to another table \(using `append!`, `insert` into or `tableInsert`\), the null column will automatically be converted to the type of the column being appended to.

Note: When you `select` only one null value, it returns a table with one column and one row.

The above three usages can be used together, such as `select *, expression as newCol1, NULL as newCol2 from t`.

If the result returned by a SQL select query is:

-   a table with one column, it is treated as a vector and can be used with the `in` keyword.

-   a table with one column and one row, it is treated as a scalar and can be used with operators such as `gt`, `ge`, `lt`, `le`, `eq`, or `ne`.


\(4\) Select column\(s\) with column series.

``` {#codeblock_mx5_zyx_2bc}
select col1...colN [as name1 ... nameN] from t
```

where `as` is an optional keyword for specifying alias.

The selected columns follow the naming rule: "prefix + numbers" \(satisfying regular expression `[a-zA-Z\_]{1,}[0-9]+`\).

-   The prefix consists of letters \(both upper and lower case\) and underscores \(\_\) and must be identical for all columns.

-   The numbering 1…N must be a continuous series of integers that satisfies `abs(1-N)` &lt;= 32768. Or use formatted numbers, such as: 0001, 0002, ..., 0010, 0011, ..., 0100, 0101, ...


## Examples {#examples}

Create a table t1 with columns timestamp, qty, price and sym.

```
sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price);
t1;
```

|timestamp|sym|qty|price|
|---------|---|---|-----|
|09:34:07|C|2200|49.6|
|09:36:42|MS|1900|29.46|
|09:36:51|MS|2100|29.52|
|09:36:59|MS|3200|30.02|
|09:32:47|IBM|6800|174.97|
|09:35:26|IBM|5400|175.23|
|09:34:16|C|1300|50.76|
|09:34:26|C|2500|50.32|
|09:38:12|C|8800|51.29|

```
select * from t1;
```

|timestamp|sym|qty|price|
|---------|---|---|-----|
|09:34:07|C|2200|49.6|
|09:36:42|MS|1900|29.46|
|09:36:51|MS|2100|29.52|
|09:36:59|MS|3200|30.02|
|09:32:47|IBM|6800|174.97|
|09:35:26|IBM|5400|175.23|
|09:34:16|C|1300|50.76|
|09:34:26|C|2500|50.32|
|09:38:12|C|8800|51.29|

The "selected" objects in the select statement don't have to be table columns. They could be variables or functions. The system will check if the "selected" object exists in the table as a column. If the answer is yes, it is treated as a table column; otherwise it is treated as a variable column or a function.

```
recordNum=1..9;
select 1..9 as recordNum, sym from t1;
```

|recordNum|sym|
|---------|---|
|1|C|
|2|MS|
|3|MS|
|4|MS|
|5|IBM|
|6|IBM|
|7|C|
|8|C|
|9|C|

```
select 3 as portfolio, sym from t1;
```

|portfolio|sym|
|---------|---|
|3|C|
|3|MS|
|3|MS|
|3|MS|
|3|IBM|
|3|IBM|
|3|C|
|3|C|
|3|C|

In the example above, we use a scalar to represent a column with same values.

Use a function in select statement:

```
def f(a):a+100;
select f(qty) as newQty, sym from t1;
```

|newQty|sym|
|------|---|
|2300|C|
|2000|MS|
|2200|MS|
|3300|MS|
|6900|IBM|
|5500|IBM|
|1400|C|
|2600|C|
|8900|C|

```
select last price from t1 group by sym;
```

|sym|last\_price|
|---|-----------|
|C|51.29|
|MS|30.02|
|IBM|175.23|

Generate a column with constants:

```
t = table(1..5 as id, `a`a`a`b`b as val);
select *, 1 as cst from t
```

|id|val|cst|
|---|---|---|
|1|a|1|
|2|a|1|
|3|a|1|
|4|b|1|
|5|b|1|

```
select *, 1 from t
```

|id|val|1|
|---|---|---|
|1|a|1|
|2|a|1|
|3|a|1|
|4|b|1|
|5|b|1|

Combine query results with keyword `in`:

```
t = table(`APPL`IBM`AMZN`IBM`APPL`AMZN as sym, 10.1 11.2 11.3 12 10.6 10.8 as val)
t;
```

|sym|val|
|---|---|
|APPL|10.1|
|IBM|11.2|
|AMZN|11.3|
|IBM|12|
|APPL|10.6|
|AMZN|10.8|

```
stk = table(1 2 3 as id, `AAPL`IBM`FB as stock);
select count(*) from t where sym in select stock from stk;
// output: 2
```

The following example generates a table containing a null column

```
t1 = table(rand(10,3) as x)
tmp=select *, NULL from t1
typestr(tmp[`NULL])
// output: VOID VECTOR
```

Then append the table tmp to t2

```
t2 = table(1..6 as x1, 1..6 as y1)
t2.append!(tmp)
t2

/* output
x1  y1
1   1
2   2
3   3
3
4
6
*/
```

Examples for column series.

\(1\) Select multiple columns and rename these columns using `as` keyword.

``` {#codeblock_avv_pzx_2bc}
t= table(`a1`a2 as sym, 2.5 1.5 as price1, 3.0 2.0 as price2, 3.5 2.5 as price3, 4.0 3.0 as price4, 4.5 3.5 as price5,  
110 200 as qty1, 120 210 as qty2, 130 230 as qty3, 140 240 as qty4, 150 260 as qty5)

select price1...price5 as p1...p5 from t
```

Output:

|p1|p2|p3|p4|p5|
|---|---|---|---|---|
|2.5|3|3.5|4|4.5|
|1.5|2|2.5|3|3.5|

\(2\) Column series are particularly useful in scenarios where multiple similar columns need to be computed or processed together. One example use case is concatenating multiple columns into a single array vector with function `fixedLengthArrayVector`.

``` {#codeblock_a5r_szx_2bc}
select fixedLengthArrayVector(price1...price5) as priceArray, fixedLengthArrayVector(qty1...qty5) as qtyArray from t
```

Output:

|priceArray|qtyArray|
|----------|--------|
|\[2.5000,3.0000,3.5000,4.0000,4.5000\]|\[110,120,130,140,150\]|
|\[1.5000,2.0000,2.5000,3.0000,3.5000\]|\[200,210,230,240,260\]|

\(3\) Calculations between column series are performed with the same rules as between vectors. In the following example, `price1*qty1`*, ... ,*`price5*qty5` will be calculated:

``` {#codeblock_lhv_5zx_2bc}
select (price1...price5) * (qty1...qty5)  as amount1...amount5 from t
```

Output:

|amount1|amount2|amount3|amount4|amount5|
|-------|-------|-------|-------|-------|
|275|360|455|560|675|
|300|420|575|720|910|

