# Creating Records · Relationships in Rell explained · Learn Chromia

> This module covers joins in the library management application. A join combines data from several entities so you can query information that spans related…

Source: https://chromia.com/learn/relationships-course/joins/

Relationships in Rell explainedLesson 2 of 6

1. [01Relationships in Rell explained](https://chromia.com/learn/relationships-course/introduction/)
2. [02Creating Records](https://chromia.com/learn/relationships-course/joins/)
3. [03Many-to-many relationships](https://chromia.com/learn/relationships-course/many-to-many/)
4. [04One-to-many relationships](https://chromia.com/learn/relationships-course/one-to-many/)
5. [05One-to-one relationship](https://chromia.com/learn/relationships-course/one-to-one/)
6. [06Set up your project](https://chromia.com/learn/relationships-course/setup/)

[Learn](https://chromia.com/learn/)[Relationships in Rell explained](https://chromia.com/learn/relationships-course/)

02 / 06

# Creating Records

This module covers joins in the library management application. A join combines data from several entities so you can query information that spans related records. After working through it, you will be able to write Inner Joins and Outer Joins to get the data you need.

A join merges records from two or more entities through a column they share. You need one whenever the data you want lives in more than one table. In a library system, for example, joining the `Book` and `Author` entities tells you which author wrote each book.

This module covers two join types:

* **Inner Join**: returns only the records whose values match in both entities.
* **Outer Join**: returns every record from the first entity, including records with no match in the other entity. Where no match exists, the missing side is `null` (Rell's `@outer` is a left outer join).

Start with a few basic joins in Rell. Add the following examples to the `queries.rell` file in the `/src/library_management` directory:

/src/library\_management/queries.rell

```
// Query to retrieve books along with their authors using an inner join
query get_books_with_authors() {
    return (book, book_author, author) @* {
        book == book_author.book,
        author == book_author.author
    } (book.title, author.author_name);
}
```

An Inner Join returns only the records that have matching values in every entity it joins. The query above lists each book together with its authors. It joins `book`, `book_author`, and `author`, matching each book to the `book` field in `book_author` and each author to the `author` field in `book_author`. As a result, books without an author are left out.

An Outer Join returns every record from the first entity, even when a record has no match in the other entity. The side without a match is filled with `NULL`.

/src/library\_management/queries.rell

```
query get_libraries_with_shelves() {
    return (l: library, @outer s: shelf @* { s.library == l }) @* {} ( @group l.library_name, @list shelves = s?.number );
}
```

This query lists every library with its shelves. Because it is an Outer Join, libraries that have no shelves still appear in the result. The `?` operator marks the match as optional, so a library without shelves gets `NULL`.

Next, you apply these joins in larger queries.

When you need detailed results, you can combine several joins in one query, as in this example:

/src/library\_management/queries.rell

```
// Query to retrieve books, their authors, and the libraries they are located in
query get_books_authors_libraries() {
    return (book, book_author, author, shelf, library) @* {
        book == book_author.book,
        author == book_author.author,
        book.shelf == shelf,
        shelf.library == library
    } (book.title, author.author_name, library.library_name);
}
```

This query returns books, their authors, and the libraries that hold them. It combines books with authors and still includes library information when a book is not linked to any library.

The next query uses an Inner Join to return members and the books they currently have on loan.

/src/library\_management/queries.rell

```
// Query to retrieve members along with the books they have on loan using an inner join
query get_members_with_loans() {
    return (member, loan, book) @* {
        member == loan.member,
        book == loan.book
    } (member.member_name, book.title);
}
```

It joins `member`, `loan`, and `book`, matching each member to the member in `loan` and each book to the book in `loan`. Only members who currently have borrowed books appear in the result.

The following query returns books on loan, the members who borrowed them, and the authors of those books.

/src/library\_management/queries.rell

```
// Query to retrieve books on loan along with the borrowing members and the authors of the books
query get_books_on_loan() {
    return (book, loan, member, book_author, author) @* {
        book == loan.book,
        member == loan.member,
        book == book_author.book,
        author == book_author.author
    } (book.title, member.member_name, author.author_name);
}
```

To query live records from the database for demonstration, start the node locally with this command:

shell

```nolines
chr node start
```

*If the node is already running, use this instead:*

shell

```nolines
chr node update
```

Then run the command below, replacing the `<operation>` placeholder with the name of an existing query, such as `get_books_on_loan`:

shell

```
chr query <operation>

// Example
chr query get_books_on_loan
```

For grouping query results by a specific key, see [this guide](/learn/associate-function/introduction/) and the [Chromia docs](https://docs.chromia.com/rell/language-features/database/overview).

The books-on-loan query above joins the `book`, `loan`, `member`, `book_author`, and author entities through their relationships. The result contains each book currently on loan, the member who borrowed it, and its authors.

Joins on large datasets can be slow, so optimize your queries for performance. Keep these points in mind:

tip

* **Indexing**: index the fields you use in JOIN conditions. Doing so can speed up queries considerably.
* **Filter early**: put filters early in the where-part of your `@` expression so fewer records need processing.
* **Minimize joins**: include only the entities you actually need, keeping the number of joins low.

You have now seen the basics of joins in Rell, focusing on Inner Joins and Outer Joins. You used them to combine data from multiple entities and learned how keys and indexes help join performance. You can now write efficient queries that pull data from several entities in your library management system.

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

[← Previous**Relationships in Rell explained**](https://chromia.com/learn/relationships-course/introduction/)[Next →**Many-to-many relationships**](https://chromia.com/learn/relationships-course/many-to-many/)