# Subqueries · Mastering Rell · Learn Chromia

> This lesson shows how nested expressions in Rell help you write efficient database queries. You will nest expressions inside the exists() and empty()…

Source: https://chromia.com/learn/rell-masterclass/sub-queries/

Mastering RellLesson 7 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/)

07 / 08

# Subqueries

This lesson shows how nested expressions in Rell help you write efficient database queries. You will nest expressions inside the `exists()` and `empty()` functions and use them with the `in` operator. Nesting lets you build more complex queries that also perform better and do more.

A subquery is a query that runs inside another query to narrow down the results. Suppose you need every customer whose orders add up to more than $500. A subquery can first compute how much each customer has spent and return a specific result, such as an array of objects with customer details.

The outer query then uses that result to list only the customers that match the condition. The subquery finds the high spenders, and the main query fetches their details.

## Adding new records

To see subqueries in action, define two entities, `customer` and `order`, in `src/main/entities.rell`. You will use them to:

1. Find every customer with at least one order.
2. Find customers who have no orders yet.
3. Find customers who bought a particular product.

Define the two entities as follows:

src/main/entities.rell

```
entity customer {
  name: text;
  city: text;
  email: text;
}

entity order {
  order_id: text;
  customer_email: text;
  product: text;
}
```

You also need two operations that create customer and order records. Put this code in `src/main/operations.rell`:

src/main/operations.rell

```
operation create_customer(name: text, city: text, email: text) {
    create customer( name, city, email );
}

operation create_order(order_id: text, customer_email: text, product: text) {
    create order( order_id, customer_email, product );
}
```

To add records for this demo, start a local Chromia node. While experimenting, start it with the `--wipe` flag so that any previously stored records are deleted.

shell

```nolines
chr node start --wipe
```

With the node running, call your operations to insert data. Create three customers with the `create_customer` operation by running these commands one after another in the terminal:

shell

```
chr tx create_customer Ann Sydney ann@mail.com
chr tx create_customer Denis London denis@mail.com
chr tx create_customer Lily Rome lily@mail.com
```

Next, insert orders with the `create_order` operation by running these commands one after another:

shell

```nolines
chr tx create_order "'1'" ann@mail.com phone
chr tx create_order "'2'" lily@mail.com tv
```

Once the data is in place, you can start running subqueries.

## Running subqueries

Rell's System Library provides built-in global functions that you can use to build subqueries. Two functions and one operator are available for this:

1. [`exists()`](https://docs.chromia.com/rell/language-features/systemlib/global-functions#existsvalue-t-boolean) - Takes a value or a collection of values and tells you whether a value is present: `true` if it is, `false` if not.
2. [`empty()`](https://docs.chromia.com/rell/language-features/systemlib/global-functions#emptyvalue-t-boolean) - Checks whether a value is `null` or an empty collection. It gives `true` when the value is `null` or the collection is empty, and `false` otherwise.
3. [`in`](https://docs.chromia.com/rell/language-features/expressions/operators) - Tests whether a given element is part of a collection, returning `true` if it is and `false` if it is not.

### Example of using exists()

The query below returns every customer with at least one order. Here, `exists` acts as the subquery and produces a `boolean` that shows whether an element or collection of elements is present. Customers for which the result is `true` are included in the collection the main query returns.

Add this code to `src/main/queries.rell`:

src/main/queries.rell

```
query get_customers_with_orders() {
    return customer @* {
        exists(order @* { .customer_email == customer.email })
    } ( $.to_struct() );
}
```

note

Use the `@*` operator in nested expressions to avoid record-counting problems in the generated nested SQL query.

Save the file, then run the query from the terminal:

shell

```nolines
chr query get_customers_with_orders
```

The result contains the two customers who have at least one order.

json

```
[
  [
    "city": "Sydney",
    "email": "ann@mail.com",
    "name": "Ann"
  ],
  [
    "city": "Rome",
    "email": "lily@mail.com",
    "name": "Lily"
  ]
]
```

### Example of using empty()

This query returns the customers who have not placed an order yet. Add it to `src/main/queries.rell`:

src/main/queries.rell

```
query get_customers_without_orders() {
    return customer @* {
        empty(order @* { .customer_email == customer.email })
    } ( $.to_struct() );
}
```

Run it from the terminal:

shell

```nolines
chr query get_customers_without_orders
```

One customer comes back, the one without any orders:

json

```
[
  [
    "city": "London",
    "email": "denis@mail.com",
    "name": "Denis"
  ]
]
```

### Example of using in

The `get_customers_who_ordered_phone,` query returns every customer who ordered a particular product, in this case a `phone`. Add it to `src/main/queries.rell`:

src/main/queries.rell

```
query get_customers_who_ordered_phone() {
    return customer @* {
        .email in order @* { .product == "phone" } ( order.customer_email )
    } ( $.to_struct() );
}
```

Run it from the terminal:

shell

```nolines
chr query get_customers_who_ordered_phone
```

You get one customer, the one who ordered a `phone`:

json

```
[
  [
    "city": "Sydney",
    "email": "ann@mail.com",
    "name": "Ann"
  ]
]
```

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

[← Previous**SELECT statement**](https://chromia.com/learn/rell-masterclass/select/)[Next →**UPDATE statement**](https://chromia.com/learn/rell-masterclass/update/)

On this page

* [Adding new records](#adding-new-records)
* [Running subqueries](#running-subqueries)