Subqueries and advanced patterns

A query that is nested inside another one is called a subquery. Subqueries are executed first, enabling their output to be used inside a larger query.

Subquery examples

SELECT inside SELECT

A SELECT can be used to populate the value of a field, which can be given an alias via the AS keyword.

SELECT 
	*,
	SELECT * FROM events WHERE type = 'activity' LIMIT 5 AS history
FROM user;

To refer to part of the outer query, the preset $parent parameter can be used.

SELECT 
	*,
	SELECT * FROM events WHERE host == $parent.id AS hosted_events
FROM user;

Other statements inside a larger query

Other statements such as CREATE, UPDATE and so on can be used as subqueries as well. This can be useful to combine multiple queries into one or keep statements that should either succeed or fail together inside a single transaction.

CREATE ONLY person:billy SET
    father = CREATE ONLY person:pete RETURN VALUE id,
    mother = CREATE ONLY person:brenda RETURN VALUE id;

{
	father: person:pete,
	id: person:billy,
	mother: person:brenda
}

Patterns such as appending a new comment id to a record are covered in record references. See also RETURN.

$parent and $this

In nested contexts, SurrealDB predefines:

• $this — the current record in the inner scope.

• $parent — the current record in the enclosing scope.

They let an inner SELECT relate its WHERE clause to the record being processed outside.

SELECT
    name,
    SELECT VALUE name FROM user WHERE member_of = $parent.member_of AS group_members
FROM user
WHERE name = "User1";

SELECT
    *,
    SELECT VALUE id FROM person WHERE $this.name = $parent.name AS people_with_same_name
FROM person;

Full detail: Reserved variables — $parent, $this.

Graph paths and inner queries

Graph traversal (->edge->table) can include a parenthesised inner query on the edge or node, similar to filtering or projecting in a subquery. This allows you to restrict or shape the edges before the traversal continues.

SELECT ->(SELECT like_strength FROM likes WHERE like_strength > 10) AS likes FROM person;

Shorthand filters are often written without a nested SELECT, but the nested form is useful when you need full SELECT power (ORDER BY, GROUP BY, and so on):

SELECT ->(likes WHERE like_strength > 10) AS likes FROM person;

More examples: Selecting inside graph queries and graph clauses on the RELATE page.