New York kdb Training 2019
Spaces still available. Register now to learn Kdb+.

QSQL Joins a Visual Explanation

Sql joins allow pulling corresponding data from one table, onto another to give a combined result table with columns from both. Most kdb joins rely on column names corresponding (rather than standard sql's more verbose explicit naming).

Example Tables

In the below descriptions tables beginning with "t" signifies a table without any key columns, "kt" signifies a keyed table.

lj - Left Join

The format of lj is: t lj kt where t is your source table and kt is your lookup table that MUST be keyed. Lj - left join - means for each row in table t, try to look up corresponding values in keyed-table kt, where there is no match use nulls. The columns used for mathing are the key columns of kt. i.e. The key columns of kt, must appear in t and their column names MUST match exactly.

Example of an ij inner join in qsql

Here you can see for each row of t there is one row in the result, including row 4 which did not have a match for `DBK in the stock table so has been filled with nulls.

Note:

  • Where a lookup table contains non-key columns with the same name as existing columns, the matched columns overwrite the original value.
  • Where multiple matches are possible (duplicate keys in keyed table), the first match is always taken.

pj - Plus Join

t pj kt Same principle as lj, but existing values are added to where column names match.
Pj - plus join - means for each row in table t, try to look up corresponding values in keyed-table kt, where there are matching numeric columns add their values.

Example of pj plus join in qsql

ij - Inner Join

t ij kt Where matches occur between t and kt on primary key columns, update or add that column. Non-matches are not returned in the result. The columns used for matching are the key columns of kt. i.e. The key columns of kt, must appear in t and their column names MUST match exactly.

Example of an ij inner join in qsql

As you can see the result has one less row than there was in trade, row 4 `DBK did not have a match by key lookup in the stock table so was dropped from the result.

Different inner join than standard sql

Notice above that we said the join returns ONLY the first match from the lookup table. This is different than standard SQL which returns the cartesian join of all matches. The code below showns how we could replicate an SQL standard inner join:

ej - Equi Join

ej[`sym; t; kt] ej - Equi Join - Exact same as ij inner join but allows specifying the column names.

uj - Union Join

uj - Union-Join all rows/columns from two tables, upserting when keyed, appending when unkeyed or no existing match found.

Unkeyed Tables: t uj t2 append, filling non-common columns with nulls.

Example of union join on unkeyed tables

Notice: Columns with common names are now one column in the result. However columns that occurred in only one table are included in the result, however where no values existed they are filled with nulls.

Keyed Tables: kt1 uj kt2 update existing records in kt1 with matches from kt2, append any non-matches.

Example of union join on keyed tables

If you have existing tables with different column names you can rename them using xcol, or if you want to change keys using xkey.