Merging/Dissolving multiple layers in QGIS Virtual Layer

Question

I have several polygon layers that I would like to merge and dissolve within a virtual layer in QGIS. So that any changes made to the individual layers will automatically update in the merged/dissolved Virtual Layer. I want to dissolve the entire layers, not based on any fields.

I’ve used ST_Union to do this with a single layer, as below:

SELECT ST_Union(geometry)
FROM "Important Wetlands"

What is the correct syntax to add multiple layers to this Virtual Layer?

The layers are in a GeoPackage.

Answer 1

Here are two ways of doing it:

1. fetch all geometries then union them

SELECT st_union(sub.geometry) 
FROM (
   SELECT geometry FROM layerA
   UNION ALL
   SELECT geometry FROM layerB
)sub

2. Union each layer independently then union again

SELECT st_union(sub.geom) 
FROM (
   SELECT st_union(geometry) as geom FROM layerA
   UNION ALL
   SELECT st_union(geometry) as geom FROM layerB
)sub

Answer 2

Another solution is to use the st_union() function for several times

SELECT
    st_union(st_union(g1.geometry, g2.geometry))
FROM
    "grid_test" AS g1,
    "grid_test2" AS g2

Input:

Output: