An outer join (left, right, full) combines data from two tables, including all matching rows and any unmatched rows from one or both tables, filling in NULL for missing data.
Analytical platforms use outer joins to achieve:
- Broader analytical capabilities: Ensure all relevant data is visible, even if there is no exact match in another table (e.g., view all products, including products with no sales).
- Identify Gaps: Easily spot data integrity issues and missing information or relationships, which is crucial for analysis and reporting.
Outer joins in Sisense
Outer joins are available beginning with 2025.4 as a preview feature (turned off by default). It is planned to be released as beta/GA in 2026.1.
Important - Outer joins are not yet ready for production, and thus are not officially supported yet. We recommend using it for testing purposes, on a dev environment only.
If you’d like to test it on your own models, you’ll need to first enable the following flag:
Admin → Server & Hardware → System Management → Configuration → 5 clicks on the logo → Base Configuration → Query → query.outerJoins.enabled = true
Once done, you can access it through the data tab, inside any data model, when editing any table relationship. The Join type drop-down (see the screenshot below) is where you can control it. A build/publish action must be performed after changes to see them reflected in the dashboard.
The default value selected is “Default”, which, for now, stands for “Inner join” as was always used in Sisense before. In the future, it might be able to inherit other flexible join behaviors from an upper-level setting/product, so by keeping that option selected, you are allowing it to stay flexible. To enforce an inner join at any time in the future, select “Inner join” explicitly.
Example data - To test the outer join, use a data model with data integrity issues, such as Sample Ecommerce, which contains countries in the Dim table (Country) that do not exist in the Fact table (Ecommerce).
For example, if you perform a full join between the 2 tables, build the model, and expose it in a dashboard widget, country.country ID 199 Tahiti will appear, side by side with N/A or NULL values in the ecommerce columns. Without an outer join, Tahiti would not appear at all, because there is no matching data in the ecommerce table.
Known issues and limitations
Planned to be addressed in 2026.1:
- Analytical engine as a prerequisite - The outer join feature is designed to work exclusively with the Analytical Engine (AE). During the preview phase of that feature, if AE is not used in a query, and the query performs a fallback (due to a “compatibility mode” setting), only inner joins will be performed, even if the table relationship indicates otherwise. Starting from 2026.1 and onwards, using and editing a join type for a relationship will be disabled if the analytical engine setting of the model is set for “compatibility mode”, rather than for “Analytical Engine”.
- Filter propagation issue - Filters are usually translated into WHERE statements, and are applied immediately on the source base tables, before any table join is performed.
This is safe and even optimal when using inner joins. When an outer join is used, this behavior may be unsafe, as the result may still include data from tables that are not part of the filtered table. For example:
Starting from 2026.1 and onwards, those WHERE statements will be propagated above the joined tables if the filters belong to the non-preserved table(s). - Data security risks - Some data security features behave like filters, and although they are not exposed in the “Analyze SQL Query” output, they are implemented on top of it and may suffer from the same filter propagation symptom mentioned above. In addition, not all data security use cases were covered thoroughly before the preview version was released, and while it will be a focus of the next release, please verify it based on your own data security rules, and share with us any concerns or use cases that should be double-verified.
- Perspectives inheritance issue - Perspectives usually inherit the relationship attributes set in the root level of the data model. Until the next version is out, it is not yet implemented for the join type attribute, and thus needs to be defined individually per perspective.
- Relationship’s pane fixed visualization order - When defining a relationship between Table X and Table Y, the current interface chooses which table will be presented on the left side of the pane, and which on the right. This fixed order means that you may need to adjust the join type to achieve the desired semantic join. For example, if you want to achieve the semantic result of Table Y LEFT JOIN Table X, but the relationship visualization order is (Table X, Table Y), you should flip it and select the “RIGHT JOIN” type instead. We recognize that having to manually flip the join type can be counterintuitive, but please note that there is no limitation on the desired result, which can still be achieved in any visualized order.
Planned to be addressed in future versions:
- Filtering NULL values in widgets - There is no current option to filter out NULL values that are created as a result of an outer-joined data set, as Sisense does not yet offer result set filters.
- Circular reference ambiguity - When there are multiple ways to reach from table X to table Y, the system will choose the shortest path that takes into account any active filter and required data points. That means that sometimes, mainly based on filter usage, the path of joined tables performed from table X to table Y may change. And while one path may define an outer join to be used, the other path may not define it. That is not a new behavior, and it may not be an issue if the data modeler considered it, so just make sure to take it into account.
- Join type flipping in query time - There could be a situation where a data model relationship is defined as Table X LEFT join Table Y, but the widget query performs Tables Y RIGHT join Table X. The result will still be the same, but for query planning purposes, Sisense might switch the join type used in the SQL to be consistent with previous query plans and ensure semantic equivalence.
Feedback that we are looking to get
In order to improve and deliver a much more mature version of the feature in 2026.1 and after, we will be highly appreciative if feedback from you, our dear users, is shared with us. Even partial feedback would be appreciated!
We’ve made a list of questions to brainstorm around it, but any open feedback is welcome, and we’ll be happy to receive it as well. To share it, feel free to pass your feedback to your CSM, and/or directly to our product manager, who’s leading this initiative: Morli Ben David at morli.bendavid@sisense.com.
- Clarity & Naming: Is the join type interface clear and easy to understand at a glance? If you were training a new user, what aspect of the UI would you anticipate causing the most confusion?
- Default Behavior: Does the default join type (currently assumed to be Inner) meet your expectations, or should the platform suggest a different default based on the data relationship, or based on any other approach?
- Data Integrity Check: Did any of the resulting dashboards or widgets built on the new outer-joined relationship display unexpected values, duplicates, or missing data that you did not see with the previous (inner join only) model?
- Query Performance: After building the model with Outer Joins, were the resulting dashboard queries faster, slower, or comparable to what you would normally expect for a similar level of data complexity?
- Stability: Were there any unexpected crashes, freezes, or data rendering issues when modeling with or querying data sets built using Full, Left, or Right Joins?
- Maturity: Given the known issues mentioned above are going to be resolved, which other missing capabilities are must-haves? Is it mature enough to go to production already?
- User Training/Documentation: What is the one piece of information or training material that would best help you explain this new feature's value to your data team or end-users?
Missing Capabilities (Gaps): Now that you have this control, what is the next most critical data modeling control or feature you feel Sisense is missing? It can be either in the data page or in other areas impacted ,such as the dashboard/etc.
