sql (for joins)

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

sql can also be used as part of a field, as described on the sql (for fields) parameter documentation page.

sql can also be used as part of a derived table, as described on the sql (for derived tables) parameter documentation page.

Definition

sql lets you write the raw SQL that will join a view to an Explore. It is currently used almost exclusively to execute RIGHT JOIN, which is not supported by the type parameter. Otherwise, there are better parameters to use to execute joins.

Previous to Looker 3.10, sql was the only method available to execute a join that was not a LEFT JOIN. For example, before 3.10 you may have created an INNER JOIN between an event Explore and a member view like this:

explore: event {
  join: member {
    sql:
      INNER JOIN member
      ON member.id = event.member_id ;;
  }
}

As of Looker 3.10 the join should be executed with sql_on or foreign_key instead, while the type of join should be defined using type as follows:

# Option 1 Using sql_on
explore: event {
  join: member {
    sql_on: ${member.id} = ${event.member_id} ;;
    type: inner
  }
}

# Option 2 Using foreign_key
explore: event {
  join: member {
    foreign_key: member_id
    type: inner
  }
}

The second common use case for sql was to join tables from two different database, which is called a "Federated Join". For example, you might have written this LookML:

# Model File
explore: view_1 {
  join: view_2 {
    sql:
      LEFT JOIN other_db.other_schema.view_2 AS view_2
      ON view_1.something = view_2.something ;;
  }
}

This can now be achieved by using sql_table_name instead, as follows:

# Model File
explore: view_1 {
  join: view_2 {
    sql_on: ${view_1.something} = ${view_2.something} ;;
    type: left_outer
  }
}

# View File
view: view_2 {
  sql_table_name: other_db.other_schema.view_2 ;;
}