WHERE

Keep only the rows you want, with comparisons, IN, BETWEEN, LIKE, IS NULL and time ranges.

• Category: Querying
• Keywords: filter, condition, rows, AND, OR, NOT, , , !, <>, <, <, >, >, BETWEEN, NOT BETWEEN, IN, NOT IN, time IN, interval, LIKE, NOT LIKE, %, _, escape, ILIKE, NOT ILIKE, IS NULL, IS NOT NULL, NULL, row comparison, tuple IN, (a, b) IN, composite key, IN VALUES, keyset paging, seek paging, fast conditions, column engine, row engine, where, like, ilike, SIMILAR TO, NOT SIMILAR TO, similar to, similar_to_escape, between
• See also: SELECT, Operators, ORDER BY, LIMIT, OFFSET (paging), Time and time zones, Conditional and NULL functions, Regular expressions

WHERE keeps the rows where the condition is true. Rows where it is false or NULL are left out.

On a time-series table, a condition on the time column (like time IN '2026-09') also makes the query fast: KDB does not read the other partitions (files). A condition like symbol = 'CITY' lets KDB jump straight to that symbol's rows. Some shapes of condition are much faster than others: see fast conditions.

Example: CITY and VOLT closes above 190 in September 2026 (sample data: sample)

SELECT time, symbol, close
FROM ochl_day
WHERE symbol IN ('CITY', 'VOLT') AND close > 190 AND time IN '2026-09'
ORDER BY time, symbol
LIMIT 5;

Result:

(5 rows)

WHERE

Syntax:

SELECT ... FROM ... WHERE condition
UPDATE table SET ... WHERE condition
DELETE FROM table WHERE condition

The condition is an expression that gives true or false. You can use columns of every table in FROM and JOIN, functions, and subqueries.

• Returns: only the rows where the condition is true.
• NULL: a condition that gives NULL counts as false (see NULL in WHERE).
• Notes: WHERE runs before GROUP BY, SAMPLE BY and window functions. To filter groups, use HAVING.
• A select alias cannot be used in WHERE. Write the expression again.
• The condition must be true or false: WHERE close is an error.
• Aggregates like count(*) cannot be used in WHERE.

Example: Pro users of the app (sample data: query-app)

SELECT id, username, city
FROM app_users
WHERE plan = 'pro';

Result:

(3 rows)

Example: Ledger lines of the bank account in the first week of September (sample data: query-app)

SELECT entry_date, debit, credit, memo
FROM ledger
WHERE account = 1100 AND entry_date >= '2026-09-01' AND entry_date < '2026-09-08'
ORDER BY line_id;

Result:

(4 rows)

Example: Forum posts written by sita (sample data: content-forum)

SELECT id, thread_id, body
FROM posts
WHERE author = 'sita';

Result:

(2 rows)

Example: The condition must be true or false (sample data: sample; shows an error)

SELECT symbol FROM ochl_day WHERE close;

Result:

Error: line 1, column 35: a condition must be true or false, got '542.2'

Example: An alias is not known in WHERE (sample data: sample; shows an error)

SELECT symbol, close - open AS change FROM ochl_day WHERE change > 0;

Result:

Error: line 1, column 59: unknown column 'change' (table 'ochl_day' has: time symbol open high low close volume)

AND, OR, NOT

Syntax:

condition AND condition
condition OR condition
NOT condition

• NULL: true OR NULL is true, false AND NULL is false. Other mixes with NULL give NULL (and the row is left out).
• Notes: NOT binds tighter than AND, and AND binds tighter than OR. So a OR b AND c means a OR (b AND c). Use ( ) whenever you mix AND and OR.
• Both sides must be true or false: 1 AND 2 is an error.

Example: Without ( ): the price and the day apply only to COAST, so CITY rows of every day come back (sample data: sample)

SELECT time, symbol, close
FROM ochl_day
WHERE symbol = 'CITY' OR symbol = 'COAST' AND close > 400
  AND time IN '2026-09-10'
LIMIT 4;

Result:

(4 rows)

Example: The same with ( ): now the time and price apply to both symbols (sample data: sample)

