Joins

Overview

q has a family of join operators, each built for one job. KX groups them in two kinds:

• Keyed joins match rows on the key column(s) of the right-hand table: lj, ij, pj, uj and ej.
• As-of joins match rows by time. For each row on the left, they look for the value that was current at that moment, or aggregate the values in a time window: aj, aj0, asof, wj and wj1.

There is also plain Join, ,, which appends rows, and Join Each, ,', which attaches columns side by side.

All the examples on this page use these tables. Paste them into a q session to follow along:

trade:([] time:09:30:00.000 09:30:01.000 09:30:02.000; sym:`AAPL`MSFT`AAPL; price:185.2 410.1 186.0; size:100 200 150)
ref:([sym:`AAPL`MSFT`GOOG] name:("Apple";"Microsoft";"Alphabet"); sector:`Tech`Tech`Comm)
quote:([] time:09:29:59.000 09:30:00.500 09:30:01.500 09:30:01.800; sym:`AAPL`MSFT`AAPL`AAPL; bid:185.0 409.9 185.8 185.9; ask:185.3 410.2 186.1 186.2)

trade and quote are ordinary (unkeyed) tables. ref is a keyed table, keyed on sym. The square brackets in its definition hold the key column.

Left Join (lj)

lj is the join you will use most. It adds columns from a keyed table to every row of the left table, matching on the key:

trade lj ref

time         sym  price size name        sector
-----------------------------------------------
09:30:00.000 AAPL 185.2 100  "Apple"     Tech  
09:30:01.000 MSFT 410.1 200  "Microsoft" Tech  
09:30:02.000 AAPL 186   150  "Apple"     Tech

The rules:

• The right argument must be a keyed table, and its key columns must be columns of the left table. Since kdb+ 4.0, an unkeyed right argument signals 'type.
• The result has one row for each row of the left table. A left row with no match keeps its values, and the new columns are null.
• Where a column exists in both tables, matched rows take the value from the right table.

That last rule includes nulls. If the right table holds a null, lj copies the null over the left value:

trade lj ([sym:enlist `AAPL] price:enlist 0n; venue:enlist `X)

time         sym  price size venue
----------------------------------
09:30:00.000 AAPL       100  X    
09:30:01.000 MSFT 410.1 200       
09:30:02.000 AAPL       150  X

If you want nulls in the right table to leave the left values alone, use ljf, which keeps the behaviour lj had before kdb+ 3.0.

Inner Join (ij) and Equi Join (ej)

ij is like lj, but keeps only the left rows that have a match. The right argument is again a keyed table:

trade ij ([sym:`AAPL`TSLA] name:("Apple";"Tesla"))

time         sym  price size name   
------------------------------------
09:30:00.000 AAPL 185.2 100  "Apple"
09:30:02.000 AAPL 186   150  "Apple"

ej does the same kind of match, but you name the columns to match on, and the right table does not need to be keyed. Every matching right row produces a result row, so a left row can appear more than once:

ej[`sym; trade; ([] sym:`AAPL`AAPL`TSLA; lot:10 20 30)]

time         sym  price size lot
--------------------------------
09:30:00.000 AAPL 185.2 100  10 
09:30:00.000 AAPL 185.2 100  20 
09:30:02.000 AAPL 186   150  10 
09:30:02.000 AAPL 186   150  20

Plus Join (pj)

pj is a left join that adds matching values instead of replacing them. It is handy for combining positions or totals. Common columns must be numeric, and new columns are zero where there is no match:

pos:([sym:`AAPL`MSFT] qty:100 200)
pos pj ([sym:`AAPL`GOOG] qty:50 25)

sym | qty
----| ---
AAPL| 150
MSFT| 200

GOOG is not in the result: like lj, the result has one row for each row of the left table.

Join (,) and Union Join (uj)

, appends the rows of one table to another. Both tables must have the same columns:

trade,([] time:enlist 09:30:03.000; sym:`GOOG; price:142.8; size:300)

time         sym  price size
----------------------------
09:30:00.000 AAPL 185.2 100 
09:30:01.000 MSFT 410.1 200 
09:30:02.000 AAPL 186   150 
09:30:03.000 GOOG 142.8 300

uj combines tables whose columns differ. The result has every column from both tables, filled with nulls where a table had no value:

