How to list all values from n number of table inside a same database having same schema?-CodePudding

It is possible to list all values in n number of tables having same structure? Table:

insert into t2 values(1,sam);
insert into t2 values(2,tony);
.
.
insert into tn values(1,jim);

Output:

1 max

1 sam

2 tony

1 jim

CodePudding user response：

You can use union or union all if I understand your question. For example if n=3,

select * from t1 union all t2 union all t3

The “union all” gives you all the rows including duplicates if the same row is in more than one table. If you wanted only distinct rows you would use “union” instead of “union all” The drawback is you have to specifically list the table names, and unions generally do not have high performance.

This second point is a bit extra, but my experience is that in nearly every case where one wants to do this kind of thing better addressed this way. As an alternative, if you can tolerate being flexible with the structure a little bit you could consider a partitioned table with partitions named t1, t2, t3,… so that you can do a select * from your table and you are done. This way you can use an arbitrary number of partitions without changing the query, and databases generally do a better job of optimization with it.

CodePudding user response：

Yes, but not in a good way.

You could query the system tables for all tables whose name fits a pattern (or that include a column name fitting a pattern) and then use that list to generate SQL text, which is then executed dynamically. So, yeah, the answer is Yes.

The more general answer is: If your data affect your schema, then you're putting data in your schema. Don't do that. The schema describes the data. The schema should change only when new kinds of data show up, not just new values.

In your t1, t2 ... tN example, what distinguishes the tables? Something does, else you'd use just one table. Whatever that thing is, it belongs in a column, where it becomes part of the data.

This kind of issue sometimes crops up when N is a year, to create a set of archive tables for performance reasons, so that "current" stuff isn't too big. I've never seen an example where that strategy worked. The first resort should be a bigger server, because time keeps creating more years and hardware is cheaper than labor. When that's not enough, there are engineering options like partitions and materialized views.