MashZone NextGen 10.2 | Appendix | Legacy Presto components | MashZone NextGen Analytics | RAQL Queries | Joins and Other Multiple-Dataset Operations | Join: Merge Columns for Dataset Rows
 
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;

Copyright © 2013-2018 | Software AG, Darmstadt, Germany and/or Software AG USA, Inc., Reston, VA, USA, and/or its subsidiaries and/or its affiliates and/or their licensors.
Innovation Release