The FROM clause lists the references (tables, views, and subqueries) that data is selected from. Drill expands the traditional concept of a “table reference” in a standard SQL FROM clause to refer to files and directories in a local or distributed file system.
The FROM clause supports the following syntax:
... FROM table_expression [, …]
Includes one or more table_references and is typically followed by the WHERE, GROUP BY, ORDER BY, or HAVING clause.
tableReference: with_subquery_table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] | table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] | ( subquery ) [ AS ] alias [ ( column_alias [, ...] ) ] | <join_clause> | [ LATERAL ] [<lateral_join_type>] <lateral_subquery> [ON TRUE] join_clause: tableReference <join_type> tableReference [ON <join_condition>] lateral_subquery: <unnest_table_expr> | ( SELECT_clause FROM <unnest_table_expr> [,...] ) lateral_join_type: [INNER] JOIN LEFT [OUTER] JOIN unnest_table_expr: UNNEST '(' expression ')' [AS] <alias_table_name>(<alias_column_name>)
A table defined by a subquery in the WITH clause.
Name of a table or view. In Drill, you can also refer to a file system directory or a specific file.
A temporary alternative name for a table or view that provides a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. You must supply an alias for a table derived from a subquery. Aliases might be required for querying nested JSON. Aliases are definitely required to resolve ambiguous references, such as using the name "user" to query the Drill profiles. Drill treats "user" as a function in this case, and the returns unexpected results. If you use a table alias, Drill treats "user" as a column identifier, and the query returns expected results. The AS keyword is always optional. Drill does not support the GROUP BY alias.
A temporary alternative name for a column in a table or view. You can use named column aliases in the SELECT list to provide meaningful names for regular columns and computed columns, such as the results of aggregate functions. You cannot reference column aliases in the following clauses:
Because Drill works with schema-less data sources, you cannot use positional aliases (1, 2, etc.) to refer to SELECT list columns, except in the ORDER BY clause.
A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or alias, though an alias is not required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause is not also specified. You can use the following subquery operators in Drill queries. These operators all return Boolean results.
In general, correlated subqueries are supported. EXISTS and NOT EXISTS subqueries that do not contain a correlation join are not yet supported.
Identifies the tables with the data you want to join, the type of join to be performed on the tables, and the conditions on which to join the tables. Starting in Drill 1.14, Drill supports lateral joins.
NOTE: See Lateral Join for additional information and examples of queries with lateral joins.
Keyword that represents a lateral join. A lateral join is essentially a foreach loop in SQL. A lateral join combines the results of the outer query with the results of a lateral subquery. When you use the UNNEST relational operator, Drill infers the LATERAL keyword.
A lateral subquery is like correlated subqueries except that you use a lateral subquery in the FROM clause instead of the WHERE clause. Also, lateral subqueries can return any number of rows; correlated subqueries return exactly one row.
References the table produced by the UNNEST relational operator. UNNEST converts a collection to a relation. You must use the UNNEST relational operator with LATERAL subqueries when a field contains repeated types, like an array of maps. You must also indicate an alias for the table produced by UNNEST.
The type of join used with the lateral subquery. Lateral subqueries support [INNER] JOIN and LEFT [OUTER] JOIN, for example:
...FROM table1 LEFT OUTER JOIN LATERAL (select a from t2) ON TRUE;
If you do not indicate the join type, Drill infers an INNER JOIN.
The join condition when the results of a lateral subquery are joined with fields in rows of the table referenced. This condition is implicit. You do not have to include the condition in the query.
Specifies the type of join between two tables in the join clause when you do not use a lateral join. The join clause supports the following join types:
[INNER] JOIN LEFT [OUTER] JOIN RIGHT [OUTER] JOIN FULL [OUTER] JOIN
A type of join specification where the joining columns are stated as a condition that follows the ON keyword, for example:
homes join listing on homes.listid=listing.listid and homes.homeid=listing.homeid
Return matching rows only, based on the join condition or list of joining columns.
Return all of the rows that the equivalent inner join would return plus non-matching rows from the "left" table, "right" table, or both tables. The left table is the first-listed table, and the right table is the second-listed table. The non-matching rows contain NULL values to fill the gaps in the output columns.
A lateral join is essentially a foreach loop in SQL. A lateral join is represented by the keyword LATERAL with an inner subquery in the FROM clause. See Lateral Join.
- Joined columns must have comparable data types.
- A join with the ON syntax retains both joining columns in its intermediate result set.
0: jdbc:drill:zk=local> SELECT tbl1.id, tbl1.type FROM dfs.`/Users/brumsby/drill/donuts.json` AS tbl1 JOIN dfs.`/Users/brumsby/drill/moredonuts.json` as tbl2 ON tbl1.id=tbl2.id; +------------+------------+ | id | type | +------------+------------+ | 0001 | donut | +------------+------------+ 1 row selected (0.395 seconds)