sql ij inner join - Kdb+ / qStudio

Home Forums Kdb+ / qStudio sql ij inner join

Tagged: 

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #218

    Ash
    Guest

    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?

    #235

    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 10 years, 10 months ago by Ryan Hamilton.
    • This reply was modified 10 years, 10 months ago by Ryan Hamilton.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.