sql_on

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 SQL ON clause that you provide.

For LookML, the order of conditions in sql_on does not matter. So sql_on: ${order.user_id} = ${user.id} ;; and sql_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 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 {
  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 {
  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 in the Use required_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 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 in the Conditions 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 our Templated 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 your sql_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: set customer.created_at equal to the value from customer.creation_date_filter .

Using _in_query , _is_selected , and _is_filtered 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 {
  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 {
  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 {
  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_items 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 {
  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 on the required_joins parameter documentation page.