User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
join

Usage

explore: explore_name {
  join: view_name {}
}

Hierarchy

join

Default Value

None

Accepts

The name of an existing view

Special Rules

  • This parameter accepts a view name, not the name of the view’s underlying table (although they are often identical)
  • If your dialect does not support symmetric_aggregates most measure types are excluded from joined views
  • You can join the same view more than once using from

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 the sql_on or the foreign_key parameter in order to establish the SQL ON clause.

You will also need to make sure that you are using appropriate join types and relationships, although the type and relationship parameters are not always explicitly required. If their default values of type: left_outer and relationship: many_to_one are appropriate for your use case, then these parameters can be excluded.

These four parameters, and their relation to the SQL that Looker generates, are 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 parameter 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 parameter 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 parameter 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 joins with other relationships need to be declared properly.

For a complete understanding, please read the relationship parameter 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 { 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 { 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 { 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 the view name and table name are identical, which may lead to the false conclusion that table names can be used.

If You Are 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 whether the database dialect supports them. The following list shows which dialects support symmetric aggregates in the most recent Looker release:

Without symmetric aggregates, join relationships that are not one-to-one 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 one-to-one join relationship, you can use the relationship parameter to force the other measure types to be included, like this:

explore: person { 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) are discussed in more detail in the blog post on Aggregate Functions Gone Bad.

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 { 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] 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 { 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 either at all events, or at only member events, as they desire. You have not forced them to look only 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 the discussion of symmetric aggregates in the Common Challenges section of 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 one-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 one-to-one 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 on Aggregate Functions Gone Bad.

Apache Druid Does Not Support Joins

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

Top