join_type should be avoided as of Looker 3.10; use
join_type lets you describe a
join as having a one-to-one relationship.
Join relationships that are not one-to-one can create inaccurate results when aggregate functions are used. Since Looker measures are in fact aggregate functions, only measures of
type: count (as
COUNT DISTINCT) are brought from joined views into the Explore. Consider this example:
In this situation, when a user works with the
order Explore, they will see all the dimensions and measures from
order. They will also see all the dimensions from
customer, but will only see the
type: count measures from
customer. Looker works this way so that repeated rows, which can occur in many-to-one and one-to-many joins, do not inflate values in functions such as
SUM. This topic is discussed in more detail in The problem of SQL fanouts Help Center article.
customer is a many-to-one relationship. If it was a one-to-one relationship instead, you could use
join_type to force the non-count measures from
customer to be included.
Declare the join of
dna as having a one-to-one relationship:
join_type should not be used unless the join is actually one-to-one
Many users are tempted to use
join_type: one_to_one, even if the join is many-to-one or one-to-many, solely to expose all measures in an Explore. We strongly suggest you do not do this, because — in certain situations — measures will not give you accurate results. Rows are often repeated in many-to-one or one-to-many joins, causing double counting and similar errors. The Help Center article The problem of SQL fanouts explains this issue in more detail.
Things to know
You can expose measures from joined views without using
In some cases, you might carefully consider the situation and decide that it is safe to add a non-count measure from a joined view to an Explore. There is a method to accomplish this. Suppose we have an Explore like this:
customer view we have a measure like this:
In this situation, it is probably safe to expose the maximum customer age as a measure in the
order Explore. To achieve this, you would add a new measure to the
order view, like this:
This method does not protect you from errors in and of itself. You must carefully consider if the interaction between the measure and your join will create inaccurate results.