User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
日本語
sql (for Joins)

sql should be avoided as of Looker 3.12.

Use a combination of sql_on, foreign_key, type, and/or sql_table_name instead, as described below.

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 this documentation page.

sql can also be used as part of a derived table, as described on this documentation page.

Definition

sql allows you to 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 ;; }

Top