INNER JOIN Statement

Joining two tables is really simple in Rell. We do this by putting all tables we want to join in the FROM part of the at-expression and specify the constraint between them in the WHERE part:

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

This will correspond to the following SQL query:

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

Now, in this scenario, we have a foreign key constraint on street from house, so we can access street directly from an instance of house, for example:

val street = house_instance.street;

This will also correspond to a join statement:

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

Similarly, accessing street in any way when working with house will result in a join.

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

Even though joining a table with a foreign key constraint may not be a heavy join, it is important to remember which Rell statements will create a join. For example, we used the struct<house> construct in the previous example in the function expression. If house has several references to other tables, the to_struct() call will make several joins. For example, let's add some references to the previous example:

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

Avoiding Unnecessary Joins

If we, in this case, use the same construct as before:

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

This will result in joins with both entities garage and house_key. If these are not needed in the function, we should be more explicit in the at-expression:

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 ) );

and change the function's signature to is_mansion(house: house_info). In this way, we can optimize which values we get from the database.

tip

Extract all values you need directly in the at expression to avoid unnecessary joins. It's best to define you own structures when needed.