SELECT command in Amazon QLDB

In Amazon QLDB, use the SELECT command to retrieve data from one or more tables. Every SELECT query in QLDB is processed in a transaction and is subject to a transaction timeout limit.

The order of the results is not specific and can vary for each SELECT query. You shouldn't rely on the results order for any query in QLDB.

To learn how to control access to run this PartiQL command on specific tables, see Getting started with the standard permissions mode in Amazon QLDB.

Syntax

SELECT [ VALUE ] expression [ AS field_alias ] [, expression, ... ]
FROM source [ AS source_alias ] [ AT idx_alias ] [ BY id_alias ] [, source, ... ]
[ WHERE condition ]

Parameters

Joins

Only inner joins are currently supported. You can write inner join queries using the explicit INNER JOIN clause, as follows. In this syntax, JOIN must be paired with ON, and the INNER keyword is optional.

SELECT expression
FROM table1 AS t1 [ INNER ] JOIN table2 AS t2
ON t1.element = t2.element

Or, you can write inner joins using the implicit syntax, as follows.

SELECT expression
FROM table1 AS t1, table2 AS t2
WHERE t1.element = t2.element

Nested query limitations

You can write nested queries (subqueries) within SELECT expressions and within FROM sources. The main restriction is that only the outermost query can access the global database environment. For example, suppose that you have a ledger with tables VehicleRegistration and Person. The following nested query is not valid because the inner SELECT tries to access Person.

SELECT r.VIN,
    (SELECT p.PersonId FROM Person AS p WHERE p.PersonId = r.Owners.PrimaryOwner.PersonId) AS PrimaryOwner
FROM VehicleRegistration AS r

Whereas the following nested query is valid.

SELECT r.VIN, (SELECT o.PrimaryOwner.PersonId FROM @r.Owners AS o) AS PrimaryOwner
FROM VehicleRegistration AS r

Examples

The following query shows a basic SELECT all wildcard with a standard WHERE predicate clause that uses the IN operator.

SELECT * FROM Vehicle
WHERE VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

The following shows SELECT projections with a string filter.

SELECT FirstName, LastName, Address 
FROM Person 
WHERE Address LIKE '%Seattle%'
AND GovId = 'LEWISR261LL'

The following shows a correlated subquery that flattens nested data. Note that the @ character is technically optional here. But it explicitly indicates that you want the Owners structure that is nested within VehicleRegistration, not a different collection named Owners (if one existed). For more context, see Nested data in the chapter Working with data and history.

SELECT 
    r.VIN, 
    o.SecondaryOwners
FROM
    VehicleRegistration AS r, @r.Owners AS o
WHERE
    r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

The following shows a subquery in the SELECT list that projects nested data, and an implicit inner join.

SELECT
    v.Make, 
    v.Model, 
    (SELECT VALUE o.PrimaryOwner.PersonId FROM @r.Owners AS o) AS PrimaryOwner
FROM 
    VehicleRegistration AS r, Vehicle AS v
WHERE 
    r.VIN = v.VIN AND r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

The following shows an explicit inner join.

SELECT
    v.Make, 
    v.Model, 
    r.Owners
FROM 
    VehicleRegistration AS r JOIN Vehicle AS v
ON
    r.VIN = v.VIN
WHERE
    r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

The following shows a projection of the document id metadata field, using the BY clause.

SELECT
    r_id,
    r.VIN
FROM
    VehicleRegistration AS r BY r_id
WHERE
    r_id = 'documentId'

The following uses the BY clause to join the DriversLicense and Person tables on their PersonId and document id fields respectively.

SELECT * FROM DriversLicense AS d INNER JOIN Person AS p BY pid
ON d.PersonId = pid
WHERE pid = 'documentId'

The following uses the Committed view to join the DriversLicense and Person tables on their PersonId and document id fields respectively.

SELECT * FROM DriversLicense AS d INNER JOIN _ql_committed_Person AS cp
ON d.PersonId = cp.metadata.id
WHERE cp.metadata.id = 'documentId'

The following returns the PersonId and index (ordinal) number of each person in the Owners.SecondaryOwners list for a document in table VehicleRegistration.

SELECT s.PersonId, owner_idx
FROM VehicleRegistration AS r, @r.Owners.SecondaryOwners AS s AT owner_idx
WHERE r.VIN = 'KM8SRDHF6EU074761'

Running programmatically using the driver

To learn how to programmatically run this statement using the QLDB driver, see the following tutorials in Getting started with the driver: