SELECT statement
The SELECT statement can be used for selecting and querying data in a database. Each SELECT statement supports selecting from multiple targets, which can include tables, records, edges, subqueries, parameters, arrays, objects, and other values.
In the Learn more section, you can find a video that explains how to use the SELECT statement to retrieve and query data from SurrealDB.
Statement syntax
SurrealQL Syntax
SELECT
VALUE @field | @fields [ AS @alias ] [ OMIT @fields ... ]
FROM [ ONLY ] @targets
[ WITH [ NOINDEX | INDEX @indexes ... ]]
[ WHERE @conditions ]
[ SPLIT [ ON ] @field, ... ]
[
GROUP [ ALL | [ BY ] @field, ... ] |
ORDER [ BY ] RAND() | @field [ COLLATE ] [ NUMERIC ] [ ASC | DESC ], ...
]
[ LIMIT [ BY ] @limit ]
[ START [ AT ] @start 0 ]
[ FETCH @fields ... ]
[ TIMEOUT @duration ]
[ TEMPFILES ]
[ EXPLAIN [ FULL ] ]
;
Example usage
Basic usage
By default, SurrealDB returns an array of JSON-like objects called records instead of a tabular structure of rows and columns.
CREATE person:tobie SET
name.first = "Tobie",
address = "1 Bagshot Row",
email = "tobie@surrealdb.com";
SELECT * FROM person;
SELECT name, address, email FROM person;
SELECT * FROM person:tobie;
SELECT name, address, email FROM person:tobie;
SELECT * FROM ONLY person:tobie;
An alias can be used to rename fields or change the structure of an object.
SELECT * FROM person;
SELECT
name.first AS user_name,
string::uppercase(address)
FROM person;
SELECT
name.first,
"Morgan Hitchcock" AS name.last,
string::uppercase(address) + "!!!" AS angry_address
FROM person;
Output
[
{
address: '1 Bagshot Row',
email: 'tobie@surrealdb.com',
id: person:tobie,
name: {
first: 'Tobie'
}
}
]
[
{
"string::uppercase": '1 BAGSHOT ROW',
user_name: 'Tobie'
}
]
[
{
angry_address: '1 BAGSHOT ROW!!!',
name: {
first: 'Tobie',
last: 'Morgan Hitchcock'
}
}
]
SurrealDB can also return specific fields as an array of values instead of the default array of objects. This only works if you select a single un-nested field from a table or a record.
SELECT VALUE name FROM person;
SELECT VALUE name FROM person:00e1nc508h9f7v63x72O;
Advanced expressions
SELECT queries support advanced expression in the field projections.
SELECT address.city FROM person;
SELECT address.*.coordinates AS coordinates FROM person;
SELECT address.coordinates AS coordinates FROM person;
SELECT address.coordinates[0] AS latitude FROM person;
SELECT array::distinct(tags) FROM article;
SELECT array::group(tags) AS tags FROM article GROUP ALL;
SELECT
(( celsius * 1.8 ) + 32) AS fahrenheit
FROM temperature;
SELECT rating >= 4 as positive FROM review;
SELECT
{ weekly: false, monthly: true } AS `marketing settings`
FROM user;
SELECT address[WHERE active = true] FROM person;
SELECT * FROM person WHERE ->(reacted_to WHERE type='celebrate')->post;
SELECT ->likes->friend.name AS friends FROM person:tobie;
SELECT *, (SELECT * FROM events WHERE type = 'activity' LIMIT 5) AS history FROM user;
SELECT address.{city, country} FROM person;
Using parameters
Parameters can be used like variables to store a value which can then be used in a subsequent query.
More info on the $parent parameter in the second example can be seen on the page for predefined variables.
LET $avg_price = (
SELECT math::mean(price) AS avg_price FROM product GROUP ALL
).avg_price;
SELECT name FROM product
WHERE [price] > $avg_price;
SELECT *, (SELECT * FROM events WHERE host == $parent.id) AS hosted_events FROM user;
Numeric ranges in a WHERE clause
Available since: v2.0.0
A numeric range inside a WHERE clause can improve performance if the range is able to replace multiple checks on a certain condition. The following code should show a modest but measurable improvement in performance between the first and second SELECT statement, as only one condition needs to be checked instead of two.
DELETE person;
CREATE |person:20000| SET age = (rand::float() * 120).round() RETURN NONE;
LET $_ = SELECT * FROM person WHERE age > 18 AND age < 65;
LET $_ = SELECT * FROM person WHERE age in 18..=65;
A numeric range inside a WHERE also tends to produce shorter code that is easier to read and maintain.
SELECT * FROM person WHERE age >= 18 AND age <= 65;
SELECT * FROM person WHERE age IN 18..=65;
Record ranges
SurrealDB supports the ability to query a range of records, using the record ID. The record ID ranges, retrieve records using the natural sorting order of the record IDs. These range queries can be used to query a range of records in a timeseries context. You can see more here about array-based Record IDs.
SELECT * FROM person:1..1000;
SELECT * FROM temperature:['London', NONE]..=['London', time::now()];
SELECT * FROM temperature:..['London', '2022-08-29T08:09:31'];
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..;
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];
Using a record range is more performant than the WHERE clause, as it does not require a table scan.
CREATE |person:1..5000| RETURN NONE;
LET $now = time::now();
LET $_ = SELECT * FROM person:1..5000;
LET $time1 = time::now() - $now;
LET $now = time::now();
LET $_ = SELECT * FROM person WHERE id >= 1 and id <= 5000;
LET $time2 = time::now() - $now;
RETURN [$time1, $time2];
Skip certain fields using the OMIT clause
Sometimes, especially with tables containing numerous columns, it is desirable to select all columns except a few specific ones. The OMIT clause can be used in this case.
CREATE person:tobie SET
name = 'Tobie',
password = '123456',
opts.security = 'secure',
opts.enabled = true;
CREATE person:jaime SET
name = 'Jaime',
password = 'asdfgh',
opts.security = 'secure',
opts.enabled = false;
SELECT * FROM person;
SELECT * OMIT password, opts.security FROM person;
SELECT * OMIT password, opts.{ security, enabled } FROM person;
More on using the FROM clause
The FROM clause can be used on targets beyond just a single table or record name.
SELECT * FROM user, admin;
LET $table = "user";
SELECT * FROM type::table($table) WHERE admin = true;
LET $table = "user";
LET $id = "admin";
SELECT * FROM type::record($table, $id);
SELECT * FROM user:tobie, user:jaime, company:surrealdb;
SELECT * FROM [3648937, "test", person:lrym5gur8hzws72ux5fa, person:4luro9170uwcv1xrfvby];
SELECT * FROM { person: person:lrym5gur8hzws72ux5fa, embedded: true };
SELECT * FROM (SELECT age >= 18 AS adult FROM user) WHERE adult = true;
Filter queries using the WHERE clause
As with traditional SQL queries, a SurrealDB SELECT query supports conditional filtering using a WHERE clause. If the expression in the WHERE clause is truthy (is present and not an empty value), then the respective record will be returned.
SELECT * FROM article WHERE published = true;
SELECT * FROM profile WHERE count(->experience->organisation) > 3;
SELECT * FROM person WHERE ->(reaction WHERE type='celebrate')->post;
SELECT * FROM user WHERE (admin AND active) OR owner = true;
SELECT address[WHERE active = true] FROM person;
SELECT name FROM person WHERE name;
The SPLIT clause
As SurrealDB supports arrays and nested fields within arrays, it is possible to use the SPLIT clause to split the result on a specific field name, returning each value in an array as a separate value, along with the record content itself. This is useful in data analysis contexts.
CREATE user SET
name = "Name",
emails = ["me@me.com", "longer_email@other_service.com"];
SELECT * FROM user SPLIT emails;
Output
[
{
emails: 'me@me.com',
id: user:tr5sxe8iygdco05faoh0,
name: 'Name'
},
{
emails: 'longer_email@other_service.com',
id: user:tr5sxe8iygdco05faoh0,
name: 'Name'
}
]
Other examples using the SPLIT clause:
SELECT * FROM country SPLIT locations.cities;
SELECT * FROM (SELECT * FROM person SPLIT loggedin) WHERE loggedin > '2023-05-01';
The GROUP BY and GROUP ALL clause
SurrealDB supports data aggregation and grouping, with support for multiple fields, nested fields, and aggregate functions. In SurrealDB, every field which appears in the field projections of the select statement (and which is not an aggregate function), must also be present in the GROUP BY clause.
SELECT country FROM user GROUP BY country;
SELECT settings.published FROM article GROUP BY settings.published;
SELECT gender, country, city FROM person GROUP BY gender, country, city;
SELECT array::group(tags) AS tags FROM article GROUP ALL;
A longer example of grouping using aggregate functions:
INSERT INTO person [
{ gender: "M", age: 20, country: "Japan" },
{ gender: "M", age: 25, country: "Japan" },
{ gender: "F", age: 23, country: "US" },
{ gender: "F", age: 30, country: "US" },
{ gender: "F", age: 25, country: "Korea" },
{ gender: "F", age: 45, country: "UK" },
];
SELECT
count() AS total,
math::mean(age) AS average_age,
gender,
country
FROM person
GROUP BY gender, country;
SELECT count() AS number_of_records FROM person GROUP ALL;
Output
[
{
average_age: 25,
country: 'Korea',
gender: 'F',
total: 1
},
{
average_age: 45,
country: 'UK',
gender: 'F',
total: 1
},
{
average_age: 26,
country: 'US',
gender: 'F',
total: 2
},
{
average_age: 22,
country: 'Japan',
gender: 'M',
total: 2
}
]
[
{
number_of_records: 6
}
]
GROUP and SPLIT incompatibility
The GROUP and SPLIT clauses are incompatible with each other due to opposing behaviour: while SPLIT is a post-processing clause that multiplies the output of a query, GROUP works in the other way by collapsing the output.
Versions before 3.0.0-beta allowed these two clauses to be used together, after which attempting to do so results in a parsing error.
SELECT * FROM person SPLIT name GROUP BY name;
Output
'Parse error: SPLIT and GROUP are mutually exclusive
--> [6:22]
|
6 | SELECT * FROM person SPLIT name GROUP BY name;
| ^^^^^^^^^^ SPLIT cannot be used with GROUP
--> [6:33]
|
6 | SELECT * FROM person SPLIT name GROUP BY name;
| ^^^^^^^^^^^^^ GROUP cannot be used with SPLIT
'
Disallowing the two clauses together forces a query that uses both to have one inside a subquery, which makes it clear which operation is to be performed first.
CREATE user SET
name = "Jack",
emails = ["my@firstemail.com", "another@builder.com"],
age = 37;
CREATE user SET
name = "Ellen",
emails = ["ruler@forest.com", "wife@tom.com"],
age = 50;
CREATE user SET
name = "Phillip",
emails = ["prior@kingsbridge.com", "boss@remigius.com"],
age = 50;
SELECT age, emails FROM (SELECT * FROM user SPLIT emails) GROUP BY age;
SELECT age, emails
FROM (
SELECT age, array::group(emails) AS emails
FROM user
GROUP BY age
)
SPLIT emails;
Using a COUNT index to speed up count() in GROUP ALL queries
Available since: v3.0.0
To speed up the count() function along with GROUP ALL to get the total number of records in a table, a COUNT index can be used. This keeps track of the total number of records as a single value as opposed to a dynamic iteration of the table to get the full count every time a query is run.
DEFINE INDEX person_count ON person COUNT;
SELECT count() AS number_of_records FROM person GROUP ALL;
math::stddev() and math::variance() in table views
Available since: v3.0.0
The math::stddev() and math::variance() functions can also be used in table views.
DEFINE TABLE person SCHEMALESS;
DEFINE TABLE person_stats AS
SELECT
count(),
age,
math::stddev(score) AS score_stddev,
math::variance(score) AS score_variance
FROM person
GROUP BY age;
INSERT INTO person [
{ id: person:alice, age: 25, score: 80 },
{ id: person:alices_rival, age: 25, score: 88 },
{ id: person:bob, age: 24, score: 90 },
{ id: person:bobs_rival, age: 24, score: 99 },
{ id: person:charlie, age: 23, score: 70 },
{ id: person:charlies_rival, age: 23, score: 77 }
];
SELECT * FROM person_stats WHERE age >= 24;
Output:
[
{
age: 24,
count: 2,
id: person_stats:[
24
],
score_stddev: 6.363961030678927719607599259dec,
score_variance: 40.50dec
},
{
age: 25,
count: 2,
id: person_stats:[
25
],
score_stddev: 5.656854249492380195206754897dec,
score_variance: 32dec
}
]
Sort records using the ORDER BY clause
To sort records, SurrealDB allows ordering on multiple fields and nested fields. Use the ORDER BY clause to specify a comma-separated list of field names that should be used to order the resulting records. The ASC and DESC keywords can be used to specify whether results should be sorted in an ascending or descending manner. The COLLATE keyword can be used to use Unicode collation when ordering text in string values, ensuring that different cases, and different languages are sorted in a consistent manner. Finally, the NUMERIC can be used to correctly sort text which contains numeric values.
SELECT * FROM user ORDER BY rand();
SELECT * FROM song ORDER BY rating DESC;
SELECT * FROM song ORDER BY artist ASC, rating DESC;
SELECT * FROM article ORDER BY title COLLATE ASC;
SELECT * FROM article ORDER BY title NUMERIC ASC;
The LIMIT clause
To limit the number of records returned, use the LIMIT clause.
SELECT * FROM person LIMIT 50;
When using the LIMIT clause, it is possible to paginate results by using the START clause to start from a specific record from the result set. It is important to note that the START count starts from 0.
SELECT * FROM user LIMIT 50 START 50;
The LIMIT clause followed by 1 is often used along with the ONLY clause to satisfy the requirement that only up to a single record can be returned.
SELECT * FROM ONLY person:jamie;
SELECT * FROM ONLY person WHERE name = "Jaime";
SELECT * FROM ONLY person WHERE name = "Jaime" LIMIT 1;
SELECT * FROM [1,2,3,4,5,6,7,8,9,10] LIMIT 5 START 4;
Result
Connect targets using the FETCH clause
Two of the most powerful features in SurrealDB are record links and graph connections.
Instead of pulling data from multiple tables and merging that data together, SurrealDB allows you to traverse related records efficiently without needing to use JOINs.
To fetch and replace records with the remote record data, use the FETCH clause to specify the fields and nested fields which should be fetched in-place, and returned in the final statement response output.
SELECT *, artist.email FROM review FETCH artist;
SELECT * FROM article WHERE author.age < 30 FETCH author;
The TIMEOUT clause
When processing a large result set with many interconnected records, it is possible to use the TIMEOUT keyword to specify a timeout duration for the statement. If the statement continues beyond this duration, then the transaction will fail, and the statement will return an error.
SELECT * FROM person WHERE ->knows->person->(knows WHERE influencer = true) TIMEOUT 5s;
The TEMPFILES clause
Available since: v2.0.0
When processing a large result set with many records, it is possible to use the TEMPFILES clause to specify that the statement should be processed in temporary files rather than memory.
This significantly reduces memory usage in exchange for slower performance.
SELECT * FROM person ORDER BY name TEMPFILES;
This requires the temporary directory to be set in the server configuration or when using the surreal start command.
The EXPLAIN clause
When EXPLAIN is used, the SELECT statement returns an explanation, essentially revealing the execution plan to provide transparency and understanding of the query performance. EXPLAIN can be followed by FULL to see the number of executed rows.
Here is the result when the field ‘email’ is not indexed. We can see that the execution plan will iterate over the whole table.
CREATE person:tobie SET
name = "Tobie",
address = "1 Bagshot Row",
email = "tobie@surrealdb.com";
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;
Output
[
{
detail: {
table: 'person'
},
operation: 'Iterate Table'
},
{
detail: {
type: 'Memory'
},
operation: 'Collector'
}
]
[
{
detail: {
table: 'person'
},
operation: 'Iterate Table'
},
{
detail: {
type: 'Memory'
},
operation: 'Collector'
},
{
detail: {
count: 1
},
operation: 'Fetch'
}
]
Here is the result when the ‘email’ field is indexed. We can see that the execution plan will proceed by utilizing the index.
DEFINE INDEX fast_email ON TABLE person FIELDS email;
CREATE person:tobie SET
name = "Tobie",
address = "1 Bagshot Row",
email = "tobie@surrealdb.com";
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN;
SELECT * FROM person WHERE email='tobie@surrealdb.com' EXPLAIN FULL;
Output
[
{
detail: {
plan: {
index: 'fast_email',
operator: '=',
value: 'tobie@surrealdb.com'
},
table: 'person'
},
operation: 'Iterate Index'
},
{
detail: {
type: 'Memory'
},
operation: 'Collector'
}
]
[
{
detail: {
plan: {
index: 'fast_email',
operator: '=',
value: 'tobie@surrealdb.com'
},
table: 'person'
},
operation: 'Iterate Index'
},
{
detail: {
type: 'Memory'
},
operation: 'Collector'
},
{
detail: {
count: 1
},
operation: 'Fetch'
}
]
The WITH clause
The query planner can replace the standard table iterator with one or several index iterators based on the structure and requirements of the query. However, there may be situations where manual control over these potential optimizations is desired or required.
For instance, the cardinality of an index can be high, potentially even equal to the number of records in the table. The sum of the records iterated by several indexes may end up being larger than the number of records obtained by iterating over the table. In such cases, if there are different index possibilities, the most probable optimal choice would be to use the index known with the lowest cardinality.
WITH INDEX @indexes ... restricts the query planner to using only the specified index(es)WITH NOINDEX forces the query planner to use the table iterator.
SELECT * FROM person
WITH INDEX ft_email
WHERE
email = 'tobie@surrealdb.com' AND
company = 'SurrealDB';
SELECT name FROM person WITH NOINDEX WHERE job = 'engineer' AND gender = 'm';
The ONLY clause
If you are selecting just one single resource, it’s possible to use the ONLY clause to filter that result from an array.
SELECT * FROM ONLY person:john;
If you are selecting from a resource where it is possible that multiple resources are returned, it is required to LIMIT the result to just one. This is needed, because the query would otherwise not be deterministic.
SELECT * FROM ONLY table_name;
SELECT * FROM ONLY table_name LIMIT 1;
The VERSION clause
Available since: v2.0.0
When you are starting a new database with memory or SurrealKV as the storage engine with versioning enabled, you can specify a version for each record. This is useful for time-travel queries. You can query a specific version of a record by using the VERSION clause. The VERSION clause is always followed by a datetime and when the specified timestamp does not exist, an empty array is returned.
The VERSION clause is currently in alpha and is subject to change. We do not recommend this for production.
CREATE user:john SET name = 'John' VERSION d'2025-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]
SELECT * FROM user:john;
[[{ id: user:john, name: 'John' }]]
SELECT * FROM user:john VERSION d'2025-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]
SELECT * FROM user:john VERSION d'2025-08-19T07:00:00Z';
[[]]
update user:john Set hight ="55"
[[{ hight: '55', id: user:john, name: 'John' }]]
SELECT * FROM user:john;
[[{ hight: '55', id: user:john, name: 'John' }]]
SELECT * FROM user:john VERSION d'2025-08-19T08:00:00Z';
[[{ id: user:john, name: 'John' }]]
Available since: v2.1.0
The VERSION clause can also take a dynamic value or parameter that resolves to a datetime.
SELECT * FROM user VERSION time::now();
LET $now = time::now();
SELECT * FROM user VERSION $now;
DEFINE FUNCTION fn::yesterday() { time::now() - 1d };
SELECT * FROM user VERSION fn::yesterday();
Selecting inside graph queries
Available since: v2.2.0
A SELECT statement and/or its clauses can be used inside graph queries as well at the graph edge portion of the query.
CREATE |person:1..4|;
RELATE person:1->likes->person:2 SET like_strength = 20, know_in_person = true;
RELATE person:1->likes->person:3 SET like_strength = 5, know_in_person = false;
RELATE person:2->likes->person:1 SET like_strength = 10, know_in_person = true;
RELATE person:2->likes->person:3 SET like_strength = 12, know_in_person = false;
RELATE person:3->likes->person:1 SET like_strength = 2, know_in_person = false;
RELATE person:3->likes->person:2 SET like_strength = 9, know_in_person = false;
SELECT ->likes AS likes FROM person;
SELECT ->(SELECT like_strength FROM likes) AS likes FROM person;
SELECT ->(SELECT like_strength FROM likes WHERE like_strength > 10) AS likes FROM person;
SELECT ->(likes WHERE like_strength > 10) AS likes FROM person;
SELECT ->(SELECT like_strength, know_in_person FROM likes ORDER BY like_strength DESC) AS likes FROM person;
SELECT ->(SELECT count() as count, know_in_person FROM likes GROUP BY know_in_person) AS likes FROM person;
SELECT ->(likes LIMIT 1) AS likes FROM person;
SELECT ->(likes START 1) AS likes FROM person;
For more examples, see the graph clauses section of the page on the RELATE statement.
Learn more
To learn more about using the SELECT statement to retrieve data from SurrealDB, check out this explainer video:
