kdb standard SQL support s)
First, in case you haven’t heard about it kdb has a standard SQL mode, you can send queries prefixed with s) and they will be interpreted as standard SQL like so:
Notice how the standard “and” syntax worked when I used s) but without it, q’s right to left evaluation causes problems. It’s about now that a lot of people get very excited, they think great I can skip learning that q-sql and use my standard SQL. Sometimes the look of joy on their faces transforms to frustration once they start using it. So let’s look at what works:
| Operation | Works? |
|---|---|
| Standard SQL Inserts | Yes |
| ORDER BY | half works |
| COUNT | Yes |
| DELETE | Yes |
| UPDATE | Yes |
| String matching | slightly works |
| NOT | NO |
| IN | Yes |
| GROUP BY | Yes |
| LIMIT / TOP | NO |
| Date Times | NO |
Standard SQL Inserts work
ORDER BY half works
“ORDER BY” will sort the columns in ascending order, attempting to use DESC has no effect.
COUNT works
DELETE works
UPDATE works
String matching slightly works
NOT fails
Modifying our String query slightly by adding NOT throws an error. My guess is that the interpreter has got confused.
IN works
GROUP BY works
LIMIT / TOP does not work
Date Times Don’t Work Right
Overall standard SQL support in kdb has got much better. However I would still recommend only using the s) syntax for plugging into an existing jdbc/odbc visualization tool and getting some immediate simple results. For any form of complex queries on strings, joins etc. support is either not there or the result may not be what you expect.