KDB Query for OR operator

Question

What is the equivalent query in KDB Web:

SELECT * FROM TABLE
WHERE (COLA = 'A' AND COLB = 'B') OR (COLA = 'C' AND COLB = 'D')

http://kdbserver:5001/?select fro table where _____________________

N.B.: cola and colb are having string datatype

Oilskin

what are the data types of COLA and COLB? Symbol or string? – Avast

Answer 1

You can do:

select from table where ((COLA like "string1")&(COLB like "string2"))|((COLA like "string3")&(COLB like "string4"))

Avast

Answer 2

select from table where ([]colA;colB) in ([]colA:`A`C;colB:`B`D)

Candelabra

for strings.. select from table where ([]`$colA;`$colB) in ([]colA:`A`C;colB:`B`D) – Candelabra

How does this perform against the other answers? – Munson

I'm seeing ~2x quicker over a 100m rows q)n:100000000; q)tab:([]a:n?`IBM`AAPL`MSFT`GOOG;b:n?1 2 3 4); q)\ts t1:select from tab where ([]a;b) in ([]a:`AAPL`GOOG`IBM`IBM`GOOG;b:1 2 3 4 5); 7318 671090016j q)\ts t2:select from tab where ((a like "AAPL")&(b=1))|((a like "GOOG")&(b=2))|((a like "IBM")&(b=3))|((a like "IBM")&(b=4))|((a like "GOOG")&(b=5)); 12462 536873248j q)r2~r1; 1b – Candelabra

Answer 3

Connor is right and his answer is quite efficient. Just want to add a version with list operation instead of table:

tab:([]cola:("aaa";"bbb";"ccc");colb:("ddd";"eee";"fff"))
select from tab where (flip(cola;colb))in\:(("aaa";"ddd");("bbb";"eee"))

Execution speed is almost identical with Connor's one

Esemplastic

Answer 4

Sometime I prefer changing type to symbol to get results,
say,

tab1:([]a:string 10?`2;b:string 10?`2; c: string 10?`2)  

--

select from tab1 where (((`$a)=`$"ci") & ((`$b)=`$"lf")) or (((`$a)=`$"en") & ((`$b)=`$"dl"))  

Tyler

Note for columns with many uniques, this will bloat the internalized symbol-list of the process – Fatal