Join: Merge Columns for Dataset Rows

A Join clause works just like joins for relational databases. It finds rows in each dataset that match a specified condition and create result rows that join the columns from each dataset.

RAQL supports inner joins, natural joins, cross joins and outer joins. Inner joins are most common and thus are the default.

The Select clause identifies columns to include in the result from both datasets. Column names are prefixed by the dataset they belong to. The From clause identifies one dataset, followed by the Join clause that identifies the second dataset.

This example uses the on condition syntax to determine which rows to join, but RAQL also supports the using column-list syntax. If the column name for the condition is identical in both datasets, you could also use:

select employees.first_name, employees.last_name,
employees.job_id, jobs.job_title
from employees
join jobs
using job_id

RAQL supports also LEFT, RIGHT and FULL outer joins in standard SQL syntax.

Examples for Outer joins

LEFT OUTER JOIN

select *
FROM OpenAuction
LEFT OUTER JOIN ClosedAuction
ON OpenAuction.itemID<=ClosedAuction.itemID;

RIGHT OUTER JOIN

SELECT *
FROM OpenAuction
RIGHT OUTER JOIN ClosedAuction
ON OpenAuction.itemID<=ClosedAuction.itemID;

FULL OUTER JOIN

SELECT *
FROM OpenAuction
FULL OUTER JOIN ClosedAuction
ON OpenAuction.itemID<=ClosedAuction.itemID;