`FROM` clause

The FROM clause is used to specify the table or view to query. It can also be used to specify targets beyond just a single table or record name.

Syntax

Clause Syntax

STATEMENT
    [FROM [ONLY] @targets;]

Data retrieval

One of the most common use cases for the FROM clause is to specify the table or view to query. You can use this clause to pull data from single or multiple tables.

All the ways you can use the FROM clause

-- Selects all records from both 'user' and 'admin' tables.
SELECT * FROM user, admin;

-- Selects all records from the table named in the variable '$table',
-- but only if the 'admin' field of those records is true.
-- Equivalent to 'SELECT * FROM user WHERE admin = true'.
LET $table = "user";
SELECT * FROM type::table($table) WHERE admin = true;

-- Selects a single record from:
-- * the table named in the variable '$table',
-- * and the identifier named in the variable '$id'.
-- This query is equivalent to 'SELECT * FROM user:admin'.
LET $table = "user";
LET $id = "admin";
SELECT * FROM type::record($table, $id);

-- Selects all records for specific users 'tobie' and 'jaime',
-- as well as all records for the company 'surrealdb'.
SELECT * FROM user:tobie, user:jaime, company:surrealdb;

-- Selects records from a list of identifiers. The identifiers can be numerical,
-- string, or specific records such as 'person:lrym5gur8hzws72ux5fa'.
SELECT * FROM [3648937, "test", person:lrym5gur8hzws72ux5fa, person:4luro9170uwcv1xrfvby];

-- Selects data from an object that includes a 'person' key,
-- which is associated with a specific person record, and an 'embedded' key set to true.
SELECT * FROM { person: person:lrym5gur8hzws72ux5fa, embedded: true };

-- This command first performs a subquery, which selects all 'user' records and adds a
-- computed 'adult' field that is true if the user's 'age' is 18 or older.
-- The main query then selects all records from this subquery where 'adult' is true.
SELECT * FROM (SELECT age >= 18 AS adult FROM user) WHERE adult = true;

Using the `ONLY` keyword

The ONLY keyword can be used to specify that only a single targets should be retrieved as a single value instead of inside of an array. This is useful when you want to retrieve data from a single table or view. The ONLY keyword can be used in conjunction with the LIMIT 1 clause to specify that only the specified value should be retrieved.

This keyword is particularly useful with SDKs as returning a single item makes deserialisation easier.

As record IDs are unique, ONLY can be used with record IDs without needing to specify LIMIT 1. The same goes for single values.

The following examples show when the ONLY keyword can be used on its own and when a LIMIT 1 clause is required to ensure that only a single value is returned.

-- Create ten random user records along with `user:one`
CREATE |user:10|, user:one;

-- Returns [user:one] inside an array
SELECT * FROM user:one;

-- Returns { id: user:one }
SELECT * FROM ONLY user:one;

-- Error: more than one user record returned
SELECT * FROM ONLY user;

-- Success: LIMIT 1 guarantees a single value
SELECT * FROM ONLY user LIMIT 1;

-- Success: single value returned
SELECT * FROM ONLY 9;

-- Error: more than one value returned
SELECT * FROM ONLY 8, 9;

-- Success: LIMIT 1 guarantees a single value
SELECT * FROM ONLY 8, 9 LIMIT 1;

FROM clause

Syntax

Data retrieval

Using the ONLY keyword

`FROM` clause

Using the `ONLY` keyword