> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Joins in the SQL API

> The best practice is to use views to specify explicit join paths for SQL API queries.

## Views

The best practice is to use [views][ref-views] to specify explicit [join
paths][ref-join-paths] for SQL API queries.

While BI tools would see a view as a table, in fact, no materialization is performed
until Cube is queried through the SQL API. At the query time, Cube will try to maximize
member pushdown, so only required parts of the view are materialized at query time.

Cube also solves fan and chasm traps based on the dimensions selected in the query, so
if measure aggregation types are properly set up, you will see correct results in BI tools
even though cubes and views are seen just as tables.

Consider the following data model:

```yaml title="YAML" theme={"dark"}
views:
  - name: orders_users
    cubes:
      - join_path: orders
        includes:
          - status
          - count

      - join_path: orders.users
        prefix: true
        includes: 
          - id
          - city
          - state
```

```javascript title="JavaScript" theme={"dark"}
view(`orders_users`, {
  cubes: [
    {
      join_path: orders,
      includes: ['status', 'count']
    },
    {
      join_path: orders.users,
      prefix: true,
      includes: ['id', 'city', 'state']
    }
  ]
})
```

With this data model, here's how you can get orders count by users' cities:

```sql theme={"dark"}
cube=> SELECT MEASURE(count) AS count, users_city FROM orders_users GROUP BY 2;

 count |   users_city
-------+---------------
  1416 | Los Angeles
  1412 | Seattle
  1365 | Mountain View
  1263 | New York
  1220 | Austin
  1164 | Chicago
  1101 | San Francisco
  1059 | Palo Alto
(8 rows)
```

## `CROSS JOIN` and `__cubeJoinField`

The SQL API also supports joins through the `__cubeJoinField` virtual column, which
allows to control how specific cubes are joined. This is considered an advanced
functionality, and views should be used where possible. Join can also be done through
`CROSS JOIN`.

Usage of `CROSS JOIN` or `__cubeJoinField` instructs Cube to perform join as it's defined
in a data model while using provided cubes as [join hints][ref-join-hints].

For example, the following query joins the `orders` and `products` tables under
the hood on `orders.product_id = products.id`, exactly the same way as the
REST (JSON) API query does:

```sql theme={"dark"}
cube=> SELECT p.name, SUM(o.count) FROM orders o LEFT JOIN products p ON o.__cubeJoinField = p.__cubeJoinField GROUP BY 1 LIMIT 5;
           name           | SUM(o.count)
--------------------------+--------------
 Tasty Plastic Mouse      |          121
 Intelligent Cotton Ball  |          119
 Ergonomic Steel Tuna     |          116
 Intelligent Rubber Pants |          116
 Generic Wooden Gloves    |          116
(5 rows)
```

Or through `CROSS JOIN`:

```sql theme={"dark"}
cube=> SELECT p.name, sum(o.count) FROM orders o CROSS JOIN products p GROUP BY 1 LIMIT 5;
           name           | SUM(o.count)
--------------------------+--------------
 Tasty Plastic Mouse      |          121
 Intelligent Cotton Ball  |          119
 Ergonomic Steel Tuna     |          116
 Intelligent Rubber Pants |          116
 Generic Wooden Gloves    |          116
(5 rows)
```

In the resulting query plan, you won't see any joins as you can't see those for
REST (JSON) API queries either:

```sql theme={"dark"}
cube=> EXPLAIN SELECT p.name, sum(o.count) FROM orders o LEFT JOIN products p ON o.__cubeJoinField = p.__cubeJoinField GROUP BY 1 LIMIT 5;
   plan_type   |         plan
---------------+-----------------------
 logical_plan  | CubeScan: request={  +
               |   "measures": [      +
               |     "orders.count"   +
               |   ],                 +
               |   "dimensions": [    +
               |     "products.name"  +
               |   ],                 +
               |   "segments": [],    +
               |   "limit": 5         +
               | }
 physical_plan | CubeScanExecutionPlan+
               |
(2 rows)
```

This feature allows you to join cubes even joined transitively only.

In most of the BI tools you'd use `__cubeJoinField` to define joins between cube
tables. In tools that allow defining custom SQL datasets, you can use joined
tables as a dataset SQL. For example:

```sql theme={"dark"}
SELECT o.count as count, p.name as product_name, p.description as product_description
FROM orders o
CROSS JOIN products p;
```

Please note we use aliasing to avoid name clashing between cube members in a
resulting data set. In this case, wrapped SQL will be properly processed by
Cube, pushing down all operations to Cube query:

```sql theme={"dark"}
cube=> SELECT product_name, SUM(count) FROM (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o CROSS
  JOIN products p
) joined
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
       product_name       | SUM(joined.count)
--------------------------+-------------------
 Tasty Plastic Mouse      |               121
 Intelligent Cotton Ball  |               119
 Ergonomic Steel Tuna     |               116
 Intelligent Rubber Pants |               116
 Generic Wooden Gloves    |               116
(5 rows)
```

