Aggregate Joins Explained: Only With, With or Without, and With

Open an existing Aggregate or create a new one: right-click your screen → Fetch data from Database. In the Sources tab, drag the Employee entity from the Data panel on the right. Then drag the Department entity. When entities have a defined relationship (Employee.DepartmentId references Department.Id), OutSystems automatically creates a join between them and shows a link line between the two entity boxes in the Sources canvas. If you add two unrelated entities, OutSystems creates a CROSS JOIN (cartesian product) — every employee row combined with every department row. This is almost never what you want. Always ensure related entities have a defined FK relationship before joining them in an Aggregate.

Look at the join indicator between Employee and Department. It shows either: - A filled circle on both ends: Only With (INNER JOIN) - A filled circle on the Employee side, empty circle on the Department side: With or Without (LEFT JOIN) - Empty circles on both ends: With (FULL OUTER JOIN) The default is determined by the FK's Is Mandatory property: - DepartmentId Is Mandatory = Yes → join defaults to Only With - DepartmentId Is Mandatory = No → join defaults to With or Without Hover over the join indicator to see a tooltip confirming the join type and which entities are on each side.

Click the join indicator between Employee and Department. A dropdown appears with three options: Only With, With or Without, With. Select 'Only With'. SQL equivalent: `INNER JOIN Department ON Employee.DepartmentId = Department.Id` Behavior: ONLY employees who have a matching Department row are returned. If any employee's DepartmentId does not match an existing Department.Id (orphaned FK), that employee is excluded from results. Use Only With when: - Every employee MUST belong to a department (mandatory relationship) - You want to display department name alongside employee data and all employees have departments - You need to filter by a department attribute (adding a department filter auto-changes unrelated joins to Only With)

1/* Only With = INNER JOIN — SQL generated by OutSystems */
2SELECT
3  Employee.Id,
4  Employee.FirstName,
5  Employee.LastName,
6  Department.Name AS DepartmentName
7FROM Employee
8INNER JOIN Department ON Employee.DepartmentId = Department.Id

Click the join indicator → select 'With or Without'. SQL equivalent: `LEFT JOIN Department ON Employee.DepartmentId = Department.Id` Behavior: ALL employees are returned. Employees with a matching department show the department's data. Employees without a matching department (DepartmentId is null or orphaned) return empty/default values for all Department attributes. In OutSystems expressions, Department attributes for unmatched rows return their default values: Text = "", Integer = 0, Boolean = false, Date = #1900-01-01#, Identifier = NullIdentifier(). Use With or Without when: - Employees can optionally be assigned to a department (optional FK) - You want to show all employees on a list, with department name where available - Building a report that should include ALL employees regardless of department assignment - Checking which employees have no department assigned (filter where DepartmentId = NullIdentifier())

1/* With or Without = LEFT JOIN — SQL generated by OutSystems */
2SELECT
3  Employee.Id,
4  Employee.FirstName,
5  Employee.LastName,
6  ISNULL(Department.Name, '') AS DepartmentName  -- default for unmatched rows
7FROM Employee
8LEFT JOIN Department ON Employee.DepartmentId = Department.Id
9
10/* In expressions: show 'Unassigned' for employees without a department */
11If(Department.Name = "", "Unassigned", Department.Name)

Click the join indicator → select 'With'. SQL equivalent: `FULL OUTER JOIN Department ON Employee.DepartmentId = Department.Id` Behavior: ALL rows from BOTH entities appear. This includes: - Employees with a matching department (both sides populated) - Employees without a matching department (Department columns empty) - Departments with no employees (Employee columns empty) This is the least commonly used join type. Typical use case: a reconciliation report showing both orphaned employees AND empty departments in a single result set. IMPORTANT: With (FULL OUTER JOIN) is not supported by all databases. O11 on SQL Server supports it. ODC on Aurora PostgreSQL also supports it. However, it is significantly heavier than INNER or LEFT JOIN — only use it when you genuinely need rows from both sides.

1/* With = FULL OUTER JOIN — SQL generated by OutSystems */
2SELECT
3  Employee.Id,
4  Employee.FirstName,
5  Department.Id AS DeptId,
6  Department.Name AS DepartmentName
7FROM Employee
8FULL OUTER JOIN Department ON Employee.DepartmentId = Department.Id
9/* Returns: matched pairs + employees without dept + depts without employees */

Drag a third entity into the Sources tab — for example, add 'EmploymentStatus' to the existing Employee + Department aggregate. OutSystems creates a second join: Employee → EmploymentStatus (since Employee has a StatusId FK to EmploymentStatus). Each join has its own indicator and can be set independently. Click each join indicator to set the type: - Employee ↔ Department: With or Without (employees may lack a department) - Employee ↔ EmploymentStatus: Only With (all employees must have a status) When joining three or more entities, the order matters for LEFT JOINs. OutSystems uses the first entity added as the 'left' base table. Subsequent entities join in the order added. TrueChange warning: if you change a join type after adding filters on the joined entity, TrueChange may show a warning that the filter makes the With or Without join behave like Only With — adding a WHERE condition on the optional side eliminates NULLs.