`FROM` clause

The FROM clause specifies the source of the data that the rest of the query should work with. Logically, the FROM clause is the place where the execution of the query begins.

A FROM clause can contain a single table, a combination of multiple tables joined using the JOIN clause, or another SELECT query in a subquery node.

Tengri also has an optional FROM-first syntax that allows you to execute a query without a SELECT statement.

Syntax

SELECT ...
FROM objectReference [ JOIN objectReference [ ... ] ]
[ ... ]

Where:

objectReference ::=
   {
      [<namespace>.]<object_name>
           [ AT | BEFORE ( <object_state> ) ]
           [ CHANGES ( <change_tracking_type> ) ]
           [ MATCH_RECOGNIZE ]
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
     | <table_function>
           [ PIVOT | UNPIVOT ]
           [ [ AS ] <alias_name> ]
     | ( VALUES (...) )
     | ( <subquery> )
           [ [ AS ] <alias_name> ]
     | DIRECTORY( @<stage_name> )
   }

Parameters

[<namespace>.]<object_name>
Specifies the name of the object (table or view) being queried.

<table_function>
Specifies the system table function, UDF table function, or class method to call in the FROM clause.

VALUES
The VALUES clause may contain literal values or expressions to be used in the FROM clause. This expression may also contain table and column aliases (not shown in the diagram above).

<subquery>
Subquery in the FROM clause.

DIRECTORY( @stage_name )
Specifies the name of the stage that includes the directory table.

[ AS ] <alias_name>
Indicates the name given for the object to which it refers. Can be used with any other subqueries in a FROM clause. The AS operator may be omitted.

JOIN
Indicates the execution of a join between two (or more) tables (or views or table functions). The join can be internal external or of another type. The join can use the JOIN keyword or an alternative supported join syntax.
For a detailed description, see JOIN clause.

Examples

Select all columns from the table named my_table:
```
SELECT *
FROM my_table;
```
Select all columns from the table using FROM-first syntax:
```
FROM my_table
SELECT *;
```
Select all columns using FROM-first syntax and omitting the SELECT clause:
```
FROM my_table;
```
Select all columns from a table named my_table through the alias mt:
```
SELECT mt.*
FROM my_table mt;
```
Using the prefix alias:
```
SELECT mt.*
FROM mt: my_table;
```
Select all columns from the my_table table in the my_schema schema:
```
SELECT *
FROM my_schema.my_table;
```
Select column i from table function range, where the first column of the range function is renamed to i:
```
SELECT t.i
FROM range(1000) AS t(i);
```
Select all columns from the subquery:
```
SELECT *
FROM (SELECT * FROM my_table);
```

Merge the two tables:

SELECT *
FROM my_table
JOIN other_table
    ON my_table.key = other_table.key;

For a detailed description, see JOIN clause.

Syntax `FROM`-first

Tengri supports the FROM-first syntax, i.e., it allows you to place the FROM clause before the SELECT clause or omit the SELECT clause entirely. Let’s demonstrate it with an example:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');

Syntax `FROM`-first with a `SELECT` clause

The following expression demonstrates the use of the FROM-first syntax:

FROM capitals
SELECT *;

+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

It’s equivalent to:

SELECT *
FROM capitals;

+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

Syntax `FROM`-first without a `SELECT` clause

The following expression demonstrates the optional nature of the SELECT clause:

FROM capitals;

+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

This is also equivalent to a full enquiry:

SELECT *
FROM capitals;

+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

FROM clause