How to union tables without sql

i want to union all two tables on custom question, but just find joins operation.

Hi @philip
That depends on the database type:
https://www.metabase.com/docs/latest/users-guide/join.html

I don’t see anything about unions in that article. Am I missing something? Unions should map column names to each other and join two datasets longwise. The article seems to be about joins where you map a columns values together for joining widewise.

For reference, I’m on Postgres which supports unions and I see no way to do a union through Metabase beside sql.

1 Like

It's a bit more complicated, but you can use FULL OUTER JOIN instead.
The idea is, to combine the tables with a full outer join on a key that never machtes (e.g. by creating a custom column having only "1" as the value for the first table and "-1" for the second table).
You will then get all of the rows, although in different columns, that you can recombine using custom columns.
Hope that helps! :smiley:

Hi, I'm trying to add a new custom column to implement your suggestions, but I can't find the right function to add +1 or -1 values in the new column.
Can anyone explain what I'm missing?
Grazie
gmn

sorry, 1 and -1 was just an example of different values, but turns out to be difficult to implement.
you can use "concat("table1","")" instead, as in the picture.
just make sure to give the other column a different value (like "table2")

Don't do it like that. Metabase has changed a lot since the original question in 2022.

Create a query using SQL with a UNION. Save it as a model (or as a question, then convert).
You can then use the model as the basis of your questions without having to do a nasty join mess every time you create a new question.

That would be a better solution, although i am not sure if it covers all cases.
Like: If you also need to filter the data. Can you filter the data in the native sql query from the "outside" model? If yes, how can i do that?? It would be really great if it is possible to hand-over filters from a model into a native sql query, but i don't know how.

I don't think you can. In practice, I tidy up the data in the database so don't need to hack around like this.
If you cached the model, you'd have equivalent performance. Depends how frequently you'd want it updated though.

It's a nasty workaround, of course! But it is a workaround for not having a union in the editor. AFAIK we cannot do the same, using native sql queries, because then, the user doesn't have the required filter.
Fitlering is crucial in many cases. (Maybe this is possible, but i don't know how...)
Anyway, thx for your support!