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, paramaters, arrays, objects, and other values.

Statement syntax

SELECT @projections
	FROM @targets
	[ WHERE @condition ]
	[ SPLIT [ AT ] @field ... ]
	[ GROUP [ BY ] @field ... ]
	[ ORDER [ BY ]
		@field [
			RAND()
			| COLLATE
			| NUMERIC
		] [ ASC | DESC ] ...
	] ]
	[ LIMIT [ BY ] @limit ]
	[ START [ AT ] @start ]
	[ FETCH @field ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
;

Example usage

The following query shows example usage of this statement.

SELECT age, name, email FROM user;

SELECT queries support advanced expression in the field projections.

-- Use an expression as an alias
SELECT age >= 18 AS adult FROM user;

-- Select nested array values only
SELECT *, tags.*.value AS tags FROM article;

-- Select filtered nested array values
SELECT addresses[WHERE active = true] FROM customer;

-- Select a remote field from connected out graph edges
SELECT ->like->friend.name AS friends FROM person:tobie;

-- Use mathematical calculations in a select expression
SELECT ( ( celsius * 2 ) + 30 ) AS fahrenheit FROM temperatue;

-- Select manually generated object structure
SELECT { weekly: false, monthly: true } AS `marketing settings` FROM user;

-- Use the result of a subquery as a returned field
SELECT *, (SELECT * FROM events WHERE type = 'activity' LIMIT 5) AS history FROM user;

-- Use the parent instance's field in a subquery
SELECT *, (SELECT * FROM events WHERE host == $parent.id) AS self_hosted FROM user;

SurrealDB supports selecting from multiple targets including tables, records, edges, parameters, subqueries, arrays, objects, values.

-- Select from multiple tables
SELECT * FROM user, admin;

-- Select data from a parameter
SELECT * FROM $parameter WHERE admin = true;

-- Select from multiple specific records
SELECT * FROM user:tobie, user:jaime, company:surrealdb;

-- Select from an array of values and records
SELECT * FROM [3648937, "test", person:lrym5gur8hzws72ux5fa, person:4luro9170uwcv1xrfvby];

-- Select from an object with nested values
SELECT * FROM { person: person:lrym5gur8hzws72ux5fa, embedded: true };

-- Select and filter records from a subquery
SELECT * FROM (SELECT age >= 18 AS adult FROM user) WHERE adult = true;

As with traditional SQL queries, the SurrealDB SELECT queries support conditional filtering using a WHERE clause. If the expression in the WHERE clause evaluates to true, then the respective record will be returned.

-- Simple conditional filtering
SELECT * FROM article WHERE published = true;

-- Conditional filtering based on graph edges
SELECT * FROM profile WHERE count(->experience->organisation) > 3;

-- Conditional filtering with boolean logic
SELECT * FROM user WHERE (admin AND active) OR owner = true;

As SurrealDB supports arrays and nested fields within arrays, it is possible 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.

-- Split the results by each value in an array
SELECT * FROM user SPLIT emails;

-- Split the results by each value in a nested array
SELECT * FROM country SPLIT locations.cities;

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.

-- Group records by a single field
SELECT country FROM user GROUP BY country;

-- Group results by a nested field
SELECT settings.published FROM article GROUP BY settings.published;

-- Group results by multiple fields
SELECT gender, country, city FROM person GROUP BY gender, country, city;

-- Group results with aggregate functions
SELECT count() AS total, math::sum(age), gender, country FROM person GROUP BY gender, country;

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 which 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.

-- Order records randomly
SELECT * FROM user ORDER BY RAND();

-- Order records descending by a single field
SELECT * FROM song ORDER BY rating DESC;

-- Order records by multiple fields independently
SELECT * FROM song ORDER By artist ASC, rating DESC;

-- Order text fields with unicode collation
SELECT * FROM article ORDER BY title COLLATE ASC;

-- Order text fields with which include numeric values
SELECT * FROM article ORDER BY title NUMERIC ASC;

To limit the number of records returned, use the LIMIT clause.

SELECT * FROM user 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.

SELECT * FROM user LIMIT 50 START 50;

One of the most powerful functions in SurrealDB is the related records 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 * FROM user:tobie FETCH account, account.users;

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;

When processing a large result set with many interconnected records, it is possible to specify the PARALLEL keyword to signify that edges and remote records should be fetched and processed in parallel.

SELECT ->purchased->product<-purchased<-person->purchased->product FROM person:tobie PARALLEL;