SELECT time, symbol, close
FROM ochl_day
WHERE (symbol = 'CITY' OR symbol = 'COAST') AND close > 400
  AND time IN '2026-09-10';

Result:

(1 row)

Example: Trades where broker 58 was the buyer or the seller, but not with broker 1 (sample data: sample)

SELECT transaction_id, symbol, buyer, seller, quantity
FROM trade_log
WHERE (buyer = 58 OR seller = 58) AND NOT (buyer = 1 OR seller = 1);

Result:

(6 rows)

Example (sample data: sample)

SELECT TRUE AND NULL AS a, FALSE AND NULL AS b, TRUE OR NULL AS c, FALSE OR NULL AS d, NOT NULL AS e;

Result:

(1 row)

=, ==, !=, <>, <, <=, >, >=

Syntax:

a = b     a == b
a != b    a <> b
a < b     a <= b     a > b     a >= b

Compare two values. == is the same as =, and <> is the same as !=.

• Returns: true or false.
• NULL: NULL on either side gives NULL. Even NULL = NULL is NULL. Use IS NULL.
• Notes: a time text without a zone is UTC. For a local time add its offset, for example +05:45: time >= '2026-09-10T11:00:00+05:45'. See time text.
• A time text must be YYYY-MM-DD with two digits: '2026-9-1' is an error.

Example: The first 5 minutes after the market opened (11:00 Nepal time) (sample data: sample)

SELECT time, symbol, close
FROM ochl_minutes
WHERE symbol = 'CITY'
  AND time >= '2026-09-10T11:00:00+05:45'
  AND time <  '2026-09-10T11:05:00+05:45';

Result:

(5 rows)

Example: Big trades: 1000 shares or more (sample data: sample)

SELECT transaction_id, symbol, quantity, rate FROM trade_log WHERE quantity >= 1000;

Result:

(4 rows)

Example (sample data: sample)

SELECT 5 = 5.0 AS a, 5 == 5 AS b, 'CITY' <> 'city' AS c, 'B' < 'a' AS d, 34 = '34' AS e;

Result:

(1 row)

Example: Text that is not a number cannot be compared with a number (sample data: sample; shows an error)

SELECT * FROM brokers WHERE name = 58;

Result:

Error: line 1, column 34: cannot compare STRING 'Keystone Securities' with LONG '58'

Example: A time must be written YYYY-MM-DD (sample data: sample; shows an error)

SELECT time, close FROM ochl_day WHERE time >= '2026-9-1';

Result:

Error: line 1, column 45: cannot compare TIMESTAMP '2025-12-28T00:00:00.000Z' with STRING '2026-9-1'

BETWEEN, NOT BETWEEN

Syntax:

value [NOT] BETWEEN low AND high

x BETWEEN a AND b is the same as x >= a AND x <= b. Both ends are included.

• Returns: true or false. NOT BETWEEN gives the opposite.
• NULL: NULL when any of the three values is NULL.
• Notes: for times, BETWEEN '2026-09-07' AND '2026-09-09' stops at 2026-09-09T00:00:00: the rest of 9 September is not included. For whole days use time >= '2026-09-07' AND time < '2026-09-10', or time IN.

Example: Days when COAST closed between 375 and 380 (sample data: sample)

SELECT time, close FROM ochl_day
WHERE symbol = 'COAST' AND close BETWEEN 375 AND 380;

Result:

(5 rows)

Example: BETWEEN with times: the end is the start of the last day (sample data: sample)

SELECT time, symbol, close FROM ochl_day
WHERE symbol = 'VOLT' AND time BETWEEN '2026-09-07' AND '2026-09-09';

Result:

(3 rows)

Example: Minutes where CITY was outside its normal band (sample data: sample)

SELECT time, close FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10' AND close NOT BETWEEN 501 AND 507;

Result:

(1 row)

IN (list), NOT IN (list)

Syntax:

value [NOT] IN (value, value, ...)
value [NOT] IN (SELECT ...)

True when the value is equal to one of the values in the list. For a list from a query, see IN (SELECT ...).

• Returns: true or false.
• NULL: NULL when the value is NULL. When the value is not found and the list has a NULL, the result is NULL, not false. So x NOT IN (1, NULL) is never true: keep NULL out of NOT IN lists.
• Notes: symbol IN ('CITY', 'COAST') on a time-series table reads only those symbols' rows (fast).
• IN 'text' without ( ) is a time range, not a list.

