sql_table_name (for joins)

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

sql_table_name can also be used as part of a view, described on the sql_table_name (for views) parameter documentation page.

sql_table_name can also be used as part of an Explore, described on the sql_table_name (for Explores) parameter documentation page.

Usage

explore: view_name {
  join: view_name {
    sql_table_name: table_name ;;
  }
}

Hierarchy

Model File

explore

join

sql_table_name

Default Value

None

Accepts

A table name in your database

Special Rules

The table must be accessible within the database connection
If the table is in a different schema, the schema must be specified
If joining across databases, the database and schema must be specified and the other database must be on the same machine and be in the same SQL dialect

Definition

Typically the view on which a join is based defines the table to be queried.

You can use sql_table_name to set the table in your database to be queried by a join. This is unusual, but it can be useful when multiple tables have the same schema; it allows the same view to be used to describe all of them. sql_table_name lets you specify which tables should be used by the view that you are joining. In such situations, consider using extends or refinements.

To reference a table from a different schema, you can use schema_name.table_name. To reference a table from a different database. you can use database_name.schema_name.table_name. However, joins across databases require that the databases be on the same machine and be in the same SQL dialect.

If you don’t explicitly specify the schema, Looker uses the default that you have set. The default is configured in the Admin section of Looker under Connections.

Examples

Make the customers join rely on the customer_1 table from the default schema:

explore: orders { join: customers { sql_table_name: customer_1 ;; sql_on: ${order.customer_id} = ${customer.id} ;; } }

Make the customers Explore rely on the customer_1 table from the analytics schema:

explore: orders { join: customers { sql_table_name: analytics.customer_1 ;; sql_on: ${order.customer_id} = ${customer.id} ;; } }

Common challenges

Tables referenced by `sql_table_name` must be accessible from the current connection

sql_table_name will be used with a join that is, in turn, part of a model. The model will have a database connection defined in it. Any table you reference in sql_table_name must be accessible within that database connection.

Things to know

To name a `join` differently than the underlying table, apply `sql_table_name` at the view level

Using sql_table_name at the join level, as described on this page, is uncommon. It should only be used when the same view can describe multiple tables.

The more typical method is to use sql_table_name at the view level to name a view differently than the underlying table name.

Instead of this:

Model File

explore: orders { join: customers { sql_table_name: customer_1 ;; sql_on: ${order.customer_id} = ${customer.id} ;; } }}

do this:

Model File

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

View File

view: customers { sql_table_name: customer_1 ;; }