join

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

join is a child of
joins is a child of
explore
explore: view_a
joins:
join: view_b   the name of an existing view
...
1st
Tab
2nd
Tab
3rd
Tab
sql_on: ${view_a.field} = ${view_b.field}
join requires one of the
following parameters:
sql_on
foreign_key

Definition

join enables you to define the join relationship between an explore and a view, so that you can combine data from multiple views. You can join in as many views as you like for any given explore.

Recall that each view is associated with a table in your database, or a derived table that you have defined in Looker. Similarly, since an explore is associated with a view, it is also connected to a table of some kind.

The table associated with the explore is placed into the FROM clause of the SQL that Looker generates. Tables that are associated with joined views are placed into the JOIN clause of the SQL that Looker generates.

The 4 Major Join Parameters

To define the join relationship (i.e. the SQL ON clause) between an explore and a view you will need use join in combination with other parameters.

It is required that you use either sql_on or foreign_key in order to establish the SQL ON clause.

Though not always explicitly required, you will also need to make sure that type and relationship are used appropriately. Often times they can be excluded because their default values are appropriate for your use case.

These 4 parameters, and their relation to the SQL that Looker generates, is shown below:

sql_on

sql_on enables you to establish a join relationship by writing the SQL ON clause directly. Because it can accomplish the same joins that foreign_key can, but is easier to read and understand, it is generally preferred.

For a complete understanding, please read the sql_on documentation.

foreign_key

foreign_key enables you to establish a join relationship using the primary key of the joined view, and connecting it with a dimension in the explore. This pattern is very common in database design, and foreign_key is an elegant way to express the join in these cases.

For a complete understanding, please read the foreign_key documentation.

type

Most joins in Looker are LEFT JOIN for the reasons discussed below. Therefore, if you do not explicitly add a type, Looker will assume you want a LEFT JOIN. However, if you do need another type of join for some reason, you can do so with type.

For a complete understanding, please read the type documentation.

relationship

In the diagram above relationship doesn’t have a straightforward impact on the SQL that Looker generates, but it is critical to the proper functioning of Looker. If you do not explicitly add a relationship Looker will assume that it is many-to-one, which is to say that many rows in the explore can have one row in the joined view. Not all joins have this type of relationship and need to be declared properly.

For a complete understanding, please read the relationship documentation.

Examples

Join the view named customer to the explore named order where the join relationship is: FROM order LEFT JOIN customer ON order.customer_id = customer.id:

- explore: order joins: - join: customer foreign_key: customer_id relationship: many_to_one # Could be excluded since many_to_one is the default type: left_outer # Could be excluded since left_outer is the default

Join the view named address to the explore named person where the join relationship is: FROM person LEFT JOIN address ON person.id = address.person_id AND address.type = 'permanent':

- explore: person joins: - join: address sql_on: ${person.id} = ${address.person_id} AND ${address.type} = 'permanent' relationship: one_to_many type: left_outer # Could be excluded since left_outer is the default

Join the view named member to the explore named event where the join relationship is: FROM event INNER JOIN member ON member.id = event.member_id:

- explore: event joins: - join: member sql_on: ${event.member_id} = ${member.id} relationship: many_to_one # Could be excluded since many_to_one is the default type: inner

Common Challenges

join Must Use View Names And Not Underlying Table Names

The join parameter only takes a view name, not the table name associated with that view. Often times the view name and table name are identical, which may lead to the false conclusion that table names can be used.

If Not Using Symmetric Aggregates, Most Measure Types Are Excluded From Joined Views

This section only applies to database dialects that do not support symmetric aggregates.

Looker’s ability to provide symmetric aggregates depends on the database dialect’s functionality. The following list shows which dialects support symmetric aggregates in the most recent Looker release:

Without symmetric aggregates, join relationships that are not 1:1 can create inaccurate results in aggregate functions. Since Looker measures are aggregate functions, only type: count measures (as COUNT DISTINCT) are brought from joined views into the explore. If you do have a 1:1 join relationship, you can use the relationship parameter to force the other measure types to be included, like this:

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

The reasons that Looker works this way (for dialects that do not support symmetric aggregates) is discussed in more detail in this blog post.

Things to Know

You Can Join The Same Table More Than Once Using from

In cases where a single table contains different types of entities, it is possible to join a view to an explore more than once. To do so you’ll need to use the from parameter. Suppose you had an order explore and needed to join a person view to it twice; once for the customer and once for the customer service representative. You might do something like this:

- explore: order joins: - join: customer from: person sql_on: ${order.customer_id} = ${customer.id} - join: representative from: person sql_on: ${order.representative_id} = ${representative.id}

Don’t Apply Business Logic In Joins If Possible

The standard Looker approach to joining is to use a LEFT JOIN whenever possible. You should think about a different approach if you find yourself doing something along these lines:

- explore: member_event from: event always_join: [member] joins: - join: member sql_on: ${member_event.member_id} = ${member.id} type: inner

In this example we’ve created an explore that only looks at events associated with known members. However, the preferred way to execute this in Looker would be to use a LEFT JOIN to get event data and member data stuck together simply, like this:

- explore: event joins: - join: member sql_on: ${event.member_id} = ${member.id}

Then you would create a dimension that you could set to yes or no, if you only wanted to look at member events, like this:

- dimension: is_member_event type: yesno sql: ${member.id} IS NOT NULL

This approach is preferable because it gives users the flexibility to look at all events, or only member events, as they desire. You have not forced them to only look at member events via the join.

If Not Using Symmetric Aggregates, Avoid Joins that Cause Fanouts

This section only applies to database dialects that do not support symmetric aggregates. See this page to determine if your dialect supports symmetric aggregates.

If your database dialect does not support symmetric aggregates you should avoid joins that result in a fanout. In other words, joins that have a 1-to-many relationship between the explore and view should generally be avoided. Instead, aggregate the data from the view in a derived table in order to establish a 1-to-1 relationship with the explore, then join that derived table into the explore.

If that sounds confusing, this important concept is broken down and explained in this blog post.

Druid Dialect Does Not Support Joins

The Druid database dialect does not support SQL joins. Using the join parameter with the Druid dialect will result in an error.

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