Example: Banks only (sample data: sample)

SELECT symbol, name FROM securities WHERE symbol IN ('CITY', 'COAST');

Result:

(2 rows)

Example: Journal lines that are not income or expense (sample data: query-app)

SELECT line_id, account, debit, credit FROM ledger
WHERE account NOT IN (4000, 4100, 5000, 5100) AND entry_id >= 7;

Result:

(3 rows)

Example: NULL in the list (sample data: sample)

SELECT 1 IN (1, NULL) AS found, 2 IN (1, NULL) AS not_found, 2 NOT IN (1, NULL) AS not_in;

Result:

(1 row)

Example: A list needs ( ) (sample data: sample; shows an error)

SELECT * FROM securities WHERE symbol IN 'CITY';

Result:

Error: line 1, column 42: expected a time interval string such as '2026-09', '2026-09-11T10:00' or '2026-09-11;1d' after IN (for a list of values write IN ('a', 'b')) but found 'CITY'

time IN 'interval'

Syntax:

time_column [NOT] IN 'YYYY'
time_column [NOT] IN 'YYYY-MM'
time_column [NOT] IN 'YYYY-MM-DD'
time_column [NOT] IN 'YYYY-MM-DDTHH[:MM[:SS]]'
time_column [NOT] IN 'text+05:45'
time_column [NOT] IN 'text' TIME ZONE 'zone'
time_column [NOT] IN 'text;stretch'
time_column [NOT] IN 'text;stretch;period;count'
time_column [NOT] IN today() | yesterday() | tomorrow() | interval(from, to)

A text after IN (without ( )) means the whole period that the text names: a year, a month, a day, an hour, a minute or a second.

• Returns: true or false. NOT IN gives the opposite.
• NULL: a NULL time gives NULL.
• Notes: without a zone, the period is in UTC. The whole trading day of the sample data (05:15 to 09:15 UTC) is inside one UTC day, so time IN '2026-09-10' is fine for market data. For a Nepal calendar day (daily bars stored at Nepal midnight) add TIME ZONE 'Asia/Kathmandu', or +05:45 in the text. Not both: text with its own offset and TIME ZONE is an error.
• TIME ZONE takes the same zone names as SAMPLE BY. On a DATE column it changes nothing: a date is already a calendar day.
• On the time column of a time-series table, KDB reads only the partitions of that period.
• The left side must be a TIMESTAMP or DATE.
• QuestDB's longer forms with ; and today() work too: see interval lists.
• More: times and time zones.

Example: All daily candles of September 2026, per symbol (sample data: sample)

SELECT symbol, count(*) AS days, min(low) AS lowest, max(high) AS highest
FROM ochl_day
WHERE time IN '2026-09'
GROUP BY symbol;

Result:

(3 rows)

Example: The 12:00 Nepal hour of CITY on 2026-09-10 (sample data: sample)

SELECT count(*) AS minutes, min(time) AS first_minute, max(time) AS last_minute
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10T12+05:45';

Result:

(1 row)

Example: Users who joined in September by Nepal date (sample data: query-app)

SELECT username, created_at
FROM app_users
WHERE created_at IN '2026-09+05:45';

Result:

(1 row)

Example: Everything except 2026 (sample data: sample)

SELECT count(*) AS old_rows FROM ochl_day WHERE time NOT IN '2026';

Result:

(1 row)

Example: Only times can be IN a period (sample data: sample; shows an error)

SELECT * FROM ochl_day WHERE symbol IN '2026';

Result:

Error: line 1, column 37: IN '...' needs a time or date on the left

Interval lists, today() and DECLARE

Syntax:

time_column IN 'text;stretch'
time_column IN 'text;stretch;period;count'
time_column IN today() | yesterday() | tomorrow() | interval(from, to)
DECLARE @name := value [, @name := value ...] SELECT ... @name ...

The QuestDB forms of a time filter. Each part after a ; is a count and a unit: T millisecond, s second, m minute, h hour, d day, w week, M month, y year.

