Listing Multiple Tables
Go Up to Listing Tables to Search with FROM
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:
- 1. The name of each referenced column is unique across all tables.
- 2. 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,
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
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
FROMclause and substituted for them in other
SELECTstatement clauses when qualifying column names. Even when joins are not involved, assigning and using correlation names can reduce the length of complex queries.