Q Language - Joins

In q language, we have different kinds of joins based on the input tables suppped 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 prevaipng 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.