• Returns: true or false. NOT IN gives the opposite.
• Notes:
  • The stretch moves the end later: the first part names a period, the stretch adds to its end.
  • text;stretch;period;count repeats the window count times, each one period after the one before. The count is at most 100,000.
  • On the time column of a time-series table, KDB reads only the partitions that meet one of the windows, and skips the blocks in the gaps between them, as for a plain time IN '2026-09-10'.
  • today(), yesterday(), tomorrow() and interval() are worked out once, when the query starts. The value after IN must be the same for every row: time IN interval(time, time) is an error.
  • TIME ZONE goes with a text only. Give today() the zone instead: today('Asia/Kathmandu').
  • DECLARE @name := value in front of a query sets a name for a value. Each @name in the query stands for that value, as if it were written there. A name holds only for its own statement.

Example: A day and the day after, with ;1d (sample data: sample)

SELECT symbol, count(*) AS days FROM ochl_day WHERE time IN '2026-09-09;1d' GROUP BY symbol ORDER BY symbol;

Result:

(3 rows)

Example: The first hour of trading on five days (05:15 to 06:15 UTC) (sample data: sample)

SELECT timestamp_floor('1d', time) AS day, count(*) AS minutes, min(time) AS first_minute, max(time) AS last_minute
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-07T05:15;59m;1d;5'
GROUP BY timestamp_floor('1d', time)
ORDER BY day;

Result:

(2 rows)

Example: Today's live prices (sample data: sample)

SELECT count(*) AS rows_today FROM live_data WHERE timestamp IN today();

Result:

(1 row)

Example: interval(from, to): both ends are included (sample data: sample)

SELECT count(*) AS minutes FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN interval('2026-09-10T05:15:00Z', '2026-09-10T05:19:00Z');

Result:

(1 row)

Example: DECLARE names a value for the query (sample data: sample)

DECLARE @day := '2026-09-10', @sym := 'CITY'
SELECT symbol, close FROM ochl_day WHERE time IN @day AND symbol = @sym;

Result:

(1 row)

Example: A count is needed after a period (sample data: sample; shows an error)

SELECT count(*) FROM ochl_day WHERE time IN '2026-09-10;1d;1d';

Result:

Error: line 1, column 42: '2026-09-10;1d;1d' has a period but no count: write time;stretch;period;count, as in '2026-09-11T10:00;30m;1d;5'

LIKE, NOT LIKE

Syntax:

text [NOT] LIKE 'pattern'

Matches text with a simple pattern.

• Returns: true or false. NOT LIKE gives the opposite.
• NULL: NULL when the text or the pattern is NULL.
• Notes: the whole text must match: 'CITY' LIKE 'CIT' is false, 'CITY' LIKE 'CIT%' is true.
• A number or symbol on the left is compared as its text.
• For patterns that ignore case, use ILIKE. For more power, use regular expressions.

Example: Securities with Bank in the name (sample data: sample)

SELECT symbol, name FROM securities WHERE name LIKE '%Bank%';

Result:

(2 rows)

Example: Gmail users (case matters: GITA.RAI@Gmail.com is not found) (sample data: query-app)

SELECT username, email FROM app_users WHERE email LIKE '%@gmail.com';

Result:

(3 rows)

Example: Usernames of exactly 4 characters (sample data: query-app)

SELECT username FROM app_users WHERE username LIKE '____';

Result:

(2 rows)

Example: Posts that do not say CITY (sample data: content-forum)

SELECT id, author, body FROM posts WHERE body NOT LIKE '%CITY%';

Result:

(7 rows)

Example: Usernames with a real _ in them: write _ (sample data: query-app)

SELECT username FROM app_users WHERE username LIKE '%\_%';

Result:

(2 rows)

Example: A price of 100% or more in a note: \% is the % sign (sample data: sample)

SELECT '100% up' LIKE '100\%%' AS a, '1000 up' LIKE '100\%%' AS b;

Result:

(1 row)

ILIKE, NOT ILIKE

Syntax:

text [NOT] ILIKE 'pattern'

Like LIKE, but English letters match in any case: 'City' ILIKE 'CIT%' is true.

