inner join
In the following example, two tables – customer and customer_analysis – are joined with the default join style, inner join. The tables are joined at the cust_id column because that is the primary index of both tables (not shown in the CALL statement), serving as default join values.
call td_analyze('join',' database= val_source, val_source; tablename= customer, customer_analysis; columns= {cust_id, income}, {cc_rev}; ');
SELECT "_joinTable_0"."cust_id " ,"_joinTable_0"."income" ,"_joinTable_1"."cc_rev" FROM "val_source"."customer" AS "_joinTable_0" INNER JOIN "val_source"."customer_analysis" AS "_joinTable_1" ON "_joinTable_0"."cust_id" = "_joinTable_1"."cust_id" ;
cid | income | cc_rev |
---|---|---|
1362480 | 50890 | 75 |
1362481 | 20855 | 0 |
1362484 | 10053 | 12 |
1362485 | 22690 | 30 |
1362486 | 10701 | 0 |
1362487 | 6605 | 13 |
1362488 | 7083 | 0 |
1362489 | 55888 | 82 |
1362492 | 40252 | 54 |
1362496 | 0 | 0 |
… | … | … |
Total results: 747 rows |
output table
In the following example, two tables – customer and customer_analysis – are joined in the same way as in the previous example, but create an output table instead. The index parameter is required when outputstyle=table or outputstyle=volatile, specifying the Primary Index of the output table.
call td_analyze(‘join’,’ database= val_source, val_source; tablename= customer, customer_analysis; columns= {cust_id, income}, {cc_rev}; outputstyle=table; outputdatabase=val_results; outputtablename=myjoin; index=cust_id; ‘);
DROP TABLE “val_results”.”myjoin”; CREATE SET TABLE “val_results”.”myjoin”, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS ( SELECT “_joinTable_0”.”cust_id” ,”_joinTable_0”.”income” ,”_joinTable_1”.”cc_rev” FROM “val_source”.” Customer” AS “_joinTable_0” INNER JOIN “val_source”.”customer_analysis” AS “_joinTable_1” ON “_joinTable_0”.”cust_id” = “_joinTable_1”.”cust_id” ) WITH DATA PRIMARY INDEX (“cust_id”);
cid | income | cc_rev |
---|---|---|
1362480 | 50890 | 75 |
1362481 | 20855 | 0 |
1362484 | 10053 | 12 |
1362485 | 22690 | 30 |
1362486 | 10701 | 0 |
1362487 | 6605 | 13 |
1362488 | 7083 | 0 |
1362489 | 55888 | 82 |
1362492 | 40252 | 54 |
1362496 | 0 | 0 |
… | … | … |
Total results: 747 rows |
leftouter join
In the following example, four tables – customer, checking_acct, credit_acct and savings_acct – are joined with a left outer join. Join columns are specified in case the columns are used for a complex view or for when a primary index of a table does not form a good key. (From the InnerJoin example, the primary index of each input table is cust_id, so it is not necessary to include a joincolumns parameter here, but it is included for tutorial purposes.)
call td_analyze('join',' joinstyle=leftouter; database= val_source, val_source, val_source, val_source; tablename= customer, checking_acct, credit_acct, savings_acct; columns= {cust_id }, {ending_balance/chk_bal}, {ending_balance/crd_bal}, {ending_balance/sav_bal}; joincolumns= {cust_id }, {cust_id }, {cust_id }, {cust_id }; ');
SELECT "_joinTable_0"."cust_id" ,"_joinTable_1"."ending_balance" AS "chk_bal" ,"_joinTable_2"."ending_balance" AS "crd_bal" ,"_joinTable_3"."ending_balance" AS "sav_bal" FROM "val_source"." customer" AS "_joinTable_0" LEFT OUTER JOIN "val_source"."checking_acct" AS "_joinTable_1" ON "_joinTable_0"."cust_id" = "_joinTable_1"."cust_id" LEFT OUTER JOIN "val_source"."credit_acct" AS "_joinTable_2" ON "_joinTable_0"."cust_id" = "_joinTable_2"."cust_id" LEFT OUTER JOIN "val_source"."savings_acct" AS "_joinTable_3" ON "_joinTable_0"."cust_id" = "_joinTable_3"."cust_id" ;
cust_id | chk_bal | crd_bal | sav_bal |
---|---|---|---|
1362480 | 86.86 | 20.00 | 1187.97 |
1362481 | |||
1362484 | 95.32 | 369.72 | 2122.17 |
1362485 | 45.49 | 1100.00 | |
1362486 | 3312.27 | 116.24 | |
1362487 | 1401.21 | 1548.23 | 12.89 |
1362488 | 122.42 | ||
1362489 | 125.43 | 20.00 | 373.65 |
1362492 | 582.82 | 2000.00 | |
1362496 | 251.06 | ||
… | … | … | … |
Total results: 747 rows |