sql ij inner join

[Ash]

Hello,

Today I was playing with kdb joins and discovered that they are not like the joins I know from mysql.
From the example http://www.w3schools.com/sql/sql_join_inner.asp I tried


q)Persons:([] P_Id:1 2 3; LastName:`hansen`Svendson`Pettersen)
q)Orders:([] O_Id:1 2 3 4 5; OrderNo:77895 44678 22456 24562 34764; P_Id:3 3 1 1 15)

q)Persons
P_Id LastName
--------------
1 hansen
2 Svendson
3 Pettersen
q)Orders
O_Id OrderNo P_Id
-----------------
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

q)Persons ij `P_Id xkey Orders
P_Id LastName O_Id OrderNo
---------------------------
1 hansen 3 22456
3 Pettersen 1 77895


Notice the kdb results has only 2rows but the sql one has 4!!!
This database is non-sql compliant or do I something wrong?

[Ryan Hamilton]

Hi Ash,

Kdb joins do not typically join every match, just the first row that matches. You can read more about kdb joins here including a cartesian join that would pull across all matches similar to standard SQL.

For your example:
“q)Persons:([] P_Id:1 2 3; LastName:`hansen`Svendson`Pettersen)
q)Orders:([] O_Id:1 2 3 4 5; OrderNo:77895 44678 22456 24562 34764; P_Id:3 3 1 1 15)

q)Persons ij `P_Id xkey Orders
P_Id LastName O_Id OrderNo
—————————
1 hansen 3 22456
3 Pettersen 1 77895

q)Persons ij `P_Id xgroup Orders
P_Id LastName O_Id OrderNo
——————————-
1 hansen 3 4 22456 24562
3 Pettersen 1 2 77895 44678

q)ungroup Persons ij `P_Id xgroup Orders
P_Id LastName O_Id OrderNo
—————————
1 hansen 3 22456
1 hansen 4 24562
3 Pettersen 1 77895
3 Pettersen 2 44678“

Pull in all matches as a nested list by using xgroup then ungroup to expand to standard table format.

-Ryan

• This reply was modified 11 years, 8 months ago by Ryan Hamilton.
• This reply was modified 11 years, 8 months ago by Ryan Hamilton.

[Author]

Posts