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.

*Keep it to basic SQL?* Show us [what you have tried](http://www.whathaveyoutried.com). — Kermit, Oct 25 '12 at 18:42
@njk The only working solution I have uses a view that is the union of the three tables. I'd like to avoid the need to create a view. The basic SQL comment is to stop people from writing stored procedures or anything before noticing vectorwise doesn't support them. — John Humphreys, Oct 25 '12 at 18:45

score 8 · Accepted Answer · answered Oct 25 '12 at 18:48

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.

score 1 · Answer 2 · answered Oct 25 '12 at 18:48

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.)

Select from union of same columns from three tables

2 Answers2