FROM

The FROM clause of a SELECT has the form

FROM <table-expr>

where a <table-expr> represents data sources (like files, API endpoints, or database pools), table subqueries, pipe queries, or joins.

Table Expressions

A table expression <table-expr> has one of the following forms:

<entity> [ ( <options> ) ] [ <as> ]
<named-query> [ <as> ]
( <query> ) [ <as> ]
<join-expr>
( <table-expr> )

<entity> is defined as in the pipe form of from, namely one of

• a text entity representing a file, URL, or pool name,
• an f-string representing a file, URL, or pool name,
• a glob matching files in the local file system or pool names in a database, or
• a regular expression matching pool names

<options> are the entity options as in pipe from.

<named-query> is the name of a common-table expression (CTE) defined in a WITH clause or a declared query.

<query> is any query inclusive of SQL operators or pipe operators.

<join-expr> is any JOIN operation, which is defined to recursively operate upon any <table-expr> defined here.

Any <table-expr> may be parenthesized to control precedence and evaluation order.

Table Aliases

The table expressions above that represent data-source entities and table subqueries may be bound to a table alias with the option <as> clause of the form

[ AS ] <alias>

where the AS keyword is optional and <alias> has the form

<table> [ ( <column> [ , <column> ... ] ) ]

<table> and <column> are identifiers naming a table or a table and the columns of the indicated table and an optional parenthesized list of columns positionally specifies the column names of that table.

Joined expression and parenthesized table expressions cannot be assigned aliases as the relational scope produced by such expression is comprised of their constituent table names and columns.

Input Table

A FROM clause is a component of SELECT that identifies the query’s input data to create the input table for SELECT.

The input table is accessed via a namespace comprised of table and column references that may then appear in the various expressions appearing throughout the query.

This namespace is called a relational scope and the FROM clause creates the input scope for SELECT.

The name space consists of the table names and aliases (and their constituent columns) created by the initial FROM clause and any JOIN clauses that appear. Any tables that are defined in table subqueries in the FROM clause are not part of the input scope.

Note

The SQL FROM clause is similar to the pipe form of the from operator but

• uses relational scoping instead of pipe scoping,
• allows the binding of table aliases to relational data sources, and
• can be combined with JOIN clauses to implement relational joins.

File Examples

Source structured data from a local file

echo '{"greeting":"hello world!"}' > hello.json
super -s -c 'SELECT greeting FROM hello.json'

{greeting:"hello world!"}

Translate some CSV into Parquet and query it

echo 'Name,Email,Phone Number,Address
John Doe,john.doe@example.com,123-555-1234,"123 Example Address, City, State"
Jane Smith,jane.smith@example.com,123-555-5678,"456 Another Lane, Town, State"' > example.csv
super -f parquet -o example.parquet example.csv
super -s -c 'SELECT collect("Phone Number") as numbers FROM example.parquet'

{numbers:["123-555-1234","123-555-5678"]}

HTTP Example

Source data from a URL

super -s -c "SELECT name FROM https://api.github.com/repos/brimdata/super"

{name:"super"}

F-String Example

Read from dynamically defined files and add a column

echo '{a:1}{a:2}' > a.sup
echo '{b:3}{b:4}' > b.sup
echo '"a.sup" "b.sup"' | super -s -c "
SELECT this, coalesce(a,b)+1 AS c
FROM f'{this}'
" -

{that:{a:1},c:2}
{that:{a:2},c:3}
{that:{b:3},c:4}
{that:{b:4},c:5}

Database Examples

Note

The SuperDB database will soon support super-structured types, which are required for SQL compatibility. Currently, database queries should be done with the pipe form of the from operator. SQL examples utilizing a SuperDB database will be documented here in a future version of SuperDB.