pixelplumber
Sub join filtering is regular SQL - it allows you to filter out results, before they are being joined.
If you had a LEFT JOIN and the joined relation does not have a record, then a join filter would return NULL instead of removing the entire row. You could circumnvent this for some filter conditions by adding an "OR IS NULL" condition - but this was still limiting and bloated the filters unnecessarily.
Example before sub join filters: A position (relation joined on index 4) might or might not have a record depending on whether a position was selected for the current contact. If it has we have to filter out the translated position by user language. If not, we must ignore this filter line entirely:
Example with sub join filters. We can filter directly on the left joined relation removing the need to check for NULL:
In some cases, in combination with relation policies, sub join filters can be much faster as records do not need to be checked for access permissions if they can be filtered out before.
To add a sub join filter, you open any query (on a form, list, relationship input field, calendar...) and toggle the new expert option. If your query has more than 1 relation (eg. at least one relation is joined) then you will find the sub join input.
