Skip to Content
SQL ReferenceQuery Data (SELECT & JOIN)

Query Data (SELECT & JOIN)

Use this page for query-only SQL patterns in KalamDB.

KalamDB supports the DataFusion SELECT query surface. For advanced query syntax and planner behavior, use the DataFusion SQL docs as the primary reference:

The visible rows and join targets still follow KalamDB table-type authorization. USER and STREAM tables are scoped by effective user, SHARED tables use ACCESS_LEVEL, and system.* access is restricted. See /docs/server/architecture/table-types.

DataFusion Statement Scope

KalamDB uses DataFusion as the engine for query-time SQL, especially read/query workloads:

  • SELECT and projection/filter/order/limit patterns
  • joins (INNER, LEFT, RIGHT, FULL, CROSS, USING)
  • subqueries and WITH (CTE)
  • set operations such as UNION / UNION ALL
  • scalar/aggregate/window function execution in query plans

KalamDB-specific operational SQL surfaces are implemented by KalamDB handlers instead of raw DataFusion SQL semantics, including:

  • DDL (CREATE/ALTER/DROP namespaces, tables, users, storage, topics)
  • topic/consumer commands (CREATE TOPIC, ALTER TOPIC, CONSUME, ACK, RESET CONSUMER GROUP)
  • live-query controls (SUBSCRIBE TO, KILL LIVE QUERY)

Basic SELECT

SQL
SELECT <columns>FROM [<namespace>.]<table_name>[WHERE <condition>][ORDER BY <expr> [ASC|DESC]][LIMIT <n>];

Example:

SQL
SELECT id, sender, content, created_atFROM chat.messagesWHERE conversation_id = 42ORDER BY created_at DESCLIMIT 50;

Filtering, Grouping, and Aggregation

SQL
SELECT sender, COUNT(*) AS message_countFROM chat.messagesWHERE conversation_id = 42GROUP BY senderHAVING COUNT(*) >= 10ORDER BY message_count DESC;

JOIN Support

KalamDB supports standard DataFusion join patterns, including:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • CROSS JOIN
  • JOIN ... USING (...)

Example (INNER JOIN):

SQL
SELECT m.id, m.content, u.emailFROM chat.messages mINNER JOIN system.users u ON m.user_id = u.user_idWHERE m.conversation_id = 42;

Example (LEFT JOIN):

SQL
SELECT c.id, c.title, m.contentFROM chat.conversations cLEFT JOIN chat.messages m ON c.id = m.conversation_idORDER BY c.id;

Subqueries and CTEs

Common DataFusion query constructs are supported, including subqueries and CTEs.

SQL
WITH ranked AS (  SELECT    sender,    COUNT(*) AS total_messages  FROM chat.messages  GROUP BY sender)SELECT *FROM rankedWHERE total_messages >= 100ORDER BY total_messages DESC;

Set Operations

You can use DataFusion set operations such as UNION and UNION ALL.

SQL
SELECT sender FROM chat.messages_2025UNION ALLSELECT sender FROM chat.messages_2026;

PostgreSQL-style JSON Queries

JSON and JSONB columns can be queried with the same common extraction syntax PostgreSQL users already know.

SQL
SELECT  doc->>'status' AS status,  doc->'items'->0 AS first_itemFROM app.ordersWHERE doc->>'status' = 'paid'  AND doc ? 'customer_id';

Use -> when you want a JSON value, ->> when you want text, and ? when you only need to test key existence.

KalamDB also exposes DataFusion JSON helpers such as json_get, json_as_text, json_length, and json_object_keys for function-style access. For the full JSON function list and current operator coverage, see /docs/server/sql-reference/functions.

Advanced Query Reference

For advanced SELECT features, rely on DataFusion SQL documentation:

Last updated on
Data ManipulationSystem Views