type

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

This page refers to the type parameter that is part of a join

type can also be used as part of a dimension, described here

type can also be used as part of a measure, described here

type can also be used as part of a dashboard filter, described here

type can also be used as part of a dashboard element, described here

Usage

type is a child of
join is a child of
joins is a child of
explore
explore: view_name_1
joins:
join: view_name_2
...
1st
Tab
2nd
Tab
3rd
Tab
type: left_outer
a join type:
left_outer
full_outer
inner
cross

Definition

type enables you to describe the type of join that you want to perform. The four possible values for type are:

left_outer (the default value)

The most common join type in Looker is left_outer (the reason for its frequent use is described below). You may know it from SQL as a LEFT JOIN. When using this type, all rows from the explore are included and data from the joined view is brought in, if available.



full_outer

The full_outer join type — which you may know from SQL as a FULL OUTER JOIN — includes all rows from the explore and joined view, even if there is no corresponding data from the other view. Please note that full outer joins are not supported in MySQL.



inner

The inner join type — which you may know from SQL as a JOIN or INNER JOIN — only includes rows that have matching records in both the explore and joined view.



cross

The cross join type — which you may know from SQL as a CROSS JOIN — is rarely used. It is sometimes helpful for zero-filling or matrix generation. It creates a row for every combination of the explore and joined view. The concept is difficult to show on a Venn diagram, and may be better understood by looking at the table example:

Previous to release 4.0 one common use case for cross joins was to join a date table into your data so that a row was shown for every date, even if there were no results. Looker’s dimension-fill functionality can now typically fill that need.

Examples

Use a LEFT JOIN to add dna data to your people data, if dna information is available:

- explore: person joins: - join: dna sql_on: ${person.dna_id} = ${dna.id} type: left_outer # Could be excluded since left_outer is the default

Use a FULL OUTER JOIN to add user data to your order data, even if the user hasn’t placed an order:

- explore: order joins: - join: user sql_on: ${order.user_id} = ${user.id} type: full_outer

Use an INNER JOIN to add user data to your event data, and limit the results to just the events that were generated by a user:

- explore: event joins: - join: user sql_on: ${event.user_id} = ${user.id} type: inner

Use a CROSS JOIN to generate all possible combinations of owner and property:

- explore: property joins: - join: owner sql_on: 1 = 1 # sql_on or foreign_key is not required type: cross # when using type: cross

Things to Know

left_outer Join Types Are Usually Preferred

When learning and using SQL, many people focus primarily on JOIN, which is technically known as an “inner join”. For this reason, one might assume that type: inner_join should be used in the majority of cases. However, Looker typically works best with type: left_outer, which is like using a LEFT JOIN in SQL.

The reason for this is that the view associated with an explore (as opposed to the views joined into an explore) is usually the “primary” data. For example, you might have an explore based on event information. Some of the events might be generated by your system and other events might be generated by your users. If you join user into event using a regular JOIN, you will only be able to see events that were created by a user, and you’ll lose visibility to system events. By using a LEFT JOIN instead, you’ll be able to see all the events, whether or not there is a user associated with them.

You May Want To Use always_join If Restricting Rows Is Important

When Looker generates SQL for a query, it attempts to create the cleanest SQL possible, and will only use the joins that are necessary for the fields a user selects. When you use type to define something other than a LEFT JOIN, you might always want a certain join (or set of joins) to be part of the query, to make sure the resulting rows include your join restrictions. You can achieve this by using the always_join parameter.

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