SQL check or uniqueness in one column in multipe tables

Question

I have a 'unique' column, 'GID_New' that is in multiple tables. Is there a way to check if it's unique across all the tables in the QGIS project in SQL?

Can it be done in one SQL search without merging the tables into one and then running something like

SELECT A.GID_New, count(*), A.TableName
FROM "Water_Merged" as A
Group by A.GID_New

And then checking for a count >1

I would like to know which table the non-unique GID_New's are from as well.

The data is in a geopackage in QGIS so the code needs to work in QGIS SQL implementation.

Answer 1

You can use union all:

select gid_new, count(*) no_matches
from (
    select gid_new from table1
    union all select gid_new from table2
    union all select gid_new from table3
) t
group by gid
having count(*) > 1

If you want to know in which table duplicates exists, then one option is string concatenation. Assuming that your database uses string_agg(), that would look like:

select gid_new, count(*) no_matches, string_agg(which, ',') which_tables
from (
    select 'table1' which, gid_new from table1
    union all select 'table2', gid_new from table2
    union all select 'table3', gid_new from table3
) t
group by gid
having count(*) > 1