Listing Tables to Search with FROM

Go Up to Understanding Data Retrieval with SELECT

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.

Important:
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;