Listing Tables to Search with FROM
Go Up to Understanding Data Retrieval with SELECT
Contents
The FROM
clause is required in a SELECT
statement. It identifies the tables, views, or select procedures from which data is to be retrieved. The complete syntax of the FROM
clause is:
FROM table | view | procedure [alias] [, table | view | procedure [alias] ...]
There must be at least one table, view, or select procedure name following the FROM keyword. When retrieving data from multiple sources, each source must be listed, assigned an alias, and separated from the next with a comma. For more information about select procedures, see Working with Stored Procedures.
Listing a Single Table or View
The FROM
clause in the following SELECT
specifies a single table, EMPLOYEE
, from which to retrieve data:
EXEC SQL SELECT LAST_NAME, FIRST_NAME, SALARY INTO :lanem, :fname, :salary FROM EMPLOYEE WHERE LNAME = 'Smith';
Use the same INTO
clause syntax to specify a view or select procedure as the source for data retrieval instead of a table. For example, the following SELECT
specifies a select procedure, MVIEW
, from which to retrieve data. MVIEW
returns information for all managers whose last names begin with the letter “M,” and the WHERE
clause narrows the rows returned to a single row where the DEPT_NO
column is 430:
EXEC SQL SELECT DEPT_NO, LAST_NAME, FIRST_NAME, SALARY INTO :lname, :fname, :salary FROM MVIEW WHERE DEPT_NO = 430;
For more information about select procedures, see Working with Stored Procedures.
Listing Multiple Tables
To retrieve data from multiple tables, views, or select procedures, include all sources in the FROM
clause, separating sources from one another by commas.
There are two different possibilities to consider when working with multiple data sources:
- The name of each referenced column is unique across all tables.
- The names of one or more referenced columns exist in two or more tables.
In the first case, just use the column names themselves to reference the columns. For example, the following query returns data from two tables, DEPARTMENT
, and EMPLOYEE
:
EXEC SQL SELECT DEPARTMENT, DEPT_NO, LAST_NAME, FIRST_NAME, EMP_NO INTO :dept_name, :dept_no, :lname, :fname, :empno FROM DEPARTMENT, EMPLOYEE WHERE DEPT_NO = 'Publications' AND MNGR_NO = EMP_NO;
In the second case, column names that occur in two or more tables must be distinguished from one another by preceding each column name with its table name and a period in the SELECT
clause. For example, if an EMP_NO
column exists in both the DEPARTMENT
and EMPLOYEE
then the previous query must be recast as follows:
EXEC SQL SELECT DEPARTMENT, DEPT_NO, LAST_NAME, FIRST_NAME, EMLOYEE.EMP_NO INTO :dept_name, :dept_no, :lname, :fname, :empno FROM DEPARTMENT, EMPLOYEE WHERE DEPT_NO = 'Publications' AND DEPARTMENT.EMP_NO = EMPLOYEE.EMP_NO;
For more information about the SELECT
clause, see Listing Columns to Retrieve with SELECT.
For queries involving joins, column names can be qualified by correlation names, brief alternate names, or aliases, that are assigned to each table in a
FROM
clause and substituted for them in other SELECT
statement clauses when qualifying column names. Even when joins are not involved, assigning and using correlation names can reduce the length of complex queries.Declaring and Using Correlation Names
A correlation name, or alias, is a temporary variable that represents a table name. It can contain up to 31 alphanumeric characters, dollar signs ($), and underscores (_), but must always start with an alphabetic character. Using brief correlation names reduces typing of long queries. Correlation names must be substituted for actual table names in joins, and can be substituted for them in complex queries.
A correlation name is associated with a table in the FROM
clause; it replaces table names to qualify column names everywhere else in the statement. For example, to associate the correlation name, DEPT
with the DEPARTMENT
table, and EMP
, with the EMPLOYEES
table, a FROM
clause might appear as:
FROM DEPARTMENT DEPT, EMPLOYEE EMP
Like an actual table name, a correlation name is used to qualify column names wherever they appear in a SELECT
statement. For example, the following query employs the correlation names, DEPT
, and EMP
, previously described:
EXEC SQL SELECT DEPARTMENT, DEPT_NO, LAST_NAME, FIRST_NAME, EMLOYEE.EMP_NO INTO :dept_name, :dept_no, :lname, :fname, :empno FROM DEPARTMENT DEPT, EMPLOYEE EMP WHERE DEPT_NO = 'Publications' AND DEPT.EMP_NO = EMP.EMP_NO;
For more information about the SELECT
clause, see Listing Columns to Retrieve with SELECT.