Filter
Top Products
Chapter 5 Joining Multiple Tables and
Manipulating data
This chapter describes how to join multiple tables and manipulate data when data is fetched from data bases. For
example, suppose that to check on the status of orders for products, data for CUSTOMER, PRODUCT, and
ORDERQTY is fetched from the stock inventory data base. This data is to be fetched from the ORDER table, but data
for PRODUCT is discovered to be unavailable there. However, PRODUCT data is available in the STOCK table. As
shown in Figure: Relationship between STOCK table, ORDER table, and COMPANY table, the STOCK table and the
ORDER table are related through ITMNO in the STOCK table and PRODNO in the ORDER table. Therefore, these
two tables can be joined and data fetched.
5.1 Deriving a New Table from Multiple Tables
5.2 Specifying Conditions to Join Tables
5.3 Manipulating Data Using Aliases in Tables
5.4 Joining a Table to Itself and Manipulating Data
5.5 Obtaining the Aggregate for Rows from Multiple Tables
5.1 Deriving a New Table from Multiple tables
To join multiple tables and fetch data, specify the table names, separated by commas, in the FROM clause of the table
expression. When multiple table names are specified, a new table is derived from the result of the FROM clause. Data
can then be fetched from the derived table. The kind of table derived when multiple table names are specified is as
follows:
The following is an example of fetching data from multiple tables:
Example:
In this example, table names TBL1 and TBL2 are specified in the FROM clause and SCM is the
schema name for these tables.
SELECT ... FROM SCM.TBL1, SCM.TBL2
57