sql_on

LookML
Version

On this Page
Docs Menu

Go to Join Parameter List

Usage

sql_on 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
sql_on: ${view_1.id}=${view_2.id}
a sql ON clause

Definition

sql_on establishes a join relationship between a view and its explore, based on a SQL ON clause that you provide.

A view can be joined directly to an explore when using sql_on, or it can be joined through a second view that is already joined to that explore.

An example of the first case, where a view is joined directly to the explore, looks like this:

- explore: order joins: - join: customer sql_on: ${order.customer_id} = ${customer.id}

The SQL that Looker would generate from this LookML is:

SELECT … FROM order LEFT JOIN customer ON order.customer_id = customer.id

In the second case, a view is joined to an explore through an intermediate view that is already joined to that explore. An example of that would be:

- explore: order_items joins: - join: order sql_on: ${order_items.order_id} = ${order.id} - join: customer sql_on: ${order.customer_id} = ${customer.id}

Here customer cannot be joined directly to order_items. Instead it must be joined through order. The SQL that Looker would generate from this LookML is:

SELECT … FROM order_items LEFT JOIN order ON order_items.order_id = order.id LEFT JOIN customer ON order.customer_id = customer.id

To make this work properly, you can see that we simply need to use the correct view names in our field references. Since customer needs to join to a field in order, we reference ${order.customer_id}.

In some older models, you might see fields referenced with the view_name.native_column_name syntax. While this still works, using the ${view_name.looker_dimension_name} syntax instead has an important advantage: you can avoid the need for the required_joins parameter. This concept is explained in more detail below.

Conditional Joins

It’s also possible to allow user input to be used in sql_on. Although there are various reasons you may want to do this, optimizing query speed on MPP databases (such as Redshift) is a major use case, as described here.

To add user input to your join condition, you’ll first need to create a filter for their input. These types of filters are described in more detail on our Templated Filters documentation. Their basic form is:

- view: view_name fields: - filter: filter_name type: number | datetime | date | string

Once you’ve added a filter to collect the user input, you use it in your sql_on parameter like this:

{% condition view_name.filter_name %} view_name.dimension_name {% endcondition %}

For example:

- explore: order joins: - join: customer sql_on: | ${order.customer_id} = ${customer.id} AND {% condition customer.creation_date_filter %} customer.created_at {% endcondition %}

This would be interpreted to mean: set customer.created_at equal to the value from customer.creation_date_filter.

Other Liquid Variables

The _in_query, _is_selected, and _is_filtered Liquid variables can be useful when used with sql_on parameter. They can allow you to modify join relationships based on the fields that a user has selected for their query. For example:

- explore: dates joins: - join: dynamic_order_counts sql_on: | ${dynamic_order_counts.period} = {% if dates.reporting_date._in_query %} ${dates.date_string} {% elsif dates.reporting_week._in_query %} ${dates.week_string} {% else %} ${dates.month_string} {% endif %}

Examples

Join the view named customer to the explore named order by matching up the customer_id dimension from order with the id dimension from customer:

- explore: order joins: - join: customer sql_on: ${order.customer_id} = ${customer.id}

Join the view named customer to the explore named order_items through the view called order. Match up the customer_id dimension from order with the id dimension from customer. Match up the order_id dimension from order_items with the id dimension from order. This would be specified as follows:

- explore: order_items joins: - join: order sql_on: ${order_items.order_id} = ${order.id} - join: customer sql_on: ${order.customer_id} = ${customer.id}

Join the views named order and inventory_item to the explore named order_items. Match up the inventory_id dimension from order_items with the id dimension from inventory_item. Match up the order_id dimension from order_items with the id dimension from order. This would be specified as follows:

- explore: order_items joins: - join: order sql_on: ${order_items.order_id} = ${order.id} - join: inventory_item sql_on: ${order_items.inventory_id} = ${inventory_item.id}

Things to Know

Use required_joins When ${view_name.looker_dimension_name} Syntax Can’t Be Used

When you reference fields in sql_on using the ${view_name.looker_dimension_name} syntax, you do not need to worry about using required_joins.

However, some older models still use the view_name.native_column_name syntax. There are also some cases when you cannot use the ${view_name.looker_dimension_name} syntax, such as when you want to apply custom SQL.

In these situations, you may need to use required_joins. They are discussed in more detail in the required_joins documentation.

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