When there is a For Each command (X Evolution 2) (or grid, etc.) that involves a join between several tables, GeneXus automatically determines the Join Type, that is to say, how this Join will be implemented between them (natural or outer). In addition, it indicates if it can be solved in the database server or the application server (Join Location).
The type of join is set based on the nullability of the attributes that make up a Foreign Key. If the foreign key can be null, an outer (or left) join will be made; otherwise, a natural (or inner) join will be used.
Navigation reports show the "=" symbol for natural joins and the "~" symbol for outer joins (see examples below).
- If the Join Location is solved in the application server, the Join Type will always be Outer.
- For compatibility reasons, exists the Join Type property.
Joins involved in a For Each command (or grid, etc.) can be "solved" in the database server (DBMS) or in the client (Application Server) according to the logic of the generated object. This is what we see in the navigation report as Join Location: client | server.
In general, it tries to solve it in the server for performance reasons but there are some exceptions, for instance:
- When the tables that take part in the navigation belong to different Data Stores
- When using VFP DBF
- When there is a For Each command that performs an Update, the join is solved in the server if:
- It is an inner join and the DBMS is SQL Server, MySQL, Oracle or PostgreSQL (for DB2 and Informix it is solved in the client).
- It is an outer join and the DBMS is MySQL (for the rest it is solved in the client).
In case multiple tables are joined, Join location will be server if at least two tables are joined in the database server.
Considering the following Transactions:
CityId (Nullable = No)
If we have a For Each that joins the two tables:
For Each CustomerName CityName
The detailed navigation wil be as follows:
Note the icon when reading the City table (natural join).
If, instead, it has been set that a Client may not have an assigned city (the CityId foreign key in Customer has the Nullable property set to Yes), the join between the tables will be an outer join:
As we can see, in both cases the join is performed in the server.