ej¶
Equi join
ej[c;t1;t2]
Where
cis a list of column names (or a single column name)t1andt2are tables
returns t1 and t2 joined on columns c.
The result has one combined record for each pair of rows of t1 and t2 that match on columns c.
The result omits rows that have no match in the other table.
q)t:([]sym:`IBM`FDP`FDP`FDP`IBM`MSFT;price:0.7029677 0.08378167 0.06046216 0.658985 0.2608152 0.5433888)
q)s:([]sym:`IBM`MSFT;ex:`N`CME;MC:1000 250)
q)t
sym price
---------------
IBM 0.7029677
FDP 0.08378167
FDP 0.06046216
FDP 0.658985
IBM 0.2608152
MSFT 0.5433888
q)s
sym ex MC
-------------
IBM N 1000
MSFT CME 250
q)ej[`sym;s;t]
sym ex MC price
-----------------------
IBM N 1000 0.7029677
IBM N 1000 0.2608152
MSFT CME 250 0.5433888
For other columns that appear in both tables, ej takes the values from t2.
q)t1:([] k:1 2 3 4; c:10 20 30 40)
q)t2:([] k:2 2 3 4 5; c:200 222 300 400 500; v:2.2 22.22 3.3 4.4 5.5)
q)ej[`k;t1;t2]
k c v
-----------
2 200 2.2
2 222 22.22
3 300 3.3
4 400 4.4
If both tables have several rows with the same key, the result has a record for each combination.
q)t3:([] k:1 2 2 3; a:`x`y`z`w)
q)ej[`k;t3;t2]
k a c v
-------------
2 y 200 2.2
2 y 222 22.22
2 z 200 2.2
2 z 222 22.22
3 w 300 3.3
Q for Mortals §9.9.5 Equi Join