Definition
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:
explore: order {
join: customer {
sql_on: ${order.customer_id} = ${customer.id} ;;
}
}
-
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 measures of type: count 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 Community post.
order to 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.
Examples
Declare the join of person to dna as having a one-to-one relationship:
explore: person {
join: dna {
sql_on: ${person.dna_id} = ${dna.id} ;;
relationship: one_to_one
}
}
-
Common challenges
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 Community post The problem of SQL fanouts explains this issue in more detail.
Things to know
You can expose measures from joined views without using join_type
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:
explore: order {
join: customer {
sql_on: ${order.customer_id} = ${customer.id} ;;
}
}
-
In the customer view we have a measure like this:
measure: max_age {
type: max
sql: ${age} ;;
}
-
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:
measure: max_customer_age {
type: max
sql: ${customer.age} ;;
}
-
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.