Usage

Definition

join lets you 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.

Major join parameters

To define the join relationship (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 key parameters, and their relation to the SQL that Looker generates, are shown here:

sql_on

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

See the sql_on parameter documentation page for more information.

foreign_key

foreign_key lets you 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, see the foreign_key parameter documentation page.

type

Most joins in Looker are LEFT JOIN for the reasons discussed in the Don’t apply business logic in joins if possible section on this page. 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 explanation, see the type parameter documentation page.

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, see the relationship parameter documentation page.

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:

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':

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:

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.

Some types of measures require symmetric aggregates

If you are not using symmetric aggregates, most measure types are excluded from joined views. For Looker to support symmetric aggregates in your Looker project, your database dialect must also support them. The following table shows which dialects support symmetric aggregates in Looker 22.6:

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 measures of type: count (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:

The reasons that Looker works this way (for dialects that do not support symmetric aggregates) are discussed in more detail in The problem of SQL fanouts Help Center article.

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:

Don’t apply business logic in joins if possible

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

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:

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:

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 on 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 a one-to-one relationship with the Explore, then join that derived table into the Explore.

This important concept is further explained in The problem of SQL fanouts Help Center article.