Oracle union multiple tables with same sub-query

Question

I have an inefficiently written Oracle SQL query with multiple unions of sub-queries (about 7 or 8 tables) that differ only by the table queried in sub-query, that I am certain can be written more succinctly. Observe the code at bottom, in that the only difference between the union-ed sub-queries is the table names (in this case table_a / table_b).

SELECT
  /*+ parallel(10) */
  Col_Alpha,
  Col_Beta,
  Col_Gamma,
  Col_Delta,
  col_epsilon
FROM
  table_a
WHERE
  Col_Theta    = 'CAT'
And Col_Kappa In ('CAR','TRUCK','PLANE')
UNION
SELECT
  /*+ parallel(10) */
  Col_Alpha,
  Col_Beta,
  Col_Gamma,
  Col_Delta,
  col_epsilon
FROM
  table_b
WHERE
  Col_Theta    = 'CAT'
AND col_kappa IN ('CAR','TRUCK','PLANE')

I tried giving a list of tables after the from clause but that did not work. I also added variations of:

from 
(table_a, table_b)

And that did not work. I have tried finding a way to compress the code but I do not know enough for a successful search. I cannot use procedures with my level of access.

I expect an output similar to what I'm getting, a union of several tables with the same columns queried and same filters across all of them, but that takes around 1/7 the amount of code.

You can change the `union` to `union all`, if appropriate. Otherwise, I don't see much that can be done other than adding indexes. — Gordon Linoff, Aug 08 '19 at 17:35
@GordonLinoff, Will changing `Union` to `Union all` make any significant performance improvement? — Ankit Bajpai, Aug 09 '19 at 05:55
Possible duplicate of [Alternative for union in Oracle](https://stackoverflow.com/questions/38097671/alternative-for-union-in-oracle) — Popeye, Aug 09 '19 at 06:08
Yes, changing `Union` to `Union all` will make performance improvement and also can give different result as `Union` removes duplicate records while `Union all` do not. — Popeye, Aug 09 '19 at 06:10

score 3 · Accepted Answer · answered Aug 13 '19 at 14:58

Unfortunately you can't get away from multiple SELECT statements. The way UNION is designed to work according to Oracle: "You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS."

In other words, you need to HAVE multiple queries in order to UNION them.

But you can move the conditions to outside the UNION so that you only have to repeat those once:

SELECT * FROM(
Select col1, col2, col3 FROM table_1 UNION
Select col1, col2, col3 FROM table_2 UNION
Select col1, col2, col3 FROM table_3 UNION
Select col1, col2, col3 FROM table_4)
WHERE Col_Theta = 'CAT'
AND Col_Kappa In ('CAR','TRUCK','PLANE');

You can also throw in ORDER BY and other conditions at the end as well. This doesn't remove all of the repetition but at least makes it so that if you change the WHERE conditions you're only changing them in one place.

Oracle union multiple tables with same sub-query

1 Answers1