Oracle SQL - using 'union all' within a 'with as'-statement and many tables

Question

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.


Show source
| oracle   | sql   | union-all   2017-08-08 23:08 1 Answers

Answers to Oracle SQL - using 'union all' within a 'with as'-statement and many tables ( 1 )

  1. 2017-08-08 23:08

    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.

Leave a reply to - Oracle SQL - using 'union all' within a 'with as'-statement and many tables

◀ Go back