# JOINs

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.

```sql
FROM first_table < join_type > second_table [ ON ( join_condition ) ]
```

Joins can be applied not only to tables, but also to other table-like objects. You can join:

* A table.
* A [view](https://docs.snowflake.com/en/user-guide/views-introduction.html) (materialized or non-materialized).
* A [table literal](https://docs.snowflake.com/en/sql-reference/literals-table.html).
* An expression that evaluates to the equivalent of a table (containing one or more columns and zero or more rows). For example:
  * The result set returned by a [table function](https://docs.snowflake.com/en/sql-reference/functions-table.html).
  * The result set returned by a subquery that returns a table.

{% embed url="<https://cdn.educba.com/academy/wp-content/uploads/2019/11/joins-in-mysql-1.png>" %}

{% embed url="<https://www.metabase.com/learn/images/sql-join-types/join-types.png>" %}
Types of Joins
{% endembed %}

{% embed url="<https://video.udacity-data.com/topher/2017/November/5a147485_full-outer-join-if-null/full-outer-join-if-null.png>" %}
`FULL OUTER JOIN` with `WHERE A.Key IS NULL OR B.Key IS NULL`
{% endembed %}

{% hint style="info" %}

* In cases, where the join cannot find matching rows from either table, the results from that table will have `NULL` value as their attribute.
* **Inner joins** can be specified in either the `FROM` or `WHERE` clauses.&#x20;
* Equi Join and Non-Equi Joins are types of Inner Joins.
* **Outer joins** and **Cross joins** can be specified in the `FROM` clause only.
  {% endhint %}

{% embed url="<https://stackoverflow.com/questions/2094793/when-is-a-good-situation-to-use-a-full-outer-join>" %}

{% embed url="<https://joins.spathon.com/>" %}

{% code title="Self JOIN" %}

```sql
SELECT o1.id AS o1_id,
       o1.account_id AS o1_account_id,
       o1.occurred_at AS o1_occurred_at,
       o2.id AS o2_id,
       o2.account_id AS o2_account_id,
       o2.occurred_at AS o2_occurred_at
FROM   orders o1
LEFT JOIN orders o2
ON     o1.account_id = o2.account_id
AND    o2.occurred_at > o1.occurred_at
AND    o2.occurred_at <= o1.occurred_at + INTERVAL '28 days'
ORDER BY o1.account_id, o1.occurred_at
```

{% endcode %}