# 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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dailyjournal.gitbook.io/notes/languages/sql/joins.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.