CODEWITHSUNDEEP
sqldatabasepostgresqlschemaunion-all
Rajaprabhu Aravindasamy
Rajaprabhu Aravindasamy

Reputation: 67217

Retrieving data from multiple schemas in a single database

I'm having more than one schemas inside a single database.

Say,

Similarly, I'm having a table called Tbl which is present in the both schemas given above.
Now i had retrieved datum from the tables SCH001.tbl and SCH002.tbl by using the union all key word like this below,

select * From SCH001.tbl union all select * From SCH002.tbl

The above query is working with out any issue, But my boss is asking me to use some other technique to achieve the same(with out union all / Union). So what i am trying to ask is, Is there any remedy available for Union all to get the same result in my case ? If not then, kindly tell the reason that why Union all cannot be replaced in this case.?

Upvotes: 0

Views: 3207

Answers (1)

Christopher Bonitz
Christopher Bonitz

Reputation: 856

If your boss thinks its ugly to use this union all (all) the time, why not make a view of it? just reference all the fields in both union parts, and present this as combined view (i say this cause its generally bad to use * in views)

i do not know if you could index such a view, however its properly worth checking out, i believe you have to use one schema only when doing indexed views, but i'm unsure of this (however if possible, this would be a better solution than "just" the union)

(new to that area) :EDIT: you cannot (in Microsoft sql) make the materialized views on a union, and/or thereby on different schemas, (they are also schema bound)

i would personally use the view anyway, its just a nice way to give a programmatic interface to your data, that you can replace one day!. :EDIT:

but fact is... its still a union under the hood

Upvotes: 2

Related Questions