FROM Clause

The FROM clause specifies the target table(s) for the SELECT, UPDATE, and DELETE statements.

Syntax

FROM table [[AS] alias]
FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria
FROM table1 CROSS JOIN table2
FROM (subquery) [AS] alias
FROM TABLE(subquery) [AS] alias
FROM LATERAL(subquery) [AS] alias
FROM table1 JOIN /*+ MAKEDEP */ table2 ON join-criteria
FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join-criteria
FROM /*+ MAKEIND */ table1 JOIN table2 ON join-criteria
FROM /*+ NO_UNNEST */ vw1 JOIN table2 ON join-criteria
FROM table1 left outer join /*+ optional */ table2 ON join-criteria
FROM ARRAYTABLE...
FROM JSONTABLE...
FROM OBJECTTABLE...
FROM TEXTTABLE...
FROM XMLTABLE...
FROM ( SELECT ...
FROM source_table(subquery) [AS] alias UNPIVOT (value_column FOR pivot_column IN (first_header_name, ..., last_header_name) ) [AS] unpivot_alias

FROM Clause Hints

MAKEIND, MAKEDEP, and MAKENOTDEP are hints used to control dependent join behaviour. They should only be used when the optimizer does not choose the most optimal plan based on query structure, metadata, and costing information. The hints may appear in a comment that proceeds the FROM clause. The hints can be specified against any FROM clause, not just a named table.

Be sure that there is no whitespace between /* and + when using e.g. /*+ MAKEDEP */;
Configure your SQL client not to remove multi-line comments ( in Squirrel: Session -> Session Properties -> SQL -> Remove multi-line comment ( /* ... */ )

NO_UNNEST can be specified against a subquery from clause or view to instruct the planner not to merge the nested SQL in the surrounding query - also known as view flattening. This hint only applies to the CData Virtuality Server planning and is not passed to source queries. NO_UNNEST may appear in a comment that proceeds the FROM clause.

Joining Stored Procedures, Nested Table References, and Lateral Joins

Join with a procedure can be defined directly or as a special case of joining with subquery, see examples below:

SQL

SELECT * FROM t1, proc() t2;; /*normal syntax*/

SELECT * FROM t1, (CALL proc()) t2;; /* subquery syntax */

One special case is when output from a table needs to be passed to the procedure on the other side of the join as a parameter. In this case, nested table syntax is used.

Nested tables may appear in the FROM clause with the TABLE keyword. They are an alternative to using a view with normal join semantics. The columns projected from the command contained in the nested table may be used just like any of the other FROM clause projected columns in join criteria, the WHERE clause, etc.

A nested table may have correlated references to the preceding FROM clause column references as long as INNER and LEFT OUTER joins are used. This is especially useful in cases where the nested expression is a procedure or function call.

Here is a valid example:

SQL

SELECT * FROM t1, TABLE(CALL proc(t1.x)) t2

And here is an invalid example, since t1 appears after the nested table in the FROM clause:

SQL

SELECT * from TABLE(CALL proc(t1.x)) t2, t1

Using a correlated nested table may result in multiple executions of the table expression - once for each correlated row.

The CData Virtuality Server also allows the ANSI standard keyword LATERAL in place of the TABLE keyword, which is more typically used with query expression subqueries:

SQL

SELECT * FROM t1, LATERAL(SELECT col1 FROM t2 WHERE t2.col2 = t1.x) t2