• Returns: true or false. NOT ILIKE gives the opposite.
• NULL: NULL when the text or the pattern is NULL.
• Notes: only the English letters A-Z are changed. Languages without upper and lower case (like Nepali) work the same as with LIKE.
• In PostgreSQL ILIKE works the same way.

Example: Gmail users in any case (sample data: query-app)

SELECT username, email FROM app_users WHERE email ILIKE '%@gmail.com';

Result:

(4 rows)

Example: Search securities by a word the user typed (sample data: sample)

SELECT symbol, name FROM securities WHERE name ILIKE '%hydro%' OR symbol ILIKE 'acme';

Result:

(2 rows)

Example (sample data: sample)

SELECT 'City' ILIKE 'CIT%' AS a, 'City' NOT ILIKE 'cit%' AS b, 'City' LIKE 'CIT%' AS c;

Result:

(1 row)

SIMILAR TO, NOT SIMILAR TO

Syntax:

text [NOT] SIMILAR TO 'pattern' [ESCAPE 'c']

A pattern between LIKE and a regular expression. The whole text must match.

• Returns: true or false. NOT SIMILAR TO gives the opposite.
• NULL: NULL when the text or the pattern is NULL.
• Notes: upper and lower case must match. It works like PostgreSQL's SIMILAR TO: KDB turns the pattern into a regular expression (similar_to_escape('a%') shows it) and reads every row.

Example: Codes that are two letters and then 2 to 4 digits (sample data: sample)

SELECT code, code SIMILAR TO '[A-Z]{2}[0-9]{2,4}' AS ok
FROM (VALUES ('NP123'), ('N1234'), ('AB12'), ('AB12345')) AS c(code);

Result:

(4 rows)

Example: One word or another, anywhere in the text (sample data: sample)

SELECT 'hydro power' SIMILAR TO '%(hydro|solar)%' AS a, 'wind' NOT SIMILAR TO '%(hydro|solar)%' AS b,
       '50%' SIMILAR TO '50#%' ESCAPE '#' AS c;

Result:

(1 row)

IS NULL, IS NOT NULL

Syntax:

value IS NULL
value IS NOT NULL

NULL means "no value". IS NULL is true when there is no value. IS NOT NULL is true when there is one.

• Returns: always true or false, never NULL.
• Notes: x = NULL does not work: it is always NULL, so no row matches. Use x IS NULL.
• NaN ("not a number") in a DOUBLE or FLOAT column:
  • A NaN made by an expression (CAST('NaN' AS DOUBLE), 'nan'::double, a driver's NaN parameter) is stored as NULL, so IS NULL finds it. 0 / 0.0 is an error (division by zero).
  • The text 'NaN' written straight into the column is stored as NaN. It is not NULL: find it with is_nan(x). See DOUBLE.

Example: Users who never logged in (sample data: query-app)

SELECT id, username, created_at FROM app_users WHERE last_login IS NULL;

Result:

(2 rows)

Example: News about the whole market (no symbol) (sample data: content-news)

SELECT id, title FROM news WHERE symbol IS NULL;

Result:

(1 row)

Example: Credit lines of the ledger (sample data: query-app)

SELECT line_id, account, credit, memo FROM ledger WHERE credit IS NOT NULL AND entry_id <= 3;

Result:

(3 rows)

Example: = NULL finds nothing (sample data: query-app)

SELECT count(*) AS with_equals FROM app_users WHERE last_login = NULL;
SELECT count(*) AS with_is_null FROM app_users WHERE last_login IS NULL;

Result:

SELECT count(*) AS with_equals FROM app_users WHERE last_login = NULL

(1 row)

SELECT count(*) AS with_is_null FROM app_users WHERE last_login IS NULL

(1 row)

Row comparison: (a, b) < (x, y)

Syntax:

(a, b, ...) = (x, y, ...)
(a, b, ...) != (x, y, ...)
(a, b, ...) < (x, y, ...)       also <=, >, >=

Compares lists of values in order, like words in a dictionary: first a with x; only when they are equal, b with y, and so on.

• Returns: true or false (or NULL, by the rules of AND and OR).
• Notes: both lists must have the same number of values. A list can be compared with another list, or looked up with (a, b) IN.
• The main use is keyset paging (also called seek paging): to get the next page, ask for the rows after the last row you showed, in ORDER BY order. It stays fast on page 1000, unlike OFFSET (see paging).
• A list cannot hold a subquery or a window function.

Example: Keyset paging of forum posts, newest first: page 1 (sample data: content-forum)

SELECT id, author, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 3;

Result:

(3 rows)

Example: Page 2: the rows after the last row of page 1 (post 6) (sample data: content-forum)

SELECT id, author, created_at FROM posts
WHERE (created_at, id) < ('2026-09-01T12:00:00+05:45', 6)
ORDER BY created_at DESC, id DESC
LIMIT 3;

Result:

(3 rows)

Example: Daily candles after (CITY, 2026-09-08), in symbol and time order (sample data: sample)

SELECT symbol, time, close FROM ochl_day
WHERE (symbol, time) > ('CITY', '2026-09-08') AND time IN '2026-09'
ORDER BY symbol, time
LIMIT 4;

Result:

(4 rows)

Example (sample data: sample)

SELECT (2, 5) < (2, 7) AS a, (1, 'b') >= (1, 'a') AS b, (1, 2) = (1, 2) AS c, (1, NULL) = (1, 2) AS d;

Result:

(1 row)

Example: Both lists need the same number of values (sample data: sample; shows an error)

SELECT symbol FROM ochl_day WHERE (symbol, time) < ('CITY', '2026-09-08', 1);

Result:

Error: line 1, column 50: the two lists compared have 2 and 3 values; expected the same number

(a, b) IN a list of keys

Syntax:

(a, b, ...) [NOT] IN ((x1, y1, ...), (x2, y2, ...), ...)
(a, b, ...) [NOT] IN (VALUES (x1, y1, ...), (x2, y2, ...), ...)
(a, b, ...) [NOT] IN (SELECT x, y, ... FROM ...)

True when the values of the row are equal, all together, to one of the listed keys. (symbol, time) IN (('CITY', '2026-09-10'), ('COAST', '2026-09-09')) means (symbol = 'CITY' AND time = '2026-09-10') OR (symbol = 'COAST' AND time = '2026-09-09').

• Returns: true or false. NOT IN gives the opposite.
• NULL: a key with a NULL that is not ruled out by another value is unknown: ('CITY', NULL) never matches, and NOT IN a list holding it is never true for CITY rows.
• Notes:
  • Works in SELECT, UPDATE and DELETE. Every key must have as many values as the list; a subquery must give that many columns.
  • Fast for long key lists: when the list covers the table's first key columns (the PRIMARY KEY, or the SORT BY / DEDUP KEY of a time-series table), KDB reads only those keys, and with the time column it reads only the partitions of those times. Deleting 200 (symbol, time) bars spread over a year reads their own partitions, not the whole year.
  • A subquery that uses columns of the outer query is not supported here; write EXISTS (SELECT ... WHERE x = a AND y = b).

Example: Two daily candles picked by (symbol, day) (sample data: sample)

SELECT symbol, time, close FROM ochl_day
WHERE (symbol, time) IN (('CITY', '2026-09-10'), ('COAST', '2026-09-09'))
ORDER BY symbol;

Result:

(2 rows)

Example: The same keys from a VALUES list and from a table (sample data: sample)

Setup:

CREATE TABLE picks (symbol STRING, day TIMESTAMP);
INSERT INTO picks VALUES ('CITY', '2026-09-10'), ('COAST', '2026-09-09');

SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (VALUES ('CITY', '2026-09-10'), ('COAST', '2026-09-09')) ORDER BY symbol;
SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (SELECT symbol, day FROM picks) ORDER BY symbol;
EXPLAIN SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (('CITY', '2026-09-10'), ('COAST', '2026-09-09'));

Result:

SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (('CITY', '2026-09-10'), ('COAST',

(2 rows)

SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (SELECT symbol, day FROM picks) ORD

(2 rows)

EXPLAIN SELECT symbol, time, close FROM ochl_day WHERE (symbol, time) IN (('CITY', '2026-09-10'), ('

(5 rows)

Example: Every key needs as many values as the list (sample data: sample; shows an error)

SELECT * FROM ochl_day WHERE (symbol, time) IN (('CITY'), ('COAST'));

Result:

Error: line 1, column 50: expected a list of 2 values like (1, 'x') for each item of (a, b) IN (...), got 'CITY'

NULL in WHERE

WHERE keeps a row only when the condition is true. When the condition is NULL (unknown), the row is left out, the same as false.

This surprises people with != and NOT IN: a row whose value is NULL is not "different from Kathmandu", it is unknown, so it is not shown.

• Notes: HAVING and the ON of a join treat NULL the same way.
• To keep NULL rows, say so: city != 'Kathmandu' OR city IS NULL, or use coalesce(city, '') != 'Kathmandu' (see coalesce).

Example: Users not in Kathmandu: the users without a city are missing (sample data: query-app)

SELECT username, city FROM app_users WHERE city != 'Kathmandu';

Result:

(4 rows)

Example: Keep the NULL cities too (sample data: query-app)

SELECT username, city FROM app_users WHERE city != 'Kathmandu' OR city IS NULL;

Result:

(6 rows)

Example: The same with coalesce (sample data: query-app)

SELECT username, coalesce(city, '(no city)') AS city FROM app_users WHERE coalesce(city, '') <> 'Kathmandu';

Result:

(6 rows)

Fast conditions

Syntax:

column op value [AND column op value ...]

KDB runs a query in one of two ways. The column engine reads whole columns at a time, on all threads. The row engine looks at one row after another. The column engine is many times faster. It takes a query only when every part of the WHERE (the parts joined by AND) has one of these shapes:

These send the whole query to the row engine. The result is the same, only slower:

• Notes:
  • A table with computed columns (amount DOUBLE AS (...)) always uses the row engine.
  • The column engine runs GROUP BY and aggregates, LATEST ON, ORDER BY column LIMIT n, and plain lists of columns. What else keeps a GROUP BY in it: see GROUP BY with many groups.
  • LIKE 'CITY' without % or _ is the same as = 'CITY': it also reads only CITY's rows.
  • Inside NOT, OR and AND a comparison with a NULL is still NULL, as on the row engine: NOT (volume > 10) does not keep a row whose volume is NULL. One shape the column engine cannot read anywhere in the tree (NOT (close * 2 > 400)) sends the query to the row engine.
  • Only a plain OR on one column (the same as an IN list) or a lone symbol = ... lets KDB skip other symbols' partitions; an OR across columns reads them all, in the column engine.
  • EXTRACT(YEAR FROM time) = 2026 AND EXTRACT(MONTH FROM time) = 8, year(time) = 2026 and date_trunc('month', time) = '2026-08-01' on a TIMESTAMP column are fast too: KDB reads them as the time range they mean (in UTC), so only that month's partitions are read (EXPLAIN: time range from the calendar condition(s)). EXTRACT(MONTH FROM time) = 8 alone, without a year, is not a range and uses the row engine, and so does a month or day that no date has (0, 13): it keeps no row.
  • A condition on a number, DATE or TIMESTAMP column also lets KDB skip partitions whose values are all outside it (see zone maps).

Example: Fast: every part is a column against a value (sample data: sample)

SELECT time, symbol, close
FROM ochl_day
WHERE (symbol = 'CITY' OR symbol = 'COAST') AND close > 400 AND time IN '2026-09'
ORDER BY time, symbol
LIMIT 4;

Result:

(4 rows)

Example: Fast too: a chart that leaves out empty bars with NOT ( ... AND ... ) (sample data: sample)

SELECT time, open, high, low, close, volume
FROM ochl_day
WHERE symbol = 'CITY' AND NOT (volume <= 0 AND high = low AND open = close)
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Example: Slow and fast ways to ask the same thing: the same rows either way (sample data: sample)

SELECT count(*) AS slow FROM ochl_day WHERE upper(symbol) = 'CITY' AND close * 2 > 1000;
SELECT count(*) AS fast FROM ochl_day WHERE symbol = 'CITY' AND close > 500;

Result:

SELECT count(*) AS slow FROM ochl_day WHERE upper(symbol) = 'CITY' AND close * 2 > 1000

(1 row)

SELECT count(*) AS fast FROM ochl_day WHERE symbol = 'CITY' AND close > 500

(1 row)