Usage

explore: view_name_1 {  join: view_name_2 {    sql_on:${view_name_1.id}=${view_name_2.id};;  }}

Definition

sql_on establishes a join relationship between a view and its Explore, based on a SQLON clause that you provide.

For LookML, the order of conditions insql_on doesn't matter. Sosql_on: ${order.user_id} = ${user.id} ;; andsql_on: ${user.id} = ${order.user_id} ;; are equivalent. You can put the conditions in either order, unless the order is relevant to your database's SQL dialect.

A view can be joined directly to an Explore when usingsql_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 {  join: customer {    sql_on: ${order.customer_id} = ${customer.id} ;;  }}

The SQL that Looker would generate from this LookML is:

SELECT...FROMorderLEFTJOINcustomerONorder.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 {  join: order {    sql_on: ${order_items.order_id} = ${order.id} ;;  }  join: customer {    sql_on: ${order.customer_id} = ${customer.id} ;;  }}

Herecustomer can't be joined directly toorder_items. Instead it must be joined throughorder. The SQL that Looker would generate from this LookML is:

SELECT...FROMorder_itemsLEFTJOINorderONorder_items.order_id=order.idLEFTJOINcustomerONorder.customer_id=customer.id

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

In some older models, you might see fields referenced with theview_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 therequired_joins parameter. This concept is explained in more detail in theUserequired_joins when${view_name.looker_dimension_name} syntax can't be used section on this page.

Conditional joins

It's also possible to allow user input to be used insql_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 in theConditions in Join Clauses Community post.

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 ourTemplated Filters page. Their basic form is:

view: view_name {  filter: filter_name {    type: number | datetime | date | string  }}

Once you've added a filter to collect the user input, you use it in yoursql_on parameter like this:

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

For example:

explore: order {  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: setcustomer.created_at equal to the value fromcustomer.creation_date_filter.

Using_in_query,_is_selected, and_is_filtered Liquid variables

The_in_query,_is_selected, and_is_filteredLiquid variables can be useful when used withsql_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 {  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 namedcustomer to the Explore namedorder by matching up thecustomer_id dimension fromorder with theid dimension fromcustomer:

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

Join the view namedcustomer to the Explore namedorder_items through the view calledorder. Match up thecustomer_id dimension fromorder with theid dimension fromcustomer. Match up theorder_id dimension fromorder_items with theid dimension fromorder. This would be specified as follows:

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

Join the views namedorder andinventory_items to the Explore namedorder_items. Match up theinventory_id dimension fromorder_items with theid dimension frominventory_item. Match up theorder_id dimension fromorder_items with theid dimension fromorder. This would be specified as follows:

explore: order_items {  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

Userequired_joins when${view_name.looker_dimension_name} syntax can't be used

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

However, some older models still use theview_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 userequired_joins. They are discussed in more detail on therequired_joins parameter documentation page.