# Joins

Join one or more datasets together using SQL joins. Supported join types include INNER, LEFT, RIGHT, FULL OUTER, and CROSS.

## Parameters

| Name        | Type        | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | Is Optional |
| ----------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------- |
| join\_dicts | join\_dicts | List of 'join\_dict' dictionaries which specify how to perform each join. Each Dictionary will have the following values 1. join\_type: Literal\['LEFT', 'RIGHT', 'INNER', 'FULL OUTER', 'CROSS'] - Type of Join to Perform 2. table\_a: str dataset.FQTN (only needed if not first 'join\_dict' in list) - Specfies the table/fqtn to join in the `FROM ...` clause - This would be the `source_table` by default if the first dictionary 3. table\_b: str ( dataset.FQTN) - Specifies the table/fqtn to join in the `<join_type> JOIN ...` clause 4. join\_on: Dict\[str, str] - Dictionary for which columns to join on between table A and table B - Key value pairs determine the ON part of the clause, like the string below ON `<table_A_col_name_dict_key> = table_B_col_name_dict_value>` - join\_on dict can contain mutiple values Example: `python internet_sales = rasgo.get.dataset(74) customer = rasgo.get.dataset(55) product = rasgo.get.dataset(75) ds = product.join(join_dicts=[ { 'table_b': internet_sales.fqtn, 'join_type':'LEFT', 'join_prefix_b':'product', 'join_on':{'PRODUCTKEY':'PRODUCTKEY'} }, { 'table_a':internet_sales.fqtn, 'table_b':customer.fqtn, 'join_on':{'CUSTOMERKEY':'CUSTOMERKEY', 'DUEDATE': 'BIRTHDATE'}, 'join_type':'INNER', 'join_prefix_b':'sales' } ]) print(ds.sql)` Will produce the following SQL: `sql SELECT ... FROM ADVENTUREWORKS.PUBLIC.DIMPRODUCT LEFT JOIN ADVENTUREWORKS.PUBLIC.FACTINTERNETSALES ON DIMPRODUCT.PRODUCTKEY = FACTINTERNETSALES.PRODUCTKEY INNER JOIN ADVENTUREWORKS.PUBLIC.DIMCUSTOMER ON FACTINTERNETSALES.CUSTOMERKEY = DIMCUSTOMER.CUSTOMERKEY AND FACTINTERNETSALES.DUEDATE = DIMCUSTOMER.BIRTHDATE` |             |

## Example

```python
internet_sales = rasgo.get.dataset(74)
customer = rasgo.get.dataset(55)
product = rasgo.get.dataset(75)

ds = product.joins(join_dicts=[
      {
      'table_b': internet_sales.fqtn,
      'join_type':'LEFT',
      'join_prefix_b':'product',
      'join_on':{'PRODUCTKEY':'PRODUCTKEY'}
      },
      {
      'table_a':internet_sales.fqtn,
      'table_b':customer.fqtn,
      'join_on':{'CUSTOMERKEY':'CUSTOMERKEY', 'DUEDATE': 'BIRTHDATE'},
      'join_type':'INNER',
      'join_prefix_b':'sales'
      },
      {
      'table_a':internet_sales.fqtn,
      'table_b':customer.fqtn,
      'join_type':'CROSS'
      }
  ])
ds.preview()
```

## Source Code

{% embed url="<https://github.com/rasgointelligence/RasgoTransforms/blob/main/rasgotransforms/rasgotransforms/transforms/joins/joins.sql>" %}