How do Relationships differ from Joins in Business Intelligence tools?

In a business intelligence tool, after you import multiple tables - you will combine different tables together in one of the following ways:

1. Set up relationships between tables.
2. Setup joins between tables.

Relationships between tables:

BI tools determine to join type at the time of report creation. Think of a relationship as a contract between two tables. When you are building a visualization e.g. bar chart/pivot grid, the BI tool brings in data from these tables using that contract after building a query with the appropriate joins. Meaning on top of 1 such data source, different reports can be created. Each of those reports will have a separate query with different joins created over that data source.

Different BI tools have default join types differently. Mostly, Left outer join or Inner join.

• What — Relate tables for multiple dimensions of analysis. You won’t see a Venn diagram. Latest and easiest one for a user.
• Why — Dynamic, flexible, automatic. You won’t need to play over a Venn diagram and setup joins. With contextual joins e.g. in Tableau, the join type is determined based on the combination of measures and dimensions in the viz, and their source tables.
• How — Define relationships on matching column between tables as one to one / one to many / many to one / many to many.

Model view in Power BI — Tables | Relationship | Columns

Joins between tables:

Joins are a more static way to combine data which every data persona loves. Joined tables are always merged into a single table — VIEW in the SQL language. If not properly set like a pro SQL user, there are chances for unmatched values, or duplicate aggregated values.

Setting up join in Tableau.

• What — Join is used to combine rows from 2 or more tables based on a related column. You can also correspond join with relational algebra SET language to understand better.
• Why — Understand better what data the visualizations should show up. Good to do this for scenarios that require a single table of data, such as extract filters and aggregation.
• How — Select tables and setup join clauses mostly in a Venn diagram icons.

Why not both? YES.

In BI tools like Power BI, Tableau, Qlik you can still specify joins between tables of a data source and get the benefit of both relationship and joins.

Set join via Merge feature in Power BI

Tableau — Joined single table again can be related with any other table(s)