JOINs

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

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 (materialized or non-materialized).

• A table literal.

• 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.

  • The result set returned by a subquery that returns a table.

Types of Joins

FULL OUTER JOIN with WHERE A.Key IS NULL OR B.Key IS NULL

• 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.

• Equi Join and Non-Equi Joins are types of Inner Joins.

• Outer joins and Cross joins can be specified in the FROM clause only.

When is a good situation to use a full outer join?Stack Overflow

Visual JOINjoins.spathon.com

Self JOIN

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