join_type

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

join_type should be avoided as of release 3.10

Use relationship instead

Definition

join_type allows you to describe a join as having a 1-to-1 relationship.

Join relationships that are not 1-to-1 can create inaccurate results when aggregate functions are used. Since Looker measures are in fact aggregate functions, only type: count measures (as COUNT DISTINCT) are brought from joined views into the explore. Consider this example:

- explore: order joins: - 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 type: count measures from customer. Looker works this way so that repeated rows, which can occur in many-to-1 and 1-to-many joins, do not inflate values in functions such as SUM. This topic is discussed in more detail in this blog post.

Order to customer is a many-to-1 relationship. If it was a 1-to-1 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 1-to-1 relationship

- explore: person joins: - join: dna sql_on: ${person.dna_id} = ${dna.id} join_type: one_to_one

Common Challenges

join_type Should Not Be Used Unless The Join Is Actually 1-to-1

Many users are tempted to use join_type: one_to_one, even if the join is many-to-1 or 1-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-1 or 1-to-many joins, causing double counting and similar errors. This blog post 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 joins: - 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.

Still have questions?
Go to Discourse - or - Email Support
Top