Skip to content

ej

Equi join

ej[c;t1;t2]

Where

  • c is a list of column names (or a single column name)
  • t1 and t2 are 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

Joins

Q for Mortals §9.9.5 Equi Join