FROM Clause
The FROM clause specifies the source tables and views from which data is to be read. The specified tables and views must exist at the time the query is issued. The from_source parameter can be:
• One or more tables or views, specified using the following syntax:
[schema.]table [[AS] corr_name]
where table is the name of a table, view, or synonym.
• A join between two or more tables or views, specified using the following syntax:
source join_type JOIN source ON search_condition
or
source join_type JOIN source USING (column {, column})
or
source CROSS JOIN source
• A derived table specified using the following syntax:
(select_stmt) corr_name [(column_list)]
where select_stmt is a SELECT statement with no ORDER BY clause, corr_name is a mandatory correlation name, and column_list is an optional list of override names for the columns in the SELECT list of the select_list.
• A table procedure specified using the following syntax:
proc_name ([param_name=]param_spec {,[param_name=]param_spec})
A maximum of 126 tables can be specified in a query, including the tables in the FROM list, tables in subselects, and tables and views resulting from the expansion of the definitions of any views included in the query.
Specifying Tables and Views
The syntax rules for specifying table names in queries also apply to views.
To select data from a table you own, specify the name of the table. To select data from a table you do not own, specify schema.table, where schema is the name of the user that owns the table. However, if the table is owned by the DBA, the schema qualifier is not required. You must have the appropriate permissions to access the table (or view) granted by the owner.
A correlation name can be specified for any table in the FROM clause. A correlation name is an alias (or alternate name) for the table. For example:
select... from employees e, managers m...
The preceding example assigns the correlation name “e” to the employees table and “m” to the managers table. Correlation names are useful for abbreviating long table names and for joining a table to itself.
If you assign a correlation name to a table, you must refer to the table using the correlation name. For example:
Correct:
select e.name, m.name
from employees e, managers m...
Incorrect:
select employees.name, managers.name
from employees e, managers m...
Last modified date: 11/09/2022