We can see this by introspecting the `EXPLAIN` plan for this query:

```sql theme={"dark"}
cube=> EXPLAIN SELECT product_name, SUM(count) FROM (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o
  CROSS JOIN products p
) joined
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
   plan_type   |         plan
---------------+-----------------------
 logical_plan  | CubeScan: request={  +
               |   "measures": [      +
               |     "orders.count"   +
               |   ],                 +
               |   "dimensions": [    +
               |     "products.name"  +
               |   ],                 +
               |   "segments": [],    +
               |   "order": [         +
               |     [                +
               |       "orders.count",+
               |       "desc"         +
               |     ]                +
               |   ],                 +
               |   "limit": 5         +
               | }
 physical_plan | CubeScanExecutionPlan+
               |
(2 rows)
```

Please note that, even if `product_description` is in the inner selection, it isn't
evaluated in the final query as it isn't used in any way.

## Joining views on a shared dimension

When you join two views on a dimension that resolves to the **same underlying
cube member** and group by that dimension, Cube doesn't perform a row-level
join. Instead it merges them into a single
[multi-fact query][ref-multi-fact-views]: each view becomes its own
aggregating subquery and the results are stitched together on the shared key,
so measures from both views are combined without fan-out.

```sql theme={"dark"}
SELECT
  o.name,
  MEASURE(o.total_amount),
  MEASURE(r.total_refund)
FROM orders_view o
LEFT JOIN returns_view r ON r.name = o.name
GROUP BY 1
```

The `JOIN` type (`INNER`, `LEFT`, `RIGHT`, `FULL`) controls which keys are
kept. This only applies to grouped queries whose `GROUP BY` is the join key. See
[multi-fact views][ref-multi-fact-views] for the full explanation.

## Multi-fact queries across views

Joins between views in the SQL API are for multi-fact queries: measures from
different views, each aggregated on a dimension the views share, combined into
one result. They are not a way to navigate the data model. A dimension you want
next to a measure belongs in the view through its [join path][ref-join-paths],
not in a join between views.

Write a multi-fact query as one common table expression (CTE) per view,
aggregated by the shared dimension, and join the CTEs on it:

```sql theme={"dark"}
WITH o AS (
  SELECT city, MEASURE(total_amount) AS total_amount
  FROM orders_view
  WHERE status = 'completed'
  GROUP BY 1
),
r AS (
  SELECT city, MEASURE(total_refund) AS total_refund
  FROM returns_view
  GROUP BY 1
)
SELECT o.city, o.total_amount, r.total_refund
FROM o
FULL JOIN r ON r.city = o.city
```

Each CTE is a [regular query][ref-queries-regular] against one view, so every
measure is aggregated before the join and nothing fans out. [Joining views on a
shared dimension](#joining-views-on-a-shared-dimension) produces the same
subquery-then-join query from a single `SELECT`.

* Put filters inside the CTE they apply to, as `WHERE status = 'completed'`
  above. There they are part of that view's query. A `WHERE` on the outer
  query is applied after the join.
* The `JOIN` type works as in SQL: `FULL JOIN` keeps every city from both
  views, `LEFT JOIN` keeps the cities of the left view, `INNER JOIN` keeps the
  cities present in both.
* Aggregating the joined result again, for example `SUM(total_amount)` by a
  coarser dimension, is only correct for additive measures. For non-additive
  measures, group each CTE at the grain you need.

With [query pushdown][ref-queries-wpd] enabled, which is the default, Cube
generates a single SQL statement for the data source, with one aggregating
subquery per view. With pushdown disabled via
[`CUBESQL_SQL_PUSH_DOWN`][ref-env-push-down], Cube runs each CTE as its own
query and joins the results as [post-processing][ref-queries-wpp].

[ref-views]: https://docs.cube.dev/docs/data-modeling/views

[ref-join-paths]: https://docs.cube.dev/docs/data-modeling/joins.md#join-paths

[ref-join-hints]: https://docs.cube.dev/docs/data-modeling/joins.md#join-hints

[ref-multi-fact-views]: https://docs.cube.dev/docs/data-modeling/multi-fact-views.md

[ref-queries-regular]: https://docs.cube.dev/reference/core-data-apis/queries.md#regular-query

[ref-queries-wpp]: https://docs.cube.dev/reference/core-data-apis/queries.md#query-with-post-processing

[ref-queries-wpd]: https://docs.cube.dev/reference/core-data-apis/queries.md#query-with-pushdown

[ref-env-push-down]: https://docs.cube.dev/reference/configuration/environment-variables.md#cubesql_sql_push_down


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.