How do you combine two SELECT statements from two different tables
into one result (assuming bioth tables have the exact same fields)?

Like, assuming that the query SELECT * FROM Table1 gives the result

ID Name
001 Smith
002 Jones

and the query SELECT * FROM Table2 gives the result

ID Name
247 Simpson
856 Beckley

I want a SELECT ... ORDER BY ID statement that will output the
following data:

ID Name
001 Smith
002 Jones
247 Simpson
856 Beckley

How do I do this?

On Jul 7, 2:59 pm, dwightarmyofchampi... (AT) hotmail (DOT) com wrote:
select id, name
from table1
union
select id name
from table2
order by id

ZeldorBlat (zeldorblat (AT) gmail (DOT) com) writes:
Or:

select id, name
from table1
union ALL
select id name
from table2
order by id

By default UNION sorts out duplicates. With UNION ALL they are retained.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

The short answer is to use a UNION or UNION ALL.

The right answer is that tables are not files, just as columns are not
fields. If two tables have the exact same structure, then they model
the exact same entity and should be in one table. Files do not behave
that way.

This is usually the result of attribute splitting -- taking the values
of an attribute and making a table for each value.