# INNER JOIN statement · Mastering Rell · Learn Chromia

> To join two tables in Rell, list every table you want to join in the FROM part of the at-expression, then state how they relate in the WHERE part:

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

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

05 / 08

# INNER JOIN statement

To join two tables in Rell, list every table you want to join in the FROM part of the at-expression, then state how they relate in the WHERE part:

rell

```
function join_example() {
    val res = (house, street) @* { street == house.street };
}
```

This Rell code maps to the following SQL query:

sql

```
SELECT A00."rowid", A01."rowid"
    FROM "street" A00, "house" A01
    INNER JOIN "street" A02 ON A01."street" = A02."rowid"
    WHERE A00."rowid" = A02."rowid"
    ORDER BY A00."rowid", A01."rowid"
```

## Implicit statements

Here, `house` has a `foreign key` constraint on `street`. This means you can reach `street` straight from a `house` instance:

rell

```nolines
val street = house_instance.street;
```

This access also produces a join:

sql

```
SELECT A01."rowid"
    FROM "house" A00
    INNER JOIN "street" A01 ON A00."street" = A01."rowid"
    WHERE A00."rowid" = ?
    ORDER BY A00."rowid"
```

The same applies whenever you touch `street` while working with `house`. *Any* such access results in a join.

rell

```
house @ { .street.address == "FakeStreet" };
house @ {} (.street);
house_instance.to_struct();
```

A join on a foreign key constraint is usually not expensive, but you should still know which Rell statements generate joins. The previous example used `struct<house>` in the function expression. When `house` references several other tables, calling `to_struct()` performs one join per reference. The next example adds a few references to illustrate this:

rell

```
entity garage {}
entity house_key {}
entity house {
    ...
    garage;
    house_key;
}
```

## Avoiding unnecessary joins

Suppose you reuse the earlier construct:

rell

```nolines
val houses = house @* {} ($.to_struct());
```

The query now joins both the `garage` and `house_key` entities. If the function doesn't need them, select the required fields explicitly in the at-expression:

rell

```
struct house_info {
    number_of_rooms: integer;
    number_of_floors: integer;
    floor_area: integer;
}
val houses = house @* { } ( house_info( .number_of_rooms, .number_of_floors, .floor_area ) );
```

Changing the function signature to `is_mansion(house: house_info)` lets you control exactly which values are fetched from the database.

tip

Pull every value you need directly in the at-expression so that no unnecessary joins occur. Define your own structs where that helps.

## Practical example

The following queries return records filtered according to each query's criteria.

Add `get_house_with_streets` to the `queries.rell` file in the `src/main` directory:

src/main/queries.rell

```nolines
query get_houses_with_streets() = (house, street) @* { street == house.street } ( street = .street.address, .number );
```

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



shell

```nolines
chr query get_houses_with_streets
```

result

```
[
  [
    "number": 23,
    "street": "Drottninggatan"
  ],
  [
    "number": 24,
    "street": "Kungsgatan"
  ],
  [
    "number": 30,
    "street": "Kungsgatan"
  ]
]
```

Next, add the `get_houses_with_specific_street` query to `queries.rell` in the `src/main` folder.

src/main/queries.rell

```nolines
query get_houses_with_specific_street(street_address: text) = house @* { .street.address == street_address } ( .number, street = .street.address );
```

Run `get_houses_with_specific_street` with the `chr` command, passing the street argument:

shell

```nolines
chr query get_houses_with_specific_street "Drottninggatan"
```

You get the following response:

result

```
[
  [
    "number": 23,
    "street": "Drottninggatan"
  ]
]
```

To fetch details for specific houses, such as the number of rooms, the number of floors, and the total area, use the `get_houses_info` query.

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

src/main/queries.rell

```nolines
query get_houses_info() = house @* { } ( house_info( .number_of_rooms, .number_of_floors, .floor_area ) );
```

shell

```nolines
chr query get_houses_info
```

The query returns:

result

```
[
  [
    "floor_area": 4,
    "number_of_floors": 4,
    "number_of_rooms": 5
  ],
  [
    "floor_area": 500,
    "number_of_floors": 2,
    "number_of_rooms": 12
  ],
  [
    "floor_area": 700,
    "number_of_floors": 3,
    "number_of_rooms": 15
  ]
]
```

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

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

On this page

* [Implicit statements](#implicit-statements)
* [Avoiding unnecessary joins](#avoiding-unnecessary-joins)