1

In my new business job I encountered a database structure where tables are numbered like 

table_1, 
table_2, 
table_3, 
...

(up to 20)

All of that tables have the same structure like Id, Name, Surname. Now I got a request to show the Id's where the name is "John" in all tables. I struggle with the amount of tables I need to select and came up with this very long query:

With query_name (Id, Name, Surname) AS (
    Select ID, Name, Surname FROM table_1
    UNION ALL
    Select ID, Name, Surname FROM table_2
    UNION ALL 
    Select ID, Name, Surname FROM table_3
    UNION ALL
    --...
    --all the other tables
) SELECT * FROM query_name WHERE Name = "John"

Is there a way to shrink my query or is there another solution for this problem? I thought of a loop but I were not able to create one within a "with as"-statement.

Thanks.

Craiten
  • 109
  • 1
  • 2
  • 12
  • If you need to do this often, wouldn't creating a view help ? – Sudipta Mondal Aug 08 '17 at 21:09
  • *materialized view that is updated x often. – xQbert Aug 08 '17 at 21:12
  • Are the table names similar? Or how we can catch your tables names for calling them in a loop/function? – mehmet sahin Aug 08 '17 at 21:16
  • What is your concern with this query - performance (execution time) or just the length of the code (possible maintenance issues)? Performance-wise the UNION ALL way should be the fastest - especially if the predicate (Name = 'John') is distributed to the base tables (you can see the explain plan to confirm), and if you have indexes on the Name column in each table. In that case only the rows with Name = 'John' should be read from each table, and UNION ALL doesn't do any further processing, it just spits out the rows. –  Aug 08 '17 at 21:55
  • If your concern is ease of maintaining the code, you could write a view (NOT a materialized view!) just for the UNION ALL. Then you can write queries against the view - with different predicates perhaps - and the parser will rewrite your queries by replacing the view name with the UNION ALL code as if you had written it that way. No need for **materialized** view, since UNION ALL is a very inexpensive operation - there is nothing time-consuming or complicated to justify materializing the view. –  Aug 08 '17 at 21:57

1 Answers1

2

Short answer: No, you will have to use the UNION statements like you did.

Long answer: I'm not sure what your company has 20 identically structured tables for, but I suspect the design is poor and you should really look at improving their database layout.

You can write a script that will programmatically UNION them all, but the amount of effort will more than likely outdo the effort required to hard code it.

If you need to use this result set frequently or with multiple queries, look into creating a view based off of it and then selecting from that.

SandPiper
  • 2,816
  • 5
  • 30
  • 52