JOINs
Last updated
Was this helpful?
Last updated
Was this helpful?
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).
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.
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
FULL OUTER JOIN
with WHERE A.Key IS NULL OR B.Key IS NULL