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, 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.