A CANDIDATES table evaluates in the LEVEL column the knowledge of candidates identified in the NAME column in relation to the language named in the LANGUAGE column:

A further TASKS table defines the required 'English language skills' (EN_LEVEL) for the tasks named in the TASK column:

Result:

The SELECT statement creates a 'cross product' TASKS x CANDIDATES and returns only those combinations of TASK (from TASKS) and NAME (from CANDIDATES) as 'matches' (=result lines) for which sufficient knowledge of English is available (ON condition).

An INNER JOIN is explicitly used here because the result should be exactly the intersection of CANDIDATES and TASKS defined by the ON condition.

• In contrast to this, a LEFT JOIN would also list TASKS in the result for which none of the CANDIDATES is sufficiently qualified. The NAME column would then remain empty.
  
  

• CANDIDATES unsuitable for all TASKS (here: 'Bruno' and 'Florence') cannot be found in either the INNER JOIN or the LEFT JOIN. These would only be listed via RIGHT JOIN with an empty TASK column.
  
  ►IMPORTANT◄ In the context of a search in Lobster Data Platform / Orchestration, in many cases a RIGHT JOIN cannot 'work' in the same way as it would directly on a database. By default, explicitly configured Joins (as well as any implicitly generated ones; see below for details) are executed as a LEFT JOIN.

In a Tuple search for Users, a Property projection accesses the 'Name' address property (address.name1).

This search returns the desired result without any further precautions, although only the 'username' (username) can be found directly in the database table for Users ( base_user ).

Access to the 'address' requires a LEFT JOIN to the database table base_address , which contains all addresses known in the system.

This LEFT JOIN, including the ON condition relevant to the context, is generated automatically when the database query is executed.

The following SELECT statement is generated for this simple Tuple search:

SELECT username, name1 FROM base_user LEFT JOIN base_address ON (id = address_id)

• The base_user table for Users refers to the field id of the table base_address for Addresses defined as the primary key via the foreign key address_id property.

• The ON condition requires the foreign key and primary key to match so that each user is assigned 'their' address.

• The LEFT JOIN ensures that Users without an assigned address can also appear in the result.

In a Tuple search for Users, a Property projection accesses the 'Roles' (roles) list field, which can contain several ID values of Roles as a value.

This search returns the desired result without any further precautions, although only the 'username' (username) can be found directly in the database table for Users ( base_user ).

So that several role IDs can be assigned to the same user, a specific detail table ( base_user_roles ) is required in which different role IDs ( role_id ) can be combined with the same foreign key ( user_id ) on the Users table.

Access to the 'roles' of a user again requires a LEFT JOIN.

The following SELECT statement is generated for this simple Tuple search:

SELECT username, role_id FROM base_user LEFT JOIN base_user_roles ON (id = user_id)

►NOTE◄ Each role ID could in turn be interpreted as a foreign key for a data record in the table for Roles (base_role), so that there is actually an (m : n) relation (Users ←(m : n) → Roles). However, this cannot be enforced here by a Property projection such as roles.roleName, because Roles are only referenced in the data model for Users and are not embedded or contained in the data model..

In a Tuple search for Users, a Typed attribute projection accesses the flag attribute for the address with the (sub)type VIP to return its 'flag value' (flagValue).

This search returns the desired result without any further precautions, although only the 'username' (username) can be found directly in the database table for Users ( base_user ).

As explained in the first example above, accessing a field of the address already implies a LEFT JOIN to the base_address table.

As can be seen in the screenshot on the left, our Typed attribute projection explicitly refers to the Attribute owner path address. By default, flag attributes for Users are not supported at all.

On the database side, the implementation of an attribute type for an owner requires a separate database table in which the attribute properties for this owner-attribute type combination are stored. Access to attribute values therefore always requires at least one implicit join.

The Projections for attributes only simplify access on the surface. However, this does not eliminate the need to look up the specific detail tables in the database.

►NOTE◄ In special cases, it may be necessary to dispense with the convenience of Projections for attributes and instead set up explicit Joins for accessing attribute values.

The following SELECT statement is generated for this simple Tuple search:

SELECT username, flag_value FROM base_user
LEFT JOIN base_address ON ( id = address_id)
LEFT JOIN base_address_flag_attribute ON ((id = address_id) AND (flag_type = VIP))

►NOTE◄ In the ON condition for the second LEFT JOIN, the id from the green address table (base_address) is used as the compare value for the address_id column in the red marked attribute table ( base_address_flag_attribute ). However, as can be seen in the ON condition for the first LEFT JOIN, this must match the foreign key address_id in the Users table (base_user) marked in blue. Therefore, it is possible to completely dispense with the first LEFT JOIN to the address if the foreign key address_id is compared directly with the address_id column of the attribute. This abbreviation can only be enforced in an explicit join. The implicit join will always follow the internally predefined schema (Entity → Attribute owner path → Attribute) if an Attribute owner path is specified that differs from the entity in the FROM section.