SELECT

Reads rows from one table or a chain of joined tables. The statement is the plan: tables are visited in the order the FROM clause lists them, and how each one is accessed is decided from the statement’s shape and the catalog, never from data statistics.

Syntax

SELECT { * | <column> [, ...] | <aggregate> [, ...] }
FROM <table> [AS <alias>]
    [JOIN <table> [AS <alias>] ON <alias.column> = <alias.column>] ...
[WHERE <condition> [AND <condition>] ...]
[GROUP BY <column> [, ...]]
[ORDER BY <column> [ASC | DESC] [, ...]]
[LIMIT <n>] [OFFSET <m>]

The clauses must appear in this order.

Examples

Point lookup

SELECT * FROM passengers WHERE id = 1;

Join

SELECT p.name, t.baggage_kg
FROM passengers AS p JOIN tickets AS t ON t.passenger_id = p.id
WHERE t.baggage_kg BETWEEN 10 AND 25;

Subquery

SELECT * FROM passengers AS p
WHERE NOT EXISTS (SELECT * FROM tickets AS t WHERE t.passenger_id = p.id AND t.checked_in = 1);

Aggregate

SELECT flight_id, COUNT(*), SUM(baggage_kg) FROM tickets GROUP BY flight_id;

Order and page

SELECT name, joined_on FROM passengers ORDER BY joined_on DESC LIMIT 3;
SELECT * FROM tickets WHERE id > 4 LIMIT 3;   -- keyset pagination

Select list

• SELECT * works on a single table only. Across a join it is refused, because its column order would depend on the join order.
• Otherwise list columns, qualified (p.name) or bare (name).
• No expressions, literals or AS aliases in the select list.

FROM and JOIN

• FROM <table> [AS <alias>]. The bare alias form FROM t a is not accepted.
• sys.<view> reads the catalog, for example sys.tables.
• Joins are inner equi-joins: JOIN <table> ON <x.col> = <y.col>, one equality per ON, both sides qualified.
• Joins run in the order you write them. Nothing reorders them.
• A join on the joined table’s primary key is a direct key lookup per row. Any other join condition is accepted and runs as a scan, or an index or Cabin lookup, of that table.
• To join a table to itself, give it two aliases: FROM t AS a JOIN t AS b ON ....

WHERE

Conditions combine with AND. Each condition is one of:

Form	Example
Compare with a value	base_fare >= 100, operators = != < <= > >=
Compare with another column	t.flight_id = f.id
Compare with a scalar subquery	base_fare > (SELECT ...)
IN / NOT IN a subquery	id IN (SELECT passenger_id FROM tickets)
EXISTS / NOT EXISTS	EXISTS (SELECT ...)
Range	baggage_kg BETWEEN 10 AND 25 (inclusive)
Null test	note IS NULL, note IS NOT NULL
Date and time	DATE(departs) = '2026-11-02', departs < NOW()

• Subqueries can be correlated and nested up to 4 levels deep.
• Comparisons with NULL are unknown, so col = NULL matches nothing. Use IS NULL.
• NOT IN (SELECT ...) is unknown, and keeps no row, when the subquery returns a NULL and no match, or when the left value is NULL and the subquery returns any row. Against an empty subquery it is true.
• IN (1, 2, 3) with a list of values is not available yet. Use a subquery.

No implicit type conversion

A comparison converts no type. A literal must be written the way the compared column’s type is written, and a mismatch is refused when the statement is compiled, with the literal’s byte position. It is refused whether or not a row would match.

Column type	Write	Refused
Integer types	n = 20	n = '20', n = 20.0
char, varchar	s = '5'	s = 5
bool	ok = 0, ok = 1	ok = 'true'
date, timestamp	'2026-10-01', or the epoch integer
decimal(p, s)	'12.34', 12.34, or an integer

kdbs> SELECT * FROM tickets WHERE baggage_kg = '23';
                                               ^
ERR column 'baggage_kg' literal at byte 41: expects an integer literal - unquoted, with no fraction

An integer outside an int8, int16 or int32 column’s range is not refused; it simply matches no row. On an int64 or uint64 column, an integer outside the type’s range is refused, a negative one on uint64 included.

Two columns, in WHERE, in ON, or a column against the value of IN (SELECT ...) or a scalar subquery, can be compared only within one family:

• integers of any width and signedness, compared by value (an int32 -1 is less than a uint64 5);
• decimals of the same width and scale;
• char with varchar, as text;
• date with date, and timestamp with timestamp.

Any other pair is refused:

kdbs> SELECT p.name FROM passengers AS p JOIN tickets AS t ON t.note = p.id
                                                              ^
ERR cannot compare 'note' (varchar) with 'id' (int64); this engine converts no type implicitly at byte 56

How a condition is executed

Condition	Access
Primary key =	Direct key lookup
Primary key range or BETWEEN	Range walk over the key order
Other column =	Secondary index if one exists, otherwise a Cabin, otherwise a filtered scan

Aggregates

• COUNT, SUM, MIN, MAX, AVG, with optional DISTINCT inside. COUNT(*) counts rows.
• GROUP BY takes column names, without parentheses.
• Every selected column must be in GROUP BY.
• SUM is checked: overflow fails the statement. AVG of an integer column is refused (it would invent digits); AVG of decimal(p, s) returns scale s.
• At most aggregate_max_groups groups (default 65,536). Exceeding it fails the statement.

ORDER BY, LIMIT and OFFSET

• Without ORDER BY, the row order is still defined: tables in written order, and rows within a table in primary-key order. So LIMIT n OFFSET m without ORDER BY always returns the same rows.
• ORDER BY takes up to 8 columns from any table in the statement, each ASC (default) or DESC. A sort column does not have to be selected.
• ORDER BY <primary key> ascending is free: rows are already in that order. Every other order is a sort.
• NULL sorts as the largest value: last with ASC, first with DESC.
• A sort must read every matching row before it can return the first one. LIMIT caps the memory a sort uses (offset + limit rows), not the rows it reads.
• A sort that would hold more than sort_max_rows rows fails the statement; it never spills to disk.
• OFFSET reads and discards the rows it skips. For deep pages, use keyset pagination: WHERE id > <last id> LIMIT n.

Limitations

• No HAVING, and no ORDER BY over grouped results.
• No outer joins, no WITH, no subqueries in FROM.
• No OR or NOT around conditions.
• No aggregates or GROUP BY inside subqueries.
• sys.* views support WHERE, LIMIT and OFFSET, but not joins, aggregates or ORDER BY. See SHOW and DESCRIBE for how a view compares values.

See also

• ANALYZE
• CREATE INDEX
• Data types