Join Examples | Vantage Analytics Library - Join Examples

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:

call td_analyze('join','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id, income},
	{cc_rev};
');

SQL:

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"
;

Results:

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:

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;
‘);

SQL:

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”);

Results: 747 rows

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:

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 };
');

SQL:

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"
;

Results:

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

Join Examples | Vantage Analytics Library - Join Examples - Vantage Analytics Library

Vantage Analytics Library User Guide

inner join

output table

leftouter join