Select a distinct value in multiple tables (sql)

HI,

I have a database with 3 tables TAB1, TAB2, TAB3 which have exactly the same columns, for example :

TAB1
cola, colb, colc, cold
TABB
cola, colb, colc, cold
...

Now I would like to search all distinct "colb" values, this is the query :

SELECT DISTINCT colb FROM TAB1

Works perfectly but now I would search all distinct "colb" values in my 3 tables "TAB1", "TAB2", "TAB3" :

SELECT DISTINCT colb FROM TAB1, TAB2, TAB3

And now SQL return me an error: "Column 'colb' in field list is ambiguous" After some search, I understood that was because 'colb' column exist in my 3 tables.

So how search in my 3 tables a distinct value from the same column ? I cannot use the 开发者_如何学CLEFT JOIN because I wan to search in my all 3 tables and not in one of them.

Do you have an idea ? Thanks

This single query with union will take care of distinct values for you.

select colb from tab1 union
  select colb from tab2 union
  select colb from tab3;

select colb from tab1 union 
select colb from tab2 union 
select colb from tab3

SELECT DISTINCT TAB1.colb,TAB2.colb,TAB3.colb FROM TAB1, TAB2, TAB3

SELECT Distinct cola from tab1 union

SELECT Distinct cola from tab2 union

SELECT Distinct cola from tab3

In sybase, you can use the syntax that @cherouvim posted, you can imagine the subquery as a table.

You may also use temp tables

select colb into #t1 from TAB1 
insert into #t1(colb) values(select colb from TAB2) 
insert into #t1(colb) values(select colb from TAB3) 
select distinct colb from #t1