Select from union of same columns from three tables

Question

I have three tables, A, B, and C. They all hold different data, but have some columns in common.

If A, B, and C all have columns C1 and C2 then how can I look up a specific C2 value using a C1 value that could be in any of the 3 tables?

Basically, I want to do a simple look-up but have it act on the union of the 3 tables - and I'd prefer to not use a view to achieve this.

Note that this is an Ingres Vectorwise database.

Answer 1

You do this by doing a union of the tables in the from clause:

select c2
from ((select c1, c2 from a) union all
      (select c1, c2 from b) union all
      (select c1, c2 from c)
     ) t
where c1 = <your value>

I've used union all for performance reasons. If you are concerned about duplicate values, either use union or add a distinct in the select.

This is standard SQL and should work in any database.

Answer 2

I don't know what you mean by " a specific C2 value using a C1 value ", but, whatever your query would be for the view, repeat that query and union the results,

SELECT *
FROM A
WHERE C2 = ?
UNION ALL
SELECT *
FROM B
WHERE C2 = ?
UNION ALL
SELECT *
FROM C
WHERE C2 = ?

(The view is a standard SQL feature, and will make any query you write easier.)