# SELECT statement · Mastering Rell · Learn Chromia

> To read records from the database, you will usually reach for the at-operator, @.

Source: https://chromia.com/learn/rell-masterclass/select/

Mastering RellLesson 6 of 8

1. [01Mastering Rell](https://chromia.com/learn/rell-masterclass/introduction/)
2. [02Comparing SQL tables and Rell entities](https://chromia.com/learn/rell-masterclass/entities/)
3. [03Code optimization example](https://chromia.com/learn/rell-masterclass/example/)
4. [04Keys and indexes](https://chromia.com/learn/rell-masterclass/indexes/)
5. [05INNER JOIN statement](https://chromia.com/learn/rell-masterclass/join/)
6. [06SELECT statement](https://chromia.com/learn/rell-masterclass/select/)
7. [07Subqueries](https://chromia.com/learn/rell-masterclass/sub-queries/)
8. [08UPDATE statement](https://chromia.com/learn/rell-masterclass/update/)

[Learn](https://chromia.com/learn/)[Mastering Rell](https://chromia.com/learn/rell-masterclass/)

06 / 08

# SELECT statement

To read records from the database, you will usually reach for the at-operator, `@`.

rell

```nolines
FROM        CARDINALITY  WHERE         WHAT  TAIL
entity_name @            { condition } ()    ;
```

An at-expression has five parts: FROM, CARDINALITY, WHERE, WHAT, and TAIL.

* FROM: the table you query. It works like FROM in SQL.
* [CARDINALITY](https://docs.chromia.com/rell/language-features/database/overview#cardinality): how many results you expect. If the actual result count doesn't satisfy it, the query fails.
  + @ exactly one result
  + @? zero or one result
  + @+ one or more results
  + @\* zero or many results
* WHERE: a filter, comparable to the WHERE clause in SQL.
* WHAT: the values the query returns. List one or more attributes, or leave it empty to get the entity reference (rowid). This part also lets you
  [sort, group, or omit fields](https://docs.chromia.com/rell/language-features/database/overview#what-part).
* TAIL: extra SQL-style clauses for multi-row results, such as `limit` or `offset`.

tip

You can also apply the at-operator to collections like `list<T>` and `set<T>`, where it often replaces a `for`
loop.

## Implicit select statements

Some SELECTs run without being obvious in the code. Reading an attribute from an entity is one such case.

rell

```nolines
val attribute = entity_instance.attribute;
```

That line produces this SQL query:

sql

```nolines
SELECT "attribute" FROM "entity_name" WHERE "rowid" = ? ORDER BY "rowid"
```

Written as an at-expression, the same database roundtrip becomes visible:

rell

```nolines
val attribute = entity_name @ { $ == entity_instance } ( .attribute );
```

Both forms map to the same SQL.

Calling `.to_struct()` on an entity fetches all of its attributes and gives you an in-memory copy. Keep in mind that this also costs a database roundtrip, so the following statements do the same thing:

rell

```nolines
val struct1 = entity_instance.to_struct();
val struct2 = entity_name @ { $ == entity_instance } (struct<entity_name>(attribute = .attribute, ...));
```

## Select statement example

Because every attribute access can trigger a SELECT, heavy use of it adds up quickly. Consider this function:

rell

```
function is_mansion(house) {
  if (house.floor_area <= 460) return false;
  if (house.number_of_rooms < 10) return false;
  return house.number_of_floors > 1 and house.number_of_floors <= 3;
}
```

It looks harmless, but it makes four database roundtrips, one per attribute. Call it inside a loop and the cost multiplies:

rell

```
val houses = house @* {};
for (house in houses) {
  if (is_mansion(house)) print("Found a mansion");
}
```

Now the roundtrip count grows linearly with the number of rows in the table.

danger

Passing entity references as function arguments is therefore risky: it can cause many database roundtrips you don't need.

Change the signature to `function is_mansion(house: struct<house>)` and compare. All the required data now comes back in one database statement:

rell

```
val houses = house @* {} ($.to_struct());
for (house in houses) {
  if (is_mansion(house)) print("Found a mansion");
}
```

tip

Avoid passing entity references to functions. Each one can trigger extra database roundtrips, and in the worst case their number grows without bound.

## Practical example

Open `queries.rell` in the `src/main` directory and add the query below.

It returns every `house` that counts as a mansion:

src/main/queries.rell

```nolines
query get_all_mansion() = house @* { .floor_area >= 406, .number_of_rooms > 10, .number_of_floors > 1 } ( $.to_struct() );
```

Run the `get_all_mansion` query from the CLI with the `chr` command:

shell

```nolines
chr query get_all_mansion
```

You should see output like this:

result

```
[
  [
    "floor_area": 500,
    "number": 24,
    "number_of_floors": 2,
    "number_of_rooms": 12,
    "street": 2
  ],
  [
    "floor_area": 700,
    "number": 30,
    "number_of_floors": 3,
    "number_of_rooms": 15,
    "street": 2
  ]
]
```

Written for this site from Chromia’s public course. Code samples are the course’s own.

[← Previous**INNER JOIN statement**](https://chromia.com/learn/rell-masterclass/join/)[Next →**Subqueries**](https://chromia.com/learn/rell-masterclass/sub-queries/)

On this page

* [Implicit select statements](#implicit-select-statements)
* [Select statement example](#select-statement-example)