WHERE keyword

WHERE clause filters data. Filter expressions are required to return boolean result.

Syntax#

The general syntax is as follows. Specific filters have distinct syntaxes detailed thereafter.

Flow chart showing the syntax of the WHERE clause

Logical operators#

QuestDB supports AND, OR, NOT as logical operators and can assemble conditions using brackets ().

Flow chart showing the detailed syntax of the WHERE clause

Example
SELECT * FROM table
WHERE
a = 1 AND (b = 2 OR c = 3 AND NOT d);

Symbol and string#

QuestDB can filter strings and symbols based on equality, inequality, and regular expression patterns.

Exact match#

Evaluates match of a string or symbol.

Flow chart showing the syntax of the WHERE clause with a string comparison

Example
SELECT * FROM users
WHERE name = 'John';
nameage
John31
John45
......

Does NOT match#

Evaluates mismatch of a string or symbol.

Flow chart showing the syntax of the WHERE clause with a string comparison

Example
SELECT * FROM users
WHERE name != 'John';
nameage
Tim31
Tom45
......

Regular expression match#

Evaluates match against a regular expression defined using java.util.regex patterns.

Flow chart showing the syntax of the WHERE clause with a regex comparison

Example
SELECT * FROM users WHERE ~=(name, 'Jo');
nameage
Joe31
Jonathan45
......

Regular expression does NOT match#

Evaluates mismatch against a regular expression defined using java.util.regex patterns.

Flow chart showing the syntax of the WHERE clause with a regex comparison

Example
SELECT * FROM users WHERE !~(name, 'Jo');
nameage
Tim31
Tom45
......

List search#

Evaluates match or mismatch against a list of elements.

Flow chart showing the syntax of the WHERE clause with a list comparison

List match
SELECT * FROM users WHERE name in('Tim', 'Tom');
nameage
Tim31
Tom45
......
List mismatch
SELECT * FROM users WHERE NOT name in('Tim', 'Tom');
nameage
Aaron31
Amelie45
......

Numeric#

QuestDB can filter numeric values based on equality, inequality, comparison, and proximity

note

For timestamp filters, we recommend the timestamp search notation which is faster and less verbose.

Equality, inequality and comparison#

Flow chart showing the syntax of the WHERE clause with a numeric comparison

Superior or equal to 23
SELECT * FROM users WHERE age >= 23;
Equal to 23
SELECT * FROM users WHERE age = 23;
NOT Equal to 23
SELECT * FROM users WHERE age != 23;

Proximity#

Evaluates whether the column value is within a range of the target value. This is useful to simulate equality on double and float values.

Flow chart showing the syntax of the WHERE clause with an EQ comparison

Equal to 23 with 0.00001 precision
SELECT * FROM users WHERE eq(age, 23, 0.00001);
tip

When performing multiple equality checks of double values against integer constants, it may be preferable to store double values as long integers with a scaling factor.

Boolean#

Flow chart showing the syntax of the WHERE clause with a boolean comparison

Using the columnName will return true values. To return false values, precede the column name with the NOT operator.

Example - true
SELECT * FROM users WHERE isActive;
userIdisActive
12532true
38572true
......
Example - false
SELECT * FROM users WHERE NOT isActive;
userIdisActive
876534false
43234false
......

Timestamp and date#

QuestDB supports both its own timestamp search notation and standard search based on inequality. This section describes the use of the timestamp search notation which is efficient and fast but requires a designated timestamp. Remember, designated timestamp can be applied dynamically.

Exact timestamp#

Syntax#

Flow chart showing the syntax of the WHERE clause with a timestamp comparison

Example - Date
SELECT scores WHERE ts = '2010-01-12T00:02:26.000Z';
timestampscore
2010-01-12T00:02:26.000Z2.4
2010-01-12T00:02:26.000Z3.1
......
Example - Timestamp
SELECT scores WHERE ts = '2010-01-12T00:02:26.000000Z';
timestampscore
2010-01-12T00:02:26.000000Z2.4
2010-01-12T00:02:26.000000Z3.1
......

Time range#

Return results within a defined range

Syntax#

Flow chart showing the syntax of the WHERE clause with a partial timestamp comparison

Results in a given year
SELECT * FROM scores WHERE ts = '2018';
timestampscore
2018-01-01T00:0000.000000Z123.4
......
2018-12-31T23:59:59.999999Z115.8
Results in a given minute
SELECT * FROM scores WHERE ts = '2018-05-23T12:15';
timestampscore
2018-05-23T12:15:00.000000Z123.4
......
2018-05-23T12:15:59.999999Z115.8

Time range with modifier#

You can apply a modifier to further customise the range. The algorithm will calculate the resulting range by modifying the upper bound of the original range by the modifier parameter.

Syntax#

Flow chart showing the syntax of the WHERE clause with a timestamp/modifier comparison

multiplier is a signed integer.

  • A positive value extends the interval.
  • A negative value reduces the interval.
Results in a given year and the first month of the next year
SELECT * FROM scores WHERE ts = '2018;1M';

The range is 2018. The modifier extends the upper bound (originally 31 Dec 2018) by one month.

timestampscore
2018-01-01T00:00:00.000000Z123.4
......
2019-01-31T23:59:59.999999Z115.8
Results in a given month excluding the last 3 days
SELECT * FROM scores WHERE ts = '2018-01;-3d';

The range is Jan 2018. The modifier reduces the upper bound (originally 31 Dec 2018) by 3 days.

timestampscore
2018-01-01T00:00:00.000000Z123.4
......
2019-01-28T23:59:59.999999Z115.8

Explicit range#

Syntax#

For non-standard ranges, users can explicitly specify the target range using the in operator.

Flow chart showing the syntax of the WHERE clause with a timestamp range comparison

lower_bound and upper_bound must be valid timestamps or dates and are inclusive.

Explicit range
SELECT * FROM scores
WHERE ts in('2018-01-01T00:00:23.000000Z' , '2018-01-01T00:00:23.500000Z');
timestampvalue
2018-01-01T00:00:23.000000Z123.4
......
2018-01-01T00:00:23.500000Z131.5