Thread: SQL query...

if i have 5 tables and all of it has column X and i wanted to get unique
values in column X for all tables..

can i do it in one query?


TIA

>if i have 5 tables and all of it has column X and i wanted to get unique
>values in column X for all tables..

>can i do it in one query?

sure. this is one way to do this:
select distinct foo.X from (
  select X from table1
  union
  select X from table2
  ....
 ) as foo;

if the values are unique within every table, then you might want to omit the
surrounding select.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> if i have 5 tables and all of it has column X and i wanted to get unique
> values in column X for all tables..
> can i do it in one query?

Yes, you want to use UNION:

SELECT foo FROM table1
UNION
SELECT foo FROM table2
UNION
SELECT foo FROM table3
UNION
SELECT foo FROM table4
UNION
SELECT foo FROM table5;

This will by default remove all duplicate entries. Use UNION ALL to
see all entries, even duplicated ones. Note that UNION (and its
companions INTERSECT and EXPECT) all require that the tables have
the same number of columns, and that each column is of a compatible
type.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302190926

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+U5SSvJuQZxSWSsgRApdKAJ4oV4G1nq9RVoNbNX4qJ8hy/fI4eQCfTqcE
r3xW8sPVsIdwotPxoZHUHGk=
=P8cL
-----END PGP SIGNATURE-----

On Wed, Feb 19, 2003 at 14:38:53 +0100,
  Mario Weilguni <mweilguni@sime.com> wrote:
> >if i have 5 tables and all of it has column X and i wanted to get unique
> >values in column X for all tables..
>
> >can i do it in one query?
>
> sure. this is one way to do this:
> select distinct foo.X from (
>   select X from table1
>   union
>   select X from table2
>   ....
>  ) as foo;
>
> if the values are unique within every table, then you might want to omit the
> surrounding select.

Union's will already only return unique values. So you don't need the outer
select distinct. You might get better performance by using union alls
with select distinct, since only one sort will be needed. For the case
where you know they are all unique, you can use union alls without the
select distinct.