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.
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.
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.
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.
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.
If you have existing tables with different column names you can rename them using xcol, or if you want to change keys using xkey.