Query multiple tables in KDB

Question

I want to query multiple tables in KDB. For ex. how to write following SQL query in KDB (I am not good at SQL so query format might be wrong):

select from table1,table2 where table1.sym=table2.sym and table1.price>table2.price

I know some ways of doing it, for ex. joins. But is there any functionality in KDB same as SQL which does it simply using dot notation on tables.

Also in SQL, we can extend above query to 'n' number of tables and filters. Can we do that in KDB without making a complex expression?

Answer 1

Short answer is No I'm afraid.

You can only query 1 table at a time. You have to do the join first. I don't know SQL well but I'm sure the SQL engine is doing a join operation under the hood anyway to be able to do this (?) so if there was a way to do this in q via a function it would be doing a join.

If you want extract column vectors you can just index into a table like this (avoid dot notation inside functions):

table[`sym]

(would get the sym column as a vector, assuming table isn't keyed) (NB Be careful when doing this on a splayed table!)

You can use that for in queries in the where clause, for example. Or if your tables are exactly the same length you can use that to create a new temporary interim table (but that's still a join of a kind!)

That's as close as you're going to get without using the usual ,, lj, uj, etc