Joins
Joining a pair of tables (or table-likes) is a base operation in queries. Both joining on a single field and on several fields are supported. Usually, table aliases are required to disambiguate fields.
Inner join is defined using the verb ij:
o)t1:((+:)`a`b`c!(1 2 3;3 4 5;6 7 8));
o)t2:((+:)`a`d`e`f!(1 2 3;3 4 5;6 7 8;("111";"222";"333")));
o)j: ij[(`t1;t1);(`t2;t2);(~`t1`a;~`t2`a)];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
-----------
1 3 6 "111"
2 4 7 "222"
3 5 8 "333"
o)
Inner join is different from the one in SQL - it does not produce Cartesian product for duplicating ids, it emits the first found for second relation.
o)t1:((+:)`a`b`c!(`g#3 2 0;3 4 5;6 7 8));
o)t2:((+:)`a`d`e`f!(`g#2 2 5;13 14 15;16 17 18;("111";"222";"333")));
o)j: ij[(`t1;t1);(`t2;t2);(~`t1`a;~`t2`a)];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
------------
2 4 16 "111"
o)
It's strongly recommended to attach attributes to a field before the join. If there is no appropriate attribute, query engine will build a temporary one every time the query is executed.
Multi-column joins are also supported. The same logic of building indices beforehand/in-time is applied here.
o)t1:((+:)`a`b`c!(3 1 2;3 4 5;6 7 8));
o)t2:((+:)`a`d`e`f!(1 2 3;4 5 3;16 17 18;("111";"222";"333")));
o)@[`t1;,`a`b;~[#];`g];
o)@[`t2;,`a`d;~[#];`g];
o)j: ij[(`t1;t1);(`t2;t2);((~`t1`a;~`t1`b);(~`t2`a;~`t2`d))];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
------------
1 4 16 "111"
2 5 17 "222"
3 3 18 "333"
o)
Left join is defined using the verb lj:
o)t1:((+:)`a`b`c!(3 1 2;3 4 5;6 7 8));
o)t2:((+:)`a`d`e`f!(`g#1 2;13 14;16 17;("111";"222")));
o)j:lj[(`t1;t1);(`t2;t2);(~`t1`a;~`t2`a)];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
------------
3 3 0N 0N0
1 4 16 "111"
2 5 17 "222"
o)
Left join differs from the one in SQL as well - it does not produce Cartesian product for duplicating ids, it emits first found for second relation.
o)t1:((+:)`a`b`c!(3 2 0;3 4 5;6 7 8));
o)t2:((+:)`a`d`e`f!(`g#2 2 5;13 14 15;16 17 18;("111";"222";"333")));
o)j:lj[(`t1;t1);(`t2;t2);(~`t1`a;~`t2`a)];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
------------
3 3 0N 0N0
2 4 16 "111"
0 5 0N 0N0
o)
Just like with ij, it's strongly recommended to attach attributes to a field before the join. If there is no appropriate attribute, query engine will build a temporary one every time the query is executed.
Multi-column left joins are also supported. The same logic of building indices beforehand/in-time is applied here.
o)t1:((+:)`a`b`c!(3 2 0;3 4 5;6 7 8));
o)t2:((+:)`a`b`e`f!(3 2 3;3 4 3;16 17 18;("111";"222";"333")));
o)@[`t2;,`a`b;~[#];`g];
o)j:lj[(`t1;t1);(`t2;t2);((~`t1`a;~`t1`b);(~`t2`a;~`t2`b))];
o)0N#.(?[j; (); 0b; `a`b`e`f!(~`t1`a;~`t1`b;~`t2`e;~`t2`f)])
a b e f
------------
3 3 16 "111"
2 4 17 "222"
0 5 0N 0N0
o)