Performing a union all
A UNION ALL
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. With this variation of UNION
all rows, including duplicates, are retained in the final worksheet.
Difficulty
Objective
You want to combine the rows of two tables that contain weather observations for different
years. Additionally, if there are duplicate rows between the two tables, you want to retain
both copies. You know how to do this in SQL by performing a UNION ALL
with
the two tables, and you would like to obtain the same results using 1010data.
SQL solution
SELECT * FROM hourly_weather_90 UNION ALL SELECT * FROM hourly_weather_91;
1010data Macro Language solution
<base table="pub.demo.weather.hourly90"/> <merge table2="pub.demo.weather.hourly91" type="all"/>
With SQL, UNION ALL
will combine all rows from both tables and will retain
duplicates. To obtain the same worksheet with 1010data, you can specify
type="union"
in the <merge>
operation. However, the
default behaviour for 1010data is to retain all rows, so not specifying a type will produce
the same results.
Further reading
If you would like to learn more about the functions and operations discussed in this recipe, click on the links below: