# lj, ljf

Left join

x lj  y     lj [x;y]
x ljf y     ljf[x;y]

Where

• x is a table
• y is
• a keyed table whose key column/s are columns of x, returns x and y joined on the key columns of y
• or the general empty list (), returns x

For each record in x, the result has one record with the columns of y joined to columns of y:

• if there is a matching record in y, it is joined to the x record; common columns are replaced from y.
• if there is no matching record in y, common columns are left unchanged, and new columns are null
q)show x:([]a:1 2 3;b:IJK;c:10 20 30)
a b c
------
1 I 10
2 J 20
3 K 30

q)show y:([a:1 3;b:IK]c:1 2;d:10 20)
a b| c d
---| ----
1 I| 1 10
3 K| 2 20

q)x lj y
a b c  d
---------
1 I 1  10
2 J 20
3 K 2  20


The y columns joined to x are given by:

q)y[select a,b from x]
c d
----
1 10
2 20


## Changes in V4.0

lj checks that y is a keyed table. (Since V4.0 2020.03.17.)

q)show x:([]a:1 2 3;b:10 20 30)
a b
----
1 10
2 20
3 30
q)show y:([]a:1 3;b:100 300)
a b
-----
1 100
3 300
q)show r:([]a:1 2 3;b:100 20 300)
a b
-----
1 100
2 20
3 300

q)(1!r)~(1!x)lj 1!y
1b
q)r~t1 lj 1!t2
1b

q)t1 lj t2
'type
  t1 lj t2
^

Changes in V3.0

Since V3.0, the lj operator is a cover for ,\: (Join Each Left) that allows the left argument to be a keyed table. ,\: was introduced in V2.7 2011.01.24.

Prior to V3.0, lj had similar behavior, with one difference - when there are nulls in the right argument, lj in V3.0 uses the right-argument null, while the earlier version left the corresponding value in the left argument unchanged:


q)show x:([]a:1 2;b:xy;c:10 20)
a b c

1 x 10
2 y 20
q)show y:([a:1 2]b:z;c:1 0N)
a| b c
-| ---
1|   1
2| z
q)x lj y        / kdb+ 3.0
a b c

1   1
2 z
q)x lj y        / kdb+ 2.8
a b c

1 x 1
2 z 20


Since 2014.05.03, the earlier version is available in all V3.x versions as ljf`.

Joins
Q for Mortals §9.9.2 Ad Hoc Left Join