Performing a union

A UNION in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. During this union, all duplicates are removed.

Difficulty

Objective

You want to combine the rows of two tables that contain weather observations for different years. You know how to do this in SQL by performing a UNION of the two tables, and you would like to obtain the same results using 1010data.

SQL solution

SELECT * 
FROM hourly_weather_90
UNION
SELECT * 
FROM hourly_weather_91;

1010data Macro Language solution

<base table="pub.demo.weather.hourly90"/>
<merge table2="pub.demo.weather.hourly91" type="union"/>

With SQL, the default behaviour of UNION is to remove all duplicates from the resultant worksheet. The default behaviour for 1010data is to retain all rows, including duplicates. In order to obtain a worksheet with no duplicates, you must specify type="union" in the <merge> operation.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<merge>