Kdb Q: Left join only on null rows

user_5314321 0
Viewed 492

I want to perform a left join between two tables t1 and t2 on the column id2 only if id2 is null on the t1 side.
As a qbie, I was wondering if it exists a better solution than mine below ?

t1: ([] id1:`AAA`BBB`CCC`DDD`EEE; id2:```02C``E25)
t2: ([] id1:`AAA`BBB`CCC`DDD`SSS; id2:`02A`02B`C2C`DD0`SPE)

Expected result:

([] id1:`AAA`BBB`CCC`DDD`EEE; id2:`02A`02B`02C`DD0`E25)

id1 id2
-------
AAA 02A
BBB 02B
CCC 02C
DDD DD0
EEE E25

My solution so far is to perform a left-join by changing the column id2 to id3 from t2 and then apply the vector-conditional function ?:

t1:t1 lj 1!select id1, id3:id2 from t2
t1[`id2]: ?[null t1[`id2];t1[`id3];t1[`id2]]
t1:delete id3 from t1

Thank you!

kdb join
3 Answers

Another approach using fill ^:

q){key[x]#y^x}[1!t1;1!t2]
id1| id2
---| ---
AAA| 02A
BBB| 02B
CCC| 02C
DDD| DD0
EEE| E25
user_38956970

One liner below keys the table t2 then indexes into that table by passing it a table of the id1 column from t1 and selects the id2 value. Then perform a fill(^) to only take this value where the id2 from t1 is null.

q)t1: ([] id1:`AAA`BBB`CCC`DDD`EEE; id2:```02C``E25)
q)t2: ([] id1:`AAA`BBB`CCC`DDD`SSS; id2:`02A`02B`C2C`DD0`SPE)
q)update (1!t2)[([]id1);`id2]^id2 from t1
id1 id2
-------
AAA 02A
BBB 02B
CCC 02C
DDD DD0
EEE E25
user_80336030

You could simply filter out the rows from t2 for the symbols that are null in t1 and just do left join on this new table. This is also useful if your table has many columns to join.

q) t1 lj 1! select from t2 where id1 in exec id1 from t1 where id2=`
user_35557000
Related