SQL concepts for joins

Just as in SQL, a join in LookML is used to combine rows from two or more tables, based on a related column between them.

In LookML an Explore — as defined by the explore LookML parameter — is used to define how a user can query the data. An Explore consists of at least one view or a set of views joined together. The main view in the Explore is always included in the query. The joined views are normally only included if they are needed to satisfy the query.

A LookML view corresponds to a SQL table (or another element that has the structure of a table) in the database, or to a derived table . The view defines what fields or columns are available in the database, and how they should be accessed.

The following example is a definition for the orders Explore.

 explore: orders {
  join: users {
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
} 

The orders view, which is the main view in the Explore, is being joined to the users view with a SQL LEFT OUTER JOIN , as indicated by the type: left_outer LookML parameter. The SQL ON clause, which is defined by the sql_on LookML parameter , does not use table_alias.column but instead refers to ${view_name.field_name} . This is so that if the table name or the column name changes in the database, that change only has to be made in one place.

The relationship parameter is important. Joins can cause fanout problems where rows are duplicated. By specifying that many orders will join to only one user, Looker recognizes that fanouts won't occur from this join, so special handling is not needed. However, one_to_many relationships can trigger a fanout.

The automatic generation of views and Explores defaults to a left outer join. In the previous example, however, it is very likely that every order will have exactly one user, so the join in this example can be an inner join.

To view the generated SQL of an Explore, you can run the Explore in the UI and then select the SQLtab in the Datapanel.

For example, if you open the OrdersExplore, which was defined previously, and then select the User IDand Countfields, the generated SQL will look like the following:

 SELECT
    `user_id` AS `orders.user_id`,
    COUNT(*) AS `orders.count`
FROM
    `thelook`.`orders` AS `orders`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500 

In this example, the users table is not referenced at all. It is only brought in if it is needed.

If you remove the User IDdimension and add in the IDdimension from the Usersview, the SQL will look like the following:

 SELECT
    `users`.`id` AS `users.id`,
    COUNT(*) AS `orders.count`
FROM
    `thelook`.`orders` AS `orders`
    INNER JOIN `thelook`.`users` AS `users` ON `orders`.`user_id` = `users`.`id`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500 

In this case, since there is a selection from the Usersview, the join is included.

The following example shows LookML in the orders Explore file, which was defined previously, and adds a join to the order_items view:

 explore: orders {
  join: users {
    type: inner
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
  join: order_items {
    type: inner
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }
} 

Now if you open the OrdersExplore in the UI, you will see the Order Itemsview. If you select the Total Sale Pricemeasure from the Order Itemsview along with the Countfrom Ordersand IDfrom Users, Looker generates the following SQL:

 SELECT
    `users`.`id` AS `users.id`,
    COUNT(DISTINCT orders.id ) AS `orders.count`,
    COALESCE(SUM(`order_items`.`sale_price`), 0) AS `order_items.total_sale_price`
FROM
    `thelook`.`orders` AS `orders`
    INNER JOIN `thelook`.`users` AS `users` ON `orders`.`user_id` = `users`.`id`
    INNER JOIN `thelook`.`order_items` AS `order_items` ON `orders`.`id` = `order_items`.`order_id`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500 

In this example, COUNT(*) AS orders.count became COUNT(DISTINCT orders.id ) AS orders.count . Looker detected that there was a possible fanout situation and automatically added the SQL DISTINCT keyword to the SQL COUNT function.