The ability to join two or more tables (and/or views) easily is a major advantage that distinguishes relational systems from non-relational systems. The join capability simplifies the task of retrieving data from different tables to build a single result table holding all the necessary data.
You implement this join by forming a query which retrieves data from more than one table. Your SELECT statement includes columns (qualified by table name) from two or more tables. The FROM clause of your query names the tables used as qualifiers in the select-statement.
All the columns specified in the SELECT form the result table. Therefore, if you specify the column CUST_NO from the CUSTOMERS table and the column CUST_NO from the ORDERS table, your result table includes two CUST_NO columns, each qualified by the original table name.
You can reference up to 20 tables in a FROM clause when you are performing a join. For example, if a view is based on five tables, you can name that view in the FROM clause, and up to fifteen other tables.
There is a special case where one or more tables are not needed and can be eliminated from a query, so that the query executes more efficiently. This case usually occurs when a view is defined that includes optional tables, but the particular use of the view does not require all the tables.
Because it is an optional table, it is accessed using a LEFT JOIN, which does not change the number of rows returned from the primary table on the left side of the join when there is no matching row. When the join is on a unique/primary key, the most rows that can be found is one, and therefore the count of rows is also not changed when the single matching row is found. Finally, if no columns in the optional table are referenced in the query (other than the left join condition, of course), this optional table has been accessed and not used. It is, therefore, eliminated from the query for the purpose of better performance.
Example:
CREATE VIEW INSURANCE AS SELECT * FROM ACCOUNT T1 LEFT JOIN CARINS T2 ON T1.COL1 = T2.COL1 T1 LEFT JOIN HOMEINS T3 ON T1.COL1 = T3.COL1 T1 LEFT JOIN LIFEINS T4 ON T1.COL1 = T4.COL1 ; SELECT T1.NAME, T2.LICENSE FROM INSURANCE WHERE T2.VIN = :HOSTVAR ;
In this example, where COL1 is the Primary key in all tables, only the ACCOUNT and CARINS tables are referenced. It doesn’t matter if there is a single matching HOMEINS or LIFEINS row found, and therefore these tables are eliminated from the query.
Also see Left Outer Joins.
Example
The following example joins the CUSTOMERS and ORDERS tables. This example is taken from DBCOBSQA, a sample program available on the installation tape.
Note: You can only join tables which have the same security type, that is to say, either the CA Datacom/DB External Security Model or the SQL Security Model. See the CA Datacom Security Reference Guide for more information about Security Models.
Problem
List the customer number, name and order ID for those customers who
have outstanding orders.
Solution
.
.
(COBOL statements)
.
.
1 EXEC SQL
2 DECLARE CUSTORD CURSOR FOR
3 SELECT CUSTOMERS.CUST_NO, ORD_ID, NAME
4 FROM CUSTOMERS, ORDERS
5 WHERE CUSTOMERS.CUST_NO = ORDERS.CUST_NO
6 ORDER BY CUSTOMERS.CUST_NO
7 END-EXEC
.
.
(COBOL statements)
.
.
Line 3
The SELECT statement specifies the columns to be selected from each table. Column names which are the same in each table are qualified by the table name, such as CUSTOMERS.CUST_NO. ORD_ID is not qualified since it exists only in the ORDERS table and NAME is not qualified since it exists only in the CUSTOMERS table.
Line 4
Both tables are named in the FROM clause, indicating that the result table includes the retrieved data from each table.
Line 5
The WHERE clause specifies that the value of the CUST_NO column in each table must be equal to be selected for the result table. If a customer does not have an outstanding order, then the CUST_NO value does not appear in the ORDERS, nor in the result of the join. The comparison is possible since the columns have comparable data types. For comparison rules, see Basic Operations (Assignment and Comparison).
Line 6
The ORDER BY clause specifies that the rows in the result table be in ascending order according to the customer number.
Sample Output
Following is the report produced by running DBCOBSQA.
----------------------------------------------- CURRENT ORDERS ---------------------------------------------PAGE 1 CUSTOMER NO CUSTOMER NAME --------------------------------------------------------------------------------------------------------------------- 0030 CANNON TOOLS CO 0230 CHEMICAL MUTUAL 1210 LINGBERGH INDUSTRIES 1210 LINGBERGH INDUSTRIES 1450 UNION TRANSPORTATION 1630 MARBURY MATERIALS 1850 TECH CASTLE RESEARCH 1890 FIRST STREET BANK CORP 2050 TRANSAMERICAN PUBLISHING END OF REPORT
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|