”;
In q language, we have different kinds of joins based on the input tables supplied and the kind of joined tables we desire. A join combines data from two tables. Besides foreign key chasing, there are four other ways to join tables −
- Simple join
- Asof join
- Left join
- Union join
Here, in this chapter, we will discuss each of these joins in detail.
Simple Join
Simple join is the most basic type of join, performed with a comma ‘,’. In this case, the two tables have to be type conformant, i.e., both the tables have the same number of columns in the same order, and same key.
table1,:table2 / table1 is assigned the value of table2
We can use comma-each join for tables with same length to join sideways. One of the tables can be keyed here,
Table1, `Table2
Asof Join (aj)
It is the most powerful join which is used to get the value of a field in one table asof the time in another table. Generally it is used to get the prevailing bid and ask at the time of each trade.
General format
aj[joinColumns;tbl1;tbl2]
For example,
aj[`sym`time;trade;quote]
Example
q)tab1:([]a:(1 2 3 4);b:(2 3 4 5);d:(6 7 8 9)) q)tab2:([]a:(2 3 4);b:(3 4 5); c:( 4 5 6)) q)show aj[`a`b;tab1;tab2] a b d c ------------- 1 2 6 2 3 7 4 3 4 8 5 4 5 9 6
Left Join(lj)
It’s a special case of aj where the second argument is a keyed table and the first argument contains the columns of the right argument’s key.
General format
table1 lj Keyed-table
Example
q)/Left join- syntax table1 lj table2 or lj[table1;table2] q)tab1:([]a:(1 2 3 4);b:(2 3 4 5);d:(6 7 8 9)) q)tab2:([a:(2 3 4);b:(3 4 5)]; c:( 4 5 6)) q)show lj[tab1;tab2] a b d c ------------- 1 2 6 2 3 7 4 3 4 8 5 4 5 9 6
Union Join (uj)
It allows to create a union of two tables with distinct schemas. It is basically an extension to the simple join ( , )
q)tab1:([]a:(1 2 3 4);b:(2 3 4 5);d:(6 7 8 9)) q)tab2:([]a:(2 3 4);b:(3 4 5); c:( 4 5 6)) q)show uj[tab1;tab2] a b d c ------------ 1 2 6 2 3 7 3 4 8 4 5 9 2 3 4 3 4 5 4 5 6
If you are using uj on keyed tables, then the primary keys must match.
”;