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 ;;
}