trade uj ([] sym:`GOOG; price:enlist 142.8; venue:`NYSE)

time         sym  price size venue
----------------------------------
09:30:00.000 AAPL 185.2 100       
09:30:01.000 MSFT 410.1 200       
09:30:02.000 AAPL 186   150       
             GOOG 142.8      NYSE

With two keyed tables, uj updates the rows whose keys match and appends the rest:

(1!([] sym:`AAPL`MSFT; qty:1 2)) uj 1!([] sym:`MSFT`GOOG; qty:5 6)

sym | qty
----| ---
AAPL| 1  
MSFT| 5  
GOOG| 6

1! keys a table on its first column.

As-of Join (aj)

Market data rarely lines up exactly in time. A trade at 09:30:02 needs the quote that was in force at 09:30:02, which is the latest quote at or before that time. That is what aj finds:

aj[`sym`time; trade; quote]

time         sym  price size bid   ask  
----------------------------------------
09:30:00.000 AAPL 185.2 100  185   185.3
09:30:01.000 MSFT 410.1 200  409.9 410.2
09:30:02.000 AAPL 186   150  185.9 186.2

The first argument lists the columns to match on. All of them except the last must match exactly, and the last one (usually time) is matched as-of. So write `sym`time, not `time`sym: KX warns that putting the columns in the wrong order causes a severe performance hit.

The time column in the result comes from trade. aj0 is the same join, but takes the time column from quote, so you can see when each matching quote happened.

For speed, KX recommends that the right table have `g# (in memory) or `p# (on disk) on sym, with time sorted within each sym.

asof is a simpler form of aj. It matches on every column of its right argument, with the last one matched as-of. The Asof practice section covers it.

Window Join (wj)

aj picks one quote per trade. wj instead aggregates all the quotes in a time window around each trade, for example the highest bid in the second before it.

wj needs the quote table sorted by sym then time, with `p# on sym. Our quote table is sorted by time only, so sort it first:

quote:update `p#sym from `sym`time xasc quote

The windows are a pair of time lists, one for the start of each window and one for the end. These windows run from one second before each trade up to the trade:

w:-1000 0+\:trade`time
wj[w; `sym`time; trade; (quote; (max;`bid); (count;`ask))]

time         sym  price size bid   ask
--------------------------------------
09:30:00.000 AAPL 185.2 100  185   1  
09:30:01.000 MSFT 410.1 200  409.9 1  
09:30:02.000 AAPL 186   150  185.9 3

Each (function; column) pair aggregates one column of the quotes in each window. The result column keeps the source column's name, so above, ask holds a count of quotes.

Windows include both their start and end times. wj also counts the quote that was in force when the window opened, because a quote stays valid until the next one arrives. For the last trade, the window is 09:30:01 to 09:30:02. The count of 3 is the two quotes inside the window plus the 09:29:59 quote that was in force at 09:30:01. wj1 counts only quotes inside the window, so it would give 2.

If the quote table is not sorted by sym then time, wj does not signal an error. It silently returns wrong results.

Join Each (,')

Join Each joins two tables (or lists) item by item. For two tables with the same number of rows, that puts their columns side by side:

trade,'([] venue:`N`Q`N)

time         sym  price size venue
----------------------------------
09:30:00.000 AAPL 185.2 100  N    
09:30:01.000 MSFT 410.1 200  Q    
09:30:02.000 AAPL 186   150  N

Which Join Should I Use?

Common Mistakes

• Passing an unkeyed table to lj or ij. Key it first with `sym xkey t or 1!t.
• Duplicate keys in the right table. A keyed table can contain the same key twice, and lj silently uses the first match. Check with count[t] = count distinct key t if you are not sure.
• Column order in aj. The as-of column goes last: aj[`sym`time;...].
• Unsorted quotes in wj. Sort by `sym`time first, or the results are wrong.

How This Topic Is Organised

The practice sections each focus on one join:

• Each-Left Join: joining a value or row to every item.
• Join (,) and Union Join (uj): appending rows.
• Join-Each (,'): attaching columns.
• Left, Inner and Plus Join: lj, ij and pj.
• Window Join (wj): moving averages and VWAPs.
• Asof and As-of Join (aj): values as